Problem with report using DLookup

[Ron Weaver]

I am trying to put together an expense report based on two tables: 'Accounts'
and 'Payments'. By using DLookup, I am able to show desired records on the
report. The problem is I can't control the report dates. What do I need to do
to accomplish this?
These are all unbound text boxes so the report has no record source.
Here is one record on the report:
To show the payment date:=DLookUp("[PaymentDate]","[Payments]","[AccountID]
= 22")
To show the payment
amount:=DLookUp("[PaymentAmount]","[Payments]","[AccountID] = 22").
Any help would be appreciated.
Thanks

 

[Duane Hookom]

If a report is "based on two tables" and you are "able to show desired
records" then how can your report have "no record souce"?

Where are your "report dates" coming from?

 

[Ron Weaver]

Duane

I used DLookup in the text box record sources to pull accounts from
'AccountID' in the Accounts table, and payment amounts from 'PaymentAmount'
in the Payments table. No record source was used for the report. As for the
report dates, they are in the Payments table under 'PaymentDate'.

If a report is "based on two tables" and you are "able to show desired
records" then how can your report have "no record souce"?

Where are your "report dates" coming from?

--
Duane Hookom
Microsoft Access MVP

I am trying to put together an expense report based on two tables: 'Accounts'
and 'Payments'. By using DLookup, I am able to show desired records on the
report. The problem is I can't control the report dates. What do I need to do
to accomplish this?
These are all unbound text boxes so the report has no record source.
Here is one record on the report:
To show the payment date:=DLookUp("[PaymentDate]","[Payments]","[AccountID]
= 22")
To show the payment
amount:=DLookUp("[PaymentAmount]","[Payments]","[AccountID] = 22").
Any help would be appreciated.
Thanks

 

[Duane Hookom]

I am guessing you want to filter the date range in table "Payments"? If so,
you could probably create a query that has a criteria based on controls on a
form. Then replace the Payments table with the name of the query.

I have even used a date criteria table with two date fields and one record.
I fed the appropriate date values into the fields and used the table in a
query to filter between the two dates.

In this report, I would probably use subreports to return the proper values
or possibly bind the report to a record source.
--
Duane Hookom
Microsoft Access MVP

Duane

I used DLookup in the text box record sources to pull accounts from
'AccountID' in the Accounts table, and payment amounts from 'PaymentAmount'
in the Payments table. No record source was used for the report. As for the
report dates, they are in the Payments table under 'PaymentDate'.

If a report is "based on two tables" and you are "able to show desired
records" then how can your report have "no record souce"?

Where are your "report dates" coming from?

--
Duane Hookom
Microsoft Access MVP

I am trying to put together an expense report based on two tables: 'Accounts'
and 'Payments'. By using DLookup, I am able to show desired records on the
report. The problem is I can't control the report dates. What do I need to do
to accomplish this?
These are all unbound text boxes so the report has no record source.
Here is one record on the report:
To show the payment date:=DLookUp("[PaymentDate]","[Payments]","[AccountID]
= 22")
To show the payment
amount:=DLookUp("[PaymentAmount]","[Payments]","[AccountID] = 22").
Any help would be appreciated.
Thanks

 

[Ron Weaver]

Duane,
I am probably making this more difficult than it really is. Thanks for the
information.

I am guessing you want to filter the date range in table "Payments"? If so,
you could probably create a query that has a criteria based on controls on a
form. Then replace the Payments table with the name of the query.

I have even used a date criteria table with two date fields and one record.
I fed the appropriate date values into the fields and used the table in a
query to filter between the two dates.

In this report, I would probably use subreports to return the proper values
or possibly bind the report to a record source.
--
Duane Hookom
Microsoft Access MVP

Duane

I used DLookup in the text box record sources to pull accounts from
'AccountID' in the Accounts table, and payment amounts from 'PaymentAmount'
in the Payments table. No record source was used for the report. As for the
report dates, they are in the Payments table under 'PaymentDate'.

If a report is "based on two tables" and you are "able to show desired
records" then how can your report have "no record souce"?

Where are your "report dates" coming from?

--
Duane Hookom
Microsoft Access MVP


:

I am trying to put together an expense report based on two tables: 'Accounts'
and 'Payments'. By using DLookup, I am able to show desired records on the
report. The problem is I can't control the report dates. What do I need to do
to accomplish this?
These are all unbound text boxes so the report has no record source.
Here is one record on the report:
To show the payment date:=DLookUp("[PaymentDate]","[Payments]","[AccountID]
= 22")
To show the payment
amount:=DLookUp("[PaymentAmount]","[Payments]","[AccountID] = 22").
Any help would be appreciated.
Thanks