Criteria using a formulated field

[Sed]

Please Help!!! Something as simple, yet the hardest to figure out.

Field1 is: "Total Shipment Charge"
Field2 is: "FSC" fuel Surcharge
Field3 is: "Distance"
Field4 is: "ACC" Accessorial
however, this field is a formula (
ACC: IIf(IsNull([Allocated Overwidth Charge Cost])=True,0,[Allocated Overwidth Charge Cost])+IIf(IsNull([Allocated Stop Charge Cost])=True,0,[Allocated Stop Charge Cost])+IIf(IsNull([Allocated Tarp Charge Cost])=True,0,[Allocated Tarp Charge Cost])

Now, Field5 is
CPM_Less_Fsc_Acc: (((([Total Shipment Charge])-[ACC])-([FSC]))/([Distance]))

But everytime I run the query, an applet pops up to tell me to enter the parameter value.

any clue?

thanks

 

[Medea]

Whenever that happens the first thing you should do is check the spelling of your fields in the formula. Check everything in the brackets.

Trish

 

[dkinley]

When working with something complex as this, I usually make a copy of the query and almost start from zero to find the culprit.

For instance, make a query and delete field 5. Run it. If no parameter input, then something is wrong with 5.

Another trick is to break out the IFF into seperate columns (each referencing the previous) with the last column being the final IFF. In this manner you can see if each IFF is indeed correct. Then begin collapsing them to one at a time to see if you can spot the error.

-dK

 

[Sed]

I've done all of that. everything works find, until I put the criteria in field5

 

[Medea]

What field is the popup referencing?

 

[dkinley]

Just guessing here, remove the [ACC] parameter and see if query runs fine. I would also check that the data in the other fields do not have a null, too.

I am not sure how Access processes records in a query - if does some sort of step mechanism. Because ACC is so complex it might not have stepped through the logic prior to field 5's step logic reaching the ACC variant ... or if a previous column fully resolved prior to going to the next column.

Perhaps someone more learned and wizened can enlighten?

-dK

 

[Sed]

Yes it does

 

[Sed]

the "ACC" field is just summing three fields together. The formula reads, "stop charge" + "Tarp charge" + "overwidth charge"

 

[dkinley]

The problem is beyond me in regards to what I outlined. Only work around I can think of is to put ACC in another query and then try and use that in the current query.

True, but there are many steps to the logic with the IFFs. I am grasping at straws in thinking if field 4 takes a step, field 5 takes a step, too. field 5 reaches the [ACC] prior to field 4 completing all of its logic. I have no idea if this is how it works - just guessing and looking for an explanation - I've never experienced a problem like this ... I will do some research to see what I can come up with.

Perhaps someone else can weigh in with direction.

Apologies,
-dK

 

[Sed]

Medea, the popup is referencing to the "ACC" field.

 

[Medea]

I re-created (i think) your table and query and it worked fine. I used your formula exactly with no issues. See attachment. Field spelling as you listed. Take a look/see attachment.

SELECT Table1.[Total Shipment Charge], Table1.FSC, Table1.Distance, IIf(IsNull([Allocated Overwidth Charge Cost])=True,0,[Allocated Overwidth Charge Cost])+IIf(IsNull([Allocated Stop Charge Cost])=True,0,[Allocated Stop Charge Cost])+IIf(IsNull([Allocated Tarp Charge Cost])=True,0,[Allocated Tarp Charge Cost]) AS ACC, (((([Total Shipment Charge])-[ACC])-([FSC]))/([Distance])) AS CPM_Less_Fsc_Acc
FROM Table1;


Trish

 

[Sed]

It doesn't seems like you put any criteria in for the CPM_Less_Fsc_Acc.
try puting "between 1 and 3

 

[Medea]

Where you able to download the sample?

Are ...

Field1 is: "Total Shipment Charge"
Field2 is: "FSC" fuel Surcharge
Field3 is: "Distance"

fields in a table or parameters in your query? I put them into a table along with the fields listed in your first formula.

 

[Sed]

Yes I was able to download it. I was only able to look at the SQL code and not the interface mode.

fields in the query

 

[Sed]

can I send my db to you?

 

[dkinley]

Please!

-dK

 

[Medea]

sure thing....

 

[Sed]

it's too big to upload, is there another option?

 

[Medea]

Make a copy...pair it down by getting rid of the table, queries, etc that are not needed and leave around 10 records. That should make it small enough.

Also, download mine again and when in query mode, select view --> Design view. That is easier to read then the SQL view.

 

[dkinley]

Also, in addition to Medea's suggestion, be sure to compact and repair and then .zip it before posting it here.

-dK