At least once a week I get an email from someone with the following problem (or some variation of it), so I wanted to post it here so you all can benefit. The student is trying to use a DLOOKUP function or build an SQL statement that is reading a form field like this:

Answer = DLOOKUP("FirstName","CustomerT","CustomerID=Forms!CustomerF!CustomerID")

SOMETIMES this will work. MUCH of the time, however, it will NOT. This is because you're sending a reference to that form field to the DLOOKUP function. It's better to send the VALUE itself, like this:

Answer = DLOOKUP("FirstName","CustomerT","CustomerID=" & Forms!CustomerF!CustomerID)

See the difference? It's subtle. Notice the quotes. What happens is that Access will determine the value of the CustomerID FIRST and then your function becomes:

Answer = DLOOKUP("FirstName","CustomerT","CustomerID=1001")

Whatever that value happens to be gets placed in there first and then the DLOOKUP is performed. The same thing happens with SQL statements...

MySQL = "SELECT * FROM CustomerT WHERE State=Forms!MyForm!State"

Should instead be:

MySQL = "SELECT * FROM CustomerT WHERE State='" & Forms!MyForm!State & "'"

NUMERIC values don't require quotes. TEXT STRINGS require quotes (use either single ' quotes or double "" double quotes). DATE values require pound # signs, like:

MySQL = "SELECT * FROM CustomerT WHERE CustomerSince>#" & Forms!MyForm!BeginDate & "#"

Remember this. Now go forth, build databases, and make me proud.