Expression builder issue

[punter]

Hi,

I build a database (attached zip fiile) and I'm having an issue with the expression builder. The form Broker Information has a field named % Freight. I want it to divide the two fields above it but it doesn't seem to want to work. The expression builder looks like it has the right formula but nothing is happening. Any thoughts on what I'm doing wrong.

Thanks as always

Eddie.

 

[boblarson]

If your field name is indeed % Freight then change the name to something like PctFreight. Do NOT, I repeat (and this is extremely important), DO NOT use special characters in field or object names as it will come back to bite you at some time.

That being said I'll take a look and see if there is anything else causing your problem and get back to you.

 

[punter]

That is what the field name is. I will make the change you have suggested.

 

[boblarson]

You have a lot of special characters in names. Fix those and things might get easier. At this point, I also see that you don't have anything to set the value of % Freight to the formula you say you want. The control source is set to the table field and not set to a formula, which is okay, if you are planning on storing the calculation (which may, or may not be good practice depending on a few factors). But, if the control source isn't set to be the formula, then you need to include it on code somewhere and I don't see that either.

But, the most IMPORTANT thing at the moment is to get rid of any $ # % characters in your field names. This will only cause you more problems down the line.

 

[punter]

Thank you for your quick reply.

I made changes in the tables so the names do not have the special characters as you suggested. Thank you for your advice.

I have attached the updated zip file. This has to be easy and I don't know why I can't make it work. It is a simple division formula. Is the expression builder not able to perform this task? What mistake am I making?

Thanks

Eddie.

 

[boblarson]

Since you changed the field names, you might want to change the field name in the calculation. Change this:

=[Freight $ on Broker Invoice]/[Broker Total Invoice Amt]

to this:

=IIf([Broker Total Invoice Amt]=0,0,[Freight Amt on Broker invoice]/[Broker Total Invoice Amt])

Also, I'm not sure why you have UNIQUE NUMBER in your table as text and not as an autonumber. I guess I don't know what you're trying to accomplish there. Plus it appears that you have a composite key, which might be okay, but it actually may not be necessary

 

[punter]

Thank you for your help Bob. I'll see if I can get it to work.

The Unique # is being generated off another form. We will not entering on this form for several weeks until after we fill out the first form. I'm using the Unique # to follow the data through the tables so I can run my querries and reports. I'm sure there is a better and I'm looking all the time to figure out any better way to do anything.

This database is actually the newest verison of the nightmare I posted on here last week. Your help has vital in getting it to the point it is at now.

Thanks

Eddie.