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

[xdanxbrash]

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

 

[xdanxbrash]

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]

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.

 

[xdanxbrash]

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]

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]

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]

> 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]

+1 for a simple and quick way to check the data types ^