data type mismatch in criteria expression - Don't understand my problem

xdanxbrash

New Member
Joined
Mar 7, 2019
Messages
6
Hi and thank you for considering my problem...

I am receiving from Access an error message....data type mismatch in criteria expression

The crazy thing is that I have run this before and not had a problem. I am trying to make a make table query for a union situation. I have three queries I am using to do this.

SELECT * INTO MakeTableQuerySalesReturnUnion
FROM (SELECT * FROM [Returnx] UNION SELECT * FROM [Salesx]) AS [%$##@_Alias];


...is my main query to achieve the make table...and the following two queries are the Returnx and Salesx queries...

This is the Salesx query...

SELECT sales.sal_pr AS [Net Sales], articles.wp AS Cost, articles.rp, employee.num_em, employee.nam_em, sales.sal_dt, CDbl([models].[modeldis]) AS Margin, stores.st_num, stores.st_nam, models.modelc, models.model, [Macys Calendar 2010 - 2020].Ret_Week_No, [Macys Calendar 2010 - 2020].Ret_Mo, [Macys Calendar 2010 - 2020].Ret_Mo_Wk, articles.article, articles.descr

FROM ((((sales INNER JOIN stores ON sales.sal_snum = stores.st_num) INNER JOIN articles ON sales.sal_art = articles.article) INNER
JOIN models ON articles.model_c = models.modelc) INNER JOIN [Macys Calendar 2010 - 2020] ON sales.sal_dt = [Macys Calendar 2010 - 2020].Cal_Dt) INNER JOIN employee ON sales.sal_emp = employee.num_em
WHERE (((sales.sal_dt) Between #2/3/2019# And #3/2/2019#));

.....and this is the Returnx query...

SELECT (-[credit].[pr_cr]) AS [Net Sales], (-[articles].[wp]) AS Cost, articles.rp, employee.num_em, employee.nam_em, credit.ym_cr, CDbl([models].[modeldis]) AS Margin, stores.st_num, stores.st_nam, models.modelc, models.model, [Macys Calendar 2010 - 2020].Ret_Week_No, [Macys Calendar 2010 - 2020].Ret_Mo, [Macys Calendar 2010 - 2020].Ret_Mo_Wk, articles.article, articles.descr
FROM ((((credit INNER JOIN stores ON credit.stc_cr = stores.st_num) INNER JOIN articles ON credit.art_cr = articles.article) INNER JOIN models ON articles.model_c = models.modelc) INNER JOIN [Macys Calendar 2010 - 2020] ON credit.ym_cr = [Macys Calendar 2010 - 2020].Cal_Dt) INNER JOIN employee ON credit.emp_cr = employee.num_em
WHERE (((credit.ym_cr) Between #2/3/2019# And #3/2/2019#));


I only have the one date criteria in both...and the crazy thing is that each individual select query (Salesx and Returnx) run fine when not run out of the main union make table query shown at the top...

What do you think I've done wrong?

Thanks,
Dan
 

Some videos you may like

Excel Facts

Return population for a City
If you have a list of cities in A2:A100, use Data, Geography. Then =A2.Population and copy down.

xdanxbrash

New Member
Joined
Mar 7, 2019
Messages
6
By the way, I just noticed that I did not have the sales.sal_dt and credit.ym_cr fields unified with a common name for the field which I have now added to be Date:...but I still get the error...Please see my new syntax even though I am still having the same problem.

Salesx

SELECT sales.sal_pr AS [Net Sales], articles.wp AS Cost, articles.rp, employee.num_em, employee.nam_em, sales.sal_dt AS [Date], CDbl([models].[modeldis]) AS Margin, stores.st_num, stores.st_nam, models.modelc, models.model, [Macys Calendar 2010 - 2020].Ret_Week_No, [Macys Calendar 2010 - 2020].Ret_Mo, [Macys Calendar 2010 - 2020].Ret_Mo_Wk, articles.article, articles.descr
FROM ((((sales INNER JOIN stores ON sales.sal_snum = stores.st_num) INNER JOIN articles ON sales.sal_art = articles.article) INNER JOIN models ON articles.model_c = models.modelc) INNER JOIN [Macys Calendar 2010 - 2020] ON sales.sal_dt = [Macys Calendar 2010 - 2020].Cal_Dt) INNER JOIN employee ON sales.sal_emp = employee.num_em
WHERE (((sales.sal_dt) Between #2/3/2019# And #3/2/2019#));




Returnx

SELECT (-[credit].[pr_cr]) AS [Net Sales], (-[articles].[wp]) AS Cost, articles.rp, employee.num_em, employee.nam_em, credit.ym_cr AS [Date], CDbl([models].[modeldis]) AS Margin, stores.st_num, stores.st_nam, models.modelc, models.model, [Macys Calendar 2010 - 2020].Ret_Week_No, [Macys Calendar 2010 - 2020].Ret_Mo, [Macys Calendar 2010 - 2020].Ret_Mo_Wk, articles.article, articles.descr
FROM ((((credit INNER JOIN stores ON credit.stc_cr = stores.st_num) INNER JOIN articles ON credit.art_cr = articles.article) INNER JOIN models ON articles.model_c = models.modelc) INNER JOIN [Macys Calendar 2010 - 2020] ON credit.ym_cr = [Macys Calendar 2010 - 2020].Cal_Dt) INNER JOIN employee ON credit.emp_cr = employee.num_em
WHERE (((credit.ym_cr) Between #2/3/2019# And #3/2/2019#));
 

xenou

MrExcel MVP
Joined
Mar 2, 2007
Messages
16,792
Office Version
  1. 2019
Platform
  1. Windows
If they work fine separately then a possible problem is that the datatypes in one or more fields are not compatible for UNION.

Note: It is also remotely possible that your field name [%$##@_Alias] is causing problems. You should avoid this type of naming.
 
Last edited:

xdanxbrash

New Member
Joined
Mar 7, 2019
Messages
6
Thank you...

I think all of the field area compatible. and the alias naming worked but I don't know what would be a better replacement for the alias as I put that in there from reading another post in this forum and after tweeking it worked. I was afraid to rename it as I figured somehow it was a defined method in Access that I did not know or understand. Do you have a recommendation on how I would address this alias? I am a little confused as I am already naming the destination table for the union output earlier in the main query...I didn't even know why I would need an alias since I had the destination table already designated. What do you think I should do?

Thank you again!
Dan
 

xenou

MrExcel MVP
Joined
Mar 2, 2007
Messages
16,792
Office Version
  1. 2019
Platform
  1. Windows

ADVERTISEMENT

instead of this alias:
SELECT * INTO MakeTableQuerySalesReturnUnion
FROM (SELECT * FROM [Returnx] UNION SELECT * FROM [Salesx]) AS [%$##@_Alias];

you can use this one:
SELECT * INTO MakeTableQuerySalesReturnUnion
FROM (SELECT * FROM [Returnx] UNION SELECT * FROM [Salesx]) AS A;

That's if you need a specific suggestion. Any valid name will work for your alias (but as I guess I pointed out, some are better than others and I would consider %$##@_Alias as a bad alias name and anything more normal as a good one (such as "MyTable", "T", "A", "X", "Result", "AliasTable", "Foo", "Bar", "Spam", "Eggs" and a hundred thousand other names that follow best practices for database object names).

If you think your fields are all compatible but aren't 100% sure then you need to investigate. Rules of compatibility allow for some flexibility but in my opinion the best rule is that the fields that match up from the different queries all have the same datatypes (i.e, Long Integer and Short Integer may be compatible, but better is that both fields be Long Integer and so on).

Obviously, you can also test with
Select * from ...
instead of
select * into ... from ...

This way you have three steps to check on ...
1) each query works
2) the union query works
3) the select into (make table) query works.
 

xdanxbrash

New Member
Joined
Mar 7, 2019
Messages
6
Thank you very much for your help!!! I know I probably seem less than capable but your clear examples help me feel confident what a reliable path will be for testing and modifications and I really appreciate it.
 

james_lankford

Well-known Member
Joined
Jan 11, 2009
Messages
1,178
> each individual select query (Salesx and Returnx) run fine when not run out of the main union make table query shown at the top...

do a make table for each query

then open each table in design mode and check the data type of each field
 

xenou

MrExcel MVP
Joined
Mar 2, 2007
Messages
16,792
Office Version
  1. 2019
Platform
  1. Windows
+1 for a simple and quick way to check the data types ^
 

Watch MrExcel Video

Forum statistics

Threads
1,122,232
Messages
5,594,962
Members
413,954
Latest member
mrsandy

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top