Date Format

  • Thread starter Thread starter PPCO
  • Start date Start date
Sure use the format function

Format([YourDateField],"mmmm, m/dd")

or set the format property of a control to
"mmmm, m/dd"


John Spencer
Access MVP 2002-2005, 2007-2009
The Hilltop Institute
University of Maryland Baltimore County
 
So that worked great! Had to use "dddd, m/dd" for what i was trying to do. I
have a bigger problem though. Some background: I am pulling data from our
accounting program using an ODBC program. Every time I refresh the data from
the program, my date formatting does not work in my queries or the make
table. I've tried modifying the linked table, but of course you can't modify
the properties for a linked table. Am I stuck, or is there a workaround?
Thanks alot for all your help

John Spencer MVP said:
Sure use the format function

Format([YourDateField],"mmmm, m/dd")

or set the format property of a control to
"mmmm, m/dd"


John Spencer
Access MVP 2002-2005, 2007-2009
The Hilltop Institute
University of Maryland Baltimore County
Wondering if there's a way to take a date and format it like this: Monday,
5/18. Thanks
Click to expand...
Click to expand...
 
Thanks for the help--just wondering if I can keep it formatted with what I
explained I'm doing above with the data transfer from our accounting program.
it changes the date format every time I refresh the data. Even though I have
the dddd, m/dd in the format field
 
How are you refreshing the data? Are you importing into a new table?

You can see your process and your data. We cannot, so it is hard to tell what
is happening.

John Spencer
Access MVP 2002-2005, 2007-2009
The Hilltop Institute
University of Maryland Baltimore County
 
Sorry...I'm using a linked table, then running a maketable query off of that
to make the data a little more manageable. Maybe I can set the date format in
the make table query...
 
Thanks for the help--just wondering if I can keep it formatted with what I
explained I'm doing above with the data transfer from our accounting program.
it changes the date format every time I refresh the data. Even though I have
the dddd, m/dd in the format field
Click to expand...

Are you importing the data into a Date/Time field in an Access table as part
of the data transfer?

Note that a Date field is NOT stored in any particular format; it's stored as
a number, a count of days and fractions of a day (times). You can display that
number in any desired date format, but the format isn't part of the stored
value.
 
Sorry...I'm using a linked table, then running a maketable query off of that
to make the data a little more manageable. Maybe I can set the date format in
the make table query...
Click to expand...

Rather than a maketable, I'd really suggest having a static local table (with
the desired datatypes, sizes and formats) and running a Delete query to empty
it followed by an Append query go fill it with the new data. Whether you do
this or use a MakeTable query, you should regularly compact the database.
 
You could have a predefined table and import into that instead of using a make
table.

The next time you need to refresh, you run a delete query to clean out the
table and an append query to populate it with the new records. To handle the
bloat you would need to periodically compact your data.

Or you can use a query against the data in the table and set the format there

SELECT Format(SomeDate,"dddd, m/dd") as MyFormattedDate
FROM SomeTable

OR if you are looking at the data in a form or on a report and not directly in
a table or a query, then use the format property of the control showing the
date to force it to the desired format.

The latter is normally the best option.

John Spencer
Access MVP 2002-2005, 2007-2009
The Hilltop Institute
University of Maryland Baltimore County
 
Back
Top