Have the following code that bombs on the first DLookup with the criteria Me.arcode, which is a textbox on the form. Bombing on the criteria part of the DLookup. I don't know how to punctuate a variable in the criteria part of the DLookup. BTW, my naming of stuff sucks, and will be changed.
Private Sub callingnumber_LostFocus()
Dim acode As Variant
acode = Mid(callingnumber, 1, 3)
Me.arcode = acode
Me.st = DLookup("[Region]", "areacodetbl", "Me.arcode")
Me.callorigin = DLookup("Description", "areacodetbl", "Me.arcode")
End Sub
For starters, anything enclosed in single or double quotes is a literal string. So, "Me.arcode" is the actual value Me.arcode - those specific 9 characters. It is not referring to the control named arcode.
This is assuming that arcode is the name of the column in the table. I'm going to assume that arcode is numeric and the value is 203. The & is the concatenation operator. What we are trying to build is a WHERE clause without the Where word. So, Access converts what you've written to "arcode = 203" and that is what gets used as the WHERE clause of the query that will return the Region field from the areacodetbl. The equivalent query would be
Select Region From areacodetbl Where arcode = 203;
Domain functions like DLookup(), DMax(), DSum(), etc are queries that work like a function. the DLookup() function returns the value of the Region field so in that sense it is like any other function. The difference is that domain functions actually run queries instead of your vba code or Access code. Now() is a VBA function. It returns the current date and time. Date() is a VBA function that returns just the current Date with the time of "midnight".
Search for VBA function list by category. This list will be invaluable to you. When you get the "by category" list (which is much more useful to the novice than the "by alpha" list), print it out. Save the link. If you want to do something with a date, look in the date section. It is quite likely that VBA has a function that will solve your problem.
Have the following code that bombs on the first DLookup with the criteria Me.arcode, which is a textbox on the form. Bombing on the criteria part of the DLookup. I don't know how to punctuate a variable in the criteria part of the DLookup. BTW, my naming of stuff sucks, and will be changed.
Private Sub callingnumber_LostFocus()
Dim acode As Variant
acode = Mid(callingnumber, 1, 3)
Me.arcode = acode
Me.st = DLookup("[Region]", "areacodetbl", "Me.arcode")
Me.callorigin = DLookup("Description", "areacodetbl", "Me.arcode")
End Sub
All the fields in the areacodetbl are short text, So I tried
Me.st = DLookup("[Region]", "areacodetbl", "arcode = " & Me.arcode)
all it returned was data from the first row of the table. I've read theDLookup articles, but I don't understand the concatenation that's required for strings. In your example both arcode and Me.arcode are the same thing. So in English I am looking for:
Me.st(textbox)=an area code, in the Region column of table areacodetbl, that is equal to the value in arcode (which is a short text field in the table).
Also you mention that I should Adjust 'arcode' within the string to the name of the actual field in question. I don't understand.
Really appreciate you time and help.
This phrase makes me think there is a disconnect between the previous descriptions and your expectation. Step away from the code. In the plainest possible language that scrupulously avoids code samples, tell us what you want, in what quantity. WHY do you seem disappointed that something was returned from the first row of the table? What SPECIFICALLY is wrong with that result?
All the fields in the areacodetbl are short text, So I tried
Me.st = DLookup("[Region]", "areacodetbl", "arcode = " & Me.arcode)
all it returned was data from the first row of the table. I've read theDLookup articles, but I don't understand the concatenation that's required for strings. In your example both arcode and Me.arcode are the same thing. So in English I am looking for:
Me.st(textbox)=an area code, in the Region column of table areacodetbl, that is equal to the value in arcode (which is a short text field in the table).
Also you mention that I should Adjust 'arcode' within the string to the name of the actual field in question. I don't understand.
Really appreciate you time and help.
Dlookup() does just that, it finds the first record that matches that criteria?
Your code is fine if aircode was a number, if text then you need quotes, single or double. You say it is text?
I tend to use single unless the field could have single quotes in it
I am going to go to my deathbed saying this all the time, but put the criteria into a string variable and Debug.Print it until you get it right. then use that in the function. Applies to sql strings as well
Code:
Dim strCeiteria AS String
strCriteria = "arcode = '" & Me.arcode & "'"
Debug.Print strCriteria
Me.st = DLookup("Region", "areacodetbl", strCriteria)
You can also walk through the code and inspect fields/variables with F8.
If it was a date you would use # either side of the date and in mm/dd/yyyy or yyyy-mm-dd format
All the fields in the areacodetbl are short text, So I tried
Me.st = DLookup("[Region]", "areacodetbl", "arcode = " & Me.arcode) all it returned was data from the first row of the table. I've read theDLookup articles, but I don't understand the concatenation that's required for strings. In your example both arcode and Me.arcode are the same thing. So in English I am looking for:
Me.st(textbox)=an area code, in the Region column of table areacodetbl, that is equal to the value in arcode (which is a short text field in the table).
Also you mention that I should Adjust 'arcode' within the string to the name of the actual field in question. I don't understand.
Really appreciate you time and help.
what this does is finds the value of region, within the table areacodetbl for the record returned by the criteria
Assuming arcode is numeric this might resolve to perhaps arcode = 0 or arcode = 1
[edit - I added this para after completing this reply. I originally thought your arcode was numeric]
you said arcode was shorttext, so this is incorrect anyway. Do you have code saying on error resume next which you haven't included. I would presume so, as you might well have other errors which are being ignored. eg if your dlookup actually works bot returns null
if you actually returned the first row, I presume arcode does have a real value, and it is working correctly. If arcode was actually blank, I expect you would have got a run time error as the criteria would ]look like arcode =
note that the value used by the test above would be nothing or null or empty, (probably null) but all of these terms are meaningful in access, (nothing, null and empty), and it might be incorrect to use any of these - I think your value of arcode is most likely 0, as you did get a value returned. What is the value of arcode in your table for the region value that was returned?
So you need a way to select a different value for arcode, and then you should get a different result.
This phrase makes me think there is a disconnect between the previous descriptions and your expectation. Step away from the code. In the plainest possible language that scrupulously avoids code samples, tell us what you want, in what quantity. WHY do you seem disappointed that something was returned from the first row of the table? What SPECIFICALLY is wrong with that result?
This phrase makes me think there is a disconnect between the previous descriptions and your expectation. Step away from the code. In the plainest possible language that scrupulously avoids code samples, tell us what you want, in what quantity. WHY do you seem disappointed that something was returned from the first row of the table? What SPECIFICALLY is wrong with that result?
My expectation is/was, use an area code to get the State and Description fields from a table that has all the area codes for the US and it's territories. But, no matter what area code in entered it always returns NJ (area code 201), which is the first entry in the area code table. However, here is all the code that I used:
The first line might be part of the problem. Shouldn't that be Dim acode As string? And, I don't understand the concatenation in the criteria of the DLookup. What is accomplished there?
The first line might be part of the problem. Shouldn't that be Dim acode As string? And, I don't understand the concatenation in the criteria of the DLookup. What is accomplished there?
Because you need to insert the VALUE of the variable and not the literal name, which is what you were doing initially, well not really, but would not have worked if you had 'arcode =' prefixing it anyway.
If you Debug.Print as suggested, you would see what is actually produced, not what you think is produced.
Use this version if arcode in your areacodetbl is a number
Code:
Dim acode As long 'don't use variants
acode = clng(Mid(callingnumber, 1, 3)) 'convert the string slice to a number
msgbox acode 'let's see what we have
'just a msgbox to see what came back
msgbox nz(DLookup("Region", "areacodetbl", "arcode = " & acode),"no match")
'no apostrophe in the arcode bit
Use this version if arcode in your areacodetbl is a string
Code:
Dim acode As string'don't use variants
acode = Mid(callingnumber, 1, 3))
msgbox acode 'let's see what we have
'just a msgbox to see what came back
'use chr(34) instead of building in apostrophes or inverted commas - I think it's easier to understand
msgbox nz(DLookup("Region", "areacodetbl", "arcode = " & chr(34) & acode & chr(34)),"no match")
concatenation. The string you end up with must look like this
if arcode is a number arcode = 201
if arcode is a string arcode = "201"
but now you have the problem of expressing this, as when you write "" together it gets treated as a single " character.
So "arcode = " & "201" doesn;t work -
This ends up being "arcode = ""201", and now you have two "" together in the middle, which get treated as a single ", hence "arcode = "201". Now you have an ummatched " character. If you add another one at the end, it's still no good,, and it's not even clear how you can actually write a single quote at the end
so you can say arcode = '201' OR arcode = chr(34)201chr(34) where chr(34) is actually a ", but this usage avoids the problems of having two "" together, which you would get if you tried "arcode = "&"201" as noted above.
if arcode is a date arcode = #28/08/2022#
note that the outer pair of " characters aren't really there when you express the string, but you have to manipulate the string formation by using string delimiters, and that also gives you issues when you have two end up with two " together, as they get treated as a single "
so you can actually write """"" (4 " characters, as 4 """"" together get treated as a single ". The inner two ""become a single ", inside quote marks so """ is then a quoted quote mark, if you will.
hence
"arcode = " & """" & "201" & """" gives you a string of arcode = "201" which is what you want - but it's very hard to actually get it correct when writing code.
errors
finally, I don't understand why some of your examples didn't produce run time errors, which is why I added the nz() in my example
I wondered if you ACTUALLY had on error resume next as a code line, and you didn't include that in your posts, thinking it wasn't important.
My expectation is/was, use an area code to get the State and Description fields from a table that has all the area codes for the US and it's territories. But, no matter what area code in entered it always returns NJ (area code 201), which is the first entry in the area code table. However, here is all the code that I used:
The first line might be part of the problem. Shouldn't that be Dim acode As string? And, I don't understand the concatenation in the criteria of the DLookup. What is accomplished there?
Because you need to insert the VALUE of the variable and not the literal name, which is what you were doing initially, well not really, but would not have worked if you had 'arcode =' prefixing it anyway.
If you Debug.Print as suggested, you would see what is actually produced, not what you think is produced. View attachment 102865
Dim acode As Variant
acode = Mid(callingnumber, 1, 3)
Debug.Print acode
Me.arcode = acode 'Put the area code in the textbox arcode
Debug.Print Me.arcode
Me.st = DLookup("Region", "areacodetbl", "arcode = '" & acode & "'")
Debug.Print Me.st
Debug.Print arcode
Debug.Print acode
Here are the printed values in the immediate window
512
512
NJ
512
512
Area code 512 is TX not NJ. No matter what area code is entered I always get NJ. The problem is in the criteria part of the DLookup.
arcode is a control on the form, acode is the variant, so the problem is here "arcode = '" & acode & "'". If I go into the QBE grid the query runs fine. At a loss.
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/query 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 then have to extract it to pull associated data with DLookup calls. It is much easier to concatenate than parse data. If area code were in its own field, area code could be typed/selected via combobox on form. This combobox would have a RowSource that includes fields such as Region and Description. Reference those data columns by their index. If data is in 2nd column its index is 1: =[cbxAC].Column(1). In a report, query could join to areacodetbl to retrieve related data.
Are st and callorigin also fields in table? If so, this is duplicating data between tables. The data is already in areacodetbl and should not have to be saved to another table.
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.
