"Data type mismatch in criteria expression" need help in query

[irishmike94]

Hi guys,

I wanted to add a query from a rugby player database with position (number) and team (either A or B). I want to run a query for players in the A team and with positions 1, 2 and 3. So the criteria are based on two fields of data. However when I try to do "A" in team, it works perfectly showing all A team players, and yet, when I add "1", "2", "3" to the position column it shows an error saying "Data type mismatch in criteria expression".

I don't know how to solve this error, and I could really use some help!!

Thanks in advance;

irishmike94

 

[Joe4]

Is the Position field Text or Numeric?
If it is numeric, try your numbers without the quotes (text qualifiers) in your criteria box, i.e.
enter
1
instead of
"1"

 

[irishmike94]

Hiya, I have tried that aswell but no luck, still showing message

 

[Joe4]

How is your Position field formatted?
Are there any blanks or NULLs in this field, or are all entries numeric?
Can you post the SQL code that you are trying that is erroring out?
(change your Query to SQL View and copy and paste the code here).

 

[irishmike94]

It is in number format, there are no NULL values, here is the SQL values;


SELECT Player_Database.[RFU Number], Player_Database.[FirstName#], Player_Database.Surname, Player_Database.DoB, Player_Database.School, Player_Database.[Mobile No], Player_Database.[Home Phone No], Player_Database.Email, Player_Database.[House Name or number], Player_Database.Town, Player_Database.[City (Optional)], Player_Database.County, Player_Database.Postcode, Player_Database.[Medical Issues], Player_Database.[Medical Issues Cont], Player_Database.[Back/forward], Player_Database.Position, Player_Database.[Team?]
FROM Player_Database
WHERE (((Player_Database.[Team?])="A")) OR (((Player_Database.Position)="1")) OR (((Player_Database.Position)="2")) OR (((Player_Database.Position)="3"));

 

[Joe4]

Tell me if any of these work (replace your criteria with the new ones below):

1st try:

WHERE (((Player_Database.[Team?])="A"));

2nd try:

WHERE (((Player_Database.[Team?])="A")) OR (((Player_Database.Position)=1)) OR (((Player_Database.Position)=2)) OR (((Player_Database.Position)=3));

3rd try:

WHERE ((Player_Database.[Team?])="A") OR ((Player_Database.Position) In (1,2,3));

 

[irishmike94]

None of them worked

 

[Joe4]

I do not understand how the first one won't work. In your first post, you said:

However when I try to do "A" in team, it works perfectly showing all A team players

Are you able to get it to work for the one criteria or not (that is all the first code does)? Remember, you are only replacing the WHERE clause of the original query with the WHERE clause I provided. So make sure you are doing that correctly (and there is a space before the word WHERE after you paste that line of code in and view the results).

If you still can't get it to work, did what you did before to get the single criteria to work and paste that SQL code here.

 

[irishmike94]

I previously made a query for A team player exclusively using the SQL code;

WHERE (((QryFullNamePlayer.[Team?])="A"));

And this was successful.

However the problem comes when adding the second set of criteria for positions in this new query. For some reason access is not letting me do it!!!!

 

[Joe4]

WHERE (((QryFullNamePlayer.[Team?])="A"));

How can that possibly work?
"QryFullNamePlayer" is not even an object in the SQL code you posted. "Player_Database" is the only object in that query.

Are you sure you are mixing/confusing two different queries together?