DSum Type Mismatch

  • Thread starter Thread starter Todd H
  • Start date Start date
T

Todd H

I am getting a TYPE MISMATCH error in the expression of the following
statement:

PrevAdj:
IIf([qryProjectLineItems.BMNo]=1,0,DSum("Amount","qryBMR","qryProjectLineItems.CostCode='"
& _
[qryProjectLineItems.CostCode] & "' And qryProjectLineItems.BMNo
Between 1 and " & _
([qryProjectLineItems.BMNo]-1)))

BMNo is a Number
Amount is Number
CostCode is Text

I am trying to sum the "previous" amounts for any given Line Item,
based on the BMNo.

Thanks, Todd
 
Probably [qryProjectLineItems].[CostCode] instead of
[qryProjectLineItems.CostCode], or remove the [ ]. Same for BMNo. Be sure
these fields do not have NULL as value, and that CostCode does not have ' in
any of its value.



Vanderghast, Access MVP
 
I am getting a TYPE MISMATCH error in the expression of the following
statement:

PrevAdj:
IIf([qryProjectLineItems.BMNo]=1,0,DSum("Amount","qryBMR","qryProjectLineItems.CostCode='"
& _
[qryProjectLineItems.CostCode] & "' And qryProjectLineItems.BMNo
Between 1 and " & _
([qryProjectLineItems.BMNo]-1)))

BMNo is a Number
Amount is Number
CostCode is Text

I am trying to sum the "previous" amounts for any given Line Item,
based on the BMNo.
Click to expand...

If you're trying to sum a field from qryBMR, you need to reference qryBMR in
the criteria, not qryProjectLineItems; or since it's unambiguous within DSum,
just leave the query name out. I'd also fix the brackets:

PrevAdj:
IIf([qryProjectLineItems].[BMNo]=1,0,DSum("Amount","qryBMR","CostCode='" & _
[qryProjectLineItems].[CostCode] & "' And [BMNo] Between 1 and " & _
([qryProjectLineItems].[BMNo]-1)))
 
Back
Top