Dcount question

[SwingXH]

I would like to count the records number according to the two dates that specified on the form.
The following codes are used, but it returned me the number for all the records.

MyDate is in the format of mm/dd/yyyy

Me.TxtTotalRec.Value = DCount("MyDate", "MyTable", "'MyDate<= [Forms]![MyForm]![TextEndDate]' And 'MyDate>= [Forms]![MyForm]![TextStartDate]'")

I also tried this, then there's a type mismatch error.
Me.TxtTotalRec.Value = DCount("MyDate", "MyTable", "MyDate<= [Forms]![MyForm]![TextEndDate] And MyDate>= [Forms]![MyForm]![TextStartDate]")

Anybody knows the right one?
Thanks a lot!
SwingXH

 

[PHV]

Have you tried this ?
Me.TxtTotalRec.Value = DCount("MyDate", "MyTable", "MyDate Between #" & Me!TextStartDate & "# And #" & Me!TextEndDate & "#")


Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ222-2244

 

[SwingXH]

Great, thank you PHV!
There's one situation that MyDate in the records could be Null, then this Dcount will not count this record. Is there any way that can include the records with NUll Mydate?
Thanks,

SwingXH

 

[PHV]

Something like this ?
Me.TxtTotalRec.Value = DCount("MyDate", "MyTable", "(MyDate Between #" & Me!TextStartDate & "# And #" & Me!TextEndDate & "#) OR MyDate Is Null")

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ222-2244