Report Text box dilemma

[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"),

 

[Tom van Stiphout]

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

 

[Bob Vance]

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

-Tom.
Microsoft Access MVP

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]

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

-Tom.
Microsoft Access MVP

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;

Actually Tom, can rptOwnerPaymentMethodBatchCI be added to this code?
Regards Bob

 

[Tom van Stiphout]

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

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

-Tom.
Microsoft Access MVP

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;

Actually Tom, can rptOwnerPaymentMethodBatchCI be added to this code?
Regards Bob