Query criteria not returning results - "Data type mismatch..."

I have a query that returns scheduling information about an employee. The
table for scheduling includes a look-up column with all employee names, and
another look-up column with store #s that the employee could working at.
There are several other fields, but those are the fields that seem to be
giving me trouble - when I try to run a query with criteria that specifies a
store # (for. ex. =22) or last name (ex. =Smith) either nothing returns, or I
get an error message "Data type mismatch in criteria expression".

I think that it has to do with my using look-up columns for those fields.
If so, please let me know how to change those to something that is not a
look-up column, but would offer a list of employee names and store #s.

Thanks,
Kate

Start by changing the LookUp fields to TextBoxes in the table and you will
see why your queries are not working as you expect.
http://www.mvps.org/access/lookupfields.htm


KateCee wrote:
I have a query that returns scheduling information about an employee. The
table for scheduling includes a look-up column with all employee names, and
another look-up column with store #s that the employee could working at.
There are several other fields, but those are the fields that seem to be
giving me trouble - when I try to run a query with criteria that specifies a
store # (for. ex. =22) or last name (ex. =Smith) either nothing returns, or I
get an error message "Data type mismatch in criteria expression".

I think that it has to do with my using look-up columns for those fields.
If so, please let me know how to change those to something that is not a
look-up column, but would offer a list of employee names and store #s.

Thanks,
Kate

--
HTH - RuralGuy (RG for short) acXP WinXP Pro
Please post back to this forum so all may benefit.

Message posted via AccessMonster.com
http://www.accessmonster.com/Uwe/For...forms/200608/1

Hi Kate

You need to set the criteria for the "bound" column of the lookup field.

If you have set the bound colum to 1 and you have 3 columns
0cm;1cm;0cm
You will "see" the 2nd column but the "stored" data is in the 1st so that is
what you need to filter.

Hope this helps


--
Wayne
Manchester, England.
Not an expert.
Enjoy whatever it is you do.



"KateCee" wrote:

I have a query that returns scheduling information about an employee. The
table for scheduling includes a look-up column with all employee names, and
another look-up column with store #s that the employee could working at.
There are several other fields, but those are the fields that seem to be
giving me trouble - when I try to run a query with criteria that specifies a
store # (for. ex. =22) or last name (ex. =Smith) either nothing returns, or I
get an error message "Data type mismatch in criteria expression".

I think that it has to do with my using look-up columns for those fields.
If so, please let me know how to change those to something that is not a
look-up column, but would offer a list of employee names and store #s.

Thanks,
Kate

I went back into my table and changed the 2 look-up columns to "text" data
type. Now, when I try to run the same query, the query comes up blank. In
this query, I am trying to take fields from a Scheduling table and fields
from a StoreDetectives table.

Could my "relationships" be off? I have a relationship between
StoreDetectiveID (primary key, autonumber) from my StoreDetective table and
StoreDetectiveID (not a primary key, detective's last name, currently NOT a
list box).

Thanks so much, your help would be greatly appreciated!

Kate

"ruralguy via AccessMonster.com" wrote:

Start by changing the LookUp fields to TextBoxes in the table and you will
see why your queries are not working as you expect.
http://www.mvps.org/access/lookupfields.htm


KateCee wrote:
I have a query that returns scheduling information about an employee. The
table for scheduling includes a look-up column with all employee names, and
another look-up column with store #s that the employee could working at.
There are several other fields, but those are the fields that seem to be
giving me trouble - when I try to run a query with criteria that specifies a
store # (for. ex. =22) or last name (ex. =Smith) either nothing returns, or I
get an error message "Data type mismatch in criteria expression".

I think that it has to do with my using look-up columns for those fields.
If so, please let me know how to change those to something that is not a
look-up column, but would offer a list of employee names and store #s.

Thanks,
Kate

--
HTH - RuralGuy (RG for short) acXP WinXP Pro
Please post back to this forum so all may benefit.

Message posted via AccessMonster.com
http://www.accessmonster.com/Uwe/For...forms/200608/1

Hi again

Relationships must be formed between field of the same "type" i.e. An
autonumber can be linked to a number field in another table. Text - text,
Date - Date, etc.

If you have simply change the lookup fields to text then you may have simply
saved to old bound column (maybe the autonumber ID) as text and this will
mess up the filtering on your query.

Go back into your table and look to see what type of field they are. "look"
at the content and then go to you query and re-do the criteria.



--
Wayne
Manchester, England.
Not an expert.
Enjoy whatever it is you do.



