I have a strange data mismatch error

G

Guest

I am trying to create a "Totals" query based on another query. The source
query has a set of calculations between dates and I want the "Totals" query
to give me the number of records grouped by time taken (basically a frequency
distribution).

Neither query has criteria expressions yet I am getting a "data type
mismatch in criteria expression" error and I can't see why.

Source query SQL:

SELECT tble_Query.QueryID, tble_Query.Qry_ELS, tble_Query.Qry_Status,
tble_Query.CreInv, tble_Query.Cont_InvName,
WorkingDays2([SLA_Date1],[SLA_Date2]) AS Logged,
WorkingDays2([SLA_Date2],[SLA_Date3]) AS Timetaken,
WorkingDays2([SLA_Date1],[SLA_Date3]) AS Completed,
WorkingDays2([SLA_Date1],[SLA_Date4]) AS Preventative,
WorkingDays2([SLA_Date1],[SLA_Date5]) AS Authorised,
WorkingDays2([SLA_Date1],[SLA_Date6]) AS Billing,
WorkingDays2([SLA_Date1],[SLA_Date7]) AS Completion
FROM tble_Query;

Second query SQL:

SELECT [SLA Master].Timetaken, Count([SLA Master].Timetaken) AS
CountOfTimetaken, [SLA Master].Qry_Status
FROM [SLA Master]
GROUP BY [SLA Master].Timetaken, [SLA Master].Qry_Status;
 
G

Guest

My workingdays function calculates the number of days between two dates
excluding weekends and public holidays. So I am not sure whether your example
would work.




Allen Browne said:
You probably need to typecast the return value from the function in the
query.

See:
Calculated fields misinterpreted
at:
http://allenbrowne.com/ser-45.html

--
Allen Browne - Microsoft MVP. Perth, Western Australia

Reply to group, rather than allenbrowne at mvps dot org.

scubadiver said:
I am trying to create a "Totals" query based on another query. The source
query has a set of calculations between dates and I want the "Totals"
query
to give me the number of records grouped by time taken (basically a
frequency
distribution).

Neither query has criteria expressions yet I am getting a "data type
mismatch in criteria expression" error and I can't see why.

Source query SQL:

SELECT tble_Query.QueryID, tble_Query.Qry_ELS, tble_Query.Qry_Status,
tble_Query.CreInv, tble_Query.Cont_InvName,
WorkingDays2([SLA_Date1],[SLA_Date2]) AS Logged,
WorkingDays2([SLA_Date2],[SLA_Date3]) AS Timetaken,
WorkingDays2([SLA_Date1],[SLA_Date3]) AS Completed,
WorkingDays2([SLA_Date1],[SLA_Date4]) AS Preventative,
WorkingDays2([SLA_Date1],[SLA_Date5]) AS Authorised,
WorkingDays2([SLA_Date1],[SLA_Date6]) AS Billing,
WorkingDays2([SLA_Date1],[SLA_Date7]) AS Completion
FROM tble_Query;

Second query SQL:

SELECT [SLA Master].Timetaken, Count([SLA Master].Timetaken) AS
CountOfTimetaken, [SLA Master].Qry_Status
FROM [SLA Master]
GROUP BY [SLA Master].Timetaken, [SLA Master].Qry_Status;
Click to expand...
Click to expand...
 
A

Allen Browne

What is the return value from the function? Variant? Long?

You may need to use:
CLng(WorkingDays2([SLA_Date1],[SLA_Date2])) AS Logged,
etc, or:
CLngNz(WorkingDays2([SLA_Date1],[SLA_Date2]),0)) AS Logged,

--
Allen Browne - Microsoft MVP. Perth, Western Australia

Reply to group, rather than allenbrowne at mvps dot org.

scubadiver said:
My workingdays function calculates the number of days between two dates
excluding weekends and public holidays. So I am not sure whether your
example
would work.




