Data type mismatch in criteria expression

[cmf2112]

Hello all--I've got a query where the date in the tables is this format yyyymmdd and I'm trying to change it to mm/dd/yyyy. I have successfully been able to do this for several dates in the PPOLC table by using the CDate function, for example, Issue Date: CDate(Mid(dbo_G_PPOLC!ISSUE_DATE,5,2) & "/" & Right(dbo_G_PPOLC!ISSUE_DATE,2) & "/" & Left(dbo_G_PPOLC!ISSUE_DATE,4)).

However, I am trying to do the same thing for a date that I needed to add to this query from another table. The date field in this new table is the same as the issue date above in the PPOLC table but when I run the query, I get the data type mismatch in criteria expression error. I cannot figure out where the problem is. Here is the expression I wrote for the new date field: Activation Date: CDate(Mid([dbo_G_PPOLM]![PROCESSED_DATE],5,2) & "/" & Right([dbo_G_PPOLM]![PROCESSED_DATE],2) & "/" & Left([dbo_G_PPOLM]![PROCESSED_DATE],4))

Does anyone have any suggestions as to how I might convert this "PROCESSED_DATE" into the mm/dd/yyyy format?

Thanks so much for your help!

 

[redneckgeek]

Is there some reason you don't want to use
Format(dbo_G_PPOLC!ISSUE_DATE,"mm/dd/yyyy") ?

 

[cmf2112]

Thanks for responding so quickly. When I try to use your suggestion I get an "Overflow" error.

 

[redneckgeek]

Are you positive dbo_G_PPOLM is Date/Time?

 

[raskew]

Hi -

Dates in Date/Time data format are stored as double-precision, floating-point

numbers (up to 15 decimal places). The integer portion of the double-

precision number represents the date, the decimal portion represents the

time.

To see this in action, from the immediate window type:

? cdbl(now())<enter>. It'll return something like 39636.3908101852

See http://support.microsoft.com/kb/q130514/ for a complete discussion of

how Access stores dates.

If your 'dates' are in fact stored as text in yyyymmdd format and are

consistent, you can return true date/time data format like this:

x = "20080707"
y = dateserial(left(x,4), mid(x,5,2), right(x,2))
? y
7/7/2008 
to prove that y is in date/time data format
? cdbl(y)
 39636

You'll need to add a field to your table, in date/time data format.

Then, run an update query to convert your string date to date/time data

format (see example above).

The true dates, when viewed, will normally appear in mm/dd/yy format. In

forms, reports, queries you can format them in various formats as desired.

HTH - Bob

 

[redneckgeek]

From the KB article raskew linked to:

Valid date values range from -647,434 (January 1, 100 A.D.) to 2,958,465 (December 31, 9999 A.D.).

That's what I was getting at with my question about the data type for the field. I think you're getting an overflow error because there is data in the field outside of that range.

A quick "fix" would be to put the criteria
WHERE ISDATE(dbo_G_PPOLC!ISSUE_DATE)=True
in you SQL. It would (I think) prevent the overflow error, but it may ignore data you want to see.

 

[cmf2112]

Redneckgeek--the data type of the field PROCESSED_DATE is Number. When I tried the ISDATE function, I did not get any output. Back to the drawing board! I really appreciate everyone's help!

Thanks!

 

[raskew]

Hi -

Just tried my previous example using a number (e.g.20080707) and it seems

to work as advertised.


Hope you're going to convert your 'dates' to date/time data format. It'll

payoff in the long run and eliminate having to play around with mickey-mouse

workarounds, plus allowing you to utilize Access' standard date functions.

Best Wishes - Bob