Variable Assignment within IF Test Expression (1 Viewer)

Lee Mac

New member
Local time
Today, 23:17
Joined
Sep 28, 2016
Messages
8
Hi All,

A pleasure to be a new member of this forum; prior programming experience, but relatively new to VBA so I apologise in advance if my questions appear neophytic.

This should hopefully be an easy one:

As part of an existing if/elseif block, I'm look to test whether a table contains valid records, and, at the same time, assign the number of valid records in such table to a local variable.

Currently my code uses the following structure:
Code:
Sub test()
    Dim cnt As Long
    
    if < ... > then
        MsgBox "< ... > Invalid"
    elseif < ... > then
        MsgBox "< ... > Invalid"
    elseif < ... > then
        MsgBox "< ... > Invalid"
    Else
        cnt = DCount("Item", "Table", "Item <> ' '")
        If cnt = 0 Then
            MsgBox "No valid records found."
        End If
    End If
End Sub
A number of criteria are tested, but I must then use a nested if statement in order to store a count of the number of valid records before testing it.

Therefore, is there a way to do the following?:
Code:
Sub test()
    Dim cnt As Long
    
    if < ... > then
        MsgBox "< ... > Invalid"
    elseif < ... > then
        MsgBox "< ... > Invalid"
    elseif < ... > then
        MsgBox "< ... > Invalid"
    ElseIf (cnt = DCount("Item", "Table", "Item <> ' '")) = 0 Then
        MsgBox "No valid records found."
    End If
End Sub
Thank you in advance for your time.
 

plog

Banishment Pending
Local time
Today, 17:17
Joined
May 11, 2011
Messages
11,668
I don't understand. What's wrong with the 2nd code snippet you posted? What errors are you getting?

Did you just take time to register, then post code to the internet instead of just testing it yourself?
 

static

Registered User.
Local time
Today, 23:17
Joined
Nov 2, 2015
Messages
823
I've no idea what you are testing but SELECT CASE is usually the answer

Code:
select case <...>
case this : doSomething
case that : doSomethingElse
case else : doNothing
end select

Although since you are doing a dcount maybe the answer is to filter a recordset using the WHERE clause and check for relevant records that way.

Code:
select some, fields, here from sometable where this like '*< ... >*' or that like '*< ... >*'
 

Lee Mac

New member
Local time
Today, 23:17
Joined
Sep 28, 2016
Messages
8
I don't understand. What's wrong with the 2nd code snippet you posted? What errors are you getting?

Did you just take time to register, then post code to the internet instead of just testing it yourself?

No - I tested the second example, however I received a type mismatch error and so I thought I may have the syntax incorrect.

Though, with more experimentation I think I may have answered my own question: it seems that a variable assignment in VBA does not return the value assigned to the variable, but something else.

I used this example as a test:
Code:
Sub test()
    Dim str As String
    If (str = InputBox("Enter your name:")) = "Lee" Then
        Debug.Print "Hello Lee"
    Else
        Debug.Print "Your name is not Lee"
    End If
End Sub
 

Lee Mac

New member
Local time
Today, 23:17
Joined
Sep 28, 2016
Messages
8
I've no idea what you are testing but SELECT CASE is usually the answer

Code:
select case <...>
case this : doSomething
case that : doSomethingElse
case else : doNothing
end select

Although since you are doing a dcount maybe the answer is to filter a recordset using the WHERE clause and check for relevant records that way.

Code:
select some, fields, here from sometable where this like '*< ... >*' or that like '*< ... >*'

Thank you for the suggestion, but I don't think I could use a Select Case expression in this circumstance, as the various test expressions are referencing different data (I understood that Select Case can only test a single item of data).
 

Minty

AWF VIP
Local time
Today, 23:17
Joined
Jul 26, 2013
Messages
10,374
it seems that a variable assignment in VBA does not return the value assigned to the variable, but something else.

