SELECT Statement syntax

[ian_w]

Seems like a silly question but I can't work it out !

How can I set a SELECT criteria to be LIKE "A*"

I can put "A*" inside a string and its fine but I don't want to be needlessly using strings to hold data if I dont need to.

 

[HiTechCoach]

Access Help File states:

Note that Is and Like can't be used as comparison operators in a Select Case statement.

You might try:

  Case  "A" to "AZZZZZZZZZ"

EDIT:
My bad. I thought you were asking about the Select Case statement, not an SQL Select statement. Time for a Coffee run ...

 

[boblarson]

but I don't want to be needlessly using strings to hold data if I dont need to.

What are you talking about "needlessly using strings?" It doesn't make sense. I think you need to give more explanation because LIKE "A*" is exactly the correct syntax. You might need to use single quotes LIKE 'A*'
but you still need to encapsulate it and pass it as a string.

 

[ian_w]

When I said needlessly using strings I mean declaring strings i.e

dim option1 as string

option1 = "A*"

and then using '" & option1 & "'" when i can just type Like 'A*" as this is in a SELECT case statement so there are a fair few SELECT statements.

 

[ian_w]

Like 'A*' worked.

Thanks for your help.

 

[Brianwarnock]

Like Boyd I thought that you could not use like in a Select Case, guess I will have to give it a go.

brian

 

[boblarson]

Like Boyd I thought that you could not use like in a Select Case, guess I will have to give it a go.

brian

Not Select CASE but a SELECT STATEMENT. They didn't ask about a select case statement, but a SQL SELECT.

 

[Brianwarnock]

He said in post 4

as this is in a SELECT case statement

perhaps I misunderstood, anyway glad its sorted.

Brian

 

[boblarson]

He said in post 4


perhaps I misunderstood, anyway glad its sorted.

Brian

Guess I missed that. Whatever it was they seem to be working. It would be nice if they posted back with what they ended up using so we could see what they were indeed talking about. It would be great for our clarification so we know what's going on too

 

[ian_w]

Hi, sorry for the confusion..

It was a a SQL select I was having trouble with.

I am opening up a filtered recordset dependent on the string being passed in, a small snippet of code to explain..

Select Case Left(seria_nol, 1)
        Case ("0")
        qstr = "SELECT * FROM Serial_Numbers WHERE Serial Like '0*'"
        Case ("1")
        qstr = "SELECT * FROM Serial_Numbers WHERE Serial Like '1*'"
        Case ("2")
        qstr = "SELECT * FROM Serial_Numbers WHERE Serial LIKE '2*'"
End Select

 

[Brianwarnock]

Thanks for the feedback.

Brian

 

[DevastatioN]

I think you might have been able to get away with:

qstr = "SELECT * FROM Serial_Numbers WHERE Serial Like '" & Left(seria_nol, 1) & "*'"

Instead of using a select statement to write the SQL. Note the single quotes.