Exasperating Error - Microsoft Access Forum


	Back to Access Forum Comments List

Upload Images @Reply Bookmark Link Email Next Unseen

Exasperating Error

Bill Carver

13 months ago

I am having trouble getting a first screen from a field using a dlookup to solve it.. I get the dreaded error 2471 with the lookup criteria that I had set

here's my dlookup string

destination = DLookup("[firstscreen]", "users", "[windowsuser] = Carver")

                        field           table     lookupfield    criteria

microsoft's example

DLookup("[LastName]", "Employees", "[EmployeeID] = 1")

the table is put in correctly as well as the field  and the criteria string is correctly formatted .. but what I'm getting is the next picture.

I also tried relating the form to a query that would pick up the username in both a public variable and a tempvar.  It works if he database has just opened.

I tried everything in on current and on load.

	Bill Carver OP @Reply 13 months ago

	Adam Schwanz @Reply 13 months ago
`DLookup("firstscreen", "users", "windowsuser = 'Carver'")`

	Bill Carver OP @Reply 13 months ago
why the ' ? Just curious. is this the famous double double quotes that is so often spoke of here? even though these are singles

	Bill Carver OP @Reply 13 months ago
btw I simplified by hard coding my name for this. however I hope to be using in its place environ(username) in my name's place. that didn't work either. do I need to work it like so? "'" & environ("username") &"'" that's a quote, apostrophe,quote both times

	Kevin Robertson @Reply 13 months ago
You are only showing the last part of your WHERE condition of your DLookup function. We need to see the entire function if we are to help with the syntax.

	Bill Carver OP @Reply 13 months ago
DLookup("firstscreen", "users","'" & environ("username") &"'"

Adam Schwanz @Reply

13 months ago

The ' are just normal syntax.

When dealing with numbers you can say "CustomerID=1" but when dealing with a string you need to say "State='FL'"

The double double quotes are more in play when you are using a variable, and the variable is a string. So when you use the environ variable you will use double double quotes. In which case it will be "State=""" & Variable & """" 3 double quotes and 4 double quotes. Single quote + double quote should also work if ordered properly. "State='" & Variable & "'" but that is more confusing to read in my opinion. Normally I use single quotes if it's a hard coded value, and if it's a variable, I use the multiple double quotes.

	Kevin Robertson @Reply 13 months ago
You're missing the first half of the WHERE condition. `DLookup("FirstScreen", "Users", "WindowsUser=""" & Environ("username") & """"` DLookup

This thread is now CLOSED. If you wish to comment, start a NEW discussion in Access Forum.

Next Unseen

New Feature: Comment Live View