Problem with Union query

[Guest]

Hi,
I have in my app 2 queries that work great, I am trying to build a union out
of them, but I get a “data type mismatch in criteria expression†message when
I run the union query. I can not see what’s wrong
The queries are:
SELECT tblIncome.NberPlateInc, tblIncome.DateInc, tblIncome.AmountInc,
NameDrv([NberPlateInc],[DateInc]) AS Driver, "Income" AS Transaction FROM
tblIncome;
and
SELECT tblExpenses.NberPlateExp, tblExpenditures.DateExp,
tblExpenses.AmountExp, NameDrv([NberPlateExp],[DateExp]) AS Driver, "Expense"
AS Transaction FROM tblExpenses;
NameDrv is a public function that retrieves the ID of the driver who drove
the vehicule whose plate number is provided as argument at a date also
provided.
Can someone help me to understand why I am getting this message?
Many thanks in advance.

 

[John Spencer]

It always helps to post the SQL text of the query that is failing. Your
Union query should look something like the following.

SELECT tblIncome.NberPlateInc
, tblIncome.DateInc
, tblIncome.AmountInc
, NameDrv([NberPlateInc], [DateInc]) AS Driver
, "Income" AS Transaction
FROM tblIncome
UNION ALL
SELECT tblExpenses.NberPlateExp
, tblExpenditures.DateExp
, tblExpenses.AmountExp
, NameDrv([NberPlateExp], [DateExp]) AS Driver
, "Expense" AS Transaction
FROM tblExpenses;

Note that there is no semi-colon after the first query in the example. I've
used Union ALL instead of just Union based on a guess that there may be
duplicates in the tables that you want to keep.

If the above fails, I would eliminate one field in each query (in the union
query) until I found which field was causing the mismatch error. The fields
must be of similar types (text, number, or date) in order for the union
query to work. For instance, if DateInc is a datetime field and DateExp is
a text field storing dates as a string, then you will get the error.

 

[Guest]

Thanks John for your prompt response.
The query text I am trying is like yours without the ALL word. Actually I
noticed that its works pretty fine if I remove the Driver calculated field.
But when I reinsert it I get the error message.
As I said each of the 2 queries works great alone.
Hope this clarifies a bit more.
--
Jean-Marie

It always helps to post the SQL text of the query that is failing. Your
Union query should look something like the following.

SELECT tblIncome.NberPlateInc
, tblIncome.DateInc
, tblIncome.AmountInc
, NameDrv([NberPlateInc], [DateInc]) AS Driver
, "Income" AS Transaction
FROM tblIncome
UNION ALL
SELECT tblExpenses.NberPlateExp
, tblExpenditures.DateExp
, tblExpenses.AmountExp
, NameDrv([NberPlateExp], [DateExp]) AS Driver
, "Expense" AS Transaction
FROM tblExpenses;

Note that there is no semi-colon after the first query in the example. I've
used Union ALL instead of just Union based on a guess that there may be
duplicates in the tables that you want to keep.

If the above fails, I would eliminate one field in each query (in the union
query) until I found which field was causing the mismatch error. The fields
must be of similar types (text, number, or date) in order for the union
query to work. For instance, if DateInc is a datetime field and DateExp is
a text field storing dates as a string, then you will get the error.

Hi,
I have in my app 2 queries that work great, I am trying to build a union
out
of them, but I get a "data type mismatch in criteria expression" message
when
I run the union query. I can not see what's wrong
The queries are:
SELECT tblIncome.NberPlateInc, tblIncome.DateInc, tblIncome.AmountInc,
NameDrv([NberPlateInc],[DateInc]) AS Driver, "Income" AS Transaction FROM
tblIncome;
and
SELECT tblExpenses.NberPlateExp, tblExpenditures.DateExp,
tblExpenses.AmountExp, NameDrv([NberPlateExp],[DateExp]) AS Driver,
"Expense"
AS Transaction FROM tblExpenses;
NameDrv is a public function that retrieves the ID of the driver who drove
the vehicule whose plate number is provided as argument at a date also
provided.
Can someone help me to understand why I am getting this message?
Many thanks in advance.

 

[John Spencer]

I think the problem is that Access does not know what type of data NameDrv
is returning.

