VBA WHERE clause in VBA parameter query

[charles.kendricks]

I have a form that is populated using this parameter query:

SELECT tblCustomer.*
FROM tblCustomer
WHERE (((tblCustomer.LName)=[Please Enter Last Name]));

I want to use VBA to accomplish the same thing...I tried:

Dim strLName As String
strLName = InputBox("What is the last name", "Name?")
Me.CustCriteria = "Search for customers with the last name of " &
Me.LName
DoCmd.OpenForm "frmCustDataInput", , , "(((tblCustomer.LName)=" &
strLName & "))"

I've tried several different derivitives of the WHERE clause with no
success thus far...What am I doing wrong?

 

[Rick Brandt]

I have a form that is populated using this parameter query:

SELECT tblCustomer.*
FROM tblCustomer
WHERE (((tblCustomer.LName)=[Please Enter Last Name]));

I want to use VBA to accomplish the same thing...I tried:

Dim strLName As String
strLName = InputBox("What is the last name", "Name?")
Me.CustCriteria = "Search for customers with the last name of " &
Me.LName
DoCmd.OpenForm "frmCustDataInput", , , "(((tblCustomer.LName)=" &
strLName & "))"

I've tried several different derivitives of the WHERE clause with no
success thus far...What am I doing wrong?

The value is a string so it needs to be quoted.

"LName = '" & strLName & "'"

 

[Douglas J. Steele]

I have a form that is populated using this parameter query:

SELECT tblCustomer.*
FROM tblCustomer
WHERE (((tblCustomer.LName)=[Please Enter Last Name]));

I want to use VBA to accomplish the same thing...I tried:

Dim strLName As String
strLName = InputBox("What is the last name", "Name?")
Me.CustCriteria = "Search for customers with the last name of " &
Me.LName
DoCmd.OpenForm "frmCustDataInput", , , "(((tblCustomer.LName)=" &
strLName & "))"

I've tried several different derivitives of the WHERE clause with no
success thus far...What am I doing wrong?

The value is a string so it needs to be quoted.

"LName = '" & strLName & "'"

Because it's possible that the name might include an apostrophe (O'Reilly),
it's probably better to use:

"LName = """ & strLName & """"

or

"LName = " & Chr$(34) & strLName & Chr$(34)

 

[David F Cox]

It is not great GUI, but would not the original SQL work the same way as it
did?

(I am too pressed to try it myself right now)