Question about calculated fields on form (1 Viewer)

[peterod]

Hi all.

Im new to access, thus im here

I have a little mysql/php experience but am now trying to build a desktop app in access.

I am trying to make a Bill of quantities application for my self build house, which will take the material dimensions

To understand my question i will need to describe my tables and forms thus far, and what I am looking for my app to do, or at least for this element of it.

I have 2 tables

Table 1 (Materials)

Material name
Material Description
Unit of measurement
Sheet material (check box)
Timber Material (Check box)
Loose quantified material(check box)

then the fields that I want to pull into the calculations field on my form from this table

length
depth/height
width
area (calculated field) [length] * [height]
volume (calculated field [length] * [height] * [depth]

then the material supplied dimensions/quantitys

pallet/box quantity
amount per metre square
coverage per roll (insulation, polythene products)

Then I have some general fields for actually sorting out the purchase orders etc.

Area of house (just the area of the house in concern, ie roof, first floor...
Order date is required
Purchase order number


Table 2 is where the questions I want answered will go to get the dimensions from the house.

It has a few fields

Question ie, please enter the length of the perimeter wall? or
please enter the internal wall area of the external wall.
answer

So that's what I am trying trying to achieve (hope its clear as I need some help with the calculation to establish the quantity for each material. This is because the calculation is different for each material, but I just want 1 quantity field on my form or report, not one for each different type of calculation which would leave me with lots of empty fields.

Is it possible to have a field which would allow a different calculation for each material?

If you cant do this, any suggestions would be greatly appreciated.

Thanks in advance.

 

[CEH]

"Is it possible to have a field which would allow a different calculation for each material?"

Check into a "if....then" statement, or a "select case".... either should enable you to choose different calculations depending on the value of the "materials" field.

 

[peterod]

so obvious now. vba is new to me. thanks again

 

[peterod]

Select Case [ID]
Case Is = "1"
[Quantity] = 196
Case Is = "2"
[Quantity] = 192
End Select

trying to test this setting a value based on the ID.

Getting invalid syntax. not sure where I am going wrong

 

[peterod]

any chance of a working example ?

 

[peterod]

this thread has had 64 views... someone must be able to offer advice. Pretty Please

 

[peterod]

IIF Statment as calculated field

Ok. (note * Im using access 2010)

So after some head scratching I now know what I am doing.

I couldnt for the life of me get an if then to work in a calculated field in my table. so after a bit of messing around and reading up on vba I finally got the IFF statement.

this simplifies the code, but makes it less readable if u ask me. Anyway

IIf([txt]="a",[Field1]*[Field2],IIf([txt]="b",[Field1]+[Field2],5*2))

A nested iff. thats what I ended up with. I have around 80 separate calcs to do in this field and i think i may have read that there is a limit on the number of nests for the iff.

Please tell me thats not true?

Just encase someone googles this example of the iff as an expression in a calculated field i will break it down

IIf([txt]="a",[Field1]*[Field2],IIf([txt]="b",[Field1]+[Field2],5*2))

Statement to be evaluated (this can be any expression)
This will return a value if the above is true, ie, if my [txt] = a
This is first nest which is called if the original iif returns false, ie, if [txt] = anything other than a
this is the statement to be evaluated for the new nested iff (the second one along the line, the new true part)
This is the statement to be evaluated if first statements return false .

in the nest (()) the yellow bit, or the false bit will always go to the end.

Hopefully it isn't frowned upon for newbies to this to be posting their findings. anyway, hth someone else in the future.

Kind regards all.

Pete

All that will be really straight forward for the regulars but i think a good help to someone in my pos in the future