I'm at a loss...ready to jump out the window

[Crevalle]

I have a table with dates in the format of "8/1/2004 16:56:21 PM" and I am having great difficulty simply running a "between" dates query.

Initially, I had it set up as: between [enter beginning date] and [enter end date]. This skips some records. If I manually put ">=#8/1/2004# and <=#9/16/2004#" it will work perfectly. Problem is, I need to allow the user to enter the range. I've tried every combo I can think of, including:

"Between [enter beg date] & " 00:00:00 AM" and [enter end date] & " 23:59:59 PM" and it will not work-- it will continue to skip records between 9/2 and 9/9. The dates are all coming from the same table, and they are formatted identically.

I've spent a good 7+ hours trying to figure this out, and I'm about ready to throw in the towel. I'm just too frustrated. I'll keep checking back to see if you can recommend anything. Thanks...

 

[SkipVought]

Crevalle,

Realize that in the MS world, Date/Time values are really numbers representing DAYS since 1/1/1900, and Time is a fraction of a DAY.

So is you are getting a Start Date, which is REALLY a number, and you want to ADD a time value, you don't want to use string concatenation, you ought to use arithmetic operators like...

Between [enter beg date] + #00:00:00# and [enter end date] + #23:59:59#

Skip,
[sub]
[red]Be advised:[/red] When transmitting sheet music...
If it ain't baroque, don't fax it! [/sub]

 

[lespaul]

maybe you can format the date field you are looking at to what the user enters:

WHERE Format(DateField(),"mm/dd/yyyy") BETWEEN [enter begin date] and [enter end date]

Leslie

 

[Crevalle]

SkipVought--I got an error message (after I entered the dates in the prompts) that read:

The expression is typed incorrectly, or is too complex to be evaluated. For example, a numberic expression may contain too many complicated elements. Try simplifying the expression by assigning parts of the expression to variables.

I wasn't sure what this meant. FWIW, the dates are originally in text format, and I had to convert them to dates in the format I originally mentioned.

Lespaul--I need to keep the hours because I have to do calculations to the decimal. For example, I need to be able to compute that a particular ticket took 4.56 days to complete, rather than just 4. How could I expand upon your formula to include this?

Thanks guys--I'll wait to hear from you.

 

[Crevalle]

FWIW, this is the formula I use to convert the original text-formatted dates into actual dates. This formula creates the dates I am trying to query against. The dates were originally in text format like this: 2004-07-23-07.53.28.625619

CDate(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)) & " " & Mid([TCR_R_INQ_STATUS]![UPDATE_DATE],12,2) & ":" & Mid([TCR_R_INQ_STATUS]![UPDATE_DATE],15,2) & ":" & Mid([TCR_R_INQ_STATUS]![UPDATE_DATE],18,2))

 

[SkipVought]

Try this

Between Format([enter beg date], "General Date") and Format([enter end date], "General Date") + #23:59:59#

This converts the string to a date.

Actually + #00:00:00# adds NOTHING other than intent.


Skip,
[sub]
[red]Be advised:[/red] When transmitting sheet music...
If it ain't baroque, don't fax it! [/sub]

 

[lespaul]

Ok, if your SQL is:

Select DateField From tblName WHERE Format(DateField(),"mm/dd/yyyy") BETWEEN [enter begin date] and [enter end date]

your datefield result should still have the time.

If we did:

SELECT Format(DateField(),"mm/dd/yyyy") From tblName WHERE Format(DateField(),"mm/dd/yyyy") BETWEEN [enter begin date] and [enter end date]

then your datefield result would only have the date portion.

Right?


Leslie