"Between" and "And"

[Bill]

I am trying to figure out why the month of August 2007 is generating "too
complex" errors in the control source of a text box in a report. This is the
formula:
"Between" & " " & [Enter Start Date] & " " & "And" & " " & [Enter End Date]

When I Enter any combination of dates except for ones that contain
"08/01-31/07", it works fine, HELP!

Bill

 

[Allen Browne]

Access generates the "too complex" message if it can't understand the SQL
statement. There can be lots of reasons, but the problem here is that you
are trying to treat *operators*, not just values, as if they were
parameters.

That approach is not going to work. If you switch your query to SQL View,
you will see that it interprets it as nonsense such as:
WHERE MyField = "Between ...
Clearly the date field doesn't match that text. In fact trying to match the
text to the date value doesn't make any sense. And since Access can't make
any sense of the query, you get the error.

If you are filtering just on these dates, see:
Limiting a Report to a Date Range
at:
http://allenbrowne.com/casu-08.html
for a couple of ways to handle this.

If you are actually trying to filter on lots of fields (including the date
range), this should help:
Search form - Handle many optional criteria
at:
http://allenbrowne.com/ser-62.html

 

[Bill]

Thank You Allen for your response. You too ScubaDiver:

I tried your first method Allen. It works for the query not matter what date
I choose. For the display on the report in the header, i still get the error
when I include August 2007 Date(s). Sept 01- Dec 31 2007 works as well even
though those dates are not here yet. I could not find where I would have a
look at the code in the report. Still need some help!

Bill

Access generates the "too complex" message if it can't understand the SQL
statement. There can be lots of reasons, but the problem here is that you
are trying to treat *operators*, not just values, as if they were
parameters.

That approach is not going to work. If you switch your query to SQL View,
you will see that it interprets it as nonsense such as:
WHERE MyField = "Between ...
Clearly the date field doesn't match that text. In fact trying to match the
text to the date value doesn't make any sense. And since Access can't make
any sense of the query, you get the error.

If you are filtering just on these dates, see:
Limiting a Report to a Date Range
at:
http://allenbrowne.com/casu-08.html
for a couple of ways to handle this.

If you are actually trying to filter on lots of fields (including the date
range), this should help:
Search form - Handle many optional criteria
at:
http://allenbrowne.com/ser-62.html

--
Allen Browne - Microsoft MVP. Perth, Western Australia

Reply to group, rather than allenbrowne at mvps dot org.

I am trying to figure out why the month of August 2007 is generating "too
complex" errors in the control source of a text box in a report. This is
the
formula:
"Between" & " " & [Enter Start Date] & " " & "And" & " " & [Enter End
Date]

When I Enter any combination of dates except for ones that contain
"08/01-31/07", it works fine, HELP!

Bill

 

[Allen Browne]

If you open your table in design view, and look at the field you are
applying these criteria to, is it a Date/Time field? Or is it a Text field?

The filtering will not work correctly if it is a Text field.
If it is a date field, make sure you performed Step 3 on the Method 1
section of:
http://allenbrowne.com/casu-08.html
Declaring the parameters will help Access understand the data type.

If you use unbound text boxes on a form, be sure to set their Format
property to Short Date or similar, so Access treats them as dates.

If you still get all dates, after opening Report1, press Ctrl+G to open the
Immediate Window, and enter this:
? Reports![Report1].Filter
substituting your report name for Report1.
Post back what it says.

--
Allen Browne - Microsoft MVP. Perth, Western Australia

Reply to group, rather than allenbrowne at mvps dot org.

Thank You Allen for your response. You too ScubaDiver:

I tried your first method Allen. It works for the query not matter what
date
I choose. For the display on the report in the header, i still get the
error
when I include August 2007 Date(s). Sept 01- Dec 31 2007 works as well
even
though those dates are not here yet. I could not find where I would have a
look at the code in the report. Still need some help!

Bill

Access generates the "too complex" message if it can't understand the SQL
statement. There can be lots of reasons, but the problem here is that you
are trying to treat *operators*, not just values, as if they were
parameters.

That approach is not going to work. If you switch your query to SQL View,
you will see that it interprets it as nonsense such as:
WHERE MyField = "Between ...
Clearly the date field doesn't match that text. In fact trying to match
the
text to the date value doesn't make any sense. And since Access can't
make
any sense of the query, you get the error.

If you are filtering just on these dates, see:
Limiting a Report to a Date Range
at:
http://allenbrowne.com/casu-08.html
for a couple of ways to handle this.

If you are actually trying to filter on lots of fields (including the
date
range), this should help:
Search form - Handle many optional criteria
at:
http://allenbrowne.com/ser-62.html

I am trying to figure out why the month of August 2007 is generating
"too
complex" errors in the control source of a text box in a report. This
is
the
formula:
"Between" & " " & [Enter Start Date] & " " & "And" & " " & [Enter End
Date]

When I Enter any combination of dates except for ones that contain
"08/01-31/07", it works fine, HELP!

Bill