Records between a selected date and 30 days prior

[robnsd]

I want to query records between a date the user selects and 30 days prior. I tired the below criteria but I get an error message. How do I do this?


Between [Enter Date:] And ([Enter Date:]-30)

 

[boblarson]

1. I would use a form instead of parameters.

2. You have them backwards. It needs to be:

Between [Enter Date:]-30 AND [Enter Date:]

 

[robnsd]

I changed the query as you stated but I still get an error message when I run it.

Error message:
"The expression is typed incorrectly, or it is too complex to be evaluated. For example, a numeric expression may contain too many complicated elements. Try simplifying the expression by assigning parts of the expression to variable."

I should let you know I have another criteria on a computed value field. The filed is [Variance] + [Adjustment1] + [Adjustment2] and the criteria is Between 1 And 49.99 Or Between -1 And -49.99

The query works when I just have it go back 30 days from the current date.

 

[boblarson]

You may have nulls you have to deal with:

Nz([Variance],0) + Nz([Adjustment1],0) + Nz([Adjustment2],0)

Also, as I said before, I would not use parameter prompts but instead use a FORM where you can select the dates and then reference the form. Read this about parameter prompts.

 

[gemma-the-husky]

i don't think the order matters

between is between

so between earlierdate and laterdate
and between laterdate and earlierdate

are both the same

 

[boblarson]

i don't think the order matters

between is between

so between earlierdate and laterdate
and between laterdate and earlierdate

are both the same

You may be right but be that as it may, the real reason for the problem is that Access doesn't like subtracting directly from the EnterDate parameter prompt. So, the use of DateAdd is crucial in this instance (based on testing):

So this instead:

Between DateAdd("d", -30, [Enter Date:]) AND [Enter Date:]