DLookup - Multiple Criteria

[jlindquist23]

I'd like to count the numbers in a subform based on set of criteria below AND the count of number of records in the subform. I need them both to display in the main form HTRecord.
1. I only want the records that coincide with the main form "ID".
2. I'd like to only count records that don't have anything in the "LastRackQty" field.
3. I'd like the count to be in the main form and not the subform, shown as "CountOfZeroLastRackQty"

If tried lots of variations of this formula:
=DCount(“LastRackQty","LoadData","ID = forms![HTRecord.ID]" AND “NZ(LastRackQty,0)>0")

LoadData is the name of the Table behind LoadData_Subform
PartData is the name of the table behind the main HTRecord form.


They are link by "ID".

[Welshgasman]

Put all the criteria into astring variable. Then debug.print it, until you get it correct. Then use that variable in the function.

[xps35]

Maybe something like:

=DCount(“LastRackQty","LoadData","ID = " & forms![HTRecord.ID] & " AND “ & NZ(LastRackQty,0) *>0)")

[June7]

Those [ ] characteres are misplaced and not even needed. If ID and calculated textbox are on main form, don't need form reference.

=DCount("LastRackQty", "LoadData", "ID = " & ID & " AND NZ(LastRackQty,0)>0")

Assumes LastRackQty is field in table/query LoadData.

The second criteria probably not need as Null records should not be counted.

[Welshgasman]

O/P is specifically looking for Null in lastrackqty though?, so I would expect =0 ?

[June7]

Ooops, see that now. Yes =0

or

=DCount("*", "LoadData", "ID = " & ID & " AND LastRackQty IS NULL")

[jlindquist23]

This one worked!
=DCount("LastRackQty", "LoadData", "ID = " & ID & " AND NZ(LastRackQty,0)>0")
Thank you!

[Welshgasman]

This one worked!
=DCount("LastRackQty", "LoadData", "ID = " & ID & " AND NZ(LastRackQty,0)>0")
Thank you!

Not what you initially said?

2. I'd like to only count records that don't have anything in the "LastRackQty" field.