Parsing the "LongDate" Format into a shorter one

In an Access 97 (Office 97) DB, the design requires date to be in the format "January 15, 2000" but the closest Access 97 format is the "LongDate" which is "Saturday, January 15, 2000".

I am using date() to automatically insert the dates when the input form opens.

Can anyone suggest how I will be able to parse this to truncate first day part and the comma?

Since the number of characters vary in the first day part depending on the day of the week I am confused. Also, will I be correct in understanding that Access "Date" data type is not a simple text string?

Thanks in advance.

Dear accesswatcher,

I'm a bit of a newbie with Access but I think this is your answer:

MyDate = Format(Date, "mmmm d, yyyy")

I was going to suggest using the InStr and Mid functions to parse the orginal text but the method above is even shorter.

Happy programming,
Jeff

Jeff,

I am super newbie.
I now see the date format, but how will i use this though?
I went to the Design view of the table. Set the data type of the field as Date/Time. Then in the default value I set that to "date()"
In the format i set "MyDate = Format(Date, "mmmm d, yyyy")".
Now when I open the Input form, The date comes as a "Longdate" with the day and everything, but when the field gets the "Focus" it parses it to just the month, dy and year. Which means the user should atleast click once inside the data field. Right?

Is this how best one can do this? or can anything be done to not need the "focus" to be on the field? or I am doing some thing worng?

accesswatcher,

I played around with it a little and this is what I found...

First, when you bring the focus to the date field formatted as "Long Date" it will always parse the "day" off the date. Your formatting efforts really aren't at play here; it's Access that is making the change. (I suspect the Access developers didn't want to deal with error trapping somebody who typed in "Monday, January 15, 2000")!

Anyway, back to the problem at hand. My earlier suggestion using the Format function is not what you need (sorry). Instead, the answer is much more simple. Set the Format of the Text Box control on your form to:

mmmm dd"," yyyy

I found the solution on the "Format Property — Date/Time Data Type" page in the Access Help. It talks all about custom date and time displays.

Hope this helps,
Jeff

Thanks again Jeff.
I will try it out and read the help pages as well.

jeff,
Just a clarification:
the text box control does not have a fotmat property. It has a whole tab full of properties you can set, but not the date format though. The help file emans the format property of the "date/Time" data type in the table. It works. But still, I have to click at least once for the right format to showup. I will keep trying. I figure anything I will be sure to post it back here.

OK! I got it!!

1. I had to set the Date/Time data type as a custom format as mmmm dd"," yyyy

2. then I have to set my Computer's Date/Time format by going under Control Panel and Regional settings, then set that LongDate style to mmmm dd, yyyy (this is one of the available options)

then the date comes in as i wanted. NO NEED TO CLICK once as earlier.

p.s.: in the help file the last couple of line talk about this. All custom Date/Time formats IN ACCESS will only work if they are set in the Regional Settings under Control Panel of the Machine as well.