Query problem in Access

[Tena_Younger]

I am taking a college course of Access and my instructor told me what to do and I checked various ms access videos and was told the same info.  Everything is going haywire with my office Access 365.  When I click on Query design, I do not get the show table.  No show table appeared and there was no show table button.  I noticed Add tables appeared on the right side of the page and that was how i entered the table.  But when I went to the criteria in the last name field and entered C*, it appears as "C*".  The Like does not appear. If I add the like it appears as Alike "C*".  I cannot do a criteria.  What is wrong.

Thank you,

[#2]

@Tena_Younger 

Up to this point, pretty much what you've described is expected.

This, however, suggests an option was set that you probably want to change.

 Alike "C*".

That's the operator for a different version of SQL, not the default, or standard SQL syntax normally used in Access.

Check here:

If this checkboxes are ticked, that's probably the reason for the ALike operator instead of the Like operator being offered. Uncheck it and see if that doesn't correct the problem.

[#3]

@George Hepworth 

There was a check in Default for new databases which I unchecked but no change.  This is what my instructor and your access video is telling me.  

With a wild card, represented by the asterisk (*), placed after the C in the Criteria row. This tells Access, "I know the first letter but nothing after that. So give me all records with C at the beginning of the LastName data, regardless of how each one ends."

So click in the Criteria row, delete the C, and type:

C*

Don't put a space after the C—you're just typing the C and, immediately after it, the asterisk (*). That's it. Then press ENTER or click into another cell in the grid, and the following appears:

Microsoft did a repair on the program and nothing changed.  I cannot get this to appear as it should.  I don't know what the problem is.  I replied yesterday but I guess I did it incorrect.

Thank you,

Tena

[#4]

@Tena_Younger 

Okay, you corrected the problem with the ANSI 92 SQL syntax and you are still not getting results from the query with the appropriate syntax.

The next step is to verify that no other parameters exist in this query. (I've had that happen myself. You can't see all of the columns in the query in the query design grid because they are off to the right, not showing.)

If you can verify that, and still no results, please confirm that there are actually records in the table which have last names starting with C.

And finally, verify that the query is based on a single table (Volunteers), or if it joins Volunteers to another table that the result of that join to additional table(s) doesn't eliminate those records which would otherwise be returned.

[#5]

@George Hepworth 

I spoke with someone in Microsoft and he saw the problem.  Yes there are names in access which begin with C and still not working. He put me in touch with uservoice.  I'll see what happens there.

Thank you,

Tena

[#6]

@Tena_Younger HOW did this support person "see" the problem? 

You should be aware that UserVoice is NOT intended to be a place for requesting support or reporting problems. It is a place where you can submit suggestions or requests for new features in Access. I am not too confident that an Access support person who sent you there for this problem really has all that much relevant experience.

But you didn't address the possible reasons I offered for one particular query not to return the desired records. One was that there might not be values in the field that match your criteria. But the other is that you may be joining two (or more) tables in the query which results in the records you want not actually being available. Can you show us the ENTIRE SQL from this query?

Thank you.

[#7]

@George Hepworth 

Hello George,

I see what you mean about uservoice.  I don't understand what you mean about - The next step is to verify that no other parameters exist in this query.  The following is the names in file: we have 3 names beginning with C.

I have 2 relationships Events ID, Locations, and Volunteers.  They are to location Id and Volunteer numbers only.  Is it possible you could look at my computer.  i am a novice at most of this,

Thank you,

[#8]

@Tena_Younger As a volunteer participant in this forum, I would not be willing to do any sort of remote connection, sorry. The risk is too great for both of us to have either of us getting access to other people's computers, IMO.

Can you open this query to SQL view and copy the entire SQL Statement and paste it here? That'll be the quickest way, I think. 

What we're looking for in addition to the WHERE clause is any JOINS between tables, or if there is only one table in the query.

[#9]

@George Hepworth 

Since I did not understand what you were asking me to do, I called my computer person and he told me to do the following:

I don't see the parentheses in front of select.  Does this help with the info you were looking for?

[#10]

@Tena_Younger 

Sorry, let's step back a bit further. I want to see the actual query that is giving you trouble. I believe in your initial screen shot you should a few columns from a table called "Volunteer".  That's the query we need to work with. Do you still have it saved?

Can you find the original one and open it in design view? It should look like your first screen shot at that point. Now I need you to change to SQL view from the query design grid view. 

When you see the SQL, copy it completely and paste it here.

Thanks.

[#11]

@George Hepworth 

This is the SQL

[#12]

@Tena_Younger 

I thought you wanted the WHERE clause to be 

WHERE tblVolunteers.LastName Like "C*" 

Instead, it looks like this SQL is trying to find only LastNames "C", which don't exist of course.

We did start out trouble-shooting the problem of ANSI-92 incorrectly converting Like to ALike, which we resolved, but now we have to use the Like Operator with the WildCard *....

[#13]

@George Hepworth 

Don't put a space after the C—you're just typing the C and, immediately after it, the asterisk (*). That's it. Then press ENTER or click into another cell in the grid, and the following appears:

C* changes to Like C*, turning the wild card into search criteria Access understands

When I put Like in front of the C* it becomes Alike.  

This is the info my professor sent me when I told her what was happening.

Access will put quotes around it because it's criteria in a text (short text) field. It should still work. Don't type like by yourself, just type the C* and let it go.

If you do that, does it return the record/s you expect? If not, is there criteria in any other column that could be contradicting the criteria you're trying to set?

[#14]

@Tena_Younger 

Hi. One last time.

I assume that what you last posted is the instructions given to you in the tutorial. They look right, but what you and I need to focus on is the ACTUAL SQL in the query.

As I asked before, what we need to see is a copy-and-paste of that SQL from the query so we can help you adjust it. Can you provide that SQL (not a screenshot of the query grid, the actual SQL)? 

It should look something like this:

SELECT Field1, Field2, Field3
FROM tblYourTableNameGoesHere

WHERE Field2 Like "C*"

Of course, yours will have the table and field names from your table, i.e. "tblVolunteer" and "LastName".

Thank you.

[#15]

@George Hepworth 

When I go to create, design view, I enter all my info again, I go to view and click on SQL view and the screen shot I sent you is what I get.  Should I be doing something different.

[#16]

@Tena_Younger 

Okay, is it possible to do a copy and paste on that SQL? I want to see it, not hear about it.

Thank you.

[#17]

@George Hepworth 

SELECT Volunteers.FirstName, Volunteers.LastName, Volunteers.PhoneNumber, Volunteers.MobilePhone
FROM Volunteers
WHERE (((Volunteers.LastName)="C*"));

[#18]

@Tena_Younger 

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*"));

[#19]

@George Hepworth 

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*"));

[#20]

@Tena_Younger  Didn't we already change that?  Make sure NEITHER option is checked.