Formatting select query from text to date

[pjk2008]

Hello all

Again I'm a bit lost. In my database I import a table and from this table I have 100's of queries. I'm stuck as the table i import has 2 bits of info. ie drug and date. I have managed to seperate out the 2 bits of information into 2 columns using a select query.

However, this is where the problem occurs. The new column for my date is currently formatted for text, so when I sort this date column, the data appears as follows:-

31/5/08
30/2/00
29/4/03
29/2/08
etc

The question is how can I format this new column?? I haven't made a new column in my table only my query.

your help would be most appreciated.

kind regards
pjk

 

[namliam]

Use the DateSerial, left, right, mid, Instr and InstrRev functions

I.e.
Left ( yourdate, instr(yourdate,"/") - 1 )
Will extract the day for you.

 

[DCrake]

Simple Software Solutions

Are you using an inport spec to draw in the data, if so then nominate the date element as a date and not a string. Access will attempt to convert it to a date. Also the field in the table should be a date field. Any string that can be converted to a date will be converted to date format.

CodeMaster:

 

[raskew]

Hi -

Use the datevalue() function to convert a string date to date/time data format.

Example using US short/date value:
x = "5/31/08"
? datevalue(x)
5/31/2008

Since you are using European date format, you need to test this to ensure that Access is correctly interpreting your dates. It should, based on your
local settings. To test, from the immediate window:

Try:
x = "31/5/08"
? datevalue(x)

Once that's sorted out, suggest you add a date/time field to your table and then use an Update query to populate your new field with actual date/time
data type dates, based on your text date.

HTH - Bob

 

[sbenj69]

why not just use the cdate() function?

cdate([yourtextdate])

 

[raskew]

why not just use the cdate() function?

Same difference.


Bob

 

[namliam]

Both datevalue and Cdate tho are unreliable as the settings on your computer may change, plus Bob, your example is "broken" because 31/5/08 will always convert properly because month 31 doesnt excist.

10/5/08 would be a better test, something like format(datevalue(),"DD-MMM-YYYY") will tell you if you have problems.

Using the DateSerial/Datevalue/CDate, left, right, mid, Instr and InstrRev functions to pick appart the different parts and "put them back together" in the 'proper' format is the only way to ensure long term success.

 

[sbenj69]

actually, that's european format.... US format is mm/dd/yyyy

so cdate will work there as well

 

[raskew]

...because 31/5/08 will always convert properly because month 31 doesnt excist.

You'd think so, but actually, in a test (using US date format settings)

x = "31/5/08"
? datevalue(x)
5/8/1931

In a US environment, think the DateSerial/Datevalue/CDate, left, right, mid, Instr and InstrRev functions approach is overkill unless you're attempting to convert a European date.

In other environments, I stand by your expertise.

Bob

 

[namliam]

overkill unless you're attempting to convert a European date.

But the OPs dates seem to be european dates??

Except for this date which is just totaly whack! 30/2/00

But you are right "31/5/08" gets mutilated to 08 May 1931
While I think it should stand for 31 May 2008, for which I still stand by:

Using the DateSerial/Datevalue/CDate, left, right, mid, Instr and InstrRev functions to pick appart the different parts and "put them back together" in the 'proper' format is the only way to ensure long term success.