Selection between dates for computed field

[Guest]

I have a database where I keep data on contracts. I want to be able to run a
report to select contracts which are terminating based on the # of months of
the contract term but I want to select the data based on 2 months prior to
the termination date so I can send them notification of the impending
termination.

I have a table where I input the “Contract Start Dateâ€(xx/xx/xxxx) and
“Contract Period(# of Months). I compute the “Contract End Dateâ€(
ContractTermination: DateAdd("m",[ContractPeriod],[ContractStartDate])) and
“Notification Dateâ€( NotifDate:
DateAdd("m",[ContractPeriod]-2,[ContractStartDate])) which is 2 months prior
to the termination date.

I want to select data based on the “Notification Dateâ€. For my selection
criteria I use “Between [Forms]![Main Switchboard]![BegDate] And
[Forms]![Main Switchboard]![ EndDate] to refer back to controls on my
switchboard where I enter the range for the notification dates I want to
review.

When I attempt to run the report I get the message, “The expression is typed
incorrectly, or it is too complicated to be evaluated.†I have tried several
query combinations to compute some of the fields in one and selection
criteria in another. If I eliminate the reference to the controls on the
switchboard the query works OK but I need to specify the date range.

I would certainly appreciate and help you can give me. Thanks

 

[Marshall Barton]

I have a database where I keep data on contracts. I want to be able to run a
report to select contracts which are terminating based on the # of months of
the contract term but I want to select the data based on 2 months prior to
the termination date so I can send them notification of the impending
termination.

I have a table where I input the “Contract Start Date”(xx/xx/xxxx) and
“Contract Period(# of Months). I compute the “Contract End Date”(
ContractTermination: DateAdd("m",[ContractPeriod],[ContractStartDate])) and
“Notification Date”( NotifDate:
DateAdd("m",[ContractPeriod]-2,[ContractStartDate])) which is 2 months prior
to the termination date.

I want to select data based on the “Notification Date”. For my selection
criteria I use “Between [Forms]![Main Switchboard]![BegDate] And
[Forms]![Main Switchboard]![ EndDate] to refer back to controls on my
switchboard where I enter the range for the notification dates I want to
review.

When I attempt to run the report I get the message, “The expression is typed
incorrectly, or it is too complicated to be evaluated.” I have tried several
query combinations to compute some of the fields in one and selection
criteria in another. If I eliminate the reference to the controls on the
switchboard the query works OK but I need to specify the date range.

Looks ok to me, except for the extra space in the name of
the end date text box.

In the future, post a Copy/Paste of the query's SQL
statement instead of trying to describe it with words. It
saves you retyping things and also saves everyone's time
debugging posting typos.

 

[Jeff Boyce]

Don

This may be a bit of a kludge...

Remove the selection criteria from your query.

Create a second query that uses your existing query as its source.

Add the selection criteria to the second query.

--
Regards

Jeff Boyce
Microsoft Office/Access MVP


Microsoft IT Academy Program Mentor
http://microsoftitacademy.com/

Microsoft Registered Partner
https://partner.microsoft.com/