Dlookup and my ignorance

[jsnelms1957]

I cannot seem to get the syntax correct. Please help.
I have a report based upon a query and sorted by Location. I need to
lookup a field in a table not part of the query.

Dlookup in tblLocs, field is Location , criteria1 = text value
listed in the reportcontrol txt89, and the report control Estno. The
value i need is a numerical value.

Hope this helps.
Shannon

 

[tina]

why don't you show us what you've tried, and we'll help you troubleshoot.

hth

 

[Al Campagna]

Shannon,
I would be best if you had indicated what you had tried for a Dlookup...
I'm not sure where you indicate what field your your going to criteria in order to
determine Location, and... I'll have to assume the report name, and that EstNo is on the
Main report... not a subreport.

=DLookup("[Location]", "tblLocs", "[SomeField] = Reports!rptYourReport!EstNo")
--
hth
Al Campagna . Candia Computer Consulting . Candia, NH USA
Microsoft Access MVP
http://home.comcast.net/~cccsolutions

"Find a job that you love, and you'll never work a day in your life."

 

[jsnelms1957]

why don't you show us what you've tried, and we'll help you troubleshoot.

hth








- Show quoted text -

Listed below is what I initially tried without success.
I thought that since the report has the data for criteria 1 that I
should try using the report control, which is txt89. The 2nd criteria
is also in the report control as txt196. Hope that is more clear.
Shannon




=DLookUp(" [tblLocs]![LocationSize] ","tblLocs","[tblLocs]![Locations]
= [Location]")

 

[tina]

assuming that the expression is set as the ControlSource of a textbox
control in the report, try

=DLookUp("[LocationSize]","tblLocs","[Locations] = '" & [txt89] & "'")

the above assumes that the [Locations] field is a Text data type. if it's
Number data type, then change the syntax to

=DLookUp("[LocationSize]","tblLocs","[Locations] = " & [txt89])

the above goes all on one line in the ControlSource property, regardless of
line-wrap in this post. as for the 2nd criteria you mentioned, you don't say
what field it's applied to in tblLocs, and don't show it in the your posted
expression, so i didn't address it.

hth

why don't you show us what you've tried, and we'll help you troubleshoot.

hth








- Show quoted text -

Listed below is what I initially tried without success.
I thought that since the report has the data for criteria 1 that I
should try using the report control, which is txt89. The 2nd criteria
is also in the report control as txt196. Hope that is more clear.
Shannon




=DLookUp(" [tblLocs]![LocationSize] ","tblLocs","[tblLocs]![Locations]
= [Location]")

 

[jsnelms1957]

assuming that the expression is set as the ControlSource of a textbox
control in the report, try

=DLookUp("[LocationSize]","tblLocs","[Locations] = '" & [txt89] & "'")

the above assumes that the [Locations] field is a Text data type. if it's
Number data type, then change the syntax to

=DLookUp("[LocationSize]","tblLocs","[Locations] = " & [txt89])

the above goes all on one line in the ControlSource property, regardless of
line-wrap in this post. as for the 2nd criteria you mentioned, you don't say
what field it's applied to in tblLocs, and don't show it in the your posted
expression, so i didn't address it.

hth

troubleshoot.

Listed below is what I initially tried without success.
I thought that since the report has the data for criteria 1 that I
should try using the report control, which is txt89. The 2nd criteria
is also in the report control as txt196. Hope that is more clear.
Shannon

=DLookUp(" [tblLocs]![LocationSize] ","tblLocs","[tblLocs]![Locations]
= [Location]")- Hide quoted text -

- Show quoted text -

Tina, you are good. Your answer is the closest I have ever been, and I
have hours into this trying to figure it out.

When I run the report a dialog box pops open asking for the parameter
for txt89. I manually entered it and it works. How do I pass the
parameter of the report control automatically. The report is sorted by
Location and the parameter dlookup needs is found in the group header
of the report. So, each grouping in the report will need to lookup the
value of the txt89 control for each sorted group. I also need to add
the other criteria that the entire report is based upon. see my attemp
below.

=DLookUp("[LocationSize]","tblLocs","[Locations] = '" & [txt89] &
"'") and report!rptEstDetByLocation!txt196

Thanks so much for everyones help so far.
Shannon