Report Text box dilemma

  • Thread starter Thread starter Bob Vance
  • Start date Start date
B

Bob Vance

The dilemma I have is a text box "tbCurrent0" on my report
"rptOwnerPaymentMethodBatch" Ive created another report
"rptOwnerPaymentMethodBatchA" and everything works except this text box
below, how does it know only to work with "rptOwnerPaymentMethodBatch" .
all i have done differen with this report is change its report "name" and
some text boxes from =DLookUp("CompanyPhone","tblCompanyInfo") to
=DLookUp("CompanyPhone1","tblCompanyInfoA"),
 
On Sat, 23 Jan 2010 19:00:03 +1300, "Bob Vance" <[email protected]>
wrote:

I suspect something is wrong with qOverDueBatch. Inspect it carefully.
Does it contain references to the previous report?

-Tom.
Microsoft Access MVP
 
Tom van Stiphout said:
I suspect something is wrong with qOverDueBatch. Inspect it carefully.
Does it contain references to the previous report?

-Tom.
Microsoft Access MVP
Click to expand...
Thanks Tom Brilliant found it , will make to new queries for my report and
change "rptOwnerPaymentMethodBatch"
Regards Bob

qOverDueBatch:
SELECT qOwnerPercentAmountInPaymentMethodBatchAll.OwnerID,
Sum(qOwnerPercentAmountInPaymentMethodBatchAll.AmountSummary) AS Dues,
iif(qOwnerPercentAmountInPaymentMethodBatchAll.AmountSummary<0,3,IIf(MonthsDue([OnDate])>=3,3,IIf(MonthsDue([OnDate])>=2,2,IIf(MonthsDue([OnDate])>=1,1,0))))
AS MD
FROM qOwnerPercentAmountInPaymentMethodBatchAll
GROUP BY qOwnerPercentAmountInPaymentMethodBatchAll.OwnerID,
iif(qOwnerPercentAmountInPaymentMethodBatchAll.AmountSummary<0,3,IIf(MonthsDue([OnDate])>=3,3,IIf(MonthsDue([OnDate])>=2,2,IIf(MonthsDue([OnDate])>=1,1,0))));

qOwnerPercentAmountInPaymentMethodBatchAll:
SELECT tblInvoice.OwnerID, tblInvoice.OwnerName, tblInvoice.InvoiceDate AS
OnDate,iif(tblInvoice.ClientInvoice=true,tblInvoice.OwnerName,funGetHorse(tblInvoice.InvoiceID)
& " @ " & Format(tblInvoice.OwnerPercent,"0.0%")) AS Description,
tblInvoice.OwnerPercentAmount AS AmountSummary, tblInvoice.InvoiceID,
tblInvoice.InvoiceNo,0 AS Flag
FROM tblInvoice
WHERE tblInvoice.OwnerID=Reports!rptOwnerPaymentMethodBatch!tbOwnerID
ORDER BY tblInvoice.InvoiceDate
UNION SELECT tblAccountStatus.OwnerID, NULL, tblAccountStatus.BillDate AS
OnDate, tblAccountStatus.ModeOfPayment AS
Description,-tblAccountStatus.PaidAmount AS Credit, NULL,BillID,-1 AS Flag
FROM tblAccountStatus
WHERE tblAccountStatus.OwnerID=Reports!rptOwnerPaymentMethodBatch!tbOwnerID;
 
Bob Vance said:
Tom van Stiphout said:
I suspect something is wrong with qOverDueBatch. Inspect it carefully.
Does it contain references to the previous report?

-Tom.
Microsoft Access MVP
Click to expand...
Thanks Tom Brilliant found it , will make to new queries for my report and
change "rptOwnerPaymentMethodBatch"
Regards Bob

qOverDueBatch:
SELECT qOwnerPercentAmountInPaymentMethodBatchAll.OwnerID,
Sum(qOwnerPercentAmountInPaymentMethodBatchAll.AmountSummary) AS Dues,
iif(qOwnerPercentAmountInPaymentMethodBatchAll.AmountSummary<0,3,IIf(MonthsDue([OnDate])>=3,3,IIf(MonthsDue([OnDate])>=2,2,IIf(MonthsDue([OnDate])>=1,1,0))))
AS MD
FROM qOwnerPercentAmountInPaymentMethodBatchAll
GROUP BY qOwnerPercentAmountInPaymentMethodBatchAll.OwnerID,
iif(qOwnerPercentAmountInPaymentMethodBatchAll.AmountSummary<0,3,IIf(MonthsDue([OnDate])>=3,3,IIf(MonthsDue([OnDate])>=2,2,IIf(MonthsDue([OnDate])>=1,1,0))));

