data type mismatch

S

scubadiver

I have a start date text formatted as 8 digits (DDMMYYYY). This query says
there is a data type mismatch but it has worked before.

SELECT CVDate(Format([StartDate],"00\/00\/0000")) AS expr1,
Count(caisreport.StartDate) AS [Count]
FROM caisreport
GROUP BY CVDate(Format([StartDate],"00\/00\/0000"))
ORDER BY CVDate(Format([StartDate],"00\/00\/0000"));
 
D

Duane Hookom

At least one of your startdate values is not what you expect. Check for nulls
or other problems.
 
S

scubadiver

Most of the dates are "00000000". Do you think this would have an impact?
Otherwise it all looks ok.

Duane Hookom said:
At least one of your startdate values is not what you expect. Check for nulls
or other problems.
--
Duane Hookom
Microsoft Access MVP


scubadiver said:
I have a start date text formatted as 8 digits (DDMMYYYY). This query says
there is a data type mismatch but it has worked before.

SELECT CVDate(Format([StartDate],"00\/00\/0000")) AS expr1,
Count(caisreport.StartDate) AS [Count]
FROM caisreport
GROUP BY CVDate(Format([StartDate],"00\/00\/0000"))
ORDER BY CVDate(Format([StartDate],"00\/00\/0000"));
Click to expand...
Click to expand...
 
D

Duane Hookom

This sounds like it would be easier to test and report back than ask the
question here, expect someone else to reply.

What did you find out?

--
Duane Hookom
Microsoft Access MVP


scubadiver said:
Most of the dates are "00000000". Do you think this would have an impact?
Otherwise it all looks ok.

Duane Hookom said:
At least one of your startdate values is not what you expect. Check for nulls
or other problems.
--
Duane Hookom
Microsoft Access MVP


scubadiver said:
I have a start date text formatted as 8 digits (DDMMYYYY). This query says
there is a data type mismatch but it has worked before.

SELECT CVDate(Format([StartDate],"00\/00\/0000")) AS expr1,
Count(caisreport.StartDate) AS [Count]
FROM caisreport
GROUP BY CVDate(Format([StartDate],"00\/00\/0000"))
ORDER BY CVDate(Format([StartDate],"00\/00\/0000"));
Click to expand...
Click to expand...
Click to expand...
 
S

scubadiver

I didn't think to check but my suspicions are correct. If I exclude the
"00000000" the query works.

00/00/0000 doesn't exist! I didn't think Access would recognise this as
invalid date though I suppose it is a bit obvious.

I can sort it out. I will create an IIF statement and do it that way.

Duane Hookom said:
This sounds like it would be easier to test and report back than ask the
question here, expect someone else to reply.

What did you find out?

--
Duane Hookom
Microsoft Access MVP


scubadiver said:
Most of the dates are "00000000". Do you think this would have an impact?
Otherwise it all looks ok.

Duane Hookom said:
At least one of your startdate values is not what you expect. Check for nulls
or other problems.
--
Duane Hookom
Microsoft Access MVP


:


I have a start date text formatted as 8 digits (DDMMYYYY). This query says
there is a data type mismatch but it has worked before.

SELECT CVDate(Format([StartDate],"00\/00\/0000")) AS expr1,
Count(caisreport.StartDate) AS [Count]
FROM caisreport
GROUP BY CVDate(Format([StartDate],"00\/00\/0000"))
ORDER BY CVDate(Format([StartDate],"00\/00\/0000"));
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

Converting Text Date to Real Date to # 8
How to SELECT with date criteria by SQL? 1
Union query - sort of 4
Counts number of entries per day in query 4
Determine if night shift by start & end time 2
Total Minutes Worked Within IVR Timing 1
Data Type Mismatch in Criteria expression 6
View Null Results in Query 1

Top