"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

 

[irishmike94]

Just imagine other table instead of qry but it works the same as that. That is not the problem

 

[Joe4]

I am trying to get some base line working to start from.
Try writing the query yourself with the single criteria (using the Table you first mentioned, not the Query), and see if you can get that working.
Then post that SQL code here. I want to see how it differs from the first criteria code I provided. That may hold a clue as to what is going on.

Also, ae any non-text entries in the Team? field? (I assume it is all text).

 

[irishmike94]

Okay bro I'll try in the morning, I've had enough for today, thanks for all your support

 

[Joe4]

The key to debugging something like this is start simple (maybe even no criteria at all) and then add them in, one at a time, checking each time to see if it works. At the point it "breaks", you know the last thing you added is the thing that broke it. And that is where you need to put your focus on.

If you are adding a whole bunch of criteria, and one far down the line crashes, restart creating a brand new query, addng that problematic criteria first (with no other criteria). That will confirm that is the cause of the problem, and then you can play around with it, seeing if you can get it to work without any interference from any other criteria.

Sometimes it is not the criteria that is the problem, but a data issue. So you may want to inspect the data you are placing the criteria on looking for any odd data (blanks, nulls, errors, etc).

 

[irishmike94]

I have sorted it;

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

Thanks for your support, without it, this result wouldn't have been reached, thank you all xxx

irismke94

 

[Joe4]

Ah, so it looks like it might have been an issue with the precedence of AND and OR, and parentheses. Glad you got it figured out!

 

[PatrickWW]

Thanks Joe this helped out a lot.