MS Access DLookUp

[cybermonkey]

Need a little assistant.
I have an Access data base with a form that pulls data from different tables. It's a listing of on-call personal.
I use DLookup in the text boxes to search the proper table and display the info.
The form is simple, the top of the form has a date picker to pull in the data from the different tables for that day.
How do I go about in the expressions builder to have a text box pull in data from a certain table based on the time of day and/or the day of the week.

The form is simple, the top of the form has a date picker to pull in the data from the different tables for that day.

For more details. I have 3 different forms. One for day time on-call, one for nights, and there is one for weekend/holidays (because some of the services have a different pager for the weekend).

Currently the weekend/holiday form pulls all the data from the nights tables, but some services have different people for days and nights even on the weekends.



Here is how is it currently setup. Each service has 3 tables. One for the day shift name, one for the nightshift name and one for the list of names and their numbers. WIth the except of one or two, the example below is one of those with only 2 tables for the service.
The text box for the name is built like this: =DLookUp("Fellow","Orthopedic Day","Day=#" & [combo8] & "#") and then the matching text box for the phone number is built like is: =DLookUp("Pager","Orthopedic Day","FELLOW='" & [Text88] & "'"). In this case, if I could build the DLookup to be able to look at the day table during certain hours and the night table for certain hours this will have the weekend/holiday form display as needed even for the services with all three tables (since those text boxes for the numbers looks at what is in the text box for the name).

[ranman256]

You don't need any Dlookups.
make a query that joins the 2 tables on Fellow.

You didn't need different tables for day,night,weekend.
1 table for all OnCalls,
a field for CallPeriod. Day,night,Wkend.
again, joined on fld:Fellow.

A form would show the user boxes to pick the Period and date.
a button would open the join query.

[ssanfu]

It sounds, to me, that your design is not normalized; maybe you have committed "Spreadsheet": your tables are designed like a spreadsheet.
Also, "Day" is a reserved word (and a built in function) in Access and shouldn't be used as object names.
You shouldn't have spaces in object names.

Maybe you would post your database with a few example records.
Change any sensitive data (names,phone numbers). Do a Compact & Repair, compress (Zip) it, then post it.