No, the field in the table is Region, and I don't reference the form explicitly.The criteria must reference a field in the table to apply parameter to. You say arcode is a control on form - is it also a field in table? The syntax is:
DLookup("field to return value from", "table to search", "field to apply filter=" & variable)
and if field is text type
DLookup("field to return value from", "table to search", "field to apply filter='" & variable & "'")
What is callingnumber control - a textbox? It seems you have area code saved as a prefix to phone number in same field. If area code were in its own field, query could join to areacodetbl and DLookup would not be needed.
Dlookup() syntax:
For String lookup
XX = Dlookup("[FieldNameToFind]","TableOrQuery", "[KeyLookup] = '" & [VarToFind] & "'")
For Numeric lookup
XX = Dlookup("[FieldNameToFind]","TableOrQuery", "[KeyLookup] = " & [VarToFind])
I can run queries all day using the QBE Grid and everything runs great. But, I read this, "Any field that is included in criteria must also be a field in domain; otherwise, the DLookup function returns a Null." The DLookup always returns NJ as a Region (area code 201) which is the first row in the table, no matter what area code is used in the search. The problem is with the criteria part of the DLookup, and the more I read the more confused I get, particularly reading the quote above.The table might be corrupted. Try exporting the area code table to excel. Then delete the table in the database. Compact the db, Import the area code data from the spreedsheet. Define the PK and add any unique indexes.
or the query might be corrupted. Try copying the SQL string from the querydef into notepad. and follow the steps above.
"arcode is a control on the form,"
You said arcode is a textbox on form.No, the field in the table is Region, and I don't reference the form explicitly.
That would be correct, you cannot search for a field that does not exist?Any field that is included in criteria must also be a field in domain
arcode = Mid(callingnumber, 1, 3) 'Extract the area code from the phone number and put in arcode txtbox on from
Me.st = DLookup("Region", "AreaCodeTable", "arcode = '" & arcode & "'") 'Put Region(state) into textbox named st[
/CODE]
In the DLookup it's the criteria that I have no clue as to its construction. I want to get the value in the region col of the AreaCodeTable that corresponds to the area code that is in callingnumber. the criteria "arcode = '" & arcode & "'" is wrong, and the last of my abortive attempts try and make it wotk. After a lot of reading and trying it still escapes me.
Thanks all for your time and responses,
Where have I not shown them? In the criteria? Can you be more specific, please.You still have not shown the fields in table AreaCodeTable?
Private Sub callingnumber_LostFocus()
arcode = Mid(callingnumber, 1, 3) 'Extract the area code from the phone number and put in arcode txtbox on form
Me.st = DLookup("Region", "AreaCodeTable", "AreaCode = '" & arcode & "'") 'Put Region(state) into textbox named st
Me.callorigin = DLookup("Description", "AreaCodeTable", "AreaCode = '" & arcode & "'") 'Put description into callorigin
End Sub
Option Explicit
to the top of every code module.Yes, you have to use fields that exist It told you that in the DLookup() from MS, which I also quoted from your post?Finally, I figured it out, and here is the code that works:
Code:Private Sub callingnumber_LostFocus() arcode = Mid(callingnumber, 1, 3) 'Extract the area code from the phone number and put in arcode txtbox on form Me.st = DLookup("Region", "AreaCodeTable", "AreaCode = '" & arcode & "'") 'Put Region(state) into textbox named st Me.callorigin = DLookup("Description", "AreaCodeTable", "AreaCode = '" & arcode & "'") 'Put description into callorigin End Sub
Your last reply pointed me to the solution, Thanks
Where are the VBA options located?You really need to addOption Explicit
to the top of every code module.
And then go to the VBA options and tick 'Require Variable Declaration' checkbox.
Option Explicit
to every newly created code module.