Dlookup sytax and punctuation (3 Viewers)

[Gasman]

Upload enough for someone else to test?

 

[Pat Hartman]

Then maybe the code is corrupted. But the DLookup() is a query. It just isn't saved as a querydef.

 

[jpl458]

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.

No, the field in the table is Region, and I don't reference the form explicitly.

Dlookup() syntax:

For String lookup
XX = Dlookup("[FieldNameToFind]","TableOrQuery", "[KeyLookup] = '" & [VarToFind] & "'")

For Numeric lookup
XX = Dlookup("[FieldNameToFind]","TableOrQuery", "[KeyLookup] = " & [VarToFind])

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.

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_Doc_Man]

If you return the same row every time, there is something wrong with the table OR there is something wrong with the criteria of the DLookup. You must pick a criteria field in the DLookup that will let you find the area code you want. The value you are feeding has to correspond to the correct field in the table. But this statement needs clarification:

"arcode is a control on the form,"

It is immaterial as to what is in "me.arcode" because that doesn't directly participate in the operation shown in post #15. You copy it but don't actually use it. What you really want is that [areacodetbl].[arcode] must exist as a valid field for the lookup. Then it must also occur that the record in the [areacodetbl] that has [arcode] of 512 has the correct value in [areacodetbl].[Region].

 

[June7]

No, the field in the table is Region, and I don't reference the form explicitly.

You said arcode is a textbox on form.

Region is the field you want to return a value from. You reference arcode in the search criteria. Is arcode a field in areacodetbl? If not, then what field holds the area code?

Did you read the rest of post 17?

Time to provide db for analysis? Follow instructions at bottom of my post.

 

[Gasman]

Any field that is included in criteria must also be a field in domain

That would be correct, you cannot search for a field that does not exist?

However you are returning Region. What is the name of the field that has the area code in it?

We are 24+ posts in here, for a simple DlookUp

Show the fields of the AreaCodetbl in a picture

 

[jpl458]

Late last nite I went back to the beginning, I reloaded the area code table and cleaned up the initial part of the code. The data used is as follow:

Table: AreaCodTable that has two 3 columns I use
AreaCode Short Text
Region Short Text examp.: IL, TX, MI
Description Short Text Contains cities in the associated area code

Form: Master Entry which contains the following text boxes:
callingnumber
arcode unbound
st unbound
callorigin unbound

Here is the code from the Lost Focus event of callingnumber textbox

 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,

 

[Gasman]

You still have not shown the fields in table AreaCodeTable?

 

[jpl458]

You still have not shown the fields in table AreaCodeTable?

Where have I not shown them? In the criteria? Can you be more specific, please.

 

[Gasman]

Show us the structure of the AreaCodeTable

Like this

 

[cheekybuddha]

Is the table called 'areacodetbl' or 'AreaCodeTable' ?

 

[jpl458]

Finally, I figured it out, and here is the code that works:

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

 

[cheekybuddha]

You really need to add Option Explicit to the top of every code module.

And then go to the VBA options and tick 'Require Variable Declaration' checkbox.

 

[Gasman]

Finally, I figured it out, and here is the code that works:

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

Yes, you have to use fields that exist It told you that in the DLookup() from MS, which I also quoted from your post?
I wanted to see a picture as proof, not just take what you typed as gospel.
So all along it has just been because you have not used the correct field name.

 

[jpl458]

You really need to add Option Explicit to the top of every code module.

And then go to the VBA options and tick 'Require Variable Declaration' checkbox.

Where are the VBA options located?

 

[cheekybuddha]

In the VBA editor menu:

Tools -> Options

 

[Gasman]

In the vba window. Tools/Options

 

[Pat Hartman]

Open ANY code module.
Click Tools
Click Options

The second option is Require Variable Declaration - Check the box

 

[cheekybuddha]

Requiring variable declaration will automatically add Option Explicit to every newly created code module.

However, you must add it manually to any already existing code module.

Once you have done that, in the VBA editor menu choose:

Debug -> Compile

It will highlight the first syntax error it finds in your code.

Correct the error, and repeat the process until no more errors are displayed