selection statment in VBA

[ice051505]

Hi All, I am very stupid on VBA coding:banghead:
My problem is that I have a query which count the number of target field in my table,(if the value in the column represent once, the count is 1; if the value repeat 3 times, the count value is 3), then I want to develop a case select or anything similar says that case 1: when all the count are 1, then.....; case 2: when not all the count are 1, then if when count = 1, balabala, else(count <>1), balabala
Can anyone tell me how to get it right? THANKS!

 

[pr2-eugin]

Try an IIF to achieve this? Something along the lines of..

caseValue : IIF(countFieldName = 1, "someBla", "otherBla")

 

[ice051505]

Thanks! But can you tell me how to write it in VBA? I thought that the IIF can not be written in VBA, am I right?

Try an IIF to achieve this? Something along the lines of..

caseValue : IIF(countFieldName = 1, "someBla", "otherBla")

 

[MSAccessRookie]

A Basic IIf() can be rewritten in VBA as an If - Then - Else - End if Statement

IIF(countFieldName = 1, "someBla", "otherBla")
 
Becomes
 
If countFieldName = 1 Then
    "someBla"
Else
    "otherBla"
End if

 

[pr2-eugin]

I thought we are dealing with Queries here..

My problem is that I have a query ...

Regardless, IIF can be used in VBA too.. It depends on how you wish to use them.. for example you want to set an unbound box with a String saying PASS if the totalMark control is >=50 else FAIL..

Using standard VBA it will be,

If Me.totalMark >= 50 Then
    Me.resultTxt = "PASS"
Else
     Me.resultTxt = "PASS"
End If

These lines of code can be simplified to

Me.resultTxt = IIF(Me.totalMark >= 50, "PASS", "FAIL")

but however if the logic is going to go beyond three deep then best to use If/Else structure.. Hope this helps..

 

[ice051505]

Thanks guys, I can write the if...then...else statment, but my only concern is I want to represent two brence with the countNumber,

one is when all the count number are 1, then I will have one way to deal with it;

the other brence is if not all the count number are 1, then for the count number are 1, I will have other way to deal with, and then, else, for the null one value, I will have the third way to deal with,

I want to know is there any method to let me represent the stuff like AllCountNumber is 1 or Null AllCountNumber is 1 ?

MANY MANY THANKS!

but however if the logic is going to go beyond three deep then best to use If/Else structure.. Hope this helps..

 

[pr2-eugin]

Hello ice051505, You can use If/ElseIf/Else structure..

If AllCountNumber = 1 
    'Do something as it is 1
ElseIf IsNull(AllCountNumber)
    'Do something as it is NULL
Else
    'Do something as it is not 1 and also it is not NULL
End If

There is also something called as SELECT CASE

Select Case AllCountNumber
    Case 1
        'Do something as it is 1
    Case 2
        'Do something as it is not 1 and also it is not NULL
    Case Else
        'Do something as it is NULL
End Select

PS : I have not tested the SELECT, but in theory should work..

 

[ice051505]

MANY Thanks!