Convert to date and time question?

[freebd99]

I recenty imported some data from a MS SQL dbase into MS Access. I the comma delimeted text file the date appear as "2004/07/23:05:05:20 PM" but when I bring it into Access it becomes "[2004/07/23:05:05:20 PM]". I am trying to track some website activity and want to manage the time spent looking at the site with this data. How can I convert "[2004/07/23:05:05:20 PM]" to a time and date that Access and will not treat as text?

Thanks for your replies.

 

[dhookom]

You can try:
DateValue(Left(YourField,10)) + TimeValue(Right(YourField, 11))

Duane MS Access MVP
[green]Ask a great question, get a great answer.[/green] [red]Ask a vague question, get a vague answer.[/red]
[green]Find out how to get great answers faq219-2884.[/green]

 

[freebd99]

Sorry if this is a dumb question, but how do I apply this code to the data.

Thanks for your reply.

 

[dhookom]

You can use these expressions in a query. Create a field/column with an expression like:
TheDateTime: DateValue(Left(YourField,10)) + TimeValue(Right(YourField, 11))

Do you actually want to calculate this value and store it back into your table? If so you will need to use an update query with this expression.


Duane MS Access MVP
[green]Ask a great question, get a great answer.[/green] [red]Ask a vague question, get a vague answer.[/red]
[green]Find out how to get great answers faq219-2884.[/green]

 

[freebd99]

OK, That expression only works when the brackets are removed. I removed the brackets from a few records and tested the expression and it works now the problem is how do I remove the brackets using an update query. The total records in the file is 20,000+ so how can I remove the brackets from the values that look like this: [2004/07/23:05:05:20 PM]

Many thanks for your help.

 

[dhookom]

Sorry, I didn't realize the []s were part of the data. You can use the Mid() function to extract characters from the middle of a string. If you can't figure this out, come back and ask. Please try to work this out on your own.

Duane MS Access MVP
[green]Ask a great question, get a great answer.[/green] [red]Ask a vague question, get a vague answer.[/red]
[green]Find out how to get great answers faq219-2884.[/green]

 

[freebd99]

I solved my problem using the following for the left and right brackets,

UPDATE visithist SET visit_history_create_date = Left([visit_history_create_date],22);

Once the bracket was removed I used your expression then converted the field to date/time.

Many thanks for taking time to repond to my post.

One other question, is there a way that I can import this data so the when the file is saved it doesn't add the brackets. I'm using software that communicates with the databases on the SQL Server that I can specify what records to select by creating my own select statements as opposed to just downloaded the all the records?

 

[dhookom]

I'm surprised the []s ever appear in the data. I expect some other software is adding them.

Duane MS Access MVP
[green]Ask a great question, get a great answer.[/green] [red]Ask a vague question, get a vague answer.[/red]
[green]Find out how to get great answers faq219-2884.[/green]