Projected Date Comparison II

[Guest]

The query field below creates an error: "The expression is typed incorrectly,
or is to complex to be evaluated". The date comparision is killing me. I
can't seem to get it to work. The ExpDateFormatter is a field that formats a
field into mm/dd/yyyy from yyyymmdd and the point of this is to compare the
expiration date to a variable start period . This comparison does not work
at this time.

Please....anybody...help.

Month 2: IIf([ExpDateFormatter]<DateAdd("d",+30,[Last Day of Start
Period]),[qryPBMRateNext.Honor]+[qryPBMRateNext.Discount],[qryPBMRateOrig.Honor]+[qryPBMRateOrig.Discount])

 

[[MVP] S.Clark]

How about using two update queries instead?

One for the first condition, the second for the second?

FWIW, Access doesn't care what format the date is in when it compares it to
another date. So, if ExpDateFormatter is some elaborate calculated field,
it may be adding to the debacle that is this IIF() statement. (Can you tell
I don't like IIF?)

 

[Van T. Dinh]

It sounds to me that [ExpDateFormateer] is a String value which is being
compared with a Date value and the comparison may be performed as a String
comparison, NOT a Date comparison.

Try:

Month 2: IIf( CDate([ExpDateFormatter]) <
DateAdd("d",+30,[Last Day of Start Period]),
[qryPBMRateNext.Honor]+[qryPBMRateNext.Discount],
[qryPBMRateOrig.Honor]+[qryPBMRateOrig.Discount] )

(assuming the Short-Date format in your Regional Settings is "mm/dd/yyyy")