Format Date to show Month & Year only

[Rats]

I have a need to enter dates so that they always appear as the first of the month. These dates are used in vlookup functions in Excel and need to be constantly recorded as eg 1 Nov 06. Currently I have to rely on notes and training to make sure users only insert the date in this way.

What I would like to do is get them to enter Nov 06 only (without a day) and have that stored as 1 nov 06. I have searched for date formatting functions and nothing addresses this. Perhaps it is something that could be achieved using VB if it is possible to do it.

Thanks for the help.

 

[gemma-the-husky]

A date is stored as a date, not as a partial date. You would either have to let your users select a drop down month and year (the way some web sites do when validating credit cards), and then assemble it all together with something like mydate = datevalue("01/" & monthpicked & "/" & yearpicked")

alternatively let your users enter any date, but reset the date to the first of the month entered, again using the native datehandling functions.

You also might be able to format a date input mask for the textbox that fixes the date portion as 01 - something like "01/mm/yy" (not sure if that is the right syntax)

Depends which you think is the nicest for your users.

 

[CEH]

Rats,
"Gemma's" mydate = datevalue("01/" & monthpicked & "/" & yearpicked") looks like it might work.... You can pull the original date apart with something like this (I've used in a query)... say you had "BirthDate" field ..... If "BirthDate" is 05/15/95 then Expr: Month([BirthDate]) would return 5, Expr: Year([BirthDate]) would return 1995, and Day([BirthDate]) would return 15

So try this
Expr4: DateValue("01/" & (Month([BirthDate])) & "/" & (Year([BirthDate])))

 

[Rats]

Thanks GTH & CEH for your suggestions. I will give them a go and let you know the result.

 

[raskew]

Hi -

If you have a partial date stored as a string (mm/yyyy), e.g. "11/2006", and you apply the datevalue() function, it will automatically return 1 as the day. Example:

x = "11/2006"
y = datevalue(x)
? y 
11/1/06 
'to show that y is returned in date/time data format
? cdbl(y)
 39022

It works equally well using a modified medium-date format, e.g. "Nov-2006"
Regardless of input format, ensure that the "yyyy" format is used to avoid confusing Access as to what represents month and year.

HTH - Bob