Dlookup in Detail

  • Thread starter Thread starter briank
  • Start date Start date
B

briank

Hello. I am getting an Err when I put this dlookup in the Detail section of
my report. I am trying to lookup ID from tblMain where the year equals a
hard coded figure and the last name in the detail section matches the Last
Name in tblMain.

=DLookUp("[ID]","[tblMain]","[FiscalYearInd]='2010'" and "[Last
Name]=Reports![Plan Review Summary Rpt![ProviderName]")
 
Extraneous quotes marks
Missing square bracket after the name of the report.
PLUS I'm not sure you can reference the control on a report that way. It
should work but you may need to change

=DLookUp("[ID]","[tblMain]","[FiscalYearInd]='2010' and
[Last Name]=Reports![Plan Review Summary Rpt]![ProviderName]")

Also is FiscalYearInd a text field or a number field. If it is a number field
then you need to remove the apostrophes around 2010.

PLUS I'm not sure you can reference the control on a report that way. It
should work but you may need to change the DLookup a bit more to specifically
refer to the current value of the ProviderName.

=DLookUp("[ID]","[tblMain]","[FiscalYearInd]='2010' and
[Last Name]=""" & Reports![Plan Review Summary Rpt]![ProviderName]& """")

I wonder why you cannot do this in the record source by adding tblMain to the
query and linking it to the relevant table that has Provider name in it. Plus
applying the criteria to limit the year to 2010.


John Spencer
Access MVP 2002-2005, 2007-2010
The Hilltop Institute
University of Maryland Baltimore County
 
Since I have posted my earlier message I have realized that some of the
lookup command was flawed and needed to be tweaked. However I am still
having issues with my new code. Can you take alook at this?

=DLookUp("[PROV_UHC_CAT_NUM]","[tblMasterProviderDataHistory_Data]","[FiscalYearInd]='2010' and [tblMasterProviderDataHistory_Data]![UHC ID] =[UHC ID]")

Marshall Barton said:
briank said:
Hello. I am getting an Err when I put this dlookup in the Detail section of
my report. I am trying to lookup ID from tblMain where the year equals a
hard coded figure and the last name in the detail section matches the Last
Name in tblMain.

=DLookUp("[ID]","[tblMain]","[FiscalYearInd]='2010'" and "[Last
Name]=Reports![Plan Review Summary Rpt![ProviderName]")
Click to expand...


That's missing a ]
Click to expand...
 
Thanks for the input John. I am still getting an error message in my report
text box. To answer your question, the record source is a stored procedure
that I was asked not to alter so I am trying a work around by using the
dlookup command. Also criteria of 2010 is a text field. As a troubleshoot, I
ran the dlookup with just the "[FiscalYearInd]=2010" as a criteria and
although it pulled up data it appears to be the wrong data. I suspect that
the second criteria will pull this together. Any thoughts on what I am doing
incorrectly on this 2nd criteria?

John Spencer said:
Extraneous quotes marks
Missing square bracket after the name of the report.
PLUS I'm not sure you can reference the control on a report that way. It
should work but you may need to change

=DLookUp("[ID]","[tblMain]","[FiscalYearInd]='2010' and
[Last Name]=Reports![Plan Review Summary Rpt]![ProviderName]")

Also is FiscalYearInd a text field or a number field. If it is a number field
then you need to remove the apostrophes around 2010.

PLUS I'm not sure you can reference the control on a report that way. It
should work but you may need to change the DLookup a bit more to specifically
refer to the current value of the ProviderName.

=DLookUp("[ID]","[tblMain]","[FiscalYearInd]='2010' and
[Last Name]=""" & Reports![Plan Review Summary Rpt]![ProviderName]& """")

I wonder why you cannot do this in the record source by adding tblMain to the
query and linking it to the relevant table that has Provider name in it. Plus
applying the criteria to limit the year to 2010.


John Spencer
Access MVP 2002-2005, 2007-2010
The Hilltop Institute
University of Maryland Baltimore County
Hello. I am getting an Err when I put this dlookup in the Detail section of
my report. I am trying to lookup ID from tblMain where the year equals a
hard coded figure and the last name in the detail section matches the Last
Name in tblMain.

=DLookUp("[ID]","[tblMain]","[FiscalYearInd]='2010'" and "[Last
Name]=Reports![Plan Review Summary Rpt![ProviderName]")
Click to expand...
.
Click to expand...
 
For a test try doing the DLookup in the VBA immediate window.
Try building it up a bit at a time to determine the cause.
?DLookup("ID","tblMain")
?DLookup("ID","tblMain","[FiscalYearInd]='2010'")
?DLookUp("[ID]","[tblMain]","[Last Name]='Artichoke'")
?DLookUp("[ID]","[tblMain]","[FiscalYearInd]='2010' and [Last Name]='Artichoke'")

If those work without an error then you can start looking at the expression in
the control and the control itself. For instance, does the control have the
same name as a field in the record source.


John Spencer
Access MVP 2002-2005, 2007-2010
The Hilltop Institute
University of Maryland Baltimore County
 
John, I used your suggestions and managed to tweak my dlookup to work albeit
with limited parameters. So far this works:
=DLookUp("[PROV_UHC_CAT_NUM]","[tblMasterProviderDataHistory_Data]","[UHC
ID]='" & [Provider ID] & "'")
Now I would like to add the 2nd criteria [FiscalYearInd]=2010 but am having
some trouble with the correct syntax. Can you offer suggestions?

John Spencer said:
Extraneous quotes marks
Missing square bracket after the name of the report.
PLUS I'm not sure you can reference the control on a report that way. It
should work but you may need to change

=DLookUp("[ID]","[tblMain]","[FiscalYearInd]='2010' and
[Last Name]=Reports![Plan Review Summary Rpt]![ProviderName]")

Also is FiscalYearInd a text field or a number field. If it is a number field
then you need to remove the apostrophes around 2010.

PLUS I'm not sure you can reference the control on a report that way. It
should work but you may need to change the DLookup a bit more to specifically
refer to the current value of the ProviderName.

=DLookUp("[ID]","[tblMain]","[FiscalYearInd]='2010' and
[Last Name]=""" & Reports![Plan Review Summary Rpt]![ProviderName]& """")

I wonder why you cannot do this in the record source by adding tblMain to the
query and linking it to the relevant table that has Provider name in it. Plus
applying the criteria to limit the year to 2010.


John Spencer
Access MVP 2002-2005, 2007-2010
The Hilltop Institute
University of Maryland Baltimore County
Hello. I am getting an Err when I put this dlookup in the Detail section of
my report. I am trying to lookup ID from tblMain where the year equals a
hard coded figure and the last name in the detail section matches the Last
Name in tblMain.

=DLookUp("[ID]","[tblMain]","[FiscalYearInd]='2010'" and "[Last
Name]=Reports![Plan Review Summary Rpt![ProviderName]")
Click to expand...
.
Click to expand...
 
DLookUp("[PROV_UHC_CAT_NUM]","[tblMasterProviderDataHistory_Data]",
"[UHC ID]='" & [Provider ID] & "' AND FiscalYearIND = 2010")

OR if you are sure that fiscalYearIND is a text value
DLookUp("[PROV_UHC_CAT_NUM]","[tblMasterProviderDataHistory_Data]",
"[UHC ID]='" & [Provider ID] & "' AND FiscalYearIND = '2010'")

I

John Spencer
Access MVP 2002-2005, 2007-2010
The Hilltop Institute
University of Maryland Baltimore County
 
Thanks John. That was exactly what I needed to make this work.

John Spencer said:
DLookUp("[PROV_UHC_CAT_NUM]","[tblMasterProviderDataHistory_Data]",
"[UHC ID]='" & [Provider ID] & "' AND FiscalYearIND = 2010")

OR if you are sure that fiscalYearIND is a text value
DLookUp("[PROV_UHC_CAT_NUM]","[tblMasterProviderDataHistory_Data]",
"[UHC ID]='" & [Provider ID] & "' AND FiscalYearIND = '2010'")

I

John Spencer
Access MVP 2002-2005, 2007-2010
The Hilltop Institute
University of Maryland Baltimore County
John, I used your suggestions and managed to tweak my dlookup to work albeit
with limited parameters. So far this works:
=DLookUp("[PROV_UHC_CAT_NUM]","[tblMasterProviderDataHistory_Data]","[UHC
ID]='" & [Provider ID] & "'")
Now I would like to add the 2nd criteria [FiscalYearInd]=2010 but am having
some trouble with the correct syntax. Can you offer suggestions?
Click to expand...
.
Click to expand...
 
Back
Top