Date Format

  • Thread starter Thread starter Tom
  • Start date Start date
T

Tom

I use a bound textbox (date field) into which today's date
is automatically entered when a record is created.

This is done via a small function in the BeforeInsert
event procedure.

Private Sub Form_BeforeInsert(Cancel As Integer)
Me![DATE] = Now()
End Sub

Until this point everything is fine....

Now the small problem. Although I specify a YYYYMM format
on the form (so it shows 200308), when I click on the
actual date field, it shows the long date format:
08/21/2003 11:57:59 AM

Again, I can overcome this in forms and reports, but I
need to concatenate this YYYYMM value with a monthly
serial number so that each record can be easily referenced
as i.e. 200308-0001, or 200308-0002, etc.

Currently, when concatenating the 2 fields, I get this
format in a query...
08/21/2003 11:57:59 AM 01
or
08/21/2003 11:57:59 AM 02


Is there a way to fix the date format and autonumber
format in query view as well?


Thanks,
Tom
 
Have you formatted that date in the textbox properties on
the form or in the acutal table?

If you've only formatted the date in the form, you'll have
this problem. Also, you may want to format the date in
code before assigning it to the textbox.

Crystal
 
Yes, I set the proper format on both table and form.

Does this put a different spin on this?

Tom
 
Tom said:
I use a bound textbox (date field) into which today's date
is automatically entered when a record is created.

This is done via a small function in the BeforeInsert
event procedure.

Private Sub Form_BeforeInsert(Cancel As Integer)
Me![DATE] = Now()
End Sub

Until this point everything is fine....

Now the small problem. Although I specify a YYYYMM format
on the form (so it shows 200308), when I click on the
actual date field, it shows the long date format:
08/21/2003 11:57:59 AM

Again, I can overcome this in forms and reports, but I
need to concatenate this YYYYMM value with a monthly
serial number so that each record can be easily referenced
as i.e. 200308-0001, or 200308-0002, etc.
Click to expand...

Tom,

Access stores dates internally as double numbers. The formattings have
no influence on that, the complete date with time is always stored.
However, you can specify an output format for the table field, and you
can use the Format function in queries. To return the above string,
you would need something like this:

SELECT Format([DATE], "YYYYMM") & "-" & [SerialNumberField] FROM
MyTable

Warning: Naming _any_ user defined object (table field, control,
whatever) 'DATE' is asking for very big trouble. Date is a built in
function, so Access can get really confused about what is meant for ex
in the above query.

Best regards
Emilia

Emilia Maxim
PC-SoftwareService, Stuttgart
http://www.maxim-software-service.de
 
Back
Top