expression is too complex message?

[Jimcb8]

I have a form that is displayed for the user to enter a date.
I wrote a query that I want to use this date(parameter) to calculate the number of days from that date.

For example: Now()-Max(Date of Distribution,DateAttended) <=180
This would take todays date and subtract the largest DateAttended date in table Distribution.

This query works fine.

When I try to substitute the function Now() with the control DateofBook that is on the form that the user entered I get this error. Expression is too complex etc.


Field line expr1: [Forms]![HomePage]![NavegationSubform].[Form]![DateofBook]-Max([Dates of Distribution]![DateAttended])

Criteria line in query <=180

I know that the path for DateofBook is correct because it prints out nicely on the report.

In the report the data source is this query that I wrote.

I tried many different ways to solve this issue. I used parenthesis around enclosing each of the fields to be subtracted for simplicity with no luck.

Any help will be greatly appreciated.

I have been struggling with this for 5 days. I don't want to put a prompt in the query for the field DateBook field, because then the user will be required to enter the same date twice. Once for the query and once on the report form.

Many thanks for your help.

Jim (new user to access2010)

 

[pr2-eugin]

Hello Jimcb8, You could make use of the DateAdd Function to get a Date and see the records that fall below that date rather than doing this way.. Something along the lines of..

SELECT someFields FROM theTableName
WHERE theDateField <= DateAdd("d", -180, [Forms]![HomePage]![NavegationSubform].[Form]![DateofBook]);

Or post your Query, by switching it to SQL view..

 

[Jimcb8]

Thanks for response.

The real problem is that the date the user enters on the form to run the report is NOT being passed to the query that will retrieve the data.
In other words:

expr1: [Forms]![HomePage]![NavegationSubform].[Form]![DateofBook]
in the field line of the query is not being passed from the form.

The path to get to the [DateofBook] value is not being passed to the query.
The query keeps asking me to input a value instead of it using the value in [DateofBook]

The question is can I enter a value in a control(DateofBook) on the form, and have the underlying query use this control value??
It does not seem to let me do this.

Your thoughts are greatly appreciated

Jim

 

[boblarson]

Not sure but could it be your misspelling of Navigation?

NavegationSubform].[

NavigationSubform

 

[Jimcb8]

Thank you, thank you, thank you.
I have been pulling my hair out over this problem.

Sometimes you just need another pair of eyes to see your error.

How can I thank you enough, you are the best.

Very grateful,

Jim