Between Dates on Query giving trouble

  • Thread starter Thread starter Andrew Tatum
  • Start date Start date
A

Andrew Tatum

Hello,

I have a search form that has a "Start" and "End" field. The entire
form works perfectly for other fields IF I don't have the following in
the query:

Between [Forms]![Message Report - Company]![qSendOnLess] And
[Forms]![Message Report - Company]![qSendOnLast]

So, in the criteria for the SendOn field I have the following above. If
that criteria is there, the query/form won't work. As soon as I take
that criteria out... everything works.

I would love to keep the Start and End fields on the form... but it
just won't work right now. What am I doing wrong here?
 
Are "Start" and "End" unbound text boxes OR labels on the form for text boxes?

Are [qSendOnLess] and [qSendOnLast] text boxes?
 
KARL said:
Are "Start" and "End" unbound text boxes OR labels on the form for text boxes?

Are [qSendOnLess] and [qSendOnLast] text boxes?
Click to expand...

As Karl mentions, knowing the source of what the values are helps, as
well as the data type of the field you are using this as the criteria of.

If its a date field, your criteria dates need to be wrapped in #. If
it's a string, it needs to be wrapped in ', etc.
 
Try telling Access the type of data to expect.

Between CDate([Forms]![Message Report - Company]![qSendOnLess]) And
CDate([Forms]![Message Report - Company]![qSendOnLast])

Also, Doesn't work is not descriptive of the problem. Better would be

I get a syntax error
I get no records returned
I get the wrong records returned
I get the following error message.
 
Back
Top