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...
 
Back
Top