|
Subject:
|
Syntax Error
|
|
Posted By:
|
RayL
|
Post Date:
|
10/24/2003 2:48:37 PM
|
I have the following attached to the Click Event of a Command Button. When I click the button, I get a syntax error, missing operator in the code line vcc = .....
I know I'm doing something stupid, but I can't figure it out. HELP!
Dim vcontact, vsubject, vcc, veng As String
Dim vnum As Variant
vnum = Forms![frmProdApproval]![ECNNo]
vsubject = "Suggested Revision of ECN No. " & vnum
veng = DLookup("[engid]", "approval", "[ecnno]= forms![frmProdApproval]![ecnno]")
vcc = DLookup("[email]", "password", "[Name] = " & veng)
vcontact = DLookup("[email]", "password", "[Name]=forms![frmProdApproval]![change by]")
DoCmd.SendObject , , acFormatRTF, vcontact, vcc, , vsubject, , True
Thanks,
Ray
|
|
Reply By:
|
sal
|
Reply Date:
|
10/24/2003 2:59:06 PM
|
Dlookup sintax is
DLookup («expr», «domain», «criteria»)
you are passing
vcc = DLookup("[email]", "password", "[Name] = " & veng)
which is the equivalent of
DLookup («expr», «domain», «criteria» & veng)
in Criteria, pass a string instead of appending to the value
let me know how it goes
Sal
|
|
Reply By:
|
Bob Bedell
|
Reply Date:
|
10/25/2003 12:09:35 AM
|
Hi Ray,
If you were using a numeric value as your criteria, vcc = DLookup("[email]", "password", "[Name] = " & veng) would be the correct syntax. Then, the original criteria expression:
"[Number] = " & veng
evaluates to:
"[Number] = " & 1
which, when concatenated, evaluates as:
"[Number] = 1"
which is a correct numeric criteria clause for DLookup.
However, since your [Name] field is apparently a string, you need syntax like:
vcc = DLookup("[email]", "password", "[Name] = '" & veng & "'")
Here, the original criteria expression:
"[Name] = '" & veng & "'")
evaluates to:
"[Name] = '" & "Smith" & "'")
which, when concatenated, evaluates as:
"[Name] = 'Smith'"
The following lists the syntax for using string, numeric, or date criteria with the Domain Aggregate functions. The syntax is the same whether referencing a control or a variable. You simply use a vanilla SQL WHERE clause without the WHERE statement. Its also a good idea to trap for Null when using the Domain Aggragates, which is what the Nz function is doing below:
Referencing controls
' String criteria
intEmployeeeID = Nz(DLookup("[EmployeeID]", "tblEmployees", _
"[LastName] = '" & Me.txtLastName & "'"), 0)
' Numeric criteria
strLastName = Nz(DLookup("[LastName]", "tblEmployees", _
"[EmployeeID] = " & Me.txtEmployeeID), "")
' Date/Time criteria
intEmployeeID = Nz(DLookup("[EmployeeID]", "tblEmployees", _
"[HireDate] = #" & Me.txtHireDate & "#"), 0)
Referencing variables
' String criteria
strLastName = Me.txtLastName
intEmployeeID = Nz(DLookup("[EmployeeID]", "tblEmployees", _
"[LastName] = '" & strLastName & "'"), 0)
' Numeric criteria
intEmployeeID = Me.txtEmployeeID
strLastName = Nz(DLookup("[LastName]", "tblEmployees", _
"[EmployeeID] = " & intEmployeeID), "")
' Date/Time criteria
datHireDate = Me.txtHireDate
intEmployeeID = Nz(DLookup("[EmployeeID]", "tblEmployees", _
"[HireDate] = #" & datHireDate & "#"), 0)
HTH,
Bob
|
|
Reply By:
|
Bob Bedell
|
Reply Date:
|
10/25/2003 12:37:57 AM
|
I should probably mention that the method of handling string criteria as posted above (while frequently used) will only work if the value of 'veng' does not itself contain an apostrophe. So only use this method if the value of 'veng' could never contain an apostrophe. If it could, you will need to write a function to double up the apostrophe inside the string so that VBA (and ADO) will interpret the double delimeter as a single delimeter.
Regards,
Bob
|
|
Reply By:
|
Bob Bedell
|
Reply Date:
|
10/25/2003 1:07:40 AM
|
Something like:
Public Function PadSingleQuotes(strIn As String) As String
Dim intI As Integer
Dim strTemp As String
Dim strCh As String
For intI = 1 To Len(strIn)
strCh = Mid(strIn, intI, 1)
If strCh = "'" Then
strTemp = strTemp & "''"
Else
strTemp = strTemp & strCh
End If
Next intI
PadSingleQuotes = strTemp
End Function
Called by:
' String criteria
strLastName = Me.txtLastName
intEmployeeID = Nz(DLookup("[EmployeeID]", "tblEmployees", _
"[LastName] = '" & PadSingleQuotes(strLastName) & "'"), 0)
Regards,
Bob
|
|
Reply By:
|
Bob Bedell
|
Reply Date:
|
10/25/2003 6:26:25 PM
|
Also wanted to mention that the declaration:
Dim vcontact, vsubject, vcc, veng As String
creates 3 Variant variables and 1 String ('veng') in VBA/VB.
In VB.NET, the same syntax would give you 4 Strings.
If you want vcontact, vsubject, vcc to be declared as string variables, then the As String clause needs to immediately follow each in VBA/VB:
Dim vcontact As String, vsubject As String
Dim vcc As String, veng As String
|
|
Reply By:
|
RayL
|
Reply Date:
|
10/27/2003 9:13:08 AM
|
Bob, Sal ... Thanks guys. You have saved my life and taught me a few things.
Ray
|
|
Reply By:
|
SerranoG
|
Reply Date:
|
10/27/2003 9:40:02 AM
|
quote:
Originally posted by RayL
vcontact = DLookup("[email]", "password", "[Name]=forms![frmProdApproval]![change by]")
Ray, I'm surprised this one didn't give you a strange result. It should be coded this way:
vcontact = DLookup("[email]", "password", "[Name]='" & _
forms![frmProdApproval]![change by] & "'")You're passing a variable. The way you originally had it expressed it merely as a literal string, not an evaluated variable.
Oh, and I know nostalgic folks love the bang (!), but consider using only periods. They still work and you get the added benefit that Access XP autocompletes variables when you write code.
Greg Serrano
Michigan Dept. of Environmental Quality, Air Quality Division
|