Allen Browne said:
You probably need to typecast the return value from the function in the
query.

See:
Calculated fields misinterpreted
at:
http://allenbrowne.com/ser-45.html

scubadiver said:
I am trying to create a "Totals" query based on another query. The
source
query has a set of calculations between dates and I want the "Totals"
query
to give me the number of records grouped by time taken (basically a
frequency
distribution).

Neither query has criteria expressions yet I am getting a "data type
mismatch in criteria expression" error and I can't see why.

Source query SQL:

SELECT tble_Query.QueryID, tble_Query.Qry_ELS, tble_Query.Qry_Status,
tble_Query.CreInv, tble_Query.Cont_InvName,
WorkingDays2([SLA_Date1],[SLA_Date2]) AS Logged,
WorkingDays2([SLA_Date2],[SLA_Date3]) AS Timetaken,
WorkingDays2([SLA_Date1],[SLA_Date3]) AS Completed,
WorkingDays2([SLA_Date1],[SLA_Date4]) AS Preventative,
WorkingDays2([SLA_Date1],[SLA_Date5]) AS Authorised,
WorkingDays2([SLA_Date1],[SLA_Date6]) AS Billing,
WorkingDays2([SLA_Date1],[SLA_Date7]) AS Completion
FROM tble_Query;

Second query SQL:

SELECT [SLA Master].Timetaken, Count([SLA Master].Timetaken) AS
CountOfTimetaken, [SLA Master].Qry_Status
FROM [SLA Master]
GROUP BY [SLA Master].Timetaken, [SLA Master].Qry_Status;
Click to expand...
Click to expand...
Click to expand...
 
G

Guest

It seems to be working now.

Thanks.




Allen Browne said:
What is the return value from the function? Variant? Long?

You may need to use:
CLng(WorkingDays2([SLA_Date1],[SLA_Date2])) AS Logged,
etc, or:
CLngNz(WorkingDays2([SLA_Date1],[SLA_Date2]),0)) AS Logged,

--
Allen Browne - Microsoft MVP. Perth, Western Australia

Reply to group, rather than allenbrowne at mvps dot org.

scubadiver said:
My workingdays function calculates the number of days between two dates
excluding weekends and public holidays. So I am not sure whether your
example
would work.




Allen Browne said:
You probably need to typecast the return value from the function in the
query.

See:
Calculated fields misinterpreted
at:
http://allenbrowne.com/ser-45.html


I am trying to create a "Totals" query based on another query. The
source
query has a set of calculations between dates and I want the "Totals"
query
to give me the number of records grouped by time taken (basically a
frequency
distribution).

Neither query has criteria expressions yet I am getting a "data type
mismatch in criteria expression" error and I can't see why.

Source query SQL:

SELECT tble_Query.QueryID, tble_Query.Qry_ELS, tble_Query.Qry_Status,
tble_Query.CreInv, tble_Query.Cont_InvName,
WorkingDays2([SLA_Date1],[SLA_Date2]) AS Logged,
WorkingDays2([SLA_Date2],[SLA_Date3]) AS Timetaken,
WorkingDays2([SLA_Date1],[SLA_Date3]) AS Completed,
WorkingDays2([SLA_Date1],[SLA_Date4]) AS Preventative,
WorkingDays2([SLA_Date1],[SLA_Date5]) AS Authorised,
WorkingDays2([SLA_Date1],[SLA_Date6]) AS Billing,
WorkingDays2([SLA_Date1],[SLA_Date7]) AS Completion
FROM tble_Query;

Second query SQL:

SELECT [SLA Master].Timetaken, Count([SLA Master].Timetaken) AS
CountOfTimetaken, [SLA Master].Qry_Status
FROM [SLA Master]
GROUP BY [SLA Master].Timetaken, [SLA Master].Qry_Status;
Click to expand...
Click to expand...
Click to expand...
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Similar Threads

Group By Data Type mismatch error 3

Top