Trying to get a label to display under certain criteria

[edunker]

I am try to develop a program on Microsoft Access where I need a label to be visible if it meets 2 certeria at the same time in a label. So lets call it label A is only visible when ID# in column 1 is present with column 3 saying D,E, or G. But not visible if column 3 says w. what would the code look like for something like this.

Thanks

[jforbes]

Probably something like this:

me.lblWithSomeCoolText.Visible = (len(me![ID]))>0 AND instr(1,"DEG", NZ(Me![Column3], "X"))>0)

[edunker]

Okay, thank you. I will try it.

[edunker]

Im not getting that to work for me. So...let me give you a little more info, I need the label93 to display if the ID# is found in tblHISTORY column SIGN_ID and also in that row Urgency is High, Medium, or Low, but if it is complete i want Label96 to display.

[jforbes]

How is it not working for you? What have you tried? Are you getting an error?

Also, you asked what it would look like, so I provided a sample of what it would typically look like. I assumed that the fields you were talking about were for the Current Record and that you were looking for code to put in the OnCurrent Event of a Form. If this is not the case, the code will look a little different.

[edunker]

Yes I am getting a run time error 424. If you look at the last reply I gave you exactly where everything is located.

[jforbes]

What have you tried? How did you go about it?

[edunker]

Me.Label93.Visible = (Len(Me![SignID])) > 0 And InStr(1, "High", Nz([tblHISTORY]![urgency], ""))

This is what I tried and got the runtime error

[mbizup]

Try breaking it down into its components:

PART 1

Me.Label93.Visible = Len(Me![SignID]) > 0
 

This will probably give you trouble if SignID is NULL.

Try this instead:

Me.Label93.Visible = Len(Trim("" & Me![SignID])) > 0
 

or this:

Me.Label93.Visible = NZ( Me![SignID], "") <> ""

PART 2

Me.Label93.Visible =  InStr(1, "High", Nz([tblHISTORY]![urgency], ""))

Are you trying to determine if the Urgency field contains the word High somewhere in it?

Try this instead:

Me.Label93.Visible = InStr(1, "High", Nz(Me.[urgency], "")) > 0

PART 3

Once you have BOTH parts working separately, put them together with an AND:

Me.Label93.Visible =(NZ( Me![SignID], "") <>  "") And (InStr(1, "High", Nz(Me.[urgency], "")) > 0)

Disclaimer - this is aircode, so syntax, parentheses etc might not be spot-on.

[mbizup]

Also - you might need to swap the order of "High" and [Urgency] in your InStr function.

The syntax should be:

Instr(StartInteger, StringToLookFor, StringToSearchIn)
 

If the result is > 0 then StringToLookFor has been found in StringToSearchIn.

[jforbes]

mbizup, thanks for the help on this. You've got the string parameters on the InStr() backwards. InStr() is a function I have to lookup the syntax every time I use it as it seems backwards to me.

edunker,
This seems to be a bit of a moving target...

what Event are you using this code in? And is this on a Form where you have bound the Form's RecordSource to tblHISTORY? If you are not using this on a form with a bound RecordSource, this needs to be done a bit differently. It would also explain the error you are getting.

If your Form is bound to tblHISTORY, you won't need to use [tblHISTORY] when referring to fields in the Recordset. You should then be able to follow mbizup's advice to get things working. Except, the InStr() function is going to look a bit different now since the values changed from "D,E, or G and sometimes W" to "High, Medium, Low".

Something like this may be more appropriate now that the picure is getting clearer:

Private Sub Form_Current()
    Dim bVisible As Boolean
 
    bVisible = True
    bVisible = bVisible And Len(Nz(Me![SIGN_ID], "")) > 0
    bVisible = bVisible And InStr(1, "HighMediumLow", Nz(Me![Urgency ], "SomethingElse")) > 0
    'bVisible = bVisible And ..... Something else you may decide to throw into the mix
 
    Me.Label93.Visible = bVisible
End Sub

In the above example, the Label starts out visible and gets hidden if any of the conditions are not met.

[mbizup]

<<You've got the string parameters on the InStr() backwards. InStr() is a function I have to lookup the syntax every time I use it as it seems backwards to me.>>

Thanks - I thought it might have been backwards. :-) The syntax always throws me too.

[edunker]

No, the form is not bound to that table. its bound to a different form. but the information I am looking for is on that table and not the table that it is bound to.

[jforbes]

Well that changes things. So something like this might be more appropriate:

Private Sub Form_Current()
    Dim bVisible As Boolean
    Dim lSignID As Long
    Dim sUrgency As String
 
    lSignID = Nz(Me![ID], 0) ' Get the ID of the current Record
    sUrgency = Nz(DLookup("Urgency ", "tblHISTORY ", "Sign_ID=" & lSignID), "") ' Get Urgency from the History Table
 
    bVisible = True
    bVisible = bVisible And InStr(1, "HighMediumLow", sUrgency) > 0
    'bVisible = bVisible And ..... Something else you may decide to throw into the mix
 
    Me.Label93.Visible = bVisible
End Sub

Since the record with the information in tblHistory isn't loaded on to the Form, you'll need to go out and get it. The Code above does this by first getting the ID from the Current Record. You have to have something available to find the record with, and if this isn't right, you may need to change it. Then taking the Current Records ID, it looks it up in tblHISORY and finds the Urgency. At this point it should have all the information that it needs to determine if the Label should be shown.

[edunker]

Okay, so I just got thrown a curve ball. I will try explaining this the best I can.

Is it possible to update the text in tblSign column Open_Work_Order to say yes if tblHISTORY Column Urgency is High, Medium, or Low for the same Sign id and then say no when tblHISTORY Column Urgency is Completed.

[jforbes]

Yeah, the curve ball can be addressed, but we typically only address one problem/question per thread. So, you'll need to ask that as a new question, and when you do, it is best to try to fully explain what you want to do. To much information is typically better than not enough.

Have you had any luck with your Label or are you shelving this and working on your new problem?

[edunker]

I am shelving that problem. I will start a new thread. thanks

[zmbd]

Along the same lines: >> https://bytes.com/topic/access/answe...inking-columns<<