SELECT Statement syntax (1 Viewer)

ian_w

Registered User.
Local time
Today, 01:00
Joined
Jun 13, 2006
Messages
64
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

Well-known member
Local time
, 19:00
Joined
Mar 6, 2006
Messages
4,357
Access Help File states:

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


You might try:

Code:
  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 ...
 
Last edited:

boblarson

Smeghead
Local time
, 17:00
Joined
Jan 12, 2001
Messages
32,059
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

Registered User.
Local time
Today, 01:00
Joined
Jun 13, 2006
Messages
64
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

Registered User.
Local time
Today, 01:00
Joined
Jun 13, 2006
Messages
64
Like 'A*' worked.

Thanks for your help.
 

Brianwarnock

Retired
Local time
Today, 01:00
Joined
Jun 2, 2003
Messages
12,701
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

Smeghead
Local time
, 17:00
Joined
Jan 12, 2001
Messages
32,059
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.
 

boblarson

Smeghead
Local time
, 17:00
Joined
Jan 12, 2001
Messages
32,059
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 :D
 

ian_w

Registered User.
Local time
Today, 01:00
Joined
Jun 13, 2006
Messages
64
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..

Code:
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
 

DevastatioN

Registered User.
Local time
, 21:00
Joined
Nov 21, 2007
Messages
242
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.
 

Users who are viewing this thread

Top Bottom