Selection between dates for computed field


G

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
 
Ad

Advertisements

M

Marshall Barton

Don S. said:
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.
 
Ad

Advertisements


Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Top