query is 'too complex to be evaluated'

[Tattyfur]

I have a form with a textbox that is being used as a criteria for a query

When I run the query I get 'this expresion is typed incorrectly, or it is too complex to be evaluated. For example, a numeric expression may contain too many complicated elemnts. Try simplifying the expression by assigning parts of the expression to variables.'

The text Box passes a DateAdd function of the form >=DateAdd("m",-3,Date())

If I enter this value directly the query works, if it is entered as [Forms]![ReportSection].[DateQuery] it throws up this error.

I can't think of any way to simplify this expression to give me what I need.

Please someone help!

Here is my query

SELECT Invoice.DateSent, DealerDistributor.DealerDistName, Invoice.InvoiceNumber, NewUnit.UnitType, NewUnit.Zones, NewUnit.Upgrade, NewUnit.TeamviewerCode
FROM (DealerDistributor INNER JOIN Invoice ON DealerDistributor.DealerDistName = Invoice.DealerDistName) INNER JOIN NewUnit ON Invoice.InvoiceNumber = NewUnit.InvoiceNumber
WHERE (((Invoice.DateSent)=[Forms]![ReportSection].[DateQuery]) AND ((NewUnit.Status)="Sent"));

 

[dhookom]

The [DateQuery] control value must be a date value. It can't contain any operators such as "=" or ">=" or "<>".

Duane
Hook'D on Access
MS Access MVP

 

[Tattyfur]

How can I pass the query a range of Dates to process then? Is there something simple I can do?

 

[lameid]

Do you mean you want a query with that as your criteria?

SELECT Invoice.DateSent, DealerDistributor.DealerDistName, Invoice.InvoiceNumber, NewUnit.UnitType, NewUnit.Zones, NewUnit.Upgrade, NewUnit.TeamviewerCode
FROM (DealerDistributor INNER JOIN Invoice ON DealerDistributor.DealerDistName = Invoice.DealerDistName) INNER JOIN NewUnit ON Invoice.InvoiceNumber = NewUnit.InvoiceNumber
WHERE (((Invoice.DateSent)>=DateAdd("m", -3, [Forms]![ReportSection].[DateQuery])) AND ((NewUnit.Status)="Sent"));

Or do you mean you want some sort to build your criteria dynamically?

In this case you would modify the SQL statemt for the query in code.... To get you started, it is the SQL property of the DAO querydef object.

 

[dhookom]

I would use two text boxes for a range of dates.

Duane
Hook'D on Access
MS Access MVP

 

[Tattyfur]

Between the two of you I have managed to get it to work, Cheers guys!