Countiif date with criteria in textbox

[RAJESHKUMAR R]

Dear Friends,

I have a Form which contains a textbox i.e. named as FR_DATE, when user click on the textbox Access allow user to select a Date from Default Calendar

Support I need here is: -

The Moment any user selects a date in FR_DATE textbox, keeping selected Date as a Criteria; How many Rows Same Records are available in “FIRMORDERS” Table Ref_Date Column must be counted and gets reflected in Text15 (That is where I have below highlighted Formula).

=IIf([FR_DATE]>0,DCount("[Ref_Date]","FIRMORDERS","[Ref_Date]"="[FR_DATE]"),0)

Currently Text15 always display Zero Only! Please suggest me with an revised Expression Builder which I can update on Text15 Control Source!

Thanks & Regards,
Rajeshkumar R

[ranman256]

1. the formula has a 'litteral' in it, dont use quotes around a field that you want a value to resolve:

=IIf(isnull([FR_DATE]),0,DCount("[Ref_Date]","FIRMORDERS","[Ref_Date]=" & me.FR_DATE ))


2. i dont see the need for the IIF, if there is no date, it wont resolve either,
just set the box formula to the count

=DCount("[Ref_Date]","FIRMORDERS","[Ref_Date]=" & me.FR_DATE))

[RAJESHKUMAR R]

Dear ranman256,

Thanks for your instant response and suggestion!

I understood and choose to use logic of Option 2; Once I paste below Formula, Few red highlighted portions are automatically coming up in Expression Builder!

=DCount("[Ref_Date]","FIRMORDERS","[Ref_Date]=" & [me].[FR_DATE])

However the Final Result coming is "#Name?"

Could you please let me what is that I am missing, which doesn't provide correct Result!

Thanks & Regards,
Rajeshkumar R

[isladogs]

Use date delimiters

=DCount("[Ref_Date]","FIRMORDERS","[Ref_Date]=#" & Me.[FR_DATE] & "#")