passing a variable to a SQL string

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

I have a sql string that contains variables the prtion of the string in
question looks like:

& "AND ((Phones.[Phone Type]= """ & PhoneType & """)) " _

This works OK if PhoneType is "Work" or "Home"

I'm tring to pass the phrase LIKE "*" for the case in which I want to
present all the phones.

Depending on how I try to do this I get various errors.

PhoneType was a string variable, but I have tried it as a variant as well.

???
 
& "AND ((Phones.[Phone Type]= """ & PhoneType & """)) " _

This works OK if PhoneType is "Work" or "Home"

I'm tring to pass the phrase LIKE "*" for the case in which I want to
present all the phones.
Click to expand...

strAny = "*" ' or whatever
' ...


strSQL = ".... " & _
" AND [Phone Type] LIKE """ & strAny & """ "



or just

" AND [Phone Type} LIKE ""*"" "

if the wildcard will never change. Don't forget that it's LIKE "%" when
you use ADP or SQL Server (or ADO?).

HTH


Tim F
 
Nope:
I'm not sure we are on the same page here. I have a variable PhoneType.

I'm trying to vary a recordsource that looks like

......
& "AND ((Phones.[Phone Type]= """ & PhoneType & """)) " _
& ".....;"

If PhoneType is defined as "Work" or "Home", then my recorsource string
works OK.

If PhoneTye is defined as "*", then it is interpreted as ="*" in the
recordsource. This searches for the * character and gives a wrong result,
which is to say, no results.

If I alter the string to :
......
& "AND ((Phones.[Phone Type]= LIKE """ & PhoneType & """)) " _
& ".....;"

It yields a syntax error.

I believe I am using PhoneType as you are using strAny in your example.

I was trying to pass PhoneType = LIKE & "*" where PhoneType is a variant,
but that didn't work. Putting LIKE in the string seems as if it should work
because LIKE "Work" and LIKE "Home" should still work. But when I put LIKE in
the string the record source responds with an error.


Tim Ferguson said:
& "AND ((Phones.[Phone Type]= """ & PhoneType & """)) " _

This works OK if PhoneType is "Work" or "Home"

I'm tring to pass the phrase LIKE "*" for the case in which I want to
present all the phones.
Click to expand...

strAny = "*" ' or whatever
' ...


strSQL = ".... " & _
" AND [Phone Type] LIKE """ & strAny & """ "



or just

" AND [Phone Type} LIKE ""*"" "

if the wildcard will never change. Don't forget that it's LIKE "%" when
you use ADP or SQL Server (or ADO?).

HTH


Tim F
Click to expand...
 
"AND Phones.[Phone Type] LIKE """ & PhoneType & """"

This will get all records that match "Home" or "Work" or whatever value you put
into PhoneType.

IF you set PhoneType to "*" and are using Access (with the Jet engine - an .mdb)
then you will get all records that have a phone type that is not null.
Nope:
I'm not sure we are on the same page here. I have a variable PhoneType.

I'm trying to vary a recordsource that looks like

.....
& "AND ((Phones.[Phone Type]= """ & PhoneType & """)) " _
& ".....;"

If PhoneType is defined as "Work" or "Home", then my recorsource string
works OK.

If PhoneTye is defined as "*", then it is interpreted as ="*" in the
recordsource. This searches for the * character and gives a wrong result,
which is to say, no results.

If I alter the string to :
.....
& "AND ((Phones.[Phone Type]= LIKE """ & PhoneType & """)) " _
& ".....;"

It yields a syntax error.

I believe I am using PhoneType as you are using strAny in your example.

I was trying to pass PhoneType = LIKE & "*" where PhoneType is a variant,
but that didn't work. Putting LIKE in the string seems as if it should work
because LIKE "Work" and LIKE "Home" should still work. But when I put LIKE in
the string the record source responds with an error.

Tim Ferguson said:
& "AND ((Phones.[Phone Type]= """ & PhoneType & """)) " _

This works OK if PhoneType is "Work" or "Home"

I'm tring to pass the phrase LIKE "*" for the case in which I want to
present all the phones.
Click to expand...

strAny = "*" ' or whatever
' ...


strSQL = ".... " & _
" AND [Phone Type] LIKE """ & strAny & """ "



or just

" AND [Phone Type} LIKE ""*"" "

if the wildcard will never change. Don't forget that it's LIKE "%" when
you use ADP or SQL Server (or ADO?).

HTH


Tim F
Click to expand...
Click to expand...
 
I tried to use LIKE in the string as you suggest, seems like it ought to
work, and it does if I create a QBE that evaluates to this sxpression.
Whenever I try to use LIKE in the recordsource string the VB editor generates
a Syntax error or it twlls me it expected an expression.

???

John Spencer (MVP) said:
"AND Phones.[Phone Type] LIKE """ & PhoneType & """"

This will get all records that match "Home" or "Work" or whatever value you put
into PhoneType.

IF you set PhoneType to "*" and are using Access (with the Jet engine - an .mdb)
then you will get all records that have a phone type that is not null.
Nope:
I'm not sure we are on the same page here. I have a variable PhoneType.

I'm trying to vary a recordsource that looks like

.....
& "AND ((Phones.[Phone Type]= """ & PhoneType & """)) " _
& ".....;"

If PhoneType is defined as "Work" or "Home", then my recorsource string
works OK.

If PhoneTye is defined as "*", then it is interpreted as ="*" in the
recordsource. This searches for the * character and gives a wrong result,
which is to say, no results.

If I alter the string to :
.....
& "AND ((Phones.[Phone Type]= LIKE """ & PhoneType & """)) " _
& ".....;"

It yields a syntax error.

I believe I am using PhoneType as you are using strAny in your example.

I was trying to pass PhoneType = LIKE & "*" where PhoneType is a variant,
but that didn't work. Putting LIKE in the string seems as if it should work
because LIKE "Work" and LIKE "Home" should still work. But when I put LIKE in
the string the record source responds with an error.

Tim Ferguson said:
"=?Utf-8?B?TWlhcGxhY2lkdXM=?=" <[email protected]>
wrote in
& "AND ((Phones.[Phone Type]= """ & PhoneType & """)) " _

This works OK if PhoneType is "Work" or "Home"

I'm tring to pass the phrase LIKE "*" for the case in which I want to
present all the phones.


strAny = "*" ' or whatever
' ...


strSQL = ".... " & _
" AND [Phone Type] LIKE """ & strAny & """ "



or just

" AND [Phone Type} LIKE ""*"" "

if the wildcard will never change. Don't forget that it's LIKE "%" when
you use ADP or SQL Server (or ADO?).

HTH


Tim F
Click to expand...
Click to expand...
Click to expand...
 
Oh, Nevermind.

I had = LIKE """ & PhoneType & """

John Spencer (MVP) said:
"AND Phones.[Phone Type] LIKE """ & PhoneType & """"

This will get all records that match "Home" or "Work" or whatever value you put
into PhoneType.

IF you set PhoneType to "*" and are using Access (with the Jet engine - an .mdb)
then you will get all records that have a phone type that is not null.
Nope:
I'm not sure we are on the same page here. I have a variable PhoneType.

I'm trying to vary a recordsource that looks like

.....
& "AND ((Phones.[Phone Type]= """ & PhoneType & """)) " _
& ".....;"

If PhoneType is defined as "Work" or "Home", then my recorsource string
works OK.

If PhoneTye is defined as "*", then it is interpreted as ="*" in the
recordsource. This searches for the * character and gives a wrong result,
which is to say, no results.

If I alter the string to :
.....
& "AND ((Phones.[Phone Type]= LIKE """ & PhoneType & """)) " _
& ".....;"

It yields a syntax error.

I believe I am using PhoneType as you are using strAny in your example.

I was trying to pass PhoneType = LIKE & "*" where PhoneType is a variant,
but that didn't work. Putting LIKE in the string seems as if it should work
because LIKE "Work" and LIKE "Home" should still work. But when I put LIKE in
the string the record source responds with an error.

Tim Ferguson said:
"=?Utf-8?B?TWlhcGxhY2lkdXM=?=" <[email protected]>
wrote in
& "AND ((Phones.[Phone Type]= """ & PhoneType & """)) " _

This works OK if PhoneType is "Work" or "Home"

I'm tring to pass the phrase LIKE "*" for the case in which I want to
present all the phones.


strAny = "*" ' or whatever
' ...


strSQL = ".... " & _
" AND [Phone Type] LIKE """ & strAny & """ "



or just

" AND [Phone Type} LIKE ""*"" "

if the wildcard will never change. Don't forget that it's LIKE "%" when
you use ADP or SQL Server (or ADO?).

HTH


Tim F
Click to expand...
Click to expand...
Click to expand...
 
The problem is that you have '= LIKE' , there should be no 'equals' in the
expression. If you look closely at Johns reply you will see the correct
syntax.

Miaplacidus said:
Oh, Nevermind.

I had = LIKE """ & PhoneType & """

John Spencer (MVP) said:
"AND Phones.[Phone Type] LIKE """ & PhoneType & """"

This will get all records that match "Home" or "Work" or whatever value you put
into PhoneType.

IF you set PhoneType to "*" and are using Access (with the Jet engine - an .mdb)
then you will get all records that have a phone type that is not null.
Nope:
I'm not sure we are on the same page here. I have a variable PhoneType.

I'm trying to vary a recordsource that looks like

.....
& "AND ((Phones.[Phone Type]= """ & PhoneType & """)) " _
& ".....;"

If PhoneType is defined as "Work" or "Home", then my recorsource string
works OK.

If PhoneTye is defined as "*", then it is interpreted as ="*" in the
recordsource. This searches for the * character and gives a wrong result,
which is to say, no results.

If I alter the string to :
.....
& "AND ((Phones.[Phone Type]= LIKE """ & PhoneType & """)) " _
& ".....;"

It yields a syntax error.

I believe I am using PhoneType as you are using strAny in your example.

I was trying to pass PhoneType = LIKE & "*" where PhoneType is a variant,
but that didn't work. Putting LIKE in the string seems as if it should work
because LIKE "Work" and LIKE "Home" should still work. But when I put LIKE in
the string the record source responds with an error.

:

"=?Utf-8?B?TWlhcGxhY2lkdXM=?=" <[email protected]>
wrote in
& "AND ((Phones.[Phone Type]= """ & PhoneType & """)) " _

This works OK if PhoneType is "Work" or "Home"

I'm tring to pass the phrase LIKE "*" for the case in which I want to
present all the phones.


strAny = "*" ' or whatever
' ...


strSQL = ".... " & _
" AND [Phone Type] LIKE """ & strAny & """ "



or just

" AND [Phone Type} LIKE ""*"" "

if the wildcard will never change. Don't forget that it's LIKE "%" when
you use ADP or SQL Server (or ADO?).

HTH


Tim F
Click to expand...
Click to expand...
Click to expand...
 
Docmd.RunSQL “DELETE CASE_TARGETS.DATE_OF_BIRTH FROM CASE_TARGETS WHERE
CASE_TARGETS.DATE_OF_BIRTH = “ & [Form_XP]![Target_DoB] & “;â€

You might try the Docmd.runwql "SQL"
 
Back
Top