LIKE criteria not working?

[vidus]

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

For the criteria I have

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]

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]

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]

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]

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]

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

LIKE '[A]%'

Thanks to both of you. Good team work.

 

[LPurvis]

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]

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]

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]

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]

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]

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]

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.