Shorter date/time format

[NielsE]

I wish to insert in a table, not the date, but just the month and year (yy/mm or mm/yy), for example 05/10 or 10/05 for October 2005 (the order of yy and mm does not matter). In addition, I need to be able to sort the records chronologically using the same field.
I have tried various solutions with the date/time field or the number field (e.g. using yy.mm where the mm are the decimals). However, it does not seem to work correctly. Any suggestions for how I can do this by configuring the format, inputs mask and validation rule?
Niels

 

[RV]

Not really a good plan

Store dates as dates.
Use formatting whenever you'd have to show your dates in a specific format.

RV

 

[raskew]

Hi -

There are instances when it may be desirable to store just month and year without specifying a specific day. If this is the case, try storing them as a string in mm/yyyy format (using just yy will possibly cause confusion).

Lookup both the DateValue() and CDate() functions in the help file. Either of these will return a full date, set to the first day of the month, which you can then sort on (e.g. as a calculated field in a query).

Example:

x = "10/2005"
y = DateValue(x)
z = CDate(x)

? y
10/1/05

? z
10/1/05

HTH - Bob

 

[raskew]

Pat -

Thanks for that correction?! I think! What did I fail to say?

Bob

 

[raskew]

Hey-

You're my hero--although I'd have to add '...look out for bridge players, they can be really sneaky and devious!'

Best wishes,

Bob