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?

 

[Pat Hartman]

Formatting isn't necessary unless the control is unbound AND does not have a format property that indicates it will be a date.

PS - if Text1 is really the name of the control, you might want to rename the control to give it a meaningful name before you add any event procedures or references that use the poor name.