Using textbox value for DCount() #Error

[Zc kaiva]

I have a textbox 'Text1' which stores date value entered as report parameter on my report. When u use it with dcount() as criteria it gives me #error. This is what i have.

=Nz(Dcount("[BusNo]","[qryBus]","[qryBus].[RegDate]=[Text1]"),0)

Can anyone help me find my mistake?

 

[plog]

1. Dcount will never return null, so Nz() is not needed.
2. You don't need to reference the query in the criteria argument.
3. You need to escape out of the criteria string to insert the date from the form.
4. You must use escape characters around the date value in the criteria.

Check out this post with very similar issue:

DCount with date criteria

Dear All I'd be very grateful for a steer on how to get the DCount function working with date critera. The idea is to have a function on a form that checks that the user isn't entering a record that already exists on [tbl], by comparing the date being entered on a form with dates that already...

www.access-programmers.co.uk

 

[Ranman256]

=Dcount("[BusNo]","qryBus","[RegDate]=#" & txtBox & "#")

 

[arnelgp]

=Dcount("[BusNo]","qryBus","[RegDate]=#" & Format$([Text1], "mm\/dd\/yyyy") & "#")

 

[Zc kaiva]

=Dcount("[BusNo]","qryBus","[RegDate]=#" & Format$([Text1], "mm\/dd\/yyyy") & "#")

I tried it but it keeps givim 0 as count.

 

[arnelgp]

does [RegDate] has "time" element in it?