Dcount with "And" and "Or"

[sdel_nevo]

hi guys

I am having a hard time getting my head around this but basically I am trying to do a dcount on multiple criteria

this is working as I would like

Nz(DCount("ItemNumber", "UNEXData", "ItemNumber = forms.UNEXdetails.ITEMNUMBER And Planned = true And Running = False And Completed = false"), 0)



this produces the correct number "1" which is correct

what I can't seem to get my head around is using an or statement for example

Nz(DCount("ItemNumber", "UNEXData", "ItemNumber = forms.UNEXdetails.ITEMNUMBER And Planned = true Or Running = true Or Completed = true"), 0)

this produces the incorrect number "67" which is incorrect

using this the values are miles off,

is this the correct way to count or is their another domain function that is used in this scenario

many thanks

Steve

[ranman256]

the 'OR' means what can appear in the 1 field: BOB or SAM (the field can be BOB or it can be SAM)

'AND' is when you want to have 2 different fields [name] = BOB AND [state] = 'NY' (all Bobs who live in NY)

[sdel_nevo]

Hi Ranman256

wow that was quick

in the code the itemnumber has only been planned once, but two others orders with the same part number are running and one has been completed

Nz(DCount("ItemNumber", "UNEXData", "ItemNumber = forms.UNEXdetails.ITEMNUMBER And Planned = true or Running =true or Completed =true"), 0) I would have thought that I would get the value back as 4

1 planned and 2 running and 1 completed I can't seem to get my head around why the value is so far out.

Steve

[ranman256]

also, (not sure about your example but)
you cant use "Planned = true or Running =true " as a string, TRUE is numeric , so dont put it inside the quotes.

If this string is not the case,
you may be wanting to ask 2 different querys planned = true
then
ask running = true

[sdel_nevo]

Hi Ranman256

many thanks for the heads up, can't believe I missed that, I will ask the two separate queries

many thanks again

steve

[John_G]

Another problem you are facing is the concept of operator precedence when it comes to AND and OR. The way you have it in your post #3, you are counting records where Running =true OR Completed =true REGARDLESS of the value of ItemNumber on the form. Is this what you really want?

If what you want is to count records for a specific ItemNumber where any of Planned, Running or Completed is true, then you need brackets:

Nz(DCount("ItemNumber", "UNEXData", "ItemNumber = forms.UNEXdetails.ITEMNUMBER And (Planned = true or Running =true or Completed =true") ), 0)

You might also want to take the form field reference out of the quotation marks:

Nz(DCount("ItemNumber", "UNEXData", "ItemNumber = " & forms.UNEXdetails.ITEMNUMBER & " And (Planned = true or Running =true or Completed =true") ), 0)

[sdel_nevo]

Hi John G
Many thanks for he reply, I will try the solution you have posted first thing in the morning when I'm back in the office.

I think this forum is fantastic it's so nice to get help and advise from such helpful people, I have really enjoyed learning vba but it seams the more I think I know the less I actually do lol,

Once again many thanks
Steve