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

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

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

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

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

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

Bob, Sal ... Thanks guys. You have saved my life and taught me a few things.

Ray

Ray, I'm surprised this one didn't give you a strange result. It should be coded this way:
      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