data type mismatch

  • Thread starter Thread starter scubadiver
  • Start date Start date
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"));
 
At least one of your startdate values is not what you expect. Check for nulls
or other problems.
 
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...
 
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...
 
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
Data Type Mismatch in Criteria expression 6
Union query - sort of 4
'Invalid argument' error 1
Please help with datatype mismatch 8
Combine 2 queries into 1 1
Access General Date Forced To Have Date And Time 2
Counts number of entries per day in query 4

Back
Top