I’ve used Access 2003 for a while now and it didn’t seem too different to the new 2007 edition. Except I can’t design a query now.
I have a table with a column with entries from a drop down menu. I want Access to pull out all the records with entries of a certain sort from this menu, every time I type an entry from this drop down menu in the query design, it tells me that there is some sort of “Date type mismatch in criteria expression”
Also, when I run a mail merge using the same database, Word 2007 will display a number in the merge field when I try to use a field from one of these drop down menus, how come?
The error message says “Data” type mismatch, not “Date”… right?
It looks like the table stores a code but has a lookup constructed (in the table design) to show meaningful values. Usually this is a foreign key.
For example you are storing a colour code into your Cars table. The colour code comes from the Colour table:
1 - Red
2 - Blue
3 - Yellow
Note that 1,2,3 are the values that will be stored in Car records, but because the lookup is defined you only see “Red”, “Blue” and “Yellow” when you select them. This is why Word is getting numbers.
Now, in the query you have to type the numbers as well even though you have gone to great pains never to see them. An inconsistency that has been in Access since day one. By the way, when you use a “Filter by Form” which is very similar to a query, Access will help you choose values by giving you the drop down choices so that you don’t have to know the numbers.
But it doesn’t work that way in the query. If you type in “Red”, the data type for that field is number and you then get the “Data type mismatch…” error.
Yes, that’s what I have in the tables the drop down menus look up, an autonumber ID beside the word values. I’ll try removing the ID when I get back to work to see if that makes a difference.
There’s a workaround. Set up a two column dropdown, with one column containing the English, and the second column containing the corresponding numbers. Set the visible width of column 2 to 0, so it won’t appear on-screen. Set the code to look at column two for data. Voila, seamless.