I used this example as a test:
Code:
Sub test()
    Dim str As String
    If (str = InputBox("Enter your name:")) = "Lee" Then
        Debug.Print "Hello Lee"
    Else
        Debug.Print "Your name is not Lee"
    End If
End Sub
That won't work as as str is not set in the If clause. You need to do it this way;

Code:
 Publice Sub test()
    Dim str As String
    
    str = InputBox("Enter your name:")
    If str = "Lee" Then
        Debug.Print "Hello Lee"
    Else
        Debug.Print "Your name is not Lee"
    End If
End Sub
 

plog

Banishment Pending
Local time
Today, 17:17
Joined
May 11, 2011
Messages
11,668
No - I tested the second example, however I received a type mismatch error and so I thought I may have the syntax incorrect.

Welcome to the world of misleading naming that is Access programming. With a name like 'DCount' you would assume the return value is a number. Right? I mean we count with numbers. Oh how wrong you are.

DCount, like DSum returns a string. Yes the data inside that string is numeric characters but Access sees it like a string. Which means you have to compare its result like a string. That means the thing on the other side of the equal signs needs to have quote marks around it.

Code:
Wrong:
ElseIf (cnt = DCount("Item", "Table", "Item <> ' '")) = 0 Then

Right:
ElseIf (cnt = DCount("Item", "Table", "Item <> ' '")) = "0" Then

Lastly, you don't even need that variable. cnt stores the value of the DCount a nano-second before you do the comparison and then you never use cnt again. So get rid of it:

Code:
 ElseIf (DCount("Item", "Table", "Item <> ' '")) = "0" Then

Don't even get me started on LAST and FIRST--just avoid them when writing SQL in Access.
 

static

Registered User.
Local time
Today, 23:17
Joined
Nov 2, 2015
Messages
823
I understood that Select Case can only test a single item of data.

Not really. As long as something can equate to being true/false

Code:
a = #9/28/2016#
b = "foo"

Select Case True
Case a = Date And b = "foo": Debug.Print "bar"
Case Else
    Debug.Print "meh"
End Select
 

Lee Mac

New member
Local time
Today, 23:17
Joined
Sep 28, 2016
Messages
8
That won't work as as str is not set in the If clause.

Many thanks Minty.

So, to confirm: in VBA, one cannot assign a value to a variable within a test expression - is this correct?
 

Lee Mac

New member
Local time
Today, 23:17
Joined
Sep 28, 2016
Messages
8
DCount, like DSum returns a string. Yes the data inside that string is numeric characters but Access sees it like a string. Which means you have to compare its result like a string. That means the thing on the other side of the equal signs needs to have quote marks around it.

I wasn't aware of this, but after some testing I can't seem to confirm this:
Code:
?typename(dcount("*","myTable"))
Long
Code:
?dcount("*","myTable")
 3 
?dcount("*","myTable")=3
True
?dcount("*","myTable")="3"
True
 

Lee Mac

New member
Local time
Today, 23:17
Joined
Sep 28, 2016
Messages
8
Not really. As long as something can equate to being true/false

Code:
a = #9/28/2016#
b = "foo"

Select Case True
Case a = Date And b = "foo": Debug.Print "bar"
Case Else
    Debug.Print "meh"
End Select

Thanks - I didn't know this was possible!
 

plog

Banishment Pending
Local time
Today, 17:17
Joined
May 11, 2011
Messages
11,668
Ugh, I hate to be pedantic on the internet. So first let me say, you're tests are accurate and what I identified as your initial issue is not your issue. I was wrong, putting quote marks around the 0 will not fix the issue. The assignment of the Dlookup to the variable was the issue.

With that said, Dlookup and DSum do return strings. What occured in the tests of your last post is called implicit conversion. Access recognized the "3" as 3 and did a numeric comparison. It still returned a string, but Access was smart enough to work with it any way.

Sorry for misdiagnosing the issue. The real issue was assigning the variable inside the If. So my ultimate suggestion still holds--just get rid of cnt and that thing should work.
 

Users who are viewing this thread

Top Bottom