Replacing a sentence with VBA-code

[Mark Anders]

One my form includes a control to calculate amount of holes. I am using
following sentence to calculate:

Holes: IIf([lenhgt] Between 120 And 800;2;IIf([lenhgt] Between 800 And
1350;3;IIf([lenhgt] Between 1350 And 1900;4;IIf([lenhgt] Between 1900 And
2450;5;IIf([lenhgt] Between 2450 And 3000;6;IIf([lenhgt] Between 3000 And
3500;7))))))

Measures are as millimeter.

There is another control on the form to calculate the distance of holes.

Is there sense to change sentences to VBA-code? Both of sentences return
right values. If so, what kind of code is needed?

 

[Jeanette Cunningham]

Hi Mark,

I am not sure that a understand your question correctly, but here goes.

The sentence below is already VBA code that tells Access how to calculate
the value for the control called Holes.

Holes: IIf([lenhgt] Between 120 And 800;2;IIf([lenhgt] Between 800 And
1350;3;IIf([lenhgt] Between 1350 And 1900;4;IIf([lenhgt] Between 1900 And
2450;5;IIf([lenhgt] Between 2450 And 3000;6;IIf([lenhgt] Between 3000 And
3500;7))))))


I would leave this sentence (code) for Holes and the other control just as
they are, unless they are giving you errors on the form.

Hope I understood your question as you intended?

Jeanette Cunnningham

 

[Marshall Barton]

One my form includes a control to calculate amount of holes. I am using
following sentence to calculate:

Holes: IIf([lenhgt] Between 120 And 800;2;IIf([lenhgt] Between 800 And
1350;3;IIf([lenhgt] Between 1350 And 1900;4;IIf([lenhgt] Between 1900 And
2450;5;IIf([lenhgt] Between 2450 And 3000;6;IIf([lenhgt] Between 3000 And
3500;7))))))

Measures are as millimeter.

There is another control on the form to calculate the distance of holes.

Is there sense to change sentences to VBA-code? Both of sentences return
right values. If so, what kind of code is needed?

If you are using an mdb to store the data, then that
calculated query field could also be written this way:

Holes: Switch(lenhgt<120;Null; lenhgt<800;2; lenhgt<1350;3;
lenhgt<1900;4; lenhgt<2450;5; lenhgt<3000;6; lenhgt<3500;7;
True;Null)

If you want to use that calculation in a text box:

=Switch(lenhgt<120;Null; lenhgt<800;2; lenhgt<1350;3;
lenhgt<1900;4; lenhgt<2450;5; lenhgt<3000;6; lenhgt<3500;7;
True;Null)

In a VBA procedure it would be like:

Dim inHoles As Integer
inHoles = Switch(lenhgt<120;Null; lenhgt<800;2; _
lenhgt<1350;3; lenhgt<1900;4; lenhgt<2450;5; _
lenhgt<3000;6; lenhgt<3500;7; True;Null)