HOW TO UPDATE DATE PART please

[mrf1xa]

Hi, sure this is a quick one for those that know!

I have a long list of various dates, in short date format, and need to modify them so that the year and month remain unchanged but the day is in all cases changed to 01. Is there an easy way to achieve this short of sending my PA suicidal?

Thanks

Nigel
Didn't someone say work is supposed to be fun? They didn't have computers then I guess....

 

[cheerio]

the DAY function will give the day of the month. If you subtract that and add 1 you will get to the first of the month.

eg mybirthday - DAY(mybirthday) + 1
gives the first day of my birthmonth.

Ken

 

[mrf1xa]

Thanks Ken. In the meantime I had come up with this which, while cruder, seems to have done the trick.

Dim orig As Date
Dim nu As Date

orig = "05/07/2002"
nu = "01" & Right(orig, 8)
Nigel
Didn't someone say work is supposed to be fun? They didn't have computers then I guess....

 

[cheerio]

Glad you found a solution that worked. Your's does rely on dates being in UK format whereas mine works anywhere. Obviously it does not matter for a one-off exercise.

Ken

 

[raskew]

Apparently--if your solution worked for you-you
are not dealing with dates but rather with strings
(dates are delimited by # #, strings by " &quot. The
left(), right() and mid() functions are intended to
be used with strings, but not with dates.

Using US short-date format, 5 Jul 02 would be
formatted as 7/5/02, and stored by Access as a
double-precision number, i.e., 37442.

To demonstrate, try this from the debug window:
x = #7/5/02#
? cdbl(x)
37442

To convert dates to the same month/year, first
day of month, you could use (continuing from
the above example):

y = dateserial(year(x), month(x), 1)
? y
7/1/02