what sql string has to be?

Hi all,
I try to query the record MemberID = <some number as long type>. I don't
know what symbol I have to patch to the sql string to open the DAO
recordset, can someone shed some light on this please.

Here's my sample code

when I use the single quote <'>, it give me error: 3464 datatype mismatch in
criteria expression
sql = "SELECT * FROM TblMembers WHERE MemberID = " & "'" & s & "'"
Set rs = db.OpenRecordset(sql, dbOpenDynaset)

when I use the number sign <#>, it give me error: 3075 Syntax error in date
in query expression 'MemberID = #232#'
sql = "SELECT * FROM TblMembers WHERE MemberID = " & "#" & s & "#"
Set rs = db.OpenRecordset(sql, dbOpenDynaset)

The data type defined for MemberID is LONG

Thanks for your help
Jing.

re: what sql string has to be?

The ' is used to simplify putting quotes (") around strings instead of using
double up quotes to generate this. It make it easier to read, but is still a
string delimiter. The # is a date delimiter. Numbers need no delimiter.
Since you say that MemberID is a Long Interger then I assume the s is a
variable holding a long integer.

sql = "SELECT * FROM TblMembers WHERE MemberID = " & s

--
Wayne Morgan
MS Access MVP


"JingleBEV" <n_quan@NOSPAMyahoo.com> wrote in message
news:4mwHc.48050$JG5.1238447@news20.bellglobal.com ...[color=blue]
> Hi all,
> I try to query the record MemberID = <some number as long type>. I don't
> know what symbol I have to patch to the sql string to open the DAO
> recordset, can someone shed some light on this please.
>
> Here's my sample code
>
> when I use the single quote <'>, it give me error: 3464 datatype mismatch[/color]
in[color=blue]
> criteria expression
> sql = "SELECT * FROM TblMembers WHERE MemberID = " & "'" & s & "'"
> Set rs = db.OpenRecordset(sql, dbOpenDynaset)
>
> when I use the number sign <#>, it give me error: 3075 Syntax error in[/color]
date[color=blue]
> in query expression 'MemberID = #232#'
> sql = "SELECT * FROM TblMembers WHERE MemberID = " & "#" & s & "#"
> Set rs = db.OpenRecordset(sql, dbOpenDynaset)
>
> The data type defined for MemberID is LONG
>
> Thanks for your help
> Jing.
>
>[/color]

re: what sql string has to be?

"JingleBEV" <n_quan@NOSPAMyahoo.com> wrote in message
news:4mwHc.48050$JG5.1238447@news20.bellglobal.com ...[color=blue]
> Hi all,
> I try to query the record MemberID = <some number as long type>. I don't
> know what symbol I have to patch to the sql string to open the DAO
> recordset, can someone shed some light on this please.[/color]

Strings and dates need to be delimited. Numbers do not need a delimiter at
all so just leave them off and your SQL should work.


sql = "SELECT * FROM TblMembers WHERE MemberID = " & s


--
I don't check the Email account attached
to this message. Send instead to...
RBrandt at Hunter dot com