Third time's a charm? Date query question

[Guest]

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)

 

[Guest]

What is [ExpDateFormatter]? a table field, a calculated field from a sub
query, a function? The other parts like [Last Day of the Period] I also
don't understand.

 

[John Spencer]

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])

 

[Guest]

Here's some explaination:

ExpDateFormatter: DateValue(Format([EXPIRE_DATE],"@@@@\/@@\/@@"))
This formats a string in yyyymmdd format to the mm/dd/yyyy

[Last Day of the Period] is a variable expression that the user would enter
to set the current period end for the "period end" vs. "expiration date"
evaluation.

What is [ExpDateFormatter]? a table field, a calculated field from a sub
query, a function? The other parts like [Last Day of the Period] I also
don't understand.

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)

 

[Guest]

Thanks for the suggestons, John. I think I've isolated the problem to the
date comparison. The "honor" and "discount" portions compute correctly, they
just don't adjust based on the comparison.

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])

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)

 

[John Spencer]

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])

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])

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)

 

[Guest]

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.

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])

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])

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)

 

[John Spencer]

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.

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.

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])

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])

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)