Adding Criteria in MsgBox DSum sub

[bsegge]

Hello,



I am attempting to write code that sums the values in a volume field. It is as follows:

Private Sub Command128_Click()

MsgBox DSum("Volume", "Volume")

End Sub

It works just fine, however, I want to add criteria that allows me to sum the volume of fields with a similar separate variable (codename).

In other words, instead of summing the entire "Volume" field, I want it to sum the volume of all rows with a certain codename that I input.

I attempted to write it this way:


Private Sub Command128_Click()

MsgBox DSum("Volume", "Volume", WHERE [CodeName] LIKE "*' & Me.txtVolume & '*")

End Sub

But it didn't work. txtVolume is a textbox object I added to the form object I am working in.

Any help is appreciated!

[pbaldy]

See if this helps:

DLookup Usage Samples

[bsegge]

So I was able to figure out the code!

Here is what I have:

Private Sub cmdVolumeCount_Click()
Dim curVol As Long
Dim txtVolume As String

If IsNull(txtVolume = Me.txtVolume) Then
curVol = DSum("Volume", "Volume")
Else
txtVolume = Me.txtVolume
curVol = DSum("Volume", "Volume", "[CodeName] LIKE '*" & txtVolume & "*'")
End If
MsgBox curVol
End Sub

It works great if the text box (txtVolume) is empty, or contains a string of text that matches a [CodeName] in the respective field.

However, if I enter a value into the txtVolume object that is not in the [CodeName] field, it gives a null error report.

Does anyone have any further suggestions as to how to fix this issue?

[pbaldy]

Where to start? Given

If IsNull(txtVolume = Me.txtVolume) Then

does the first refer to the variable or the control? In other words, don't name a variable the same as a control. You'll confuse Access and yourself. On the same line, does that ever evaluate to True? I wouldn't expect so, as the equation would either be true or false, thus never Null. Could be wrong though. Your error probably comes from the fact that a Currency variable can't hold Null, which is probably what comes from the function. Either declare the variable as Variant or wrap the function in the Nz() function.

[pbaldy]

Looks like I was wrong about the test:

?null=4
Null

[bsegge]

The txtVolume variable is whatever data that is input into the txtVolume object (text box) that is within the form object I'm working with.

It is True whenever the entry into the txtVolume object matches a value in the [CodeName] field.

My code works for when the txtVolume object is both completely blank, and when the data entered matches the [CodeName] field.

The only time it doesn't work is when the data entered does not match the [CodeName] field

[pbaldy]

Did you miss:

Your error probably comes from the fact that a Currency variable can't hold Null, which is probably what comes from the function. Either declare the variable as Variant or wrap the function in the Nz() function.

[bsegge]

There are no currency data types in the table.

The fields are [CodeName] - Short Text, [VolumeDate] - Date/Time, and [Volume] - Number.

[pbaldy]

Sorry, I didn't notice that you used cur to prefix a Long, but my comment applies to Long as well:

Dim curVol As Long

[bsegge]

I apologize, I must be confused.

I used the prefix cur to mean current. As in, current volume.

Does using cur make it a currency?

[pbaldy]

No, many of us prefix variables with something that represents the data type, so I assumed you had declared it as Currency. In any case, the only data type that can hold a Null is Variant, so my original suggestion remains valid:

Either declare the variable as Variant or wrap the function in the Nz() function.

like:

curVol = Nz(DSum("Volume", "Volume", "[CodeName] LIKE '*" & txtVolume & "*'"), 0)

[bsegge]

Thank you SO much for having the patience to work through that with me.

I implemented the Nz() suggestion and it worked perfectly.

I appreciate your guidance and help!

[pbaldy]

No problem.