Use Form Combo Box as Dlookup Criteria (1 Viewer)

david_johnson

Registered User.
Local time
Today, 08:30
Joined
Oct 12, 2017
Messages
12
So I canNOT figure out what is wrong with my dlookup language. I used the table references just to make certain its clear. I want to look up a value (mthly_rpt_period_month) in "monthly_report_period" based on the user entry in combo box "mnthly_rpt_due_date" on the form "Switchboard" based on the correlating field "mthly_rpt_period_due_date" that's also in the "monthly_report_period" table.

=DLookUp(" [monthly_report_period]![mthly_rpt_period_month] ","monthly_report_period","[monthly_report_period]![mthly_rpt_period_due_date] = ' " & [mnthly_rpt_due_date] & " ' ")
 

Gasman

Enthusiastic Amateur
Local time
Today, 13:30
Joined
Sep 21, 2011
Messages
14,052
Dlookup requires
Field to return
Domain (can be table or query)
Criteria
https://www.techonthenet.com/access/functions/domain/dlookup.php
So hazarding a guess at your code you would use

Code:
=DLookUp("mthly_rpt_period_month","monthly_report_period","mthly_rpt_period_due_date  =  #" & Me.mnthly_rpt_due_date  &"#")
I tend not to use the field [] brackets where not needed

Also you appear to have several versions on month?
mthly, mnthly, monthly... that must be hard to keep track of?
 

isladogs

MVP / VIP
Local time
Today, 13:30
Joined
Jan 14, 2017
Messages
18,186
Your syntax is wrong. Assuming its a date field, try this

Code:
=DLookUp("mthly_rpt_period_month","monthly_report_period","mthly_rpt_period_due_date = #" & [mnthly_rpt_due_date] & "#")

Note that you don't need square brackets if you use underscores in place of spaces

EDIT I see gasman got there quicker....
 
Last edited:

david_johnson

Registered User.
Local time
Today, 08:30
Joined
Oct 12, 2017
Messages
12
I appreciate the help. This code gives returns "#ERROR" in the textbox. Not sure what I'm doing wrong.
 

isladogs

MVP / VIP
Local time
Today, 13:30
Joined
Jan 14, 2017
Messages
18,186
Did you put an equals sign at the start? I originally left it out by mistake
 

david_johnson

Registered User.
Local time
Today, 08:30
Joined
Oct 12, 2017
Messages
12
I did. The textbox now has this as the control source: =DLookUp("mthly_rpt_period_month","monthly_report_period","mthly_rpt_period_due_date = '" & [mnthly_rpt_due_date] & "'").

I have quadruple-checked the variable/table names and had someone else check them too. If I didn't shave my head I'd most likely pull my hair out.
 

isladogs

MVP / VIP
Local time
Today, 13:30
Joined
Jan 14, 2017
Messages
18,186
I did. The textbox now has this as the control source: =DLookUp("mthly_rpt_period_month","monthly_report_period","mthly_rpt_period_due_date = '" & [mnthly_rpt_due_date] & "'").

I have quadruple-checked the variable/table names and had someone else check them too. If I didn't shave my head I'd most likely pull my hair out.

If you look at both my initial reply and Gasman's, we both used date delimiters
What you have written applies to a text string
 

Minty

AWF VIP
Local time
Today, 13:30
Joined
Jul 26, 2013
Messages
10,355
See the link in my signature. Both Ridders and Gasman have highlighted the error with the date formatting.
 

Users who are viewing this thread

Top Bottom