other solution to If-Then?

G

Guest

Hi all,

I got a code for my command button to open different forms depending on
values of different fields. Luckily it works all right, but i suspect there
could be another solution here (like, may be, "select case" or smth else?)
instead of such a long code... Does anybody have any suggestions?

Private Sub Command0_Click()
On Error GoTo Err_Command0_Click

Dim stDocName As String
Dim stLinkCriteria As String


If [BA-176-USD_G] > 0 Then
stDocName = "BA-176-Received"
Else
End If

If [BA-176-USD_G] <= 0 Then
stDocName = "BA-176-Spent"
Else
End If

If [BA-324-VND_G] > 0 Then
stDocName = "BA-324-Received"
Else
End If

If [BA-324-VND_G] <= 0 Then
stDocName = "BA-324-Spent"
Else
End If

If [BA-305-VND_G] > 0 Then
stDocName = "BA-305-Received"
Else
End If

If [BA-305-VND_G] <= 0 Then
stDocName = "BA-305-Spent"
Else
End If

If [BA-351-USD_G] > 0 Then
stDocName = "BA-351-Received"
Else
End If

If ([BA-351-USD_G] <= 0) Then
stDocName = "BA-351-Spent"
Else
End If

If [Cash-VND] > 0 Then
stDocName = "Cash Received"
Else
End If

If [Cash-VND] <= 0 Then
stDocName = "Cash Spent"
Else
End If


stLinkCriteria = "[ID]=" & Me!ID1
DoCmd.OpenForm stDocName, , , stLinkCriteria

Exit_Command0_Click:
Exit Sub

Err_Command0_Click:
MsgBox Err.Description
Resume Exit_Command0_Click
End Sub

Thank you.
Lana
 
G

Guest

Only really by not testing for both a positive and negative value.
e.g.

If [BA-176-USD_G] > 0 Then
stDocName = "BA-176-Received"
Else
End If

If [BA-176-USD_G] <= 0 Then
stDocName = "BA-176-Spent"
Else
End If

Could be re-written as;

If [BA-176-USD_G] > 0 Then
stDocName = "BA-176-Received"
Else
stDocName = "BA-176-Spent"
End If

I take it that only one field e.g. [BA-716-USD_G] can be non-zero at any one
time?
Otherwise you'll only get a report from the last statement.

Hope this helps.
 
G

Guest

That is very strange looking code: are you sure that your
example matches what you have? and that it works correctly?

How come you have to many matches for zero? Is it meant
to be nested?

Note that the un-nested if-then statements as shown have
the same effect as nested if-then statements written in the
opposite order. Can you see that?

Here is a case statement with slightly different logic. This inverts
the precedence, (to restore the precedence just reverse the order),
but since it doesn't match on zero, perhaps that doesn't matter:

Select case True
case [BA-176-USD_G] >0 : stDocName = "BA-176-Received"
case [BA-176-USD_G] <0 : stDocName = "BA-176-Spent"
case [BA-324-VND_G] >0 : stDocName = "BA-324-Received"
case [BA-324-VND_G] <0 : stDocName = "BA-324-Spent"
case [BA-305-VND_G] >0 : stDocName = "BA-305-Received"
case [BA-305-VND_G] <0: stDocName = "BA-305-Spent"
case [BA-351-USD_G] >0: stDocName = "BA-351-Received"
case [BA-351-USD_G] <0: stDocName = "BA-351-Spent"
case [Cash-VND] > 0: stDocName = "Cash Received"
case [Cash-VND] < 0: stDocName = "Cash Spent"
case else stDocName = ""
end select

(david)

PS: don't use punctuation in your field names if you can possibly
avoid it. That minus sign (cash minus vnd) is a problem waiting to
happen.
 
G

Guest

Hi David,

I tried the below code, but it would say "Invalid use of Null". Why would it
be?

Private Sub Command0_Click()
On Error GoTo Err_Command0_Click

Dim stDocName As String
Dim stLinkCriteria As String

Select Case True
Case [BA-176-USD_G] > 0: stDocName = "BA-176-Received"

Case [BA-176-USD_G] <= 0 And (IsNull([CW_No])): stDocName =
"BA-176-Spent"

Case ([BA-176-USD_G] <= 0) And ([CW_No] > "0"): stDocName =
"BA-176-Withdrawal"

Case [BA-324-VND_G] > 0: stDocName = "BA-324-Received"

Case [BA-324-VND_G] <= 0 And (IsNull([CW_No])): stDocName = "BA-324-Spent"

Case ([BA-324-VND_G] <= 0) And ([CW_No] > "0"): stDocName =
"BA-324-Withdrawal"

Case [BA-305-VND_G] > 0: stDocName = "BA-305-Received"

Case [BA-305-VND_G] <= 0 And (IsNull([CW_No])): stDocName = "BA-305-Spent"

Case ([BA-305-VND_G] <= 0) And ([CW_No] > "0"): stDocName =
"BA-305-Withdrawal"

Case [BA-351-USD_G] > 0: stDocName = "BA-351-Received"

