Military conversion

[trustsun]

I need to split this value into two separate fields; 6:00PM [AB-CF], it’s now in one field.

The time value needs to be converted into a military value. Any help, please. So, they need to look like this separate by fields.

1. [AB-CF]
2. 6:00PM but this needs to be in military time.

 

[PHV]

A starting point (SQL code):
SELECT Mid(yourField,InStr(yourField," ")+1) AS Field1, TimeValue(Left(yourField,InStr(yourField," ")-1)) AS Field2
FROM yourTable

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ181-2886

 

[trustsun]

I'm still getting both values in the first field and a error in the second one.

 

[dhookom]

PH's solution worked exactly as he suggested. If you have a problem, you should post back with your query SQL view and the error message you are getting.

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]

 

[cheerio]

PH has assumed that there is a space after the time and he has split the data into what comes after that space and what comes before. This is based on the data you provided.

If you have one or more spaces BEFORE the time the logic above will fail. You would need to remove those spaces with a TRIM function.

 

[trustsun]

OK cheerio there are spaces, this is how the field look: (6:00 PM [AB-CF]), without the ().I need to spilt them into separate fields and converting the time value into military time.

thanks,

 

[PHV]

And this ?
SELECT Mid(yourField,InStr[!]Rev[/!](yourField," ")+1) AS Field1, TimeValue(Left(yourField,InStr[!]Rev[/!](yourField," ")-1)) AS Field2
FROM yourTable

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ181-2886