Display Pass or Fail based on number range

[aselm01]

I have a form for entering data on voltage measurements for a circuit card. For each field of entered data I have a Pass/Fail field that I currently enter manually. What I want to do is automate this so that if I enter a voltage value that is inside a certain range then the form checks it and returns a Pass or Fail in the next field. This will also have to be saved into the table that the data is going into.

Here is an example of the range I would need to check against: +4.900Vdc to +5.100Vdc, anything within is pass anything above or below is a fail. The same would have to work for a negative value such as -15.75 to -14.75. For positive numbers the sign is not needed, but I would like to be able to include the negative sign if possible. The sign is not that big a deal to keep if it really complicates things.

Does the field in the table this would be stored in need to be a specific type like "Yes/No"? Or can it be a text field?

The Table name is TBL_IO, the first of the field names for this are "P5Vdc_IO_PWA" (number field, this is where the value will be stored), "P5Vdc_IO_PWA_PF" (text field, this is where I would store the Pass/Fail). The field names are similar for another 15 fields and so I would have to repeat this for all.



For any who try to help Thanks in advance!

[June7]

Probably should not actually save the pass/fail. Storing calculated data is usually bad idea. Calculate when needed. An IIf expression in query or textbox should serve. Try:

IIf([fieldname] BETWEEN 4.9 AND 5.1 OR BETWEEN -15.75 AND -14.75, "Pass", "Fail")

BETWEEN AND operator is inclusive for the range ends.

[aselm01]

Probably should not actually save the pass/fail. Storing calculated data is usually bad idea. Calculate when needed. An IIf expression in query or textbox should serve. Try:

IIf([fieldname] BETWEEN 4.9 AND 5.1 OR BETWEEN -15.75 AND -14.75, "Pass", "Fail")

BETWEEN AND operator is inclusive for the range ends.

I received a syntax error when trying it in a query and it just did not work when I put it in the field I want it to go in.

Here is the db so you can see my forms that I am using. Right now I am using list boxes for selecting pass or fail. Ultimately I would like the form to be auto matic so that when I enter the data it places a green PASS or a Red Fail in the pass/fail field.

As far as storing goes how do you get it to show up on a report? Use the query field in the report design?

Tony

[June7]

Sorry, slight error in suggested expression:

PF: IIf([P5Vdc_IO_PWA] Between 4.9 And 5.1 Or [P5Vdc_IO_PWA] Between -15.75 And -14.75, "PASS", "FAIL")


Use textbox Conditional Format to get the colored text.

Bind textbox to the query field or do the same calc in textbox on report.

[aselm01]

Expression works for me in my db.

I don't see a table named TBL_IO. Did you mean TBL_AIO?

Which objects are involved in this issue? Where did you attempt the calc?


Use textbox Conditional Format to get the colored text.

Bind textbox to the query field or do the same calc in textbox on report.

Sorry, yes it should be AIO. I created a blank query and added table "TBL_AIO". Then chose the field "P5Vdc_IO_PWA_PF" and put your string in the criteria field and tried to run the query. That is where I got the error. I want the above field to show Pass or Fail for the field "P5Vdc_IO_PWA".

All three tables (TBL_AIO, TBL_SLD, & TBL_Fog) will have the same type of Pass/Fail field so this will be a global thing. Also what type of field should they be to make this work?

As far as the color and a textbox how will that work with the above on the form?

[June7]

I edited my post while you were posting. I found the table and tested expression in a query. The revised expression does work.

PF: IIf([P5Vdc_IO_PWA] Between 4.9 And 5.1 Or [P5Vdc_IO_PWA] Between -15.75 And -14.75, "PASS", "FAIL")

[aselm01]

I edited my post while you were posting. I found the table and tested expression in a query. The revised expression does work.

PF: IIf([P5Vdc_IO_PWA] Between 4.9 And 5.1 Or [P5Vdc_IO_PWA] Between -15.75 And -14.75, "PASS", "FAIL")

I created a new query, based on TBL_AIO, selected the field P5Vdc_IO_PWA_PF, and put the following string, IIf([P5Vdc_IO_PWA] Between 4.9 And 5.1, "Pass", "Fail") into the criteria box and it works now. So I don't know what I was doing wrong. Because it looked the same the last time and I got a mismatch error.

Thanks!

Now to try the color code thing...

[June7]

Textbox Conditional Formatting is on the Design tab.

If these 3 tables all have the same kind of data and essentially the same fields, why not one table?

[aselm01]

Textbox Conditional Formatting is on the Design tab.

If these 3 tables all have the same kind of data and essentially the same fields, why not one table?

I wanted to keep them separate in case I needed to do some stuff later on.

On the query Pass/Fail thing. It looks like I was premature. I blanked out the pass/fail entries in the table and tried the query and it did not work. It just returned blank fields when ran. I don't know what I am doing wrong. Here is a picture of what the query looks like for just the first two fields I am working on.

[June7]

The expression goes on the Field line. The expression will create a field with values of either Pass or Fail. Then you can apply criteria to the constructed field like:

= "Pass"

or

= "Fail"

By calculating the Pass/Fail value the _PF fields are not needed in table. Don't have to rely on users to input the Pass/Fail. The query calc provides the value. This is 'automation'.

[aselm01]

The expression goes on the Field line. The expression will create a field with values of either Pass or Fail. Then you can apply criteria to the constructed field like:

= "Pass"

or

= "Fail"

By calculating the Pass/Fail value the _PF fields are not needed in table. Don't have to rely on users to input the Pass/Fail. The query calc provides the value. This is 'automation'.

Okay I just tested it again and this time I played with the values and it worked as it should. I was putting the string in the wrong place every time. Now I can go and remove those fields for pass/fail in the tables. As you said they are no longer needed.

Now I will also work on the color code thing.

Thanks again June7!