Date Type Mismatch

[JWT]

Hello,
Have a stock room with some item rarely used and want to phase out items least used. I am going to list items added three years ago and not been used in the last three years.
I have a table that is downloaded with the dates as [mm]=month [dd]=day [cc]=century [yy]=year.
Put together as Expr1: CDate(Format([ptadmm] & "/" & [ptaddd] & "/" & [ptadcc] & [ptadyy],"mm/dd/yy")) and use <Date()-1095 in the criteria.
Now this works in the add in date because all spaces are filled but the trouble is with the pulled dates.
If I set a similar expression I get #Error for items that have never been picked, all the dates show as 0. I have been able to use this Expr2: IIf([ptlpcc]=0,Date()-4500,CDate(Format([ptlpmm] & "/" & [ptlpdd] & "/" & [ptlpcc] & [ptlpyy],"mm/dd/yy"))) to fill in all items with a date.
I wanted this to fill using three years from current date but if I add <Date()-1095 in the criteria I get a pop up with Date Type Mismatch In Criteria Expression.
I can not change the way the download fills the table and want to know if there is another way around this?

 

[namliam]

I am sure there is a way, but am unclear as to your problem.... Also i am unsure as to your reasoning to use the Cdate(Format()) context instead of the eg dateserial function AND the use off 1095 (presumably 3 years) vs the dateadd function.

For 1 change i would suggest changing the cdate(format()) to dateserial for sure...

As for your others... Maybe some examples are needed...

Regards

The Mailman

 

[JWT]

Thanks Namliam,

Here a screen shot of the table I pull my dates from; the date is split in to four columns and sent, as numbers not date. I used Cdate(Format()) to put together a date I can view as mm/dd/yyyy. This is the same type of format used to find the date items added to the database and set to show only items three-years from current date. I was trying to substitute a date for columns that showed zeros in the items pulled from stock. Now I can get this to fill in a fake date but the less then three-year filter returns “DATE TYPE MISMATCH”
For now I am taking the two numbers in the “CC” column 19 & 20 and using IIF=0 show as 19. I am trying to have this done in the table transfer so I do not have this problem but may not be able to change the transfer.
This is not set the way I want this to pull data, I want all Items entered in the system but not pulled from stock. If any easer way please let me know how to set this up.
Thank you, JWT

 

[JWT]

Here is a screen shot of my query.

 

[GumbyD]

JWT -

The reason you are getting the error is because you are using the Cdate function over just part of your expression. All of the output of your expression must be able recognizable by Access as the same datatype - so it needs to all be date values or all be numbers etc.

namliam is correct you should be using the DateSerial function to build your date values and the DateAdd function to get 3 years ago. Here is how I would write your expression:

cdate(iif([ptlpcc]=0,dateadd("yyyy",-3,now()),DateSerial([Ptlpcc]&[ptlpyy],[ptlpmm],[ptlpdd])))

This will return the date value if it exists or 3 years ago if it does not. You will need criteria to get all the records older than what you want to keep. If you want to see all the entries over 2 years old your criteria would be <dateadd("yyyy",-2,Now())

Good Luck!
GumbyD

 

[JWT]

Thank you

GumbyD

Thank you with this one, it ran OK and I would never have put that together the way you did.
I had to modify it just a bit to work with the odd way the [ptlpyy] year comes in to the table. The years for the 90’s were fine but for 2000 to 2003 only the last digit shows. Dates like 1/5/2002 look like 1/5/202 and the before three years would not work. This is what I changed to and it returns date the way I want them. Let me if you would refine it another way. Again Thank You, JWT

Expr3: CDate(IIf([ptlpcc]=0,DateAdd("yyyy",-13,Date()),DateSerial([Ptlpcc] & IIf([ptlpyy]>9,[ptlpyy],"0" & [ptlpyy]),[ptlpmm],[ptlpdd])))