Need Help with Update Query

[Chuck W]

Hi,
I have a table called tbl3T that has two fields. One is called DisDate
which is a text field with date in them in the format of 03312008, 12082007
etc. I have a separate field called NumericMonth which is blank. I want to
create an update query using the left function that will take the left two
characters from DisDate and populate the NumericMonth field with these
values. Can someone help?

Thanks,

 

[Damon Heron]

Why bother? You have the month stored in the first field. If you want
access to it, (on a form or report, for instance) just use the left function
there.

Damon

 

[Clifford Bass]

Hi Chuck,

You could use Mid(DisDate, 3, 2).

However I would suggest that is not a wise course. You will have to
remember always to update the value whenever the DisDate is updated. It
would be better to convert the entire field into an actual date field, say
named "DisDateNew". Use something like DateSerial(Right(DisDate, 4),
Mid(DisDate, 3, 2), Left(DisDate, 2)). Then delete the existing "DisDate"
and rename the "DisDateNew" to "DisDate". Then whenever you only want the
month you would use Month(DisDate). It might be a good idea to make a backup
before doing the above.

Hope that helps,

Clifford Bass

 

[Clifford Bass]

Hi Chuck,

Correction: You could use Left(DisDate, 2).

update tbl3T set NumericMonth = Left(DisDate, 2);

However I would suggest that is not a wise course. You will have to
remember always to update the value whenever the DisDate is updated. It
would be better to convert the entire field into an actual date field, say
named "DisDateNew". Use something like DateSerial(Right(DisDate, 4),
Left(DisDate, 2), Mid(DisDate, 3, 2)). Then delete the existing "DisDate"
and rename the "DisDateNew" to "DisDate". Then whenever you only want the
month you would use Month(DisDate). It might be a good idea to make a backup
before doing the above.

Hope that helps,

Clifford Bass

 

[John Spencer]

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.

UPDATE tbl3T
SET NumericMonth = Val(Left([DisDate],2))
WHERE DisDate Like "[01][0-9]*"

Better yet might be to store a real date in a datetime field and then be able
to use that and the various date functions as appropriate.
UPDATE tbl3T
SET RealDateField = CDate(Format([DisDate],"@@-@@-@@@@"))
WHERE IsDate(Format([DisDate],"@@-@@-@@@@"))

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