Second set of eyes please?

[Timtropolis]

good morning all,

I am currently going buggy in trying to get a piece of SQL code to work.

To me this seems pretty straight forward but no matter how I code this, I am getting no results. The date range on the form is #1/1/1900# and Now(). I've also substituted #5/10/2006# for Now() with no luck. There is valid data so I know that's not it. The problem seems to be with the criteria because once I remove that I get results, but I'll be darned if I can see what I'm doing wrong.

SELECT tblUTD.[Event Id]
FROM tblUTD
WHERE (((tblUTD.[Start Occurrence]) Between [Forms]![filter_form]![Txt_OccurenceDate_Start] And [Forms]![filter_form]![Txt_OccurenceDate_end]));

the tblUTD.[Start Occurrence]) field is defined as a date in the table.
The two form input fields are defined as short dates. Anyone see anything
wrong here??

TIA,
Tim

 

[Smart]

SELECT tblUTD.[Event Id]
FROM tblUTD
WHERE (((tblUTD.[Start Occurrence]) Between [Forms]![filter_form]![Txt_OccurenceDate_Start] And [Forms]![filter_form]![Txt_OccurenceDate_end]));

Have you tried creating a query with the above selections to produce results
then looking at the sql it produces

 

[Timtropolis]

That's exactly what I did do here... the query as its written in my original post was copied and pasted right from Access and created via the graphic u/i. I have also written the query with "hard" dates (as opposed to references from the input form). When I did that, the query produced correct results.

 

[neileg]

You need to wrap the criteria in # marks.

WHERE (((tblUTD.[Start Occurrence]) Between #[Forms]![filter_form]![Txt_OccurenceDate_Start]# And #[Forms]![filter_form]![Txt_OccurenceDate_end]#));