Ok, a couple of possible solutions come to mind.

Drop the Driver calculation from the Union Query and then use the UNION
query as the source for another query. In the new query calculate the value
NameDrv(NberPlateInc,DateInc) and display that along with the other fields.

OR try to force the type by using something like the following in both
queries.

NameDrv([NberPlateExp], [DateExp]) & "" AS Driver

or

CStr(NameDrv([NberPlateExp], [DateExp]) & "") AS Driver

Thanks John for your prompt response.
The query text I am trying is like yours without the ALL word. Actually I
noticed that its works pretty fine if I remove the Driver calculated
field.
But when I reinsert it I get the error message.
As I said each of the 2 queries works great alone.
Hope this clarifies a bit more.
--
Jean-Marie

It always helps to post the SQL text of the query that is failing. Your
Union query should look something like the following.

SELECT tblIncome.NberPlateInc
, tblIncome.DateInc
, tblIncome.AmountInc
, NameDrv([NberPlateInc], [DateInc]) AS Driver
, "Income" AS Transaction
FROM tblIncome
UNION ALL
SELECT tblExpenses.NberPlateExp
, tblExpenditures.DateExp
, tblExpenses.AmountExp
, NameDrv([NberPlateExp], [DateExp]) AS Driver
, "Expense" AS Transaction
FROM tblExpenses;

Note that there is no semi-colon after the first query in the example.
I've
used Union ALL instead of just Union based on a guess that there may be
duplicates in the tables that you want to keep.

If the above fails, I would eliminate one field in each query (in the
union
query) until I found which field was causing the mismatch error. The
fields
must be of similar types (text, number, or date) in order for the union
query to work. For instance, if DateInc is a datetime field and DateExp
is
a text field storing dates as a string, then you will get the error.

Hi,
I have in my app 2 queries that work great, I am trying to build a
union
out
of them, but I get a "data type mismatch in criteria expression"
message
when
I run the union query. I can not see what's wrong
The queries are:
SELECT tblIncome.NberPlateInc, tblIncome.DateInc, tblIncome.AmountInc,
NameDrv([NberPlateInc],[DateInc]) AS Driver, "Income" AS Transaction
FROM
tblIncome;
and
SELECT tblExpenses.NberPlateExp, tblExpenditures.DateExp,
tblExpenses.AmountExp, NameDrv([NberPlateExp],[DateExp]) AS Driver,
"Expense"
AS Transaction FROM tblExpenses;
NameDrv is a public function that retrieves the ID of the driver who
drove
the vehicule whose plate number is provided as argument at a date also
provided.
Can someone help me to understand why I am getting this message?
Many thanks in advance.

 

[Guest]

Thank you very John for your help
I will give it a try and let you know
Thanks

--
Jean-marie
Douala, Cameroun

I think the problem is that Access does not know what type of data NameDrv
is returning.

Ok, a couple of possible solutions come to mind.

Drop the Driver calculation from the Union Query and then use the UNION
query as the source for another query. In the new query calculate the value
NameDrv(NberPlateInc,DateInc) and display that along with the other fields.

OR try to force the type by using something like the following in both
queries.

NameDrv([NberPlateExp], [DateExp]) & "" AS Driver

or

CStr(NameDrv([NberPlateExp], [DateExp]) & "") AS Driver

Thanks John for your prompt response.
The query text I am trying is like yours without the ALL word. Actually I
noticed that its works pretty fine if I remove the Driver calculated
field.
But when I reinsert it I get the error message.
As I said each of the 2 queries works great alone.
Hope this clarifies a bit more.
--
Jean-Marie

It always helps to post the SQL text of the query that is failing. Your
Union query should look something like the following.

SELECT tblIncome.NberPlateInc
, tblIncome.DateInc
, tblIncome.AmountInc
, NameDrv([NberPlateInc], [DateInc]) AS Driver
, "Income" AS Transaction
FROM tblIncome
UNION ALL
SELECT tblExpenses.NberPlateExp
, tblExpenditures.DateExp
, tblExpenses.AmountExp
, NameDrv([NberPlateExp], [DateExp]) AS Driver
, "Expense" AS Transaction
FROM tblExpenses;

