Parameter Query with Between

[Walker5]

I have a parameter query where I want to get a week's worth of info so I put this in the WHERE clause:

Between [Begin Date] And [Begin Date]+6

Unfortunatley I run into error 3071:
"This 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 variables."

Any ideas as to how I can get around this?

 

[xenou]

You could *try* giving Access lots of hints with parenthesis, such as:
Between (([Begin Date]) And (([Begin Date])+6))

An alternative syntax:
>=[Begin Date] AND <=([Begin Date] + 6)

Or (more robust in case dates have time portions stored):
>=[Begin Date] AND <([Begin Date] + 7)

 

[Walker5]

Good thinking, I had already tried both of those. I think I just figured it out with this:
Between [Begin Date] And DateAdd("d",6,[Begin Date])
It seems to be working, now I just need to test it.

 

[xenou]

An afterthought: maybe the original would work if you explicitly define your parameters as dates (under Query | Parameters in Access 2003 and somewhere in the ribbon in Access 2007/2010).

 

[Walker5]

Thanks for your time, Xenou. I couldn't find it in the ribbon (which is not unusual) but I did change the format to "short Date" in the properties box of that field but it still didn't work. Oh, well. Thanks again.

 

[boblarson]

My personal view is you should AVOID parameter prompts for queries and use an INPUT FORM for selecting/using values. There are many reasons but some are

1. You can ensure the correct format is recognized.

2. The user, if they make a mistake, doesn't have to go through all of the prompts again and type it all over.

and there are others but those are the big ones.