"KateCee" wrote:

I went back into my table and changed the 2 look-up columns to "text" data
type. Now, when I try to run the same query, the query comes up blank. In
this query, I am trying to take fields from a Scheduling table and fields
from a StoreDetectives table.

Could my "relationships" be off? I have a relationship between
StoreDetectiveID (primary key, autonumber) from my StoreDetective table and
StoreDetectiveID (not a primary key, detective's last name, currently NOT a
list box).

Thanks so much, your help would be greatly appreciated!

Kate

"ruralguy via AccessMonster.com" wrote:

Start by changing the LookUp fields to TextBoxes in the table and you will
see why your queries are not working as you expect.
http://www.mvps.org/access/lookupfields.htm


KateCee wrote:
I have a query that returns scheduling information about an employee. The
table for scheduling includes a look-up column with all employee names, and
another look-up column with store #s that the employee could working at.
There are several other fields, but those are the fields that seem to be
giving me trouble - when I try to run a query with criteria that specifies a
store # (for. ex. =22) or last name (ex. =Smith) either nothing returns, or I
get an error message "Data type mismatch in criteria expression".

I think that it has to do with my using look-up columns for those fields.
If so, please let me know how to change those to something that is not a
look-up column, but would offer a list of employee names and store #s.

Thanks,
Kate

--
HTH - RuralGuy (RG for short) acXP WinXP Pro
Please post back to this forum so all may benefit.

Message posted via AccessMonster.com
http://www.accessmonster.com/Uwe/For...forms/200608/1

Hi Kate,
It is not easy to tell where your issue lies. You should now be able to view
your tables in DataSheet view and see what kind of data is in the fields you
have criteria for. Maybe post your SQL for the query here and we can see it
enough to offer suggestions.

KateCee wrote:
I went back into my table and changed the 2 look-up columns to "text" data
type. Now, when I try to run the same query, the query comes up blank. In
this query, I am trying to take fields from a Scheduling table and fields
from a StoreDetectives table.

Could my "relationships" be off? I have a relationship between
StoreDetectiveID (primary key, autonumber) from my StoreDetective table and
StoreDetectiveID (not a primary key, detective's last name, currently NOT a
list box).

Thanks so much, your help would be greatly appreciated!

Kate

Start by changing the LookUp fields to TextBoxes in the table and you will
see why your queries are not working as you expect.
[quoted text clipped - 14 lines]
Thanks,
Kate

--
HTH - RuralGuy (RG for short) acXP WinXP Pro
Please post back to this forum so all may benefit.

Message posted via AccessMonster.com
http://www.accessmonster.com/Uwe/For...forms/200608/1

Sorry about my english

Gli inglesi non è la mia prima lingua


--
Wayne
Manchester, England.
Not an expert.
Enjoy whatever it is you do.



"ruralguy via AccessMonster.com" wrote:

Hi Kate,
It is not easy to tell where your issue lies. You should now be able to view
your tables in DataSheet view and see what kind of data is in the fields you
have criteria for. Maybe post your SQL for the query here and we can see it
enough to offer suggestions.

KateCee wrote:
I went back into my table and changed the 2 look-up columns to "text" data
type. Now, when I try to run the same query, the query comes up blank. In
this query, I am trying to take fields from a Scheduling table and fields
from a StoreDetectives table.

Could my "relationships" be off? I have a relationship between
StoreDetectiveID (primary key, autonumber) from my StoreDetective table and
StoreDetectiveID (not a primary key, detective's last name, currently NOT a
list box).

Thanks so much, your help would be greatly appreciated!

Kate

Start by changing the LookUp fields to TextBoxes in the table and you will
see why your queries are not working as you expect.
[quoted text clipped - 14 lines]
Thanks,
Kate

--
HTH - RuralGuy (RG for short) acXP WinXP Pro
Please post back to this forum so all may benefit.

Message posted via AccessMonster.com
http://www.accessmonster.com/Uwe/For...forms/200608/1

Hi RG & Wayne,

I followed Wayne's suggestions to change the relationships, and RG's
suggestion to post my SQL for the troublesome query. I am now seeing one
record that I had started to enter on another table, but I am not able to
type anything in this query datasheet, I just get an error noise.

I made the changes in my relationships, hoping that helped some. Please
help! Any suggestions would be greatly appreciated, as this database is due
by Friday and I am nowhere near being able to deliver a functioning database.