qOwnerPercentAmountInPaymentMethodBatchAll:
SELECT tblInvoice.OwnerID, tblInvoice.OwnerName, tblInvoice.InvoiceDate AS
OnDate,iif(tblInvoice.ClientInvoice=true,tblInvoice.OwnerName,funGetHorse(tblInvoice.InvoiceID)
& " @ " & Format(tblInvoice.OwnerPercent,"0.0%")) AS Description,
tblInvoice.OwnerPercentAmount AS AmountSummary, tblInvoice.InvoiceID,
tblInvoice.InvoiceNo,0 AS Flag
FROM tblInvoice
WHERE tblInvoice.OwnerID=Reports!rptOwnerPaymentMethodBatch!tbOwnerID
ORDER BY tblInvoice.InvoiceDate
UNION SELECT tblAccountStatus.OwnerID, NULL, tblAccountStatus.BillDate AS
OnDate, tblAccountStatus.ModeOfPayment AS
Description,-tblAccountStatus.PaidAmount AS Credit, NULL,BillID,-1 AS Flag
FROM tblAccountStatus
WHERE
tblAccountStatus.OwnerID=Reports!rptOwnerPaymentMethodBatch!tbOwnerID;
Click to expand...
Actually Tom, can rptOwnerPaymentMethodBatchCI be added to this code?
Regards Bob
 
Not easily, but there are some alternatives. For example you can
"publish" the value Reports!rptOwnerPaymentMethodBatch!tbOwnerID in
some (perhaps hidden) textbox that is always available, and then
change your query to reference that field.
Then add a line just before you open each report to set the value of
that textbox.

-Tom.
Microsoft Access MVP

Bob Vance said:
Tom van Stiphout said:
I suspect something is wrong with qOverDueBatch. Inspect it carefully.
Does it contain references to the previous report?

-Tom.
Microsoft Access MVP
Click to expand...
Thanks Tom Brilliant found it , will make to new queries for my report and
change "rptOwnerPaymentMethodBatch"
Regards Bob

qOverDueBatch:
SELECT qOwnerPercentAmountInPaymentMethodBatchAll.OwnerID,
Sum(qOwnerPercentAmountInPaymentMethodBatchAll.AmountSummary) AS Dues,
iif(qOwnerPercentAmountInPaymentMethodBatchAll.AmountSummary<0,3,IIf(MonthsDue([OnDate])>=3,3,IIf(MonthsDue([OnDate])>=2,2,IIf(MonthsDue([OnDate])>=1,1,0))))
AS MD
FROM qOwnerPercentAmountInPaymentMethodBatchAll
GROUP BY qOwnerPercentAmountInPaymentMethodBatchAll.OwnerID,
iif(qOwnerPercentAmountInPaymentMethodBatchAll.AmountSummary<0,3,IIf(MonthsDue([OnDate])>=3,3,IIf(MonthsDue([OnDate])>=2,2,IIf(MonthsDue([OnDate])>=1,1,0))));

qOwnerPercentAmountInPaymentMethodBatchAll:
SELECT tblInvoice.OwnerID, tblInvoice.OwnerName, tblInvoice.InvoiceDate AS
OnDate,iif(tblInvoice.ClientInvoice=true,tblInvoice.OwnerName,funGetHorse(tblInvoice.InvoiceID)
& " @ " & Format(tblInvoice.OwnerPercent,"0.0%")) AS Description,
tblInvoice.OwnerPercentAmount AS AmountSummary, tblInvoice.InvoiceID,
tblInvoice.InvoiceNo,0 AS Flag
FROM tblInvoice
WHERE tblInvoice.OwnerID=Reports!rptOwnerPaymentMethodBatch!tbOwnerID
ORDER BY tblInvoice.InvoiceDate
UNION SELECT tblAccountStatus.OwnerID, NULL, tblAccountStatus.BillDate AS
OnDate, tblAccountStatus.ModeOfPayment AS
Description,-tblAccountStatus.PaidAmount AS Credit, NULL,BillID,-1 AS Flag
FROM tblAccountStatus
WHERE
tblAccountStatus.OwnerID=Reports!rptOwnerPaymentMethodBatch!tbOwnerID;
Click to expand...
Actually Tom, can rptOwnerPaymentMethodBatchCI be added to this code?
Regards Bob
Click to expand...
 
Back
Top