Hello Duane,
You asked "Why do you need to use DLookup when you might have been able to
pull the Run_From_Postcode value from the report's field list?"
I thought it best to answer that one seperately...
This may get too complex to explain, but here goes...
The reason for comparing vai a report control is because the 'Run_No_Entry'
is a proxy field that takes its value from a User parameter in the query, and
matches that parameter against a complex postcode matching query.
I needed a way to get the chosen parameter value into the report so that the
user can be reminded which Run No they chose. I managed to do this by
creating the Proxy [Run_No_Entry] which gets its value from the query
[Run_No], but it also returns the (correct) other Run Numbers that match
postcodes against the input criteria, [Run_No]; so I had to group the
[Run_No_Entry] proxy; that way it returned only the Input Criteria value down
an additional new column, and I can then reference that on the Report. My
problem now is that I need to get the [Postcode_From] value that matches the
User Input value from the query.
This is the SQL for it:
SELECT A.KeyCode, A.Run_No, A.Run_From_Postcode, A.Run_To_Postcode,
A.Run_From, A.Run_To, A.Run_Point_Venue_A, A.Run_Point_Address_A, B.Run_No AS
Run_No_Entry
FROM FindCodes AS A, FindCodes AS B
WHERE (((A.KeyCode)=
.[Run_To_PostCode] Or
(A.KeyCode)=.[Run_From_PostCode]) AND ((B.Run_No)=[Find Run No]))
GROUP BY A.KeyCode, A.Run_No, A.Run_From_Postcode, A.Run_To_Postcode,
A.Run_From, A.Run_To, A.Run_Point_Venue_A, A.Run_Point_Address_A, B.Run_No
ORDER BY A.Run_No;
If you have the time to read this, (I'll understand if you don't of course.)
Here's a link to where I have got to so far:
http://www.microsoft.com/office/com...8ada&catlist=&dglist=&ptlist=&exp=&sloc=en-us
Duane Hookom said:
Did it work? Did you get an error? What did you see in the report? Is there a
field in the query named [Run_From_Postcode]? Is the a control in the same
section of the report named Run_No_Entry?
Why do you need to use DLookup when you might have been able to pull the
Run_From_Postcode value from the report's field list?
Also, if you copied and pasted your expression in your reply, why is
DLookup() have an uppercase "U"?
--
Duane Hookom
Microsoft Access MVP
efandango said:
Hello Duane,
I tried that, changing the recordsource first.
The recordsourse is called: 'Other_Points_Via_Run_No'
Here's what I pasted into the controlsource of the control.
=DLookUp("[Run_From_Postcode]","[Other_Points_Via_Run_No]","[Run_No]=" &
[Run_No_Entry])
To Confirm; the query is the only recordsource for the report. And, also
Run_No is also numeric.
Duane Hookom said:
You can't use "Me!". Assuming Run_No is numeric and the query is truly your
report's record source, try:
=DLookUp("[Run_From_Postcode]",[RecordSource],"[Run_No]=" & [Run_No_Entry])
--
Duane Hookom
Microsoft Access MVP
:
My Query is Called: ‘Other_Points_Via_Run_No’
I have an Unbound Text Box on my report Called [RunFromPostCodeEntry] and
also a Bound control that holds a Run No. [Run_No_Entry].
I want [RunFromPostCodeEntry] to look up a Postcode in the reports
underlying query, using the [Run_No_Entry] as reference against the Report’s
[Run_No_Entry] for a record query field called Run_From_Postcode.
But my code in the [RunFromPostCodeEntry] Source property doesn’t work?
This is my code:
=DLookUp("[Run_From_Postcode]","[Other_Points_Via_Run_No]","[Run_No]=Me!Run_No_Entry]")
