convert date to YYYY/MM/DD

[deceasedlavy]

how do i do this? i found an old thread concerning this but it must not apply either to vista or to the most recent version of office.

 

[Rabbie]

Try

Format(DateValue,"YYYY/MM/DD")

 

[deceasedlavy]

Try

Format(DateValue,"YYYY/MM/DD")

forgive me, i'm a novice. i have no idea what you're talking about. pretend i've just opened access for the first time in my life.

 

[Rabbie]

forgive me, i'm a novice. i have no idea what you're talking about. pretend i've just opened access for the first time in my life.

Then I would reccomend a good book on the subject

Seriously I thought In had given you a complete answer to your question.

If you have a date called

DateValue

then using

Format(DateValue,"YYYY/MM/DD") will display the date in that format.

Can you give a little more detail of what you want to do so we can help you

 

[deceasedlavy]

Then I would reccomend a good book on the subject

Seriously I thought In had given you a complete answer to your question.

If you have a date called

DateValue

then using

Format(DateValue,"YYYY/MM/DD") will display the date in that format.

Can you give a little more detail of what you want to do so we can help you

okay, here goes: i'm just creating a very simple access database, using what i believe to be the most recent version of ms office, on windows vista. one column is for dates. the only options i can see for displaying the date in a cell formatted for date/time, in the "format" menu on the "datasheet" tab, are "general date" (mm/dd/yyyy), "long date" (ridiculously long), "medium date" (dd-mmm-yy), and "short date" (mm/dd/yy). i want yyyy/mm/dd. i don't know what you mean by "if you have a date called datevalue then using format (datevalue, 'yyyy/mm/dd')." where can i find this option? or, where am i supposed to input it? hopefully this clears up how little knowledge i have of access. thanks...

 

[raskew]

Take a look at this MSKB article http://support.microsoft.com/kb/q130514/ showing how Access stores dates/times, provided they are in date/time data format -- not as formatted text. Strongly suggest you convert your text dates to date/time format. They you can format them as needed.

If you have a date called

DateValue

...

DateValue is an Access function. Using it as a field/variable name invites problems down the road.

Bob

 

[deceasedlavy]

Take a look at this MSKB article http://support.microsoft.com/kb/q130514/ showing how Access stores dates/times, provided they are in date/time data format -- not as formatted text. Strongly suggest you convert your text dates to date/time format. They you can format them as needed.



DateValue is an Access function. Using it as a field/variable name invites problems down the road.

Bob

thanks for the link, but neither that article, nor any that it links to, answers my question, as far as i can tell. my dates are in date/time format, not text format. i JUST WANT TO CHANGE THE ORDER in which the values are DISPLAYED from MM/DD/YYYY to YYYY/MM/DD. sorry if i'm being thick-headed about something but i'm no closer to an answer than when i first asked the question. thanks...

 

[deceasedlavy]

never mind, i figured it out; i had to change the regional settings in the control panel. as far as i can tell, there's no way to format JUST access dates the way i want them. thanks for the help anyway...

 

[raskew]

If you read the link I provided, you'll see that Access stores dates as long integers. If you want to see a different format, indicate that in your query, form or report.

? format(date(), "yyyy/mm/dd")
2008/04/08


Bob

 

[boblarson]

as far as i can tell, there's no way to format JUST access dates the way i want them.

That statement is definitely NOT TRUE. But, due to your limited knowledge you couldn't understand what Rabbie was telling you.

I would HIGHLY suggest you work through the Access tutorials here:

http://www.functionx.com

as they will help you understand what you need to know about Access in order to be successful.

 

[gemma-the-husky]

the advice is good

very rarely would you not want to store a date as a date. Date's are inherently manageable, sortable etc

what you are looking at is merely PRESENTATION of the date - you shouldnt want or need to change the way it is ACTUALLY stored in the dbs.

 

[neileg]

forgive me, i'm a novice. i have no idea what you're talking about. pretend i've just opened access for the first time in my life.

The advice you have been given is sound. It assumes however that you have an ability level of 1, not 0.

Most people coming to Access have used a spreadsheet before and believe that an Access table is the same thing, more or less. It isn't. In a spreadsheet, you store, manipulate and display data all in the same place. In a relational database:
Tables - store data only. Users should never see a table so you don't care how data is stored so long as it is efficient and controlled
Queries - used to extract or manipulate data. Users would not normally use queries either
Forms - The principal user interaction. Used for data input and display. Can also hold VBA code for complex manipulation
Reports - Used mostly for print output but also used for on screen display in a non-interactive format

Now how your date fields are stored in the table is completely irrelevant. Access will normally show table level data in a format that matches your Windows regional setting but dates and times are actually stored as decimal numbers. While you can apply a new format to your dates in a query (useful for preparing data for export), it is better practice to apply the format in the form or report. This is where the Format() statements you have been given would be used.

Hope that helps a little.

 

[Bungy Strap]

I'm really, really sorry to crash into this thread, but I'm so envious at all the good advice that is being given.....and yet I've also got a wierd problem with dates and Access 2007 and NOT ONE reply to my post of 1st April in the General forum! Neil, Gemma, Bob and Bob - any chance of you having a think about this?

I'm trying to import into Access 2007 a txt file containing dates in the format ddmmyyyyy. I'm using the import specification method, setting the date delimiter to blank, nothing, or "" if you see what I mean - simply by deleting the default "/", but in the resulting table the relevant fields are blank, and in the ImportErrors table it shows a type conversion failure. The attached shows the specifics.

I've used this technique sucessfully for many years with Access 97 and 2003, but I can't get it to work with 2007.

I can create a workaround by importing them as text into a temporary table and then doing a query to convert it into the right format, but this isn't an ideal solution because I have several old '97 and 2003 databases which I have converted to 2007, and which now no longer work, and I really don't want to have to get into them to modify them in this way. And unfortunately I can't change the date format in the text files which I receive.

Can anyone see what I am doing wrong, or has Access 2007 really taken a backward step?

Yours in hope.

Benjy

 

[neileg]

Sorry, Benjy. I'm avoiding A2007 for as long as possible. Our place has only just standardised on A2003!

 

[gemma-the-husky]

right

i think if you have a date showing in a text file as

17122007 (ie UK 17th December 2007) then this does not represent a valid date as far as Access is concerned, (perhaps its held as text, not a numeric) so access is just ignoring this field, and setting it to null - you may have an import error table reflecting this.

so you need to import this, probably as a text string, into a text field, and then massage it in some way into a valid date.

i think the best function for this is dateserial, but there may be others.

 

[Bungy Strap]

Thank you anyway for your reply Neil.

Gemma - I'm beginning to sort of agree with your conclusion...but it is frustrating that Access 97 and 2003 had no problem with dates in this format. If 2007 really doesn't like txt dates in ddmmyyyy format then I've got to redesign a lot of databases, and that seems like a reaaly odd backward step which has been taken for no obvious reason. Curses.