No records returned for query date January to September 3

[BrianLe]

In AC97, I have the following query. It works when the StartUnload date selected on [Forms]![frmCarsUnloadedReport]![tbxStartUnload]
is Oct-Dec. However, when the selected date is Jan-Sept, no records are returned.

The format of the dates in tblCarsUnloaded and the form's tbxStartUnload are both General Date

SELECT tblCarsUnloaded.CarsUnloaded, tblCarsUnloaded.Weight, tblCarsUnloaded.StartUnload, tblCarsUnloaded.FinishUnload, FROM tblCarsUnloaded
WHERE (((Format([tblCarsUnloaded].[StartUnload],'mm/ddyy'))>=[Forms]![frmCarsUnloadedReport]![tbxStartUnload] And (Format([tblCarsUnloaded].[StartUnload],'mm/dd/yy'))<=[Forms]![frmCarsUnloadedReport]![tbxFinishUnload]));

How do I get the query to return records when the selected "StartUnload" date is January to September?

Thanks

Brian

 

[dhookom]

Your SQL should never work since there is a comma to the left of "FROM tblCarsUnloaded..."

I would get rid of the Format function which changes your date to a text value. Also, is there a reason you didn't use "Between...."?

SELECT tblCarsUnloaded.CarsUnloaded, tblCarsUnloaded.Weight, tblCarsUnloaded.StartUnload, tblCarsUnloaded.FinishUnload 
FROM tblCarsUnloaded
WHERE [tblCarsUnloaded].[StartUnload] Between
   [Forms]![frmCarsUnloadedReport]![tbxStartUnload] And
   [Forms]![frmCarsUnloadedReport]![tbxFinishUnload];

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]

 

[HarleyQuinn]

Don't think this would be the cause of what you're seeing but are you really wanting to format like this?

Format([tblCarsUnloaded].[StartUnload],[red]'mm/ddyy'[/red])

HarleyQuinn
---------------------------------
The most overlooked advantage to owning a computer is that if they foul up there's no law against wacking them around a little. - Joe Martin

Get the most out of Tek-Tips, read FAQ222-2244 before posting.

 

[HarleyQuinn]

Well spotted Duane, and a good call on the between.

HarleyQuinn
---------------------------------
The most overlooked advantage to owning a computer is that if they foul up there's no law against wacking them around a little. - Joe Martin

Get the most out of Tek-Tips, read FAQ222-2244 before posting.

 

[traingamer]

Try it without formatting the dates into strings. Or make sure that you format them the same - 'mm/ddyy' vs 'mm/dd/yy'

Greg
"Personally, I am always ready to learn, although I do not always like being taught." - Winston Churchill

 

[traingamer]

Dang - slow again - distracted by office chatter .

Greg
"Personally, I am always ready to learn, although I do not always like being taught." - Winston Churchill

 

[HarleyQuinn]

No danger of that in my office at the minute

HarleyQuinn
---------------------------------
The most overlooked advantage to owning a computer is that if they foul up there's no law against wacking them around a little. - Joe Martin

Get the most out of Tek-Tips, read FAQ222-2244 before posting.

 

[BrianLe]

Thanks to all of you. I edited the query for clarity and forgot to delete the comma. The reason I used the format was that if I select the start and finish date to be the same as a record with the tblCarsUnloaded.StartUnload date, the record would not show.

To address both issues, I now have the following query WHERE that seems to work.

WHERE [tblCarsUnloaded].[StartUnload] Between
   [Forms]![frmCarsUnloadedReport]![tbxStartUnload] And
   ([Forms]![frmCarsUnloadedReport]![tbxFinishUnload] + 1);

Gave a star to Duane, but it won't let me give one to Harley or Greg. Here's one * * for each of you.

Thanks again,

Brian

 

[dhookom]

Your issue was the "time" values attached to your dates. You could be a little more accurate with:

WHERE DateValue([tblCarsUnloaded].[StartUnload]) Between
   [Forms]![frmCarsUnloadedReport]![tbxStartUnload] And
   ([Forms]![frmCarsUnloadedReport]![tbxFinishUnload]);

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]

 

[BrianLe]

I found that if I just previewed my new query, I got an error about it being too complex or something, so it wasn't as good as I thought. Duane, your latest suggestion works great.

Thanks,

Brian