Bad Date - Format Problem

[Dustin B]

I have a linked table that I pull info from. The problem is that he source
is not something I can control (.CSV file downloaded from a vendor). The
date from the vendor's CSV file is just mm/dd. What I did to get past this
is to make the field properties for this field text and then concantinate
this text file with Format(Date(), "YY"). This works but now I can't do any
calculations on the date say add three to it. I have tried using CVDate in
the below and in a query of the new event date. I am either not using
something properly or the way I am going about this is wrong. Any help would
be appreciated.

Field in a query to add yy to the end of the bad date that only has mm/dd:
New Event Date: IIf([Trace Results]![EVENT DATE]<>"",[Trace Results]![EVENT
DATE] & Format(Date(),"/yy"),"")

Attempts to add days to the result of the above field resulting in type
mismatch:
ETA: CVDate([qryMakeTraceDateFormat]![Event Date])+[tblTransits]![Transit]
[tblTransits]![Transit] = 1-12

ETA: [qryMakeTraceDateFormat]![Event Date]+10

ETA: CVDate([qryMakeTraceDateFormat]![Event Date])+10

 

[mcescher]

I have a linked table that I pull info from.  The problem is that he source
is not something I can control (.CSV file downloaded from a vendor).  The
date from the vendor's CSV file is just mm/dd.  What I did to get past this
is to make the field properties for this field text and then concantinate
this text file with Format(Date(), "YY").  This works but now I can't doany
calculations on the date say add three to it.  I have tried using CVDatein
the below and in a query of the new event date.  I am either not using
something properly or the way I am going about this is wrong.  Any help would
be appreciated.

Field in a query to add yy to the end of the bad date that only has mm/dd:
New Event Date: IIf([Trace Results]![EVENT DATE]<>"",[Trace Results]![EVENT
DATE] & Format(Date(),"/yy"),"")

Attempts to add days to the result of the above field resulting in type
mismatch:
ETA: CVDate([qryMakeTraceDateFormat]![Event Date])+[tblTransits]![Transit]
[tblTransits]![Transit] = 1-12

ETA: [qryMakeTraceDateFormat]![Event Date]+10

ETA: CVDate([qryMakeTraceDateFormat]![Event Date])+10

I'm using Access 2002, and not seeing "CVDate" in my help file, but I
do see CDate, so try something like this.

New Event Date: IIf([Trace Results]![EVENT DATE]<>"",
CDate([Trace Results]![EVENT DATE] & Format(Date(),"/yy")),"")

or this

ETA: CDate([qryMakeTraceDateFormat]![Event Date])+10

FYI, DateAdd would be a great function for this.

Hope this helps,
Chris M.

 

[John W. Vinson]

I have a linked table that I pull info from. The problem is that he source
is not something I can control (.CSV file downloaded from a vendor). The
date from the vendor's CSV file is just mm/dd.

So 05/13 might be 5/13/2008, or 5/13/2002, or 5/13/2009? Is there ANY
information in the file - or external to it - which would allow you to
unambiguously determine the year?

What I did to get past this
is to make the field properties for this field text and then concantinate
this text file with Format(Date(), "YY"). This works but now I can't do any
calculations on the date say add three to it. I have tried using CVDate in
the below and in a query of the new event date. I am either not using
something properly or the way I am going about this is wrong. Any help would
be appreciated.

For one thing, NULL is not the same as "" so your <>"" criterion won't work;
secondly, a date is not a text string. Try

NewEventDate: IIF(IsNull([Trace Results].[Event Date], NULL, CDate([Trace
Results]![EventDate] & "/" & Year(Date()))

Field in a query to add yy to the end of the bad date that only has mm/dd:
New Event Date: IIf([Trace Results]![EVENT DATE]<>"",[Trace Results]![EVENT
DATE] & Format(Date(),"/yy"),"")

Attempts to add days to the result of the above field resulting in type
mismatch:
ETA: CVDate([qryMakeTraceDateFormat]![Event Date])+[tblTransits]![Transit]
[tblTransits]![Transit] = 1-12

ETA: [qryMakeTraceDateFormat]![Event Date]+10

ETA: CVDate([qryMakeTraceDateFormat]![Event Date])+10

Use the DateAdd() function to add days to a date/time value.