Microsoft Access Developer 9

Microsoft Access Developer 9
By Richard Rost

4 years ago

DCount Variable vs IF DCount Upload Images Link
Keith Stanton
6 months ago

Hello,

Is there a reason that I have either forgotten or we haven't been taught yet that we assign a value to a variable vs performing the DCount right in the IF statement?

Why this?

    Dim C
    C = Nz(DCount("*", "QuestionT", "TestID=" & TestCombo), 0)
    If C = 0 Then

vs this?

    If Nz(DCount("*", "QuestionT", "TestID=" & TestCombo), 0) = 0 Then

both work, curious to know if there is a reason for one option over the other.

Adam Schwanz
6 months ago

You can use whichever, using variables is usually a lot more flexible/friendly. You can save a value for later use, change the value of the variable, or use them to not have to repeat code.

Lot easier to say
C = DCOUNT
If C = 0 Then
ElseIf C=1 Then
ElseIf C=2 Then
etc...

Then to do
If Nz(DCount("*", "QuestionT", "TestID=" & TestCombo), 0) = 0 Then
ElseIf Nz(DCount("*", "QuestionT", "TestID=" & TestCombo), 0) = 1 Then
ElseIf Nz(DCount("*", "QuestionT", "TestID=" & TestCombo), 0) = 2 Then
etc.

There's also the whole running through loops, you can do C=C+1 because it can change/save the value, you can't do Nz(DCount("*", "QuestionT", "TestID=" & TestCombo), 0) = Nz(DCount("*", "QuestionT", "TestID=" & TestCombo), 0) + 1 more than once. Variables are just better ;)

Adam Schwanz
6 months ago

Actually, I don't think you can do that DCOUNT = DCOUNT at all, but you get the point.

Keith Stanton
6 months ago

ah ok, that helps a lot. I haven't come across the need to have multiple options like that yet so I've just used it in an IF statement, but I can see how much more useful the variant option be if more than 1 option/outcome needed to be taken into consideration.
Thank You!

Richard Rost
6 months ago

Yeah, the only reason I would assign it to a variable is if I was going to use it more than once.

This thread is now closed. If you wish to comment, start a NEW discussion, below.

Back to Microsoft Access Developer 9 Comments