Divide Date Time Stamp

[Eskimo]

In a query, I have a series of fields that can be queried okay.

However, I am trying to run a sub-query or new query on the original query
that will allow me to Manipulate the value. With some luck I came up with
this.

Now the original dataset has a date-time combination field labelled
"Acq_Time" and the format of that field is yyyy.mm.dd hh:mm:ss.

I was able to pull out the date portion only by typing and expression
Date: Left[(Acq_Time, 10) and this gave me a good result like yyyy.mm.dd

However now, I would like to convert that date result to a proper yyyy/mm/dd

Any idea how I can do that?

Thanks,
Eskimo

 

[Jeff Boyce]

It isn't clear from your description whether the field holds a true MS
Access Date/Time value or merely some text that looks like a date & time.

If the field is designated as a Date/Time value, you can use formatting to
display it in many different ways, including the one you requested
("yyyy/mm/dd").

Regards

Jeff Boyce
Microsoft Office/Access MVP

 

[Jerry Whittle]

You're working too hard. The following in a query will produce a string in
your requested format. It will have leading zeros. No need for the Left.
Don't call the field "Date" as that's a reserved word and just might cause
some grief.

TheDate: Format([Acq_Time], "yyyy/mm/dd")

 

[Lord Kelvan]

if i understand you try this

date: Format([Acq_Time],"yyyy/mm/dd")



regards
kelvan

 

[Eskimo]

Hi Jeff,

I did not know if it was an issue, but the field holds just plain text and
not a true MS Access Date/Time value.

I'll re-run the import to make sure its date/time. and try the suggestions
that follow.

Thanks,

Jonathan

It isn't clear from your description whether the field holds a true MS
Access Date/Time value or merely some text that looks like a date & time.

If the field is designated as a Date/Time value, you can use formatting to
display it in many different ways, including the one you requested
("yyyy/mm/dd").

Regards

Jeff Boyce
Microsoft Office/Access MVP

In a query, I have a series of fields that can be queried okay.

However, I am trying to run a sub-query or new query on the original query
that will allow me to Manipulate the value. With some luck I came up with
this.

Now the original dataset has a date-time combination field labelled
"Acq_Time" and the format of that field is yyyy.mm.dd hh:mm:ss.

I was able to pull out the date portion only by typing and expression
Date: Left[(Acq_Time, 10) and this gave me a good result like yyyy.mm.dd

However now, I would like to convert that date result to a proper
yyyy/mm/dd

Any idea how I can do that?

Thanks,
Eskimo

 

[Jeff Boyce]

Thanks for the clarification.

Access handles values it believes to be Date/Time values differently than it
handles values it believes to be "just text". ... and provides different
functions for strings than it does for dates...

Regards

Jeff Boyce
Microsoft Office/Access MVP

Hi Jeff,

I did not know if it was an issue, but the field holds just plain text and
not a true MS Access Date/Time value.

I'll re-run the import to make sure its date/time. and try the suggestions
that follow.

Thanks,

Jonathan

It isn't clear from your description whether the field holds a true MS
Access Date/Time value or merely some text that looks like a date & time.

If the field is designated as a Date/Time value, you can use formatting
to
display it in many different ways, including the one you requested
("yyyy/mm/dd").

Regards

Jeff Boyce
Microsoft Office/Access MVP

In a query, I have a series of fields that can be queried okay.

However, I am trying to run a sub-query or new query on the original
query
that will allow me to Manipulate the value. With some luck I came up
with
this.

Now the original dataset has a date-time combination field labelled
"Acq_Time" and the format of that field is yyyy.mm.dd hh:mm:ss.

I was able to pull out the date portion only by typing and expression
Date: Left[(Acq_Time, 10) and this gave me a good result like
yyyy.mm.dd

However now, I would like to convert that date result to a proper
yyyy/mm/dd

Any idea how I can do that?

Thanks,
Eskimo