Dim var@@@ As Integer Question...

[Fish218]

So I have the following two sets of the same code. The only difference between the two is highlighted in red. Why does the top code work and the bottom doesn't?? The only difference is removing "As Integer" to get the bottom code to work. Isn't varFishCount an integer in the query?

Private Sub Length_AfterUpdate()
Dim varYear As Integer
Dim varAssessment As Integer
Dim varCollector As Integer
Dim varFishCount



varYear = [Forms]![frmCommercial2]![Form1]![Text422]
varAssessment = [Forms]![frmCommercial2]![Form1]![Text444]
varCollector = [Forms]![frmCommercial2]![Form1]![Combo431]
varFishCount = DLookup("[CountofIndividualFishID]", "qry2012", "[CollectionYear]=" & varYear & " And [CollectorID]=" & varCollector & " And [AssessCodeID]=" & varAssessment)

If (IsNull(varFishCount)) Then
[Text46] = 0
[Text48] = 1
ElseIf Not (IsNull(varFishCount)) Then
[Text46] = varFishCount
[Text48] = varFishCount + 1
End If
[FishNum] = [Forms]![frmCommercial2]![Form1]![Text422] & "-" & [Forms]![frmCommercial2]![Form1]![Text444] & "-" & [Forms]![frmCommercial2]![Form1]![Combo431] & "-" & (Format([Text48], "0000"))
[DataEntryID].Value = [Forms]![frmCommercial2]![Combo23]
End Sub



Private Sub Length_AfterUpdate()
Dim varYear As Integer
Dim varAssessment As Integer
Dim varCollector As Integer
Dim varFishCount As Integer

varYear = [Forms]![frmCommercial2]![Form1]![Text161]
varAssessment = [Forms]![frmCommercial2]![Form1]![Text367]
varCollector = [Forms]![frmCommercial2]![Form1]![CollectorID]
varFishCount = DLookup("[CountofIndividualFishID]", "qry2012", "[CollectionYear]=" & varYear & " And [CollectorID]=" & varCollector & " And [AssessCodeID]=" & varAssessment)

If (IsNull(varFishCount)) Then
[Text51] = 0
[Text53] = 1
ElseIf Not (IsNull(varFishCount)) Then
[Text51] = varFishCount
[Text53] = varFishCount + 1
End If
[FishNum] = [Forms]![frmCommercial2]![Form1]![Text161] & "-" & [Forms]![frmCommercial2]![Form1]![Text367] & "-" & [Forms]![frmCommercial2]![Form1]![CollectorID] & "-" & (Format([Text53], "0000"))
End Sub

[pbaldy]

Well, you haven't said what the error was. An undeclared variable is a Variant by default. Variant can hold a Null and Integer can't, so I suspect the DLookup is returning a Null. The first code would handle that, the second wouldn't.

[Fish218]

Got it. Improper use of Null was the error, so what you said makes sense.

[pbaldy]

By the way, most of us use the first bit of the variable name to indicate what data type it is, so

Dim intWhatever As Integer
Dim varWhatever As Variant
Dim dteWhatever As Date

I assume you're using var to indicate it's a variable, which is fine. I'm just throwing it out there. The important thing about a naming convention is that it has meaning to you.

[Fish218]

Good assumption on your part and a lack of Access naming convention knowledge on mine. Thanks for the tip.