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

briank wrote:
> 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]")

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" wrote:

> briank wrote:
>
> >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]")
>
>
> That's missing a ]
>
> --
> Marsh
> MVP [MS Access]
> .
>

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" wrote:

> 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
>
> briank wrote:
> > 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]")
> .
>

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

briank wrote:
> 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, 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" wrote:

> 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
>
> briank wrote:
> > 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]")
> .
>

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

briank wrote:
> 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?

Thanks John. That was exactly what I needed to make this work.

"John Spencer" wrote:

> 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
>
> briank wrote:
> > 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?
> .
>