VBA code If then problem

[yoshi88]

Hi,

I'm trying to add an If...Then code in my report to hide some fields. One the field is a descritption and if its equal to 5, 7, 8 or 10 I want it to be invisible. My code goes like this:


If Description1=(5 or 7 or 8 or 10) Then
Description1.Visible = False
Else
Description1.Visible = True
End if


The problem is that (5 or 7 or 8 or 10) doesn't seem to be recognize correctly. Is there another way to code this thing.

Frank

 

[jrbarnett]

   If Description1=5 or Description1=7 or Description1=8 or Description1=10 Then
      Description1.Visible = False
   Else
      Description1.Visible = True
   End if

This is for Description1 being a numeric value. If its is a text field (string), you need to put double quote marks around each of the numbers.

John

 

[yoshi88]

Thanks for your reply, its working noew. But is ther a way to code it without typing Description1=... for every value. I have more than 40 values to type and if I use the Not Description1=... i would have to type more than 50 values.

Also, my report represent 3 shifts per days for 7 days, so I have 21 Description1name Description1SunAM, Description1SunPM, Description1SunNight, ... Would I have to copy the code for every 21 fields? Or can I do something else.

Thanks, as you can see I'm not use to work with VBA


Frank

 

[dhookom]

Select Case Description1
Case 5, 7, 8, 10
Description1.Visible = False
Case Else
Description1.Visible = True
End Select

You can set up code to loop through controls
Dim intCtrl as Integer
For intCtrl = 1 to 21
Me("Description" & intCtrl).Visible = _
Instr(",5,7,8,10,","," & Description1 & ",") = 0
Next

Do you actually have repeating fields like this or is this a result of a crosstab query? I would actually try to make this more data driven which would mean creating a table with the values of 5,7,8,10.

Duane
MS Access MVP
[green]Ask a great question, get a great answer.[/green]
[red]Ask a vague question, get a vague answer.[/red]
[green]Find out how to get great answers faq219-2884.[/green]
[blue]Ask me about my grandson, get a grand answer.[/blue]