Case ([BA-351-USD_G] <= 0) And (IsNull([CW_No])): stDocName =
"BA-351-Spent"

Case ([BA-351-USD_G] <= 0) And ([CW_No] > "0"): stDocName =
"BA-351-Withdrawal"

Case [Cash-VND] > 0 And (IsNull([CW_No])): stDocName = "Cash Received"

Case [Cash-VND] <= 0: stDocName = "Cash Spent"

Case [NotPaid] = True: stDocName = "PREQs"

Case IsNull([Cash-VND]) And IsNull([BA-351-USD_G]) And
IsNull([BA-305-VND_G]) And IsNull([BA-324-VND_G]) And IsNull([BA-176-USD_G])
And [NotPaid] = False: stDocName = "blanks"


Case Else

End Select

stLinkCriteria = "[ID]=" & Me!ID1
DoCmd.OpenForm stDocName, , , stLinkCriteria

Exit_Command0_Click:
Exit Sub

Err_Command0_Click:
MsgBox Err.Description
Resume Exit_Command0_Click

End Sub


Lana


david@epsomdotcomdotau said:
That is very strange looking code: are you sure that your
example matches what you have? and that it works correctly?

How come you have to many matches for zero? Is it meant
to be nested?

Note that the un-nested if-then statements as shown have
the same effect as nested if-then statements written in the
opposite order. Can you see that?

Here is a case statement with slightly different logic. This inverts
the precedence, (to restore the precedence just reverse the order),
but since it doesn't match on zero, perhaps that doesn't matter:

Select case True
case [BA-176-USD_G] >0 : stDocName = "BA-176-Received"
case [BA-176-USD_G] <0 : stDocName = "BA-176-Spent"
case [BA-324-VND_G] >0 : stDocName = "BA-324-Received"
case [BA-324-VND_G] <0 : stDocName = "BA-324-Spent"
case [BA-305-VND_G] >0 : stDocName = "BA-305-Received"
case [BA-305-VND_G] <0: stDocName = "BA-305-Spent"
case [BA-351-USD_G] >0: stDocName = "BA-351-Received"
case [BA-351-USD_G] <0: stDocName = "BA-351-Spent"
case [Cash-VND] > 0: stDocName = "Cash Received"
case [Cash-VND] < 0: stDocName = "Cash Spent"
case else stDocName = ""
end select

(david)

PS: don't use punctuation in your field names if you can possibly
avoid it. That minus sign (cash minus vnd) is a problem waiting to
happen.

Lana said:
Hi all,

I got a code for my command button to open different forms depending on
values of different fields. Luckily it works all right, but i suspect there
could be another solution here (like, may be, "select case" or smth else?)
instead of such a long code... Does anybody have any suggestions?

Private Sub Command0_Click()
On Error GoTo Err_Command0_Click

Dim stDocName As String
Dim stLinkCriteria As String


If [BA-176-USD_G] > 0 Then
stDocName = "BA-176-Received"
Else
End If

If [BA-176-USD_G] <= 0 Then
stDocName = "BA-176-Spent"
Else
End If

If [BA-324-VND_G] > 0 Then
stDocName = "BA-324-Received"
Else
End If

If [BA-324-VND_G] <= 0 Then
stDocName = "BA-324-Spent"
Else
End If

If [BA-305-VND_G] > 0 Then
stDocName = "BA-305-Received"
Else
End If

If [BA-305-VND_G] <= 0 Then
stDocName = "BA-305-Spent"
Else
End If

If [BA-351-USD_G] > 0 Then
stDocName = "BA-351-Received"
Else
End If

If ([BA-351-USD_G] <= 0) Then
stDocName = "BA-351-Spent"
Else
End If

If [Cash-VND] > 0 Then
stDocName = "Cash Received"
Else
End If

If [Cash-VND] <= 0 Then
stDocName = "Cash Spent"
Else
End If


stLinkCriteria = "[ID]=" & Me!ID1
DoCmd.OpenForm stDocName, , , stLinkCriteria

Exit_Command0_Click:
Exit Sub

Err_Command0_Click:
MsgBox Err.Description
Resume Exit_Command0_Click
End Sub

Thank you.
Lana
Click to expand...
Click to expand...
 
G

Guest

Either remove the error handler, or go to VBA options,
and set 'break on all errors'

At which point does it say "Invalid use of Null" ?

Also, why are you comparing to "0". That works, but surely
you should be comparing to 0, not "0".

(david)

Lana said:
Hi David,

I tried the below code, but it would say "Invalid use of Null". Why would it
be?

Private Sub Command0_Click()
On Error GoTo Err_Command0_Click

Dim stDocName As String
Dim stLinkCriteria As String

Select Case True
Case [BA-176-USD_G] > 0: stDocName = "BA-176-Received"

Case [BA-176-USD_G] <= 0 And (IsNull([CW_No])): stDocName =
"BA-176-Spent"

Case ([BA-176-USD_G] <= 0) And ([CW_No] > "0"): stDocName =
"BA-176-Withdrawal"

Case [BA-324-VND_G] > 0: stDocName = "BA-324-Received"