SELECT [OnlyScheduling].[ScheduleID], [OnlyScheduling].[StoreDetective],
[OnlyScheduling].[Week-ending date], [OnlyScheduling].[SundayStart],
[OnlyScheduling].[SundayEnd], [OnlyScheduling].[SunStore],
[OnlyScheduling].[MondayStart], [OnlyScheduling].[MondayEnd],
[OnlyScheduling].[MonStore], [OnlyScheduling].[TuesdayStart],
[OnlyScheduling].[TuesdayEnd], [OnlyScheduling].[TuesStore],
[OnlyScheduling].[WednesdayStart], [OnlyScheduling].[WednesdayEnd],
[OnlyScheduling].[WedStore], [OnlyScheduling].[ThursdayStart],
[OnlyScheduling].[ThursdayEnd], [OnlyScheduling].[ThursStore],
[OnlyScheduling].[FridayStart], [OnlyScheduling].[FridayEnd],
[OnlyScheduling].[FriStore], [OnlyScheduling].[SaturdayStart],
[OnlyScheduling].[SaturdayEnd], [OnlyScheduling].[SatStore], [Store
Detectives].[StoreDetectiveID], [Store Detectives].[LastName], [Store
Detectives].[FirstName], [Store Detectives].[MiddleInitial], [Store
Detectives].[HomeStore], [Store Detectives].[FieldManager], [Store
Detectives].[Position]
FROM [Store Detectives] INNER JOIN OnlyScheduling ON [Store
Detectives].[LastName] =[OnlyScheduling].[StoreDetective];



"ruralguy via AccessMonster.com" wrote:

Hi Kate,
It is not easy to tell where your issue lies. You should now be able to view
your tables in DataSheet view and see what kind of data is in the fields you
have criteria for. Maybe post your SQL for the query here and we can see it
enough to offer suggestions.

KateCee wrote:
I went back into my table and changed the 2 look-up columns to "text" data
type. Now, when I try to run the same query, the query comes up blank. In
this query, I am trying to take fields from a Scheduling table and fields
from a StoreDetectives table.

Could my "relationships" be off? I have a relationship between
StoreDetectiveID (primary key, autonumber) from my StoreDetective table and
StoreDetectiveID (not a primary key, detective's last name, currently NOT a
list box).

Thanks so much, your help would be greatly appreciated!

Kate

Start by changing the LookUp fields to TextBoxes in the table and you will
see why your queries are not working as you expect.
[quoted text clipped - 14 lines]
Thanks,
Kate

--
HTH - RuralGuy (RG for short) acXP WinXP Pro
Please post back to this forum so all may benefit.

Message posted via AccessMonster.com
http://www.accessmonster.com/Uwe/For...forms/200608/1

Can you give a few samples of the data that is in these fields

[Store Detectives].[LastName]
[OnlyScheduling].[StoreDetective]

Is [StoreDetective] a concencated field / autonumber / The same as LastName
etc
etc

If you're "sure" that these field "type" is OK try change this last section
of your SQL (to alter the join type) to

FROM [Store Detectives] RIGHT JOIN OnlyScheduling ON
[StoreDetectives].[LastName] =[OnlyScheduling].[StoreDetective];



--
Wayne
Manchester, England.
Not an expert.
Enjoy whatever it is you do.

Of course, here are a few examples:

[StoreDetectives].[LastName] is a "text" data type, display control "text
box" and contains employee's last names (smith, bouchard, cummings)

[OnlyScheduling].[StoreDetective] is a "text" data type, display control
"text box" and should contain the employee's last name as well (smith,
bouchard, cummings). This was how I was trying to make a relationship
between the StoreDetectives table and the OnlyScheduling table. Earlier
entries into this field are showing as #s, but I think that is b/c it was
previously a look-up column, which I then changed to a text box.

Thanks for your quick response and all your help thus far. This database
has been the biggest headache for a while now, and I just want to get it
right, it should not be this complicated!

Kate

"Wayne-I-M" wrote:

Can you give a few samples of the data that is in these fields

[Store Detectives].[LastName]
[OnlyScheduling].[StoreDetective]

Is [StoreDetective] a concencated field / autonumber / The same as LastName
etc
etc

If you're "sure" that these field "type" is OK try change this last section
of your SQL (to alter the join type) to

FROM [Store Detectives] RIGHT JOIN OnlyScheduling ON
[StoreDetectives].[LastName] =[OnlyScheduling].[StoreDetective];



--
Wayne
Manchester, England.
Not an expert.
Enjoy whatever it is you do.