Re: Third time's a charm? Date query question

Well, it looks as if I can't count parentheses.

Month2: IIF(DateValue(Format([EXPIRE_DATE],"@@@@\/@@\/@@")) <
DateAdd("M",1,[Last Day of the Period]),
[qryPBMRateNext].[Honor]+[qryPBMRateNext].[Discount],
[qryPBMRateOrig].[Honor]+[qryPBMRateOrig].[Discount])


Again, let me say, that the best way to trouble shoot is to take it in pieces.
First: DateValue(Format([EXPIRE_DATE],"@@@@\/@@\/@@"))

Next: Month2: IIF(DateValue(Format([EXPIRE_DATE],"@@@@\/@@\/@@")) <
DateAdd("M",1,[Last Day of the Period]), True, False)

Hopefully I got the suggested statement correctly and you won't need to trouble
shoot this step by step.



Tim Whitley wrote:

I'm still new at this but the expression you suggested is still too complex.
In regard to the calculated field. The ExpDateFormatter converts the
yyyymmdd string to mm/dd/yyyy. It used the
"Format([EXPIRE_DATE])),"@@@@\/@@\/@@)" that you placed in the expression. I
thought by using the seperate field, it would simplify the expression enough
to run. Either way, it's still too complex.

"John Spencer" wrote:

From one of your other posts, I see that ExpDateFormatter is a CALCULATED field.
You will have to use the calculation instead of referencing it.


Try the following and see if it works:

Month2: IIF(DateValue(Format([EXPIRE_DATE])),"@@@@\/@@\/@@")) <
DateAdd("M",1,[Last Day of the
Period]), True, False)

Month2: IIF(DateValue(Format([EXPIRE_DATE])),"@@@@\/@@\/@@")) <
DateAdd("M",1,[Last Day of the
Period]), [qryPBMRateNext].[Honor]+[qryPBMRateNext].[Discount],
[qryPBMRateOrig].[Honor]+[qryPBMRateOrig].[Discount])



John Spencer wrote:

First: What is ExpDateFormatter? It looks as if it is a field. If it is a
field is it a date field or text field that contains a string that looks
like a date?

Try breaking your statement down into parts and testing each part. For
instance, does this work or do you get an error>

Month2: IIF(DateValue([ExpDateFormatter) < DateAdd("M",1,[Last Day of the
Period]), True, False)

How about
Calc1: [qryPBMRateNext.Honor]+[qryPBMRateNext.Discount]

Calc2: [qryPBMRateOrig.Honor]+[qryPBMRateOrig.Discount]

Those both should fail as the appear to be incorrectly bracketed.

They should probably be
Calc1: [qryPBMRateNext].[Honor]+[qryPBMRateNext].[Discount]

Calc2: [qryPBMRateOrig].[Honor]+[qryPBMRateOrig].[Discount]

Now if all the parts are working, you can assemble them into a whole. If
one of them fails, then you can start working on the reason for the failure.
Month2: IIF(DateValue([ExpDateFormatter) < DateAdd("M",1,[Last Day of the
Period]), [qryPBMRateNext].[Honor]+[qryPBMRateNext].[Discount],
[qryPBMRateOrig].[Honor]+[qryPBMRateOrig].[Discount])

"Tim Whitley" <TimWhitley@xxxxxxxxxxxxxxxxxxxxxxxxx> wrote in message
news:39B16A5D-3FA1-4887-AF8A-E8BBF487485B@xxxxxxxxxxxxxxxx
The following query should evaluate the Expiration Date
("ExpDateFormatter",
originally a string value, now formatted in a separate field as a
"mm/dd/yyyy" date) and compare it to the "last day of the period" (a
variable) plus one month. I keep getting an error stating this expression
is
too complex. How can I fix this and will the code work if fixed? I'm a
newbie so I really need the preschool version.

I will eventually have to replicate this for 12 months of forecasting.

Month 2: IIf([ExpDateFormatter]<DateAdd("m",1,[Last Day of the
Period]),[qryPBMRateNext.Honor]+[qryPBMRateNext.Discount],[qryPBMRateOrig.Honor]+[qryPBMRateOrig.Discount])

I appreciate those who have already tried to help me ( I almost feel
beyond
help) but I really need a workable solution)

.

Relevant Pages

  • Re: Third times a charm? Date query question
    ... field is it a date field or text field that contains a string that looks ... Those both should fail as the appear to be incorrectly bracketed. ... I keep getting an error stating this expression ... How can I fix this and will the code work if fixed? ...
    (microsoft.public.access.queries)
  • Re: Third times a charm? Date query question
    ... field is it a date field or text field that contains a string that looks ... Those both should fail as the appear to be incorrectly bracketed. ... I keep getting an error stating this expression ... How can I fix this and will the code work if fixed? ...
    (microsoft.public.access.queries)