dcount challenge

[detrie]

Access 2000

Hi All

I am running a dcount
I need to count values that are between (and including) 3000 and 3499 the field is cmbLkpSCType
I also need to count values that are between (and including) 3500 and 3999
The cmbLkpSCType values are 3000 through 3499 the field is cmbLkpType

My challenge is to find a way of counting one and not include the other

If Me.cmbLkpSCType = "3000" Then
strBuild = Me.cmbLkpSCCountry & (Right([cmbLkpSCYear], 2)) & (Left([cmbLkpSCType], 2)) & (Left([cmbLkpSCBudgetUnit], 2))
strCt = DCount("strSourceCodeID", "tblProjectSegment", "(Left([strSourceCodeID], 8) ='" & strBuild & "')")
End If
'*****************
If Me.cmbLkpSCType = "3500" Then
strBuild = Me.cmbLkpSCCountry & (Right([cmbLkpSCYear], 2)) & (Left([cmbLkpSCType], 2)) & (Left([cmbLkpSCBudgetUnit], 2))
strCt = DCount("strSourceCodeID", "tblProjectSegment", "(Left([strSourceCodeID], 8) ='" & strBuild & "')")
End If

TIA
Detrie

 

[KenHigg]

Just a quick note about doing something like this; In order to isolate errors, I think I would try to take the embedded functions out from within each other... Like do the left() and right() befor you get to thie other functions like dcount()


Just a thought...

 

[detrie]

Hi Pat,
I 'DO' need the between clause, "BETWEEN 3000 and 3499" also "BETWEEN 3500 and 3999" I am having trouble with it. That is why I have the IF statements. What I need to happen is to count the string US06AB30xx and US06AB35xx as seperate counts... (not sure if this makes sense).
My table has 1 field with some 3000 records that needs to be counted to create the next SC# for the 'Prefix' selected

Detrie