Need if statement to ignore 0's

[SeadnaS]

I'm using this if statement:

If Me.MODEL_NO.Value Like "LAC225*" Then
If Me!CHILD_SUB.Form.[1] And Me!CHILD_SUB.Form.[2] And Me!CHILD_SUB.Form.[3] And Me!CHILD_SUB.Form.[4] And Me!CHILD_SUB.Form.[5] And Me!CHILD_SUB.Form.[6] And Me!CHILD_SUB.Form.[7] And Me!CHILD_SUB.Form.[8] >= 18.5 Then
Me!passfail1.Value = "Pass"
Else: Me!passfail1.Value = "Fail"
End If

I have text boxes on the subform named 1 to 8 i need the statement to ignore text boxes with 0. So if the user doesn't enter a number in box 8 it will act as though there is only 7 numbers.

Any help much appreciated!

 

[SeadnaS]

ok im now using a case select statement and i have a lot more x numbers. I need it to not include 0's so the user doesnt have to fill all the boxes.

Heres my code:

passfail1.Value = "Fail"
Select Case Me!CHILD_SUB.Form.[x1] And Me!CHILD_SUB.Form.[x2] And Me!CHILD_SUB.Form.[x3] And Me!CHILD_SUB.Form.[x4] And Me!CHILD_SUB.Form.[x5] And _
Me!CHILD_SUB.Form.[x6] And Me!CHILD_SUB.Form.[x7] And Me!CHILD_SUB.Form.[x8] And Me!CHILD_SUB.Form.[x9] And Me!CHILD_SUB.Form.[x10] And _
Me!CHILD_SUB.Form.[x11] And Me!CHILD_SUB.Form.[x12] And Me!CHILD_SUB.Form.[x13] And Me!CHILD_SUB.Form.[x14] And Me!CHILD_SUB.Form.[x15] And _
Me!CHILD_SUB.Form.[x16] And Me!CHILD_SUB.Form.[x17] And Me!CHILD_SUB.Form.[x18] And Me!CHILD_SUB.Form.[x19] And Me!CHILD_SUB.Form.[x20] And _
Me!CHILD_SUB.Form.[x21] And Me!CHILD_SUB.Form.[x22] And Me!CHILD_SUB.Form.[x23] And Me!CHILD_SUB.Form.[x24] And Me!CHILD_SUB.Form.[x25] And _
Me!CHILD_SUB.Form.[x26] And Me!CHILD_SUB.Form.[x27] And Me!CHILD_SUB.Form.[x28] And Me!CHILD_SUB.Form.[x29] And Me!CHILD_SUB.Form.[x30] And _
Me!CHILD_SUB.Form.[x31] And Me!CHILD_SUB.Form.[x32] And Me!CHILD_SUB.Form.[x33] And Me!CHILD_SUB.Form.[x34] And Me!CHILD_SUB.Form.[x35] And _
Me!CHILD_SUB.Form.[x36]

Case Is >= 17.5
Select Case Me.MODEL_NO
Case Is = "LAC13010", "LAC20010", "LAC20015", "LAC20020"
Me.passfail1.Value = "Pass"
End Select

Case Is >= 17
Select Case Me.MODEL_NO
Case Is = "LAC22515", "LAC22520", "LAC25010", "LAC25015", "LAC25020", "LAC25030"
Me.passfail1.Value = "Pass"
End Select

Case Is >= 16.5
Select Case Me.MODEL_NO
Case Is = "LAC27515", "LAC27520", "LAC30015", "LAC30020", "LAC30030"
Me.passfail1.Value = "Pass"
End Select

Case Is >= 16
Select Case Me.MODEL_NO
Case Is = "LAC32515", "LAC32520"
Me.passfail1.Value = "Pass"
End Select

Case Is >= 15
Select Case Me.MODEL_NO
Case Is = "LAC35015", "LAC35020", "LAC35030", "LAC37515"
Me.passfail1.Value = "Pass"
End Select

Case Is >= 14.5
Select Case Me.MODEL_NO
Case Is = "LAC40015", "LAC40020"
Me.passfail1.Value = "Pass"
End Select
Case Else
Me.passfail1.Value = "Fail"
End Select

 

[ZmrAbdulla]

You must be having a table for model numbers right?
It will be easy if you add a field to store passvalue for each model. So that you can compare the value to the entered value. Creating a Select case is right for this purpose.

Zameer Abdulla

 

[SeadnaS]

No the model number is entered by the user. Should I create a table with the model numbers? Is there a way of doing it without a table for model numbers? I'd like to be able to use my case select statement but change it so that it ignores text boxes with 0 in them.

 

[dhookom]

IMO, your table structure seems un-normalized. It looks more like a spreadsheet than a relational database. Un-normalized tables create issues like you are experiencing.

Also, I would not hard-code all of those values into code. Values belong in tables, not in code. I would create a table that contains model numbers and the passing values.

Duane
Hook'D on Access
MS Access MVP

 

[SeadnaS]

