variable

[JIMMIE WHITAKER]

This works fine:

Dim STRSQL, VTEMP
VTEMP = Text3
STRSQL = "SELECT * FROM Instructors" _
& " WHERE FIRSTNAME LIKE ('s' + '%')"
Form_INSTDATA.R ecordSource = STRSQL

This does not work:
Dim STRSQL, VTEMP
VTEMP = Text3
STRSQL = "SELECT * FROM Instructors" _
& " WHERE FIRSTNAME LIKE (text3 + '%')"
Form_INSTDATA.R ecordSource = STRSQL

I tried with vtemp, quotes, no quotes, quoting every way you can possible
imagine. I spent 2 hours quoting various ways.
Tth top one works with the & " WHERE FIRSTNAME LIKE ('s' + '%')"
How do I get a variable in there instead of using a letter?
Access 2000 form and ms sql/msde tables.
Again top deal works like a champ.

[Simon Hayes]

Re: variable


"JIMMIE WHITAKER" <kpsklab@worldn et.att.net> wrote in message
news:%%DMc.1324 70$OB3.70872@bg tnsc05-news.ops.worldn et.att.net...[color=blue]
> This works fine:
>
> Dim STRSQL, VTEMP
> VTEMP = Text3
> STRSQL = "SELECT * FROM Instructors" _
> & " WHERE FIRSTNAME LIKE ('s' + '%')"
> Form_INSTDATA.R ecordSource = STRSQL
>
> This does not work:
> Dim STRSQL, VTEMP
> VTEMP = Text3
> STRSQL = "SELECT * FROM Instructors" _
> & " WHERE FIRSTNAME LIKE (text3 + '%')"
> Form_INSTDATA.R ecordSource = STRSQL
>
> I tried with vtemp, quotes, no quotes, quoting every way you can possible
> imagine. I spent 2 hours quoting various ways.
> Tth top one works with the & " WHERE FIRSTNAME LIKE ('s' + '%')"
> How do I get a variable in there instead of using a letter?
> Access 2000 form and ms sql/msde tables.
> Again top deal works like a champ.
>
>
>
>[/color]

Assuming that Text3 is just a string, then something like this should work:

STRSQL = "SELECT * FROM Instructors" _
& " WHERE FIRSTNAME LIKE ('" & Text3 & "' + '%')"

Or this may be slightly simpler:

STRSQL = "SELECT * FROM Instructors" _
& " WHERE FIRSTNAME LIKE ('" & Text3 & "%')"

In general, it's helpful in this situation to print the value of STRSQL
after you've constructed it, because then you can see what SQL is being sent
to the server - you can also paste it into Query Analyzer to help work out
what's wrong.

Simon

[JIMMIE WHITAKER]

Re: variable

Thanks, I got it working. It sure is something how the single and double
quotes have to be arranged for this. I thought I had tryed every way
possible.
The lines below is what worked: AGAIN THANK VERY MUCH.

Private Sub Command7_Click( )
Dim STRSQL
STRSQL = "SELECT * FROM Instructors" _
& " WHERE FIRSTNAME LIKE ('" & Text3 & "%')"
Form_INSTDATA.R ecordSource = STRSQL
End Sub



"Simon Hayes" <sql@hayes.ch > wrote in message
news:4103a3ee$1 _3@news.bluewin .ch...[color=blue]
>
> "JIMMIE WHITAKER" <kpsklab@worldn et.att.net> wrote in message
> news:%%DMc.1324 70$OB3.70872@bg tnsc05-news.ops.worldn et.att.net...[color=green]
> > This works fine:
> >
> > Dim STRSQL, VTEMP
> > VTEMP = Text3
> > STRSQL = "SELECT * FROM Instructors" _
> > & " WHERE FIRSTNAME LIKE ('s' + '%')"
> > Form_INSTDATA.R ecordSource = STRSQL
> >
> > This does not work:
> > Dim STRSQL, VTEMP
> > VTEMP = Text3
> > STRSQL = "SELECT * FROM Instructors" _
> > & " WHERE FIRSTNAME LIKE (text3 + '%')"
> > Form_INSTDATA.R ecordSource = STRSQL
> >
> > I tried with vtemp, quotes, no quotes, quoting every way you can[/color][/color]
possible[color=blue][color=green]
> > imagine. I spent 2 hours quoting various ways.
> > Tth top one works with the & " WHERE FIRSTNAME LIKE ('s' + '%')"
> > How do I get a variable in there instead of using a letter?
> > Access 2000 form and ms sql/msde tables.
> > Again top deal works like a champ.
> >
> >
> >
> >[/color]
>
> Assuming that Text3 is just a string, then something like this should[/color]
work:[color=blue]
>
> STRSQL = "SELECT * FROM Instructors" _
> & " WHERE FIRSTNAME LIKE ('" & Text3 & "' + '%')"
>
> Or this may be slightly simpler:
>
> STRSQL = "SELECT * FROM Instructors" _
> & " WHERE FIRSTNAME LIKE ('" & Text3 & "%')"
>
> In general, it's helpful in this situation to print the value of STRSQL
> after you've constructed it, because then you can see what SQL is being[/color]
sent[color=blue]
> to the server - you can also paste it into Query Analyzer to help work out
> what's wrong.
>
> Simon
>
>[/color]