Help with If Statement

[chrisguk]

Hi all,

Im a beginner and I have been trying to figure out this IF statement.

I have the following:

qry = [sometable field]
status = [some table field]

IF qry > 0 then

"Do something"

Else

"Do something else"

I want to incorporate something a little more complicated like so.

I want to "do something" if the qry > 0 and "do something else" if it is not, but I also want it to "do something else" if the status = "Closed" or if it says something else in the field I want it to "do something"

Hope someone can help

 

[RuralGuy]

Look into the Select Case structure.

 

[chrisguk]

Look into the Select Case structure.

Okay I came up with this. But it doesnt work. Have I made an error in the code.

Basic I want it to check for "CLOSED" in the field Log_status as well.

Private Sub view_closed_queries_Click()

Dim qrycounter As String
Dim status As String
status = ("[tbllog]![Log_status]")
qrycounter = DCount("[Logid]", "tbllog", "[tbllog].[sitesid] = " & [Forms]![frmsites]![sitesid] & " ")

    Select Case status & qrycounter

          Case qrycounter > 0
               DoCmd.OutputTo acOutputQuery, "qryview_closed_queries", acFormatXLS, "All_Queries.xls", True

          Case status <> "CLOSED"
              DoCmd.OutputTo acOutputQuery, "qryview_closed_queries", acFormatXLS, "All_Queries.xls", True

          Case Else
                MsgBox ("There are no Closed Queries on this Site")

      End Select

    End Sub

 

[RuralGuy]

Select Case only looks at one argument but based on what you put together you do not need it anyway. Just look at the values directly.

 

[DCrake]

If you have more than one condition you first have to prioratise the questions.

If Status = "Closed" Then
    Do Nothing
Else
    If X = 1 Then
       Do X
    ElseIf X = 2 Then
       Do Y
    Else
       Do Z
    End If
End If

 

[chrisguk]

If you have more than one condition you first have to prioratise the questions.

If Status = "Closed" Then
    Do Nothing
Else
    If X = 1 Then
       Do X
    ElseIf X = 2 Then
       Do Y
    Else
       Do Z
    End If
End If

If my memory serves me well, this is a nested IF statement, is that right?

Did I have the code correct for the way I want to look in the table for the "CLOSED" value though?

 

[vbaInet]

You said the code you wrote wasn't working so obviously your code structure isn't correct. DCrake has given you a structure to work from.

From what you've explained it's not clear enough to you a concise code structure.

So based on your explanations, here's another code structure:

If qry > 0 then
     do something
else
     if status = "closed" then
           do something
     else
            do something else
     end if
end if

 

[chrisguk]

You said the code you wrote wasn't working so obviously your code structure isn't correct. DCrake has given you a structure to work from.

From what you've explained it's not clear enough to you a concise code structure.

So based on your explanations, here's another code structure:

If qry > 0 then
     do something
else
     if status = "closed" then
           do something
     else
            do something else
     end if
end if

Thank you for the reply. I really appreciate any advice you guys give me. Its tough trying to learn something new but im getting there slowly

I wasnt clear when I asked if my structure was correct. What I meant is the top part of the code which I have pasted below. Is this the correct way to call an item from the database:

Dim qrycounter As String
Dim status As String
status = ("[tbllog]![Log_status]")

 

[vbaInet]

Slowly but surely

I wasnt clear when I asked if my structure was correct. What I meant is the top part of the code which I have pasted below. Is this the correct way to call an item from the database:

Dim qrycounter As String
Dim status As String
status = ("[tbllog]![Log_status]")

Not quite, but there are a couple of ways. For now have a look at the DLookup() function:

http://www.mvps.org/access/general/gen0018.htm

http://www.techonthenet.com/access/functions/domain/dlookup.php