Query expression needed to edit data

B

BobV

I have an Access db for which the data is imported from another type of db.
One of the fields in the source db is a date field, but it's formatted as:

2/2/2004 04:17:58 PM

Access won't import this format as a date field, so I changed it to a text
field during the import process. Now, I would like to delete the ending
space and the AM or PM (basically, everything after the seconds digits).
How would I go about doing that? Afterwards, I figured I could then covert
it to a Date/Time field, and use it the way it was intended, since I need to
run queries based on a date range.

Also, is the format of the date itself a problem......if so, how would I add
0's where necessary?.....02/02/2004 instead of 2/2/2004.

Any help would be appreciated...thanks,
Bob
 
J

JulieD

Hi Bob

just tried this is a simple table (table1) - two fields
first field
name: textfield
datatype:text
value: 2/2/2004 04:17:58 PM
second field
name: datefield
datatype: date/time
value:

then created an update query that set the value of datefield to
Left([table1].[textfield],Len([table1].[textfield])-3)

and it seemed to work fine

hope this helps

Cheers
JulieD
 
A

Alp Bekisoglu

Hi John,

I'm sorry to jump in on the issue but you have pointed to something I had
been looking for. Caould you tell me how can I get that date number? I had
once figured it out in Excel but completely forgot how I did it.

I have tried a lot of things in Access to get that numeric value but to no
avail. Your advice will be highly appreciated.

Thanks,

Alp
 
J

John Spencer (MVP)

Use the CDbl function on a valid date.

CDbl(SomeDateField)

OR

Cdbl(CDate("2/29/04 9:01:25 AM")) returns 38046.3759837963

You might want to include the NZ function to handle nulls if your data might be null

Cdbl(Nz(SomeDateField))
 
A

Alp Bekisoglu

Thank you very much both for the info and the advice. I did ask the question
earlier and couldn't get a proper answer then.

Thanks again.

Alp
 
Top