Robert,
It's really not that hard.
If we have the following DLookup:
DLookup("myField", "myTable", "someField = 3")
....what this equates to is:
SELECT myField FROM myTable WHERE someField = 3
So you see, DLookup simply presents this query in a different way.
Each of the arguments is enclosed in quotes, because VBA must pass these
arguments to the query engine, which will build a valid SQL query out of
them. The third argument of the DLookup function must be a valid SQL WHERE
clause, without the keyword "WHERE".
If the variable comparison operator is a literal value, you can include it
inside the quotes, as shown below:
"someField = 3"
"someField = 'ABC'"
But if it is contained within a variable, you don't want the variable name
included in the WHERE clause; you want its value; so the variable must be
excluded from the string, like so:
"someField = " & intMyNumber
"someField = '" & strMyString & "'"
Of course, as you can infer from the above example, string values must be
passed to the query engine as literal string values, which is why we include
either single quotes ( ' ) or double quotes ( " ).
You can test this in the Immediate window:
?"someField = 3"
someField = 3
?"someField = " & 3
someField = 3
intMyNumber = 3
?"someField = intMyNumber"
someField = intMyNumber
?"someField = " & intMyNumber
someField = 3
strMyString = "ABC"
?"someField = strMyString"
someField = strMyString
?"someField = " & strSomeField
someField = ABC
?"someField = '" & strSomeField & "'"
someField = 'ABC'
?"someField = """ & strSomeField & """"
someField = "ABC"
?"someField = """ & "ABC" & """"
someField = "ABC"
Regards,
Graham R Seach
Microsoft Access MVP
Sydney, Australia
Microsoft Access 2003 VBA Programmer's Reference
http://www.wiley.com/WileyCDA/WileyTitle/productCd-0764559036.html
Robert Solomon said:
s1 is meant to be a string. When I pasted your code in, it works!
Finally! But I have no clue as to why the syntax is structured as it
is.
Maybe DLookup is easy, but its criteria syntax remains quite hard.
Thanks again!
Well of course it won't work when [CPT Code] = ' s1 '. You have to put s1
OUTSIDE the literal string.
Also, you've declared s1 and s2 as strings, yet you assigned s1=36246. If
it's really a string, you should assign its value like so:
s1 = "36246"
The following will work:
s1 = "36246"
s2 = Nz(DLookup("[CPT Description]", "[CPTList]", _
"[CPT Code] = '" & s1 & "'"), "Not Found")
If it's not meant to be a string, then declare it as a numeric data type,
maybe Long Integer. In that case, the following will work:
Dim s1 As Long
Dim s2 As String
s1 = 36246
s2 = Nz(DLookup("[CPT Description]", "[CPTList]", _
"[CPT Code] = " & s1), "Not Found")
Regards,
Graham R Seach
Microsoft Access MVP
Sydney, Australia
Microsoft Access 2003 VBA Programmer's Reference
http://www.wiley.com/WileyCDA/WileyTitle/productCd-0764559036.html
Robert Solomon said:
Dim casedetailsrst As Recordset
Dim s1 As String
Dim s2 As String
Set casedetailsrst = Me!Child13.Form.RecordsetClone
s1 = 36246
s2 = Nz(DLookup("[CPT Description]", "[CPTList]", _
"[CPT Code] = '36246' "), "Not Found")
With casedetailsrst
.AddNew
!CasesRef = Me!CaseNo
!CPTDescription = s2
!CPTCode = s1
.Update
.Move 0, .LastModified
