TYPE CONVERSION

[Access Rookie]

My goal is to automatically update the End Date to display the create date
from a record previously reported. For example the end date for the first
record listed below should be 9/24/2009 12:41:00 PM and the end date for the
third record listed below should be 8/21/2009 9:00:00 AM.


ID CATEGORY RANGE CREATE_DATE END_DATE
3426 Service 24 months 9/15/2009 9:35:00 AM
3426 Service 24 months 9/24/2009 12:41:00 PM
3426 Service 36 months 8/20/2009 12:23:00 PM
3426 Service 36 months 8/21/2009 9:00:00 AM

I have the query listed below but it does not work. I keep getting the
following type conversion failure: Microsoft Office Access didn't update
41575 fields due to type conversion failure.

UPDATE TBL_EXTRACT SET Tbl_EXTRACT.END_DATE =
DMin("CREATE_DATE","[TBL_EXTRACT]","ID=" & [ID] & " AND CREATE_DATE>" &
Format([CREATE_DATE],"\#yyyy-mm-dd\#") & "AND RANGE = """ & [RANGE] & """" &
"AND CATEGORY = """ & [CATEGORY] & """")
WHERE (((Tbl_EXTRACT.END_DATE) Is Null) AND ((Tbl_EXTRACT.CATEGORY) Is Not
Null));

Any assistance proved will be greatly appreciated.

 

[John Spencer]

Responded to in your earlier posting. Where you remarked that Create_Date can
be null. Also I noted that your expression is MISSING some spaces before the
word AND.

STEP 1: BACKUP your data before attempting the following.
STEP 2: BACKUP your data before attempting the following.

Without a backup you cannot restore the data if this does not work the way you
expect.

First Idea:
Restrict records to be updated to those that have a Create_Date value other
than Null.

UPDATE TBL_EXTRACT
SET Tbl_EXTRACT.END_DATE =
DMin("CREATE_DATE","[TBL_EXTRACT]","ID=" & [ID] & " AND CREATE_DATE>" &
Format([CREATE_DATE],"\#yyyy-mm-dd\#") & " AND RANGE = """ & [RANGE] & """ AND
CATEGORY = """ & [CATEGORY] & """")
WHERE Tbl_EXTRACT.END_DATE Is Null
AND Tbl_EXTRACT.CATEGORY Is Not Null
AND Create_Date Is Not Null

Second Idea: modify the DMIN to handle nulls

UPDATE TBL_EXTRACT
SET Tbl_EXTRACT.END_DATE =
DMin("CREATE_DATE","[TBL_EXTRACT]","ID=" & [ID] & " AND CREATE_DATE>" &
Format(NZ([CREATE_DATE],#12-31-2999#),"\#yyyy-mm-dd\#") & " AND RANGE = """ &
[RANGE] & """ AND CATEGORY = """ & [CATEGORY] & """")
WHERE Tbl_EXTRACT.END_DATE Is Null
AND Tbl_EXTRACT.CATEGORY Is Not Null;

The problem with a Null create date meant that the
Format(Create_Date,"\#yyyy-mm-dd\#")
expression was returning a zero-length string instead of a date literal. That
probably caused the error message. If this doesn't fix the problem then we
need to look elsewhere.

John Spencer
Access MVP 2002-2005, 2007-2010
The Hilltop Institute
University of Maryland Baltimore County