I have created a table with the model numbers and the passfail specs. I have changed my model number text box into a combo box that you can select the model number with and the 2nd column has the passfail specs in it. I changed my case select to this:

passfail1.Value = "Fail"
Select Case Me!CHILD_SUB.Form.[x1] And Me!CHILD_SUB.Form.[x2] And Me!CHILD_SUB.Form.[x3] And Me!CHILD_SUB.Form.[x4] And Me!CHILD_SUB.Form.[x5] And _
Me!CHILD_SUB.Form.[x6] And Me!CHILD_SUB.Form.[x7] And Me!CHILD_SUB.Form.[x8] And Me!CHILD_SUB.Form.[x9] And Me!CHILD_SUB.Form.[x10] And _
Me!CHILD_SUB.Form.[x11] And Me!CHILD_SUB.Form.[x12] And Me!CHILD_SUB.Form.[x13] And Me!CHILD_SUB.Form.[x14] And Me!CHILD_SUB.Form.[x15] And _
Me!CHILD_SUB.Form.[x16] And Me!CHILD_SUB.Form.[x17] And Me!CHILD_SUB.Form.[x18] And Me!CHILD_SUB.Form.[x19] And Me!CHILD_SUB.Form.[x20] And _
Me!CHILD_SUB.Form.[x21] And Me!CHILD_SUB.Form.[x22] And Me!CHILD_SUB.Form.[x23] And Me!CHILD_SUB.Form.[x24] And Me!CHILD_SUB.Form.[x25] And _
Me!CHILD_SUB.Form.[x26] And Me!CHILD_SUB.Form.[x27] And Me!CHILD_SUB.Form.[x28] And Me!CHILD_SUB.Form.[x29] And Me!CHILD_SUB.Form.[x30] And _
Me!CHILD_SUB.Form.[x31] And Me!CHILD_SUB.Form.[x32] And Me!CHILD_SUB.Form.[x33] And Me!CHILD_SUB.Form.[x34] And Me!CHILD_SUB.Form.[x35] And _
Me!CHILD_SUB.Form.[x36]

Case Is >= Me!MODEL_NO.Column(2)
Me.passfail1.Value = "Pass"

Case Else
Me.passfail1.Value = "Fail"
End Select

Now it doesn't work at all. Could anyone shed any light on this? Also i still don't know how to get it to ignore the x number boxes that contain 0.

 

[dhookom]

SeadnaS,
Do you understand that the following is not correct?

   If 4 And 4 And 4 And 4 = 4 Then

It would need to be something like:

   If 4 = 4  And 4 = 4 And 4 = 4 And 4 = 4 Then

In addition, your tables still seem un-normalized.

Duane
Hook'D on Access
MS Access MVP

 

[ZmrAbdulla]

What are these 36 columns? Or how do you get or enter the value in them?
If the model number is entered by the user then how do you get the pass value if you have no record of the model? You must be having a master table contains modelnumber, passvalue etc.. If the user enters a wrong model number then it shouldn't be entertained by the database.

Zameer Abdulla

 

[SeadnaS]

So I have the table with the Pass/fail specs and the model number. I have the code that I supplied in my last post. I still don't understand what is wrong with it. In the combo box called MODEL_NO the pass/fail specs are in column 2.

The 36 numbers are entered in 36 text boxes bound to 36 columns in a child table linked to my master table.

 

[dhookom]

What's wrong (IMO):
[ol][li]36 repeating columns suggests un-normalized[/li]
[li]the logic of your statement as I suggested earlier[/li]
[li]possibly the column numbering of the combo box which starts with 0 not 1[/li][/ol]

Duane
Hook'D on Access
MS Access MVP

 

[ZmrAbdulla]

If you must need the 36 columns(it shows un-normalized as Duane says); you can create a query based on the child table with an additional field for total of 36 columns. Then compare the values on the master form and child form.

Still.. something or somewhere a mistake on your db setup. You must review it again



Zameer Abdulla

 

[SeadnaS]

I need the 36 fields. The 36 fields are not always going to be filled though. Most of the time only 8 or 16 will be used. So i need a way to get my code to work, and work in a way that it only uses the fields where numbers have been entered and ignores the ones where nothing has been entered which will be left at 0.

 

[SeadnaS]

I've scrapped the table with the pass fail specs. I have them coded into my case select. Now that doesn't work. I think i'm going to have to go back my huge if statement.

 

[MajP]

Seadna,
Back in late May I warned you that this design is poor and will give you lots of problems. I said you could fix the table design or use a normalizing query. Instead you continue with these rediculous band-aids. In your post on finding the range I demonstrated how to do this. Recommend you relook at that. If you make a single union query this problem would become trivial. You are just beating yourself up and wasting your time with this approach.

 

[SeadnaS]

Would you like to see a blank version of the DB?
This is a different one from back then Majp.

 

[SeadnaS]

I have normalised the CHILD table with the Union query you showed me on my old post, thanks. So what should I do next?

 

[dhookom]

Can you share the SQL and/or fields from the union query?

Duane
Hook'D on Access
MS Access MVP