Specifing a variable field in a Dlookup function in a query

[TommyT77]

Table PPM, collects information from a user, including fields called “Week No” and “Day”.

Week No	Day	Description of Work	…
1	Saturday	Digging holes
1	Tuesday	Filling in holes
2	Sunday	Digging more holes

I have another table called “Rail Weeks”, which is used to lookup dates (as strings and NOT dates). The primary field is “Week No” and the rest of the fields are days of the week:

Week No	Saturday	Sunday	Monday	Tuesday	Wednesday	Thursday	Friday
1	01-01-13	02-01-13	03-01-13	04-01-13	05-01-13	06-01-13	07-01-13
2	08-01-13	09-01-13	10-01-13	…

I want the returned query to show all the fields in the “PPM” table plus the resolved date from the “Rail Weeks” table.

Week No	Day	Description of Work	Date (Lookup from Railways)
1	Saturday	Digging holes	01-01-13
1	Tuesday	Filling in holes	04-01-13
2	Sunday	Digging more holes	09-01-13

I’ve been looking at the Dlookup function, and I’ve managed to get it to work as an expression in a query, but it only returns me dates for a single set field from the “rail weeks” table (day of the week), for each record specified by the Week No in the “PPM” table. For example the below expression will return dates on Monday, for each week entered by a user in the PPM table but takes no account of what day is entered into the PPM table.

Expr1: DLookUp("[Monday]","[Rail Weeks]","[Wk No Data] =" & [PPM]![Week No])

[June7]

Not seeing a field named [Wk No Data].

Try:

DLookup([Day], "[Rail Weeks]", "[Week No]=" & [Week No])

[TommyT77]

Thanks very much June7. It's amazing how easy it is to over complicate something. I've been trying all sorts complicated VBA to solve this problem - Many thanks.

By the way, the field [Wk No Data] should have read [Week No] from the rail weeks table - not that is matters. Problem solved.

Thanks. TommyT