Forum Discussion
Query problem in Access
We are almost there.
General rule is that a comparison using a wildcard needs to be done using Like, whereas a comparison based on an exact match needs to be done using =
Once we cleared the problem with Access converting Like to ALike because of the ANSI92 option being set, we can now write it correctly as:
WHERE (((Volunteers.LastName) Like "C*"));
I changed it with the space Like "C")) see the copy attached
SELECT Volunteers.FirstName, Volunteers.LastName, Volunteers.PhoneNumber, Volunteers.MobilePhone
FROM Volunteers
WHERE (((Volunteers.LastName) Like "C*"));
When I select the run feature, it comes back as ALike "C*"
When I go back to the SQL it reverts back to below: The Like does not stay
SELECT Volunteers.FirstName, Volunteers.LastName, Volunteers.PhoneNumber, Volunteers.MobilePhone, Volunteers.Status
FROM Volunteers
WHERE (((Volunteers.LastName)="C*"));
OK. Thank you for all your help. I can now continue my class before we end in a couple of weeks.
Tena_Younger I'm not entirely sure how to make the option permanent because I don't know how it's being flipped back to ANSI92.
I went to options again, removed the check mark and I am going into the query design to reenter the info again.
The info came up and the Like came in front of the C*. How can I be sure the check mark does not return again.
This is what i have been waiting for. Thank you.
What, specifically, did you do between the two screenshots?What I need is not statements of outcomes, (...can't get results...) but the actual steps taken.
Thanks.
It is unchecked again. See attached.
It is checked again.
I cannot get results either way.
Tena_Younger The first suggestion we got was pretty simple and straightforward.
Close the accdb.
Close MS Access entirely.
Restart MS Access.
Open this accdb, but do NOTHING else.
Check the options again.
What do you see now?
ok thanks,
What changed? If it was unchecked, and now it's again checked and greyed out so it can't be changed, something else has to be going on. I've never heard of anything similar and am at a loss as to why that might happen.
I am going to call on the cavalry and see if we can find some additional insight.Tena_Younger Sorry, I keep forgetting we're just getting started. So your Options now look like this, correct?
If so, you should now be able to write a criteria as WHERE LastName Like "C*" and have it run correctly.
What is it that you are asking me to do regarding - to ensure that this is an accdB, not an accdE? I went back into options and I noticed the SQL greyed out box with a check no longer has a check.
George Hepworth We also need to ensure that this is an accdB, not an accdE. We also need to be sure you have it opened with Exclusive control.
This is the first time I am working in queries. What do you mean by uncheck that option in a normal accdb. I am to do what.
Tena_Younger I apologize. I thought you'd fixed that first thing and all this time it was still checked. No wonder the problem is still occurring.
It should be possible to uncheck that option in a normal accdb. However, it may be that existing queries with the ALike operator are preventing it. Let's start by making sure no existing queries have that situation. Remove any criteria of that type and then see.
