I've made a form where people can search for tickets.
On my form I have a field where the user can give in a day (e.g. 2) (=Reported Date X)
Then the user can select from a combobox: 5 - 10 - 15 - 30 (=Reported Date Y)

Now I want a query that shows all the tickets that are 5,10,15 or 30 days (Reported Date Y) old after 2 days (Reported Date X) (example above).

So the question is: he needs to show all tickets that are Y-days old after X-days.

For this I've made a Now() textbox on my form. In the query I've given in this:
Between [Forms]![PROJECT]![Now_Tekst + Reported Date X] And [Forms]![PROJECT]![Now_Tekst + Reported Date Y]

But this doesn't work. Does anyone have a suggestion?

Kind regards
Dries

Try:

Between ([Forms]![PROJECT]![Now_Tekst]+[Forms]![PROJECT]![Reported Date X]) AND ([Forms]![PROJECT]![Now_Tekst]+[PROJECT]![Reported Date X]+[Forms]![PROJECT]![Reported Date Y])

However your field Now_Text in the form Projects I assume you have set its default property to =Date() and formatted it to Short Date, or..?

You could perhaps use the Date() directly in the query and elliminate Now_Tekst field.

Between ((Date()+[PROJECT]![Reported Date X])) AND ((Date()+[PROJECT]![Reported Date X]+[Forms]![PROJECT]![Reported Date Y]))

JR

But this doesn't work. Does anyone have a suggestion?



how does it not work? that is - do you see no records returned? or all of them? or the wrong ones? is it giving you an error? prompting you for data? what?

You could also look into the DateDiff function and see if that helps

JR

NOW() returns the current time as well as the date, which can make comparing dates tricky, so unless you specifically need a time component to your date DO NOT use NOW(), If all you require is the current date use Date()

If you haven't got it to work, here is a samplebase. I'v moved the calculation to the form and used a simpler Between clause.

JR