Query expression needed to edit data

  • Thread starter Thread starter BobV
  • Start date Start date
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
 
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
 
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
 
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))
 
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
 
Back
Top