DLookUp in report criteria search from a table and query

[jeffschultz]

I am trying to do a DLookUp in a report, but I keep getting an error or name issue.

I can get this DLookUp to work in my form (Here is the line in the control source in my form, =DLookUp("[WageAmount]","tblWages","[WageName]=" & "[MEETrt]")

But when attempting to write it in the report I just can't make it work.

Here is the line that I am putting into the control source on the report.

=DLookUp("[WageAmount]","tblWages","[WageName]=" & "[Summer Contract Query]![MeetingRate]")

Can a DLookUp work when the criteria is also from another query or table?



These are text fields.

Any assistance would be greatly appreciated.

 

[isladogs]

You need to use text delimiters in both cases.

In your report use a variable in the filter criteria to replace the current approach

 

[jeffschultz]

Figured out the text delimiters.

Could you provide an example of how I would use a variable in the filter criteria to replace the current approach?

The WageAmount is number btw.

Thanks

 

[isladogs]

For the first item, you should now have the following using text delimiters

=DLookUp("[WageAmount]","tblWages","[WageName]='" & [MEETrt] & "'")

In order to answer your question, i need some more info:

What is the form control called?

Are you opening your report filtered to one record?
If so, what code are you using to open the report?

Why do you need to reference the table in your report?
What is the record source for your report?

 

[jeffschultz]

I was not having issues with the DLookUp on the form. The script that I was working fine. It was on the report that I could not get the DLookUp to work.

In anycase,

The form name is called Payroll_Information

Are you opening your report filtered to one record? - No

If so, what code are you using to open the report? - No code to open the report just opening the report in the All Objects Menu.

Why do you need to reference the table in your report? - I need the WageAmount to show on the report. The report is the employee contracts and we want to show the amount as it relates to positions assigned.

What is the record source for your report? - Summer Contract Query

 

[isladogs]

I was just stating what the text delimiters should look like.
If that's not what you have, it shouldn't work.

Can you post a stripped down copy of your database with the form, report together with the relevant queries/table(s).

 

[jeffschultz]

Thank you for any assistance you can provide.

 

[isladogs]

First of all, the reason your Dlookup worked using a number delimiter is because you used a lookup based on the WageAmount currency field. Using lookups at table level (which you effectively have done here) makes it very difficult to see what's going on ... and impossible to problem solve at a distance

There are several major issues with this database & I strongly recommend you stop & do a significant redesign before proceeding.
This won't be what you want to hear but it would be a mistake to ignore this advice

The fundamental issue is that your data is not normalised (please look it up if unclear what that means).
As a result you will be fighting with Access every step of the way instead of working to its strengths

There are many excellent tutorials about normalisation online & also Access learning videos on You Tube. Time spent on this area now will be repaid over & over again in time saved later

The Staff Information table is suitable for Excel but not Access
There are lots of field pairs ending in rate / wage
The many relationships with the wages table are a big mistake

Remove ALL these wage/rate fields! Yes I really do mean that!

Then link Staff Information to Position Titles table using Position Held (note you spelt Position wrongly) and Short Forms. Then link Positions to WageName in tblWages

Similarly all the expiry date fields can be reduced to just two - ExpiryDate & ExpiryType. There may be other such changes to do

As an aside I also recommend that table names & field names should never contain spaces. This will also save you a lot of time & effort in future

Use a query linking all 3 tables to get the WageAmount value for each member of staff. Use the query as your form record source & scrap all the DLookups which would be VERY slow in any case.

Once you have redesigned in this way, the report should also work with no DLookups based on the same query as record source

There are too many changes needed to make a minor modification worthwhile

Good luck with your project & feel free to come back with specific questions