DAO Recordset

  • Thread starter Thread starter crosley
  • Start date Start date
C

crosley

Hi All,
Can anyone tell me what is wrong with this syntax I am trying to use to
search a record set.

"SELECT Last, First FROM " & "tbl_Names WHERE ([Last]) = '" & (Me.Last) & "'"
And "([First]) = '" & (Me.First) & "'")

I keep getting data type mismatch...


crosley
 
crosley said:
Hi All,
Can anyone tell me what is wrong with this syntax I am trying to use
to search a record set.

"SELECT Last, First FROM " & "tbl_Names WHERE ([Last]) = '" &
(Me.Last) & "'" And "([First]) = '" & (Me.First) & "'")

I keep getting data type mismatch...
Click to expand...

You've got a variety of mismatched quotes. Try this:

YourSQLVariable = _
"SELECT [Last], [First] FROM tbl_Names " & _
"WHERE [Last] = '" & Me.Last & _
"' And [First] = '" & Me.First & "'"

Your use of the reserved words Last and First as field names may give
trouble, but I hope enclosing them in brackets as above will prevent
that.
 
Give this a try and watch out for word wrap:

"SELECT [Last], [First] FROM tbl_Names WHERE [Last] = '" & Me.Last & "'" & "
And [First] = '" & Me.First & "'"

Remember the "&" symbol concatenates the string, or adds the strings to
together. You only need it where you are adding variables and inserting
quotes around them.

To help with syntax, write the SQL out how it would appear without
variables. After you get this right, go back and add in the variables.
 
I am afraid that does not work the full tecx is really:

Set rst = DBEngine(0)(0).OpenRecordset("SELECT Last, First FROM " &
"tbl_Names WHERE ([Last]) = '" & (Me.Last) & "'" And "([First]) = '" & (Me.
First) & "'")













Dirk said:
Hi All,
Can anyone tell me what is wrong with this syntax I am trying to use
Click to expand...
[quoted text clipped - 4 lines]
I keep getting data type mismatch...
Click to expand...

You've got a variety of mismatched quotes. Try this:

YourSQLVariable = _
"SELECT [Last], [First] FROM tbl_Names " & _
"WHERE [Last] = '" & Me.Last & _
"' And [First] = '" & Me.First & "'"

Your use of the reserved words Last and First as field names may give
trouble, but I hope enclosing them in brackets as above will prevent
that.
Click to expand...
 
Thank you much....it works wonderfully........
xRoachx said:
Give this a try and watch out for word wrap:

"SELECT [Last], [First] FROM tbl_Names WHERE [Last] = '" & Me.Last & "'" & "
And [First] = '" & Me.First & "'"

Remember the "&" symbol concatenates the string, or adds the strings to
together. You only need it where you are adding variables and inserting
quotes around them.

To help with syntax, write the SQL out how it would appear without
variables. After you get this right, go back and add in the variables.
Hi All,
Can anyone tell me what is wrong with this syntax I am trying to use to
Click to expand...
[quoted text clipped - 6 lines]
crosley
Click to expand...
Click to expand...
 
crosley said:
I am afraid that does not work the full tecx is really:

Set rst = DBEngine(0)(0).OpenRecordset("SELECT Last, First FROM
" & "tbl_Names WHERE ([Last]) = '" & (Me.Last) & "'" And "([First]) =
'" & (Me. First) & "'")


Dirk said:
You've got a variety of mismatched quotes. Try this:

YourSQLVariable = _
"SELECT [Last], [First] FROM tbl_Names " & _
"WHERE [Last] = '" & Me.Last & _
"' And [First] = '" & Me.First & "'"

Your use of the reserved words Last and First as field names may give
trouble, but I hope enclosing them in brackets as above will prevent
that.
Click to expand...
Click to expand...

You didn't use the correction I posted. This works for me:

Set rst = DBEngine(0)(0).OpenRecordset( _
"SELECT [Last], [First] FROM tbl_Names " & _
"WHERE [Last] = '" & Me.Last & _
"' And [First] = '" & Me.First & "'")
 
Back
Top