DLookup on Report 2

[janetlyn]

I have a report whose control source is a query involving two tables. The Primary key in the two tables is [ProjectNo]. On this report in the header is shows the [ProjectNo]. However, I would also like it to show the ProjectNo name which is in a totally different table. So I did a DLookup. I am not getting any errors, however it is not showing up on the report either. Here is my DLookup statement:

RPT-ProjectHrsSearch![Text36]=DLookUp("[ClientOrg]& ', ' & [ProjectBldg]& ', ' & [ProjectDesc]& ', ' & [CityState]","TBL-ProjectNumberTitle","[ProjectNo]= 'Report!RPT-ProjectHrsSearch![ProjectNo]'")

I also tried taking out the first part before the = sign so it started off =DLookup(...... but that did not work either. Any suggestions what could be wrong? Thanks for the help, Janet Lyn

 

[dhookom]

Use a text box with a control source of:
=DLookUp("[ClientOrg]& ', ' & [ProjectBldg]& ', ' & [ProjectDesc]& ', ' & [CityState]","TBL-ProjectNumberTitle","[ProjectNo]= " & [ProjectNo])
If ProjectNo is text:
=DLookUp("[ClientOrg]& ', ' & [ProjectBldg]& ', ' & [ProjectDesc]& ', ' & [CityState]","TBL-ProjectNumberTitle","[ProjectNo]= """ & [ProjectNo] & """")

I would just include TBL-ProjectNumberTitle in the report's record source and skip the DLookup().



Duane
MS Access MVP
Find out how to get great answers faq219-2884.

 

[mgolla]

Janet,

Is the project no a string or a numeric field?

If it is string the try this:

RPT-ProjectHrsSearch![Text36]=DLookUp("[ClientOrg]& ', ' & [ProjectBldg]& ', ' & [ProjectDesc]& ', ' & [CityState]","TBL-ProjectNumberTitle","[ProjectNo]= '" & Report!RPT-ProjectHrsSearch![ProjectNo]& "'")

If it is numeric try this:

RPT-ProjectHrsSearch![Text36]=DLookUp("[ClientOrg]& ', ' & [ProjectBldg]& ', ' & [ProjectDesc]& ', ' & [CityState]","TBL-ProjectNumberTitle","[ProjectNo]= " & Report!RPT-ProjectHrsSearch![ProjectNo])



HTH
Mike