shorter way to write an If, then, else statement in VBA.

[BBBryan]

Hi All,
I have an If, then, else statement in VBA.
Is there a different shorter way to skinny the wording in VBA
LIke useing IN (.., ...., ...., ) ect

I have this but I have to add a whole bunch more names into it.
ElseIf Parent![QAPerson] = "" Or Parent![QAPerson] <> "Bryan" Or Parent![QAPerson] <> "Hamid " Or Parent![QAPerson] <> "Allison" Or Parent![QAPerson] <> "Pat" Or Parent![QAPerson] <> "Ken" Or Parent![QAPerson] <> "Rik" Or Parent![QAPerson] <> "Gavin" Or Parent![QAPerson] <> "Dave"



Thankds BBryan

 

[plog]

An overall suggestion is to use a table to store all the names that are to be excluded. Then either link that if this is to be a query, or do a DCount into that table to see if [QAPerson] returns >0 zero.

With that said, the problem with your logic as stated is that it will always return True:

Parent![QAPerson] <> "Bryan" Or Parent![QAPerson] <> "Hamid "

If QAPerson='Bryan', then it doesn't equal "Hamid" so the above logic will return True.
If QAPerson='Hamid', then it doesn't equal "Bryan" so the above logic will return True.

Your logic is flawed. Most likely you want ANDs not ORs.

 

[pbaldy]

I assume that's air code since the logic wouldn't work. I don't think In() works in VBA. In any case, it seems like you're putting logic into code that should be in a table. I'd have a user maintainable table with the names in it and use a recordset or DCount() to test.

 

[pbaldy]

This is what happens when typing on an iPad; too slow.

 

[BBBryan]

Thanks plog,
As you suggested I still am not sure how to link a query to this statement.
I have a table with these people and I can Add a check mark and make a query.
As for the logic ... This is the only way I know who to do this.
the short story is
If the textbox is blank or these people are not named in the text box use this report.
I have about 20 people to put in this statement.


Thanks BBryan

 

[BBBryan]

After re-reading your suggests.
I think I may have not All of the code in. Just where i wanted to shorten the code.
I have a button on my form that when I click on it depending on the name I what it to open a certain Report.

Here is the full code.

Private Sub Item_DblClick(Cancel As Integer)
If Parent![QAPerson] = "Bryan" Then
DoCmd.OpenReport "PITP Piping (LetterSizePort) BRYAN H Rp", acViewPreview
ElseIf Parent![QAPerson] = "Hamid" Then
DoCmd.OpenReport "PITP Piping (LetterSizePort) HAMID S Rp", acViewPreview
ElseIf Parent![QAPerson] = "Allison" Then
DoCmd.OpenReport "PITP Piping (LetterSizePort) ALLISON S Rp", acViewPreview
ElseIf Parent![QAPerson] = "Pat" Then
DoCmd.OpenReport "PITP Piping (LetterSizePort) PAT D Rp", acViewPreview
ElseIf Parent![QAPerson] = "Ken" Then
DoCmd.OpenReport "PITP Piping (LetterSizePort) KEN N Rp", acViewPreview
ElseIf Parent![QAPerson] = "" Or Parent![QAPerson] <> "Bryan" Or Parent![QAPerson] <> "Hamid" Or Parent![QAPerson] <> "Allison" Or Parent![QAPerson] <> "Pat" Or Parent![QAPerson] <> "Ken" Then
DoCmd.OpenReport "PITP Piping (LetterSizePort) BLANK Rp", acViewPreview
End Sub


Thanks Bryan

 

[pbaldy]

That can't be all the code, as that wouldn't compile (no End If). Your code is logically flawed in two ways. First, using OR with <> won't work, you'd need AND there. That said, you don't need all those tests in the last ElseIf. If the choice was Bryan, the first If would have been satisfied and code would never have gotten to the last ElseIf. Same goes for other tests that have already been done.

 

[BBBryan]

Thanks pbaldy,
I see what you mean on the last one I would just put
Else
DoCmd.OpenReport "PITP Piping (LetterSizePort) BLANK Rp", acViewPreview.

And yes I did have End if. I must have missed it when copied the code.


Thanks BBryan

 

[pbaldy]

No problem. I hope the various reports have different structures or something, not just different filtering.

 

[plog]

I hope the various reports have different structures or something, not just different filtering.

Pbaldy tied it up. You beat me this time. Of course, that's not going to stop me from expounding.

You shouldn't have the same report for every person, if those are the same except for the person's data they show. You should use the same report and use the filter argument of the DoCmd.OpenReport to limit it to just the data you want to see.

Also, as pbaldy beat me to as well--your logic on that ElseIf accomplishes what you want only because you made 2 mistakes which canceled each other out. Like I demonstrated before, if you use ORs and negation(<>) your logic will always return true and execute what you tell it to. So if it makes it to the IfElse, its always executing no matter what the value of QAPerson is.

Further, your double checking the values you already checked for in all those Ifs. So, you don't need an ElseIf there, you simply need an Else without any logic. Again, though, you shouldn't have all those Ifs in the first place--you use the filter argument to open a report that shows everyones data.

 

[pbaldy]

If the different reports just have different criteria, you can add criteria to the source query or use this method:

http://www.baldyweb.com/wherecondition.htm

 

[BBBryan]

Thanks Guys,
I do have the same text info in each report, different name, but the big difference is I have 3 pictures of each persons signature. I couldn't get get it to work by adding the pictures from a dropdown and using one report. when I add in Pictures from my dropdown the picture doesn't show in the report.

So that is why I have set up a copy of each person with there signature.

Plus I have this database on a thumb drive which another persons uses when I am away. There is no Network at the location. So I need the pictures inbedded.


By the way I Looked at your www.whereCondition and I can use that on another thing I had on the go.....

I guess Im really digging a good hole of how not to build a database LOL


Thanks BBryan

 

[arnelgp]

Dim strPerson As String
Dim strPersons As String

strPerson = Trim(Parent![QAPerson] & "")
strPersons="\Bryan\Hamid\Allison\Pat\Ken\Rik\Gavin\Dave"

ELSEIF (strPerson = "") Or (Instr(strPersons, "\" & strPerson) = 0)

 

[pbaldy]

Dim strPerson As String
Dim strPersons As String

strPerson = Trim(Parent![QAPerson] & "")
strPersons="\Bryan\Hamid\Allison\Pat\Ken\Rik\Gavin\Dave"

ELSEIF (strPerson = "") Or (Instr(strPersons, "\" & strPerson) = 0)

Why would you perform a test that doesn't need to be made?