Note that there is no semi-colon after the first query in the example.
I've
used Union ALL instead of just Union based on a guess that there may be
duplicates in the tables that you want to keep.

If the above fails, I would eliminate one field in each query (in the
union
query) until I found which field was causing the mismatch error. The
fields
must be of similar types (text, number, or date) in order for the union
query to work. For instance, if DateInc is a datetime field and DateExp
is
a text field storing dates as a string, then you will get the error.

Hi,
I have in my app 2 queries that work great, I am trying to build a
union
out
of them, but I get a "data type mismatch in criteria expression"
message
when
I run the union query. I can not see what's wrong
The queries are:
SELECT tblIncome.NberPlateInc, tblIncome.DateInc, tblIncome.AmountInc,
NameDrv([NberPlateInc],[DateInc]) AS Driver, "Income" AS Transaction
FROM
tblIncome;
and
SELECT tblExpenses.NberPlateExp, tblExpenditures.DateExp,
tblExpenses.AmountExp, NameDrv([NberPlateExp],[DateExp]) AS Driver,
"Expense"
AS Transaction FROM tblExpenses;
NameDrv is a public function that retrieves the ID of the driver who
drove
the vehicule whose plate number is provided as argument at a date also
provided.
Can someone help me to understand why I am getting this message?
Many thanks in advance.

 

[Guest]

John
While I was testing your advice I foung out what was the issue. There were a
problem in my fonction with nulls and that was generating the message. I
corrected the function and fixed the issue. Sorry for having bothered you
with this.
Many thanks again for your help
--
Jean-Marie
Douala, Cameroun

I think the problem is that Access does not know what type of data NameDrv
is returning.

Ok, a couple of possible solutions come to mind.

Drop the Driver calculation from the Union Query and then use the UNION
query as the source for another query. In the new query calculate the value
NameDrv(NberPlateInc,DateInc) and display that along with the other fields.

OR try to force the type by using something like the following in both
queries.

NameDrv([NberPlateExp], [DateExp]) & "" AS Driver

or

CStr(NameDrv([NberPlateExp], [DateExp]) & "") AS Driver

Thanks John for your prompt response.
The query text I am trying is like yours without the ALL word. Actually I
noticed that its works pretty fine if I remove the Driver calculated
field.
But when I reinsert it I get the error message.
As I said each of the 2 queries works great alone.
Hope this clarifies a bit more.
--
Jean-Marie

It always helps to post the SQL text of the query that is failing. Your
Union query should look something like the following.

SELECT tblIncome.NberPlateInc
, tblIncome.DateInc
, tblIncome.AmountInc
, NameDrv([NberPlateInc], [DateInc]) AS Driver
, "Income" AS Transaction
FROM tblIncome
UNION ALL
SELECT tblExpenses.NberPlateExp
, tblExpenditures.DateExp
, tblExpenses.AmountExp
, NameDrv([NberPlateExp], [DateExp]) AS Driver
, "Expense" AS Transaction
FROM tblExpenses;

Note that there is no semi-colon after the first query in the example.
I've
used Union ALL instead of just Union based on a guess that there may be
duplicates in the tables that you want to keep.

If the above fails, I would eliminate one field in each query (in the
union
query) until I found which field was causing the mismatch error. The
fields
must be of similar types (text, number, or date) in order for the union
query to work. For instance, if DateInc is a datetime field and DateExp
is
a text field storing dates as a string, then you will get the error.

Hi,
I have in my app 2 queries that work great, I am trying to build a
union
out
of them, but I get a "data type mismatch in criteria expression"
message
when
I run the union query. I can not see what's wrong
The queries are:
SELECT tblIncome.NberPlateInc, tblIncome.DateInc, tblIncome.AmountInc,
NameDrv([NberPlateInc],[DateInc]) AS Driver, "Income" AS Transaction
FROM
tblIncome;
and
SELECT tblExpenses.NberPlateExp, tblExpenditures.DateExp,
tblExpenses.AmountExp, NameDrv([NberPlateExp],[DateExp]) AS Driver,
"Expense"
AS Transaction FROM tblExpenses;
NameDrv is a public function that retrieves the ID of the driver who
drove
the vehicule whose plate number is provided as argument at a date also
provided.
Can someone help me to understand why I am getting this message?
Many thanks in advance.