LIKE criteria not working? (1 Viewer)

vidus · Jul 22, 2010

I am trying to create a simple query to view fields that start with a certain letter.

For the criteria I have

Code:

LIKE '[A*]'

Ill give a few examples of the field data:
A10-003
E10-004
T10-006
A10-005

So as you can see, the query should return everything that starts with an A, the first and the last...

... but it returns nothing. What is wrong here? Why will this not work? I should mention, this is an access front end running sql, not sure if that makes any difference to the criteria requirements.

BobMcClellan · Jul 22, 2010

if the access front end is an .adp and you are using sql as the back end...
it should be LIKE 'A%' as opposed to LIKE 'A*'

HTH,
..BOB

vidus · Jul 22, 2010

BobMcClellan said:
if the access front end is an .adp and you are using sql as the back end...
it should be LIKE 'A%' as opposed to LIKE 'A*'

HTH,
..BOB

Thanks for the reply. I changed it to a %, but it still doesnt return any records...

LPurvis · Jul 22, 2010

Assuming you'd have mentioned if it were an ADP (not that that's by any means a totally safe assumption... :-s) - you've created a character class by surrounding the criteria in square brackets.
Unless this is your intent - you shouldn't include them.

The example Bob has supplied would actually be what you need (the LIKE 'A*' syntax I mean).

FWIW if you only included the letter in the character class then you'd have been fine. i.e. LIKE '[A]*' would work too, but is unnecessary.

Cheers.

vidus · Jul 22, 2010

LPurvis said:
Assuming you'd have mentioned if it were an ADP (not that that's by any means a totally safe assumption... :-s) - you've created a character class by surrounding the criteria in square brackets.
Unless this is your intent - you shouldn't include them.

Cheers.

Yes its an ADP. Access creates the brackets automatically. You can type like "A*", but it will convert it to LIKE '[A*]' without intervention.

And it still doesnt work with LIKE '[A]*' or LIKE '[A]%' or any combination.

Sigh...

vidus · Jul 22, 2010

Woops!! I made a mistake.. it DOES work as follows:

LIKE '[A]%'

Thanks to both of you. Good team work.

LPurvis · Jul 22, 2010

OK, first of all, please mention in the questions if a file is as radically distinct as an ADP. There's just no reason not to offer that.

Secondly where are you entering this? A View in design view?

Edit: I see you've just posted that it does work.

Glad you're sorted.

vidus · Jul 22, 2010

LPurvis said:
OK, first of all, please mention in the questions if a file is as radically distinct as an ADP. There's just no reason not to offer that.

Secondly where are you entering this? A View in design view?

Edit: I see you've just posted that it does work.

Glad you're sorted.

I wasnt aware it was so radically distinct. Not everyone is as experienced as you, forgive me.

LPurvis · Jul 22, 2010

Well, that's fair enough. It all takes time. Didn't want to imply you should have definitely known.
However it's rare that you'll get too much information presented in a question... if you know what I mean.
(You can pretty safely go to town a bit

Cheers.

vidus · Jul 22, 2010

I know what you mean. Excuse my slowness, I am switching from mdb to sql style. Still learning

If anyone is still watching this, I just realized I need to exclude the records where the field Active = False.

So maybe something like this?

LIKE '[A]%' WHERE.... I really dont know where to start.

LPurvis · Jul 22, 2010

What kind of data type is Active? If you've upsized it was perhaps a Boolean (Yes/No) field and is now a Bit column?

WHERE FirstFieldName LIKE 'A%' AND Active = 0

If this is you creating a View in the ADP then consider doing that editing in the SQL pane. The Query grid will adapt to match.

Cheers.

vidus · Jul 22, 2010

LPurvis said:
What kind of data type is Active? If you've upsized it was perhaps a Boolean (Yes/No) field and is now a Bit column?

WHERE FirstFieldName LIKE 'A%' AND Active = 0

If this is you creating a View in the ADP then consider doing that editing in the SQL pane. The Query grid will adapt to match.

Cheers.

Sorry, its a bit yes.

That works great, thanks. Easier than I though it would be... just an AND. Sometimes you think it will be more difficult that it actually is.

LPurvis · Jul 22, 2010

No worries.
The basics of SQL are indeed pretty simple and fairly standard across the differing syntax of T-SQL (as you're using at the moment from your ADP) and Jet/ACE SQL as would be in an MDB/ACCDB.

Cheers.

LIKE criteria not working? (1 Viewer)

vidus

Confused User

BobMcClellan

Giving Up Is Unacceptable

vidus

Confused User

LPurvis

AWF VIP

vidus

Confused User

vidus

Confused User

LPurvis

AWF VIP

vidus

Confused User

LPurvis

AWF VIP

vidus

Confused User

LPurvis

AWF VIP

vidus

Confused User

LPurvis

AWF VIP

Similar threads

Users who are viewing this thread