Full Version: Right direction, wrong code .. or just wrong direction ??
UtterAccess Forums > Microsoft® Access > Access Forms
smkdvr42
I have forms setup ... 4 fields and the values depends on what you pick
Light Hazard would give me a 1, Ordinary Hazard gives me a 2 and so on
I have two separate problems ..
At the bottom of the form I want to get a total value of all four field values
=sum(field1)+(field2)+(field3)+(field4)
That equation just gives me the numbers in each field
example being
Field 1 = 1
Field 2 = 1
Field 3 = 1
Field 4 = 1
Oget 1111 instead of 4
Also, when the field is chosen and I get a value of 1 .. it won't update my table
I have a feeling my entire problem is based around the way I have my form setup to give me a value based on what I choose
=IIf([OCCUPANCY]="Light Hazard","1",IIf([OCCUPANCY]="Ordinary Hazard","2",IIf([OCCUPANCY]="Extra Hazard","3")))
Is what I'm trying to accomplish possible or am I just running around in circles?
BrianS
you're almost there, take the quotes off your number values in your iif
IIf([OCCUPANCY]="Light Hazard",1,IIf([OCCUPANCY]="Ordinary Hazard",2,IIf([OCCUPANCY]="Extra Hazard",3)))
your values are treated as string which are concatenated by the + operator.
There are better ways i am sure to accomplish your task. Perhaps if you can provide more information a better solution can be provided.
Most likely you need to normalize your table and create a table for the Occupancy types then use a combo box.
theDBguy
Welcome to Utter Access!
How are you choosing the values? Why are you using an IIf() statement? If these are supposed to be values stored in the table, why not use a combobox to display the text value and to store the numeric value in the table?
Just my 2 cents...
ace
If you are just trying to get a total of the current values of
the four fields then the Sum function is not what you want.
simple =(field1)+(field2)+(field3)+(field4) does that.
smkdvr42
Ok, removing the quotes from around the number did the trick .. I learned something now I can go home =)
chose the IIF() statements because other people will be entering the information in .. this way they don't have to know that light hazard = 1, etc. They choose the right option and it automatically gives the value
In the end when I pull the report the value is the only thing that I need
Is there a better way to do this to make the data show up in the tables?
In the end I want to print a report with data ... without it in the tables i have nothing
BrianS
Yes, the proper way to do this is to set up an OccupancyType table with the desired values as records

tblOccupancyType
-OccupancyTypeID - Integer - Primary Key
-OccupancyType - Text

You should then define a relationship between this table and your main table. You can then use a combo box on your form to select the desired value instead of the user typing it in. The combo should be bound to the OccupancyTypeID foreign key in your main table and its row source should be the OccupancyType table or a query of that table. Your will want to set the number of columns to 2 in combo box as well as set the first columns width to 0 so its hidden - no need to show the OccupancyTypeID field to the user.
smkdvr42
Ok, one question about the combo box
If I choose that way .. the values won't be automacially put in right? They will have to manually enter the values won't they?
If that is the case I don't want to go that route .. I don't want to hand out sheets to say X gets this value, y gets this value
Should I upload my work so far for you to get a better idea on what I'm trying to accomplish?
Thanks for all the help so far!
BrianS
the purpose of the combo box is to let your users choose a value from a list. Since you now have a table with the OccupancyType descriptions along with the values, you can make your combo box display the description as well by setting its number of columns to 2 and hiding the value column like i previously described.
smkdvr42
I'm not sure I follow the route your trying to take me .. I'm going to upload what I have so you can see what I have done so far .. maybe that will help me better understand your direction
This is a "lo-fi" version of UA. To view the full version with more information, formatting and images, please click here.