Date Format

I have an import file (from Excel) that has a date in the following format:
yyyymmdd

I have created an input mask that adds a forward slash / to the date when I
do the import and thus creating yyyy/mm/dd

I would like to have it as mm/dd/yyyy either in the table (preferred) or
when I open the form and pull the data through a query have the date format
as mm/dd/yyyy.

Any suggestions on an expression I can use in the query?

On the form I have a combo box that the user selects a number and then a
list box is populated with all of the data (6 different fields) related to
that particular number.

Even though the input mask on the table improves the date format, when I
open the form the date reverts back to the yyyymmdd format, no slashes.

Thanks,

John

"=?Utf-8?B?RGF0ZSBGb3JtYXRpbmcgUHJvYmxlbQ==?=" Date Formating
wrote in
:


I have an import file (from Excel) that has a date in the following
format: yyyymmdd

Importing from Excel is a notoriously cross-your-fingers-and-maybe-it'll-
work kind of affair. Is the field data type definitely a DateTime?

I have created an input mask that adds a forward slash / to the date
when I do the import and thus creating yyyy/mm/dd

Huh? Input mask only constrains user keystrokes, and does not affect data
values at all.

I would like to have it as mm/dd/yyyy either in the table (preferred)
or when I open the form and pull the data through a query have the
date format as mm/dd/yyyy.

It's stored as a double-precision floating point serial number, but that
should not matter to you (as long as it really is a DateTime value). You
can have any output format you want.

Any suggestions on an expression I can use in the query?

How about Format(MyDateField, "mm/dd/yyyy")

On the form I have a combo box that the user selects a number and then
a list box is populated with all of the data (6 different fields)
related to that particular number.

Don't sound very Normalised... :-(

Even though the input mask on the table improves the date format, when
I open the form the date reverts back to the yyyymmdd format, no
slashes.

I strongly suspect that this is a text or a numeric field rather than a
DateTime. There is no legal date format in the world that looks like
yyyymmdd so I would not be surprised that it was not parsed. You probably
need a conversion like

DateSerial(CInt(Mid(DateString,1,4)), _
CInt(Mid(DateString,5,2)), _
CInt(Mid(DateString,7,2)))

or if it's a numeric, do the same thing with Div and Mod operators. If you
can get a reliable and safe conversion you can do lots of useful things
with a proper date value.

On the other hand, the yyyymmdd still sorts, compares, and selects in the
right way, so do you really need to massage it further?

Best wishes


Tim F

Thanks Tim!

The field that imports into the table shows up in the table as a text field.
I have everything set up in a form to pull the data I need. I just wanted
it to show up in the form as a an actual date format (mm/dd/yyyy) instead of
the text format of (yyyymmdd).

What do you mean below by "Normalized"?

On the form I have a combo box that the user selects a number and then
a list box is populated with all of the data (6 different fields)
related to that particular number.

Don't sound very Normalised... :-(


Thanks,

John

"=?Utf-8?B?Sm9obiBEYWlseQ==?=" wrote
in :


What do you mean below by "Normalized"?



Among other things, it means Holding Exactly One Piece of Information in
One Field -- in other words not six bits. Six dates ought to be held in six
fields, or, more probably, in one field in six records of another table.

Normalisation is a design process that is based on Relational Theory, which
in turn is based on Set Theory, which you probably covered in school. R is
the basis of the current generation of (relational) database systems: if
you want to do anything useful with Access, you really do need some working
knowledge of the background.

(That is the short version: the long story fills entire textbooks and
postgrad courses!)

All the best


Tim F