Case [BA-324-VND_G] <= 0 And (IsNull([CW_No])): stDocName = "BA-324-Spent"

Case ([BA-324-VND_G] <= 0) And ([CW_No] > "0"): stDocName =
"BA-324-Withdrawal"

Case [BA-305-VND_G] > 0: stDocName = "BA-305-Received"

Case [BA-305-VND_G] <= 0 And (IsNull([CW_No])): stDocName = "BA-305-Spent"

Case ([BA-305-VND_G] <= 0) And ([CW_No] > "0"): stDocName =
"BA-305-Withdrawal"

Case [BA-351-USD_G] > 0: stDocName = "BA-351-Received"

Case ([BA-351-USD_G] <= 0) And (IsNull([CW_No])): stDocName =
"BA-351-Spent"

Case ([BA-351-USD_G] <= 0) And ([CW_No] > "0"): stDocName =
"BA-351-Withdrawal"

Case [Cash-VND] > 0 And (IsNull([CW_No])): stDocName = "Cash Received"

Case [Cash-VND] <= 0: stDocName = "Cash Spent"

Case [NotPaid] = True: stDocName = "PREQs"

Case IsNull([Cash-VND]) And IsNull([BA-351-USD_G]) And
IsNull([BA-305-VND_G]) And IsNull([BA-324-VND_G]) And IsNull([BA-176-USD_G])
And [NotPaid] = False: stDocName = "blanks"


Case Else

End Select

stLinkCriteria = "[ID]=" & Me!ID1
DoCmd.OpenForm stDocName, , , stLinkCriteria

Exit_Command0_Click:
Exit Sub

Err_Command0_Click:
MsgBox Err.Description
Resume Exit_Command0_Click

End Sub


Lana


david@epsomdotcomdotau said:
That is very strange looking code: are you sure that your
example matches what you have? and that it works correctly?

How come you have to many matches for zero? Is it meant
to be nested?

Note that the un-nested if-then statements as shown have
the same effect as nested if-then statements written in the
opposite order. Can you see that?

Here is a case statement with slightly different logic. This inverts
the precedence, (to restore the precedence just reverse the order),
but since it doesn't match on zero, perhaps that doesn't matter:

Select case True
case [BA-176-USD_G] >0 : stDocName = "BA-176-Received"
case [BA-176-USD_G] <0 : stDocName = "BA-176-Spent"
case [BA-324-VND_G] >0 : stDocName = "BA-324-Received"
case [BA-324-VND_G] <0 : stDocName = "BA-324-Spent"
case [BA-305-VND_G] >0 : stDocName = "BA-305-Received"
case [BA-305-VND_G] <0: stDocName = "BA-305-Spent"
case [BA-351-USD_G] >0: stDocName = "BA-351-Received"
case [BA-351-USD_G] <0: stDocName = "BA-351-Spent"
case [Cash-VND] > 0: stDocName = "Cash Received"
case [Cash-VND] < 0: stDocName = "Cash Spent"
case else stDocName = ""
end select

(david)

PS: don't use punctuation in your field names if you can possibly
avoid it. That minus sign (cash minus vnd) is a problem waiting to
happen.

Lana said:
Hi all,

I got a code for my command button to open different forms depending on
values of different fields. Luckily it works all right, but i suspect there
could be another solution here (like, may be, "select case" or smth else?)
instead of such a long code... Does anybody have any suggestions?

Private Sub Command0_Click()
On Error GoTo Err_Command0_Click

Dim stDocName As String
Dim stLinkCriteria As String


If [BA-176-USD_G] > 0 Then
stDocName = "BA-176-Received"
Else
End If

If [BA-176-USD_G] <= 0 Then
stDocName = "BA-176-Spent"
Else
End If

If [BA-324-VND_G] > 0 Then
stDocName = "BA-324-Received"
Else
End If

If [BA-324-VND_G] <= 0 Then
stDocName = "BA-324-Spent"
Else
End If

If [BA-305-VND_G] > 0 Then
stDocName = "BA-305-Received"
Else
End If

If [BA-305-VND_G] <= 0 Then
stDocName = "BA-305-Spent"
Else
End If

If [BA-351-USD_G] > 0 Then
stDocName = "BA-351-Received"
Else
End If

If ([BA-351-USD_G] <= 0) Then
stDocName = "BA-351-Spent"
Else
End If

If [Cash-VND] > 0 Then
stDocName = "Cash Received"
Else
End If

If [Cash-VND] <= 0 Then
stDocName = "Cash Spent"
Else
End If


stLinkCriteria = "[ID]=" & Me!ID1
DoCmd.OpenForm stDocName, , , stLinkCriteria

Exit_Command0_Click:
Exit Sub

Err_Command0_Click:
MsgBox Err.Description
Resume Exit_Command0_Click
End Sub

Thank you.
Lana
Click to expand...
Click to expand...
Click to expand...
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Similar Threads

Sum complications 2
Calculating SUM 2
Select Case - "Invalid use of Null" Problem 7
If Statements 2
Pass data to new form 2
User Defined 3
Object dosent support this property or method 2
Datashhet View from another Form wont work 2

Top