|
| If this is your first visit, be sure to check out the FAQ by clicking the link above. You may have to register before you can post: click the register link above to proceed. To start viewing messages, select the forum that you want to visit from the selection below. |
|
|||||||
|
|
Thread Tools | Display Modes |
|
#1
April 6th, 2010, 03:02 PM
posted to microsoft.public.access.reports
|
|||
|
|||
Dlookup in Detail
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]") 
|
|
#2
April 6th, 2010, 04:15 PM
posted to microsoft.public.access.reports
|
|||
|
|||
|
Dlookup in Detail
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]
|
|
#3
April 6th, 2010, 04:41 PM
posted to microsoft.public.access.reports
|
|||
|
|||
|
Dlookup in Detail
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]")
|
|
#4
April 6th, 2010, 05:03 PM
posted to microsoft.public.access.reports
|
|||
|
|||
|
Dlookup in Detail
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] .
|
|
#5
April 6th, 2010, 05:49 PM
posted to microsoft.public.access.reports
|
|||
|
|||
|
Dlookup in Detail
See John's reply. He points out several potential problems
that you need to consider. -- Marsh MVP [MS Access] briank wrote: 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 ]
|
|
#6
April 6th, 2010, 05:51 PM
posted to microsoft.public.access.reports
|
|||
|
|||
|
Dlookup in Detail
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]") .
|
|
#7
April 6th, 2010, 06:33 PM
posted to microsoft.public.access.reports
|
|||
|
|||
|
Dlookup in Detail
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?
|
|
#8
April 6th, 2010, 07:08 PM
posted to microsoft.public.access.reports
|
|||
|
|||
|
Dlookup in Detail
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]") .
|
|
#9
April 6th, 2010, 07:32 PM
posted to microsoft.public.access.reports
|
|||
|
|||
|
Dlookup in Detail
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?
|
|
#10
April 6th, 2010, 08:19 PM
posted to microsoft.public.access.reports
|
|||
|
|||
|
Dlookup in Detail
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? .
|
| Thread Tools | |
| Display Modes | |
|
|
Linear Mode
