Dcount with multiple criteria

[krberube]

Not sure if this belonged in reports or queries, so I chose general. I have looked at several DCount threads but haven't quite found my answer. I want to use Dcount in an unbound textbox in a report. It counts the number of records in another table - comparison
the first part of the statement works fine ( up to 'iss'"). When i added the between date part i'm not getting any # returned in my report. I want to addthe criteria of RecDate between the 2 dates on the open form.
Can anyone tell me where my problem lies? ( if this makes sense)

Thanks
Kevin


=DCount("[type]","comparison","[type] Like 'iss*'" And ("[comparison].[RecDate]" Between Forms!DateInputforRMAsReturned!Text0 And Forms!DateInputforRMAsReturned!Text2))

 

[KenHigg]

I would break the two pcs out and get them to working separately first...

 

[WayneRyan]

Kevin,

Indented for readability:

=DCount("[type]", _
        "comparison", _
        "[type] Like 'iss*'" And " & _
        "RecDate" Between #" & Forms!DateInputforRMAsReturned!Text0 & "# And #" & Forms!DateInputforRMAsReturned!Text2 & "#")

If it's on the form DateInputforRMAsReturned:

=DCount("[type]", _
        "comparison", _
        "[type] Like 'iss*'" And " & _
        "RecDate" Between #" & Me.Text0 & "# And #" & Me.Text2 & "#")

Wayne

 

[krberube]

Still working on Kens Idea - got them to work seperatly, just can't combine them.
WayneRyan -- tried you suggestion and get a syntax error

Still trying though.
thanks

 

[KenHigg]

Did you seperate them with ()'s: (xxx) and (yyy)

???

 

[WayneRyan]

Kevin,

Mine had a type, the 3rd line had "RecDate". Shouldn't have had the
trailing ".

Post your current syntax. This shouldn't be too difficult.

Wayne

 

[krberube]

below works for counting between the dates:

=DCount("[RecDate]","comparison","[RecDate] Between Forms!DateInputforRMAsReturned!Text0 And Forms!DateInputforRMAsReturned!Text2")

below works for counting like "iss":

=DCount("[type]","comparison","[type] Like 'iss*'")

I tried combining them like:
=DCount("[RecDate]","comparison","[RecDate] Between Forms!DateInputforRMAsReturned!Text0 And Forms!DateInputforRMAsReturned!Text2") And (comparison!type Like "iss*")

but it asks for an entry for "comparison"

not really sure what to try next.?

how do you create the little windows in the POST to place the code in?

 

[WayneRyan]

Kevin,

The parser can't resolve comparison!type, it probably thinks that it's a
form field (on a closed form).

Change: comparison!type --> [Type]

However, I think that type might be reserved, leading to unpredictable
results.

For code:

(code)
This Is Some Code
(/code)

In the above, change the parenthesis to square brackets.

Wayne

 

[krberube]

I agree this should'nt be to hard ( Ken has helped me in the past on something I thought was simple and it took us awile to get it right).

I changed type --> type1 just in case. It still prompts me for type.

=DCount("[RecDate]","comparison","[RecDate] Between Forms!DateInputforRMAsReturned!Text0 And Forms!DateInputforRMAsReturned!Text2") And ([Type1] Like "iss*")

is it because my ---- And ([Type1] Like "iss*") ---- doesn't tell dcount to look at the comparison table for type1 ???

maybe i'll go back to your example.

 

[WayneRyan]

Kevin,

Hopefully ...

=DCount("[RecDate]","comparison","[RecDate] Between #" & Forms!DateInputforRMAsReturned!Text0 & "# And #" & Forms!DateInputforRMAsReturned!Text2 & "# And [Type1] Like 'iss*')

Wayne

 

[krberube]

different error this time.
"the expression you entered has an invalid string. A string can be up to 2048 character long, including opening and closing quote marks."

 

[krberube]

I found another post you did with Justin72
http://www.access-programmers.co.uk/forums/showthread.php?t=81678&highlight=dcount+dates

It looks the same as I am dealing with now.
so i tried:

=DCount("[type1]", "comparison", "[type1] Like 'iss*' AND " & "[RecDate]" Between #" & Forms!DateInputforRMAsReturned!Text0 & "# And #" & Forms!DateInputforRMAsReturned!Text2 & "#")

and get an invalid date code message.

???

 

[WayneRyan]

Kevin,

Oops ... sorry!

Like 'iss*'") <-- Forgot the trailing "

Tough day for me too.

Wayne

 

[krberube]

That was it!!!!!! Thanks!!!!
Here is the code, maybe it will help someone else out in the future as well

=DCount("[RecDate]","comparison","[RecDate] Between #" & Forms!DateInputforRMAsReturned!Text0 & "# And #" & Forms!DateInputforRMAsReturned!Text2 & "# And [Type1] Like 'Rec*'")

Thanks again guys!!
Kevin

 

[WayneRyan]

Kevin,

=DCount("[type1]", "comparison", "[type1] Like 'iss*' AND " & "[RecDate]" Between #" & Forms!DateInputforRMAsReturned!Text0 & "# And #" & Forms!DateInputforRMAsReturned!Text2 & "#")

This one has an extra " after [RecDate]

"[RecDate]" Between

The second " should go away.

Wayne

 

[WayneRyan]

Kevin,

Glad to help.

The syntax for these (and IIfs) can be frustrating.

see ya,
Wayne

 

[krberube]

good way of looking at it Pat, Thanks for the suggestion.
Thanks again WayneRyan.

Kevin