DLOOKUP in MS Access (1 Viewer)

[Sbrads]

Hi All,
I use MS Access a lot and have had much success with it. I have seldom had to use the DLookup function and for some, no doubt, silly reason I ak struggling with it today.
I have a single form which runs from a Query and it has a field in it called Pay where I am trying to lookup from an entireley different query a simple YES or NO using the DLOOKUP function to look up the policy number on the form and find the YES or NO on the query. As I have used this function very little in the past I now find myself struggling to get it right. If anyone can help I would very grateful.
I have attached a PDF below with screenshots which hopefully explains what I am trying to do.

Think you

 

[pbaldy]

Your field is text:

General: DLookup Usage Samples

 

[XPS35]

Remove the quotationmarks around PolicyNumber.

 

[plog]

=DLookUp("PAY","Adviser Tabs","PolicyNumber =" & "PolicyNumber")

1. When you poorly name fields and objects (tables, queries, reports, etc.) you get punished by having to do extra work. "Adviser Tabs" is a poor name because it contains a space. When you do that you have to put it inside brackets like so "[Adviser Tabs]". Only use alphanumeric characters and underscores in names.

2. You are not referencing "PolicyNumber" correctly in the criteria of your Dlookup.

A. Because that second PolicyNumber is in quotes you are literally using the value "PolicyNumber" instead of the value inside PolicyNumber. That second PolicyNumber should not be surrounded by quotes.

B. Because the field PolicyNumber is a string, you must start and the right part of the equation with single quotes. What you are telling the computer is to compare the PolicyNumber field in Adviser Tabs with whatever string is inside the PolicyNumber field on the form.

"PolicyNumber = '" & PolicyNumber & "'"

 

[Sbrads]

'" & PolicyNumber & "'"

Thank you so much. DLOOKUP is one function I have not used much at all and I have learned a lot from you in this exercise. I was aware of not leaving gaps in Query and Table Names but I guess at times I can get sloppy. Thank you again, much appreciated.

 

[Pat Hartman]

Welcome

To help us all to help you -- Rather than posting a PDF, it is much easier for us to look at code that you post as text. Use the code option from the editing menu so that your code retains its proper spacing as it is pasted.

PS - not sure how you managed to circumvent the web site rules about the posting of attachments by new members but you did. Violation of forum policy will result in you being barred. Do not circumvent forum rules again.