strange date problem 2

[simon551]

Hi,

I have a query that filters records between dates based on form fields. The query is only returning the transactions in the months of the dates in the form fields.

Example:
The form dates are 1/1/2006 and 10/25/2006 and the query is only returning records with a date of January or October.

This is the criteria: Between [Forms]![frmSelections]![txtDate1] And [Forms]![frmSelections]![txtDate2]

And this is the field:
TransDate: Format([Date],"Short Date")

I had to put the format statement in there because the data was formatted as YYYY-MM-DD, but I didn't know how to handle that format.

The query w/out the criteria returns 1082 records. With the criteria, 147 records. All the records in the table are between 1/1/06 and 10/25/06.

?

 

[lespaul]

Is the field in the table a true date or is it text? Is the field really named [Date]?

Leslie

Anything worth doing is a lot more difficult than it's worth - Unknown Induhvidual

Essential reading for anyone working with databases:
The Fundamentals of Relational Database Design
Understanding SQL Joi

 

[SkipVought]

Him

The Format function returns TEXT and not a date

Conseqently, your collation is MONTH then DAY then YEAR which will NOT give you the results you want.

So you mst CONVERT the string to a real date

Between #[Forms]![frmSelections]![txtDate1]# And #[Forms]![frmSelections]![txtDate2]#

Skip,

_[red][/red]

 

[simon551]

Thanks!

Skip, I get this error message: "The expression you entered has an invalid date value."

Leslie, it is date right now and called date, however, it was text before (many moons ago).

 

[SkipVought]

Date is a reserve word. I would not name a field Date

Are both txtDate1 & txtDate2 strings in mm/dd/yyyy format?

A REAL DATE is a NUMBER not a STRING.

Today's DATE is 39016. That DATE VALUE can be FORMATTED for display in a number of different ways that does not alter the VALUE. Using the FORMAT function returns a STRING that lokks like a date but is NOT A REAL DATE.

Bottom line: Either work with ...

ALL DATES or

ALL STRINGS that are FORMATTED to collate in YYYY MM DD sequence.

Skip,

_[red][/red]

 

[simon551]

Thanks for your help, Skip. The date field is an exported field from another database. I don't have the ability to change the name in the other database.

txtDate1 and txtDate2 are unbound text boxes on a form. I'm not sure how to format them as YYYY MM DD as you recommend.

I tried:
TransDate: Format([Date],"yyyy/mm/dd")
This formats correctly, as viewed w/out criteria

and then in the form, typing yyyy/mm/dd for the format.
This returns zero records (I also tried this as "YYYY/MM/DD")

 

[SkipVought]

"...collate in YYYY MM DD sequence"

YYYY MM DD is a COLLATE SEQUENCE not a FORMAT.

When dealing with STRING data, the COLLATE SEQUENCE is very important.

Which of these STRINGS will collate (SORT) first...
[tt]
"01/01/2006"
"02/01/2005"
"03/01/2004"
[/tt]
They COLLATE in the order in which I entered them. NOT GOOD!

In order to COLLATE correctly, then need to be in YEAR, MONTH, DAY sequence (YYYY MM DD), so the STRINGS must be formatted...
[tt]
"2004/03/01"
"2005/02/01"
"2006/01/01"
[/tt]


Skip,

_[red][/red]

 

[TheAceMan1]

. . . and this:

[blue]#Format([[purple][b]DateField[/b][/purple]],"mm/dd/yyyy")# Between #Format([Forms]![frmSelections]![txtDate1],"mm/dd/yyyy")# And #Format([Forms]![frmSelections]![txtDate2],"mm/dd/yy")#[/blue]

See Ya! . . . . . .