Between dates with lengthy formats

[Crevalle]

We are going nuts here attempting to figure out how to use our query to pull records (by date) from a table that contains dates in the following format: 2004-08-16-11.00.45.148210

We would like to, for instance, pull records for dates between 8/1/2004 and 8/11/2004 (hours/mins/secs are really not important to us). We cannot simply change the format of the table, as it is linked, and populated by another department automatically).

Simply put, we'd like for a user to be able to input between "8/1/2004" and "8/11/2004" and pull back results, without having to go through the ridiculous task of inputting "8/1/2004 11.00.45.148210" etc.

Can anyone help us?

 

[dhookom]

Crevalle,
What is the data type of your "date" field? Is it text?
I expect you could use
Between Format([Enter Start],"yyyy-mm-dd") and Format([Enter End],"yyyy-mm-dd")
Since your stored values include a time element, enter and ending date of the next date or add .9999 to the value entered by the user.

Duane
MS Access MVP
Find out how to get great answers faq219-2884.

 

[flyover789]

you could try to add the format in your query.
something like:

where format(yourtable.thedate;"mm/dd/yyyy") between "8/1/2004" and "8/11/2004"

just typed, not tested

HTH,
fly

[blue]Typos, that don't affect the functionality of code, will not be corrected.[/blue]

Martin Serra Jr.

http://www.mserrasystems.com

 

[Crevalle]

dhookom--The date is text formatted.

I will try both of your suggestions and will report back. Thanks for such quick replies--I really do appreciate it (and so does everyone else with me!)

 

[Crevalle]

I just realized that, since the table holds the "date" in text format, I cannot implement either suggestion, correct?

 

[Crevalle]

Sorry for the multiple posts, but thought this info may help. We have been using the following formula to adjust the text-formatted date (but it doesn't work 100%):

Month(Left([TCR_R_INQ_STATUS]![UPDATE_DATE],10)) & "/" & Day(Left([TCR_R_INQ_STATUS]![UPDATE_DATE],10)) & "/" & Year(Left([TCR_R_INQ_STATUS]![UPDATE_DATE],10))

Can this be used in a solution to our problem?

 

[flyover789]

No, I believe, that both suggestions should work anyway.

If not you could also try using something like
Format(Right(yourtable.thedate;10);"mm/dd/yyyy")

just try it!

HTH,
fly

[blue]Typos, that don't affect the functionality of code, will not be corrected.[/blue]

Martin Serra Jr.

http://www.mserrasystems.com

 

[PHV]

Have you tried something like this ?
WHERE CDate(TCR_R_INQ_STATUS.UPDATE_DATE) Between #8/1/2004# And #8/11/2004#

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

 

[Crevalle]

PHV--I tried that, and it worked like a charm! Thanks!

I'm sure the rest of your suggestions would have worked too, but I must have been implementing them incorrectly. Thanks to all--I really appreciate it!

 

[MichaelRed]

I would use a calculated field and a short function to generate the "MS" version of the date variables.

The Function:

Public Function basStr2Date(strDtIn As String) As Date

    'Purpose: Retrieve the Date part of a "Dash" seperated String
    'The format of the string is:       2004-08-16-11.00.45.148210
    'Michael Red 8/16/2004

    'Sample Usage:      ? basStr2Date("2004-08-16-11.00.45.148210") _
                        8/16/2004

    Dim MyAry() As String

    MyAry = Split(strDtIn, "-")

    basStr2Date = DateValue(MyAry(1) & "/" & MyAry(2) & "/" & MyAry(0))

End Function

The only requirement / expectation is that the string consist of "hyphen (or dash) seperated values with the first three elements representing the year, month and day, respectively. SEt up your criteria on the calculated field.





MichaelRed
mlred@verizon.net