Navigation Subforms with Dcount Control Source Referring to Date Range

[Bluecider]

Well.. I'm stumped.



I have a main form 'frm_MAIN' which calls a subform 'frm_STATS' from a Navigation Control on the main form.

Within the 'frm_STATS' I have a text box where I'm trying to report the number of records from table 'tbl_JOBS' based on a date range (two text boxes with date picker) on the same form 'frm_STATS'.
Date From textbox = 'mndate'
Date To textbox = 'mxdate'

Here is my control source dcount:

=DCount("*","tbl_JOBS","[CreationDate] between DateValue('" & [Forms]![frm_MAIN].[NavigationSubform].[Form].[mndate] & "') AND DateValue('" & [Forms]![frm_MAIN].[NavigationSubform].[mxdate] & "')")

I'm getting a #Name? error in the text box where the Dcount exists above.

Any ideas?

[June7]

Date parameters use # delimiter not ' .

I don't see anything else wrong with syntax referencing textbox.

[Bluecider]

Still yields same error #Name?

=DCount("*","tbl_JOBS","[CreationDate] between DateValue(#" & [Forms]![frm_MAIN].[NavigationSubform].[Form].[mndate] & "#) AND DateValue(#" & [Forms]![frm_MAIN].[NavigationSubform].[mxdate] & "#)")

Now, I know that maybe this isn't the most elegant way to do this. Though I just wanted to save myself pushing the results via VBA into the text boxes.

When opening the subform 'frm_STATS' by itself, I had no error with doing this:

=DCount("*","tbl_JOBS","[CreationDate] between DateValue('" & [Forms]![frm_STATS].[mndate] & "') AND DateValue('" & [Forms]![frm_STATS].[mxdate] & "')")

So I think it's something to do with calling the subform which is within the navigational controls 'NavigationSubform'

[June7]

CreationDate is a date/time type field?

I was afraid Navigation Form was the primary issue. I have never used them because they are trickier to reference. Review https://www.accessforums.net/showthread.php?t=32053

If you want to provide db for analysis, follow instructions at bottom of my post.

[Bluecider]

Ok... Got it to work:


=DCount("*","tbl_JOBS","[CreationDate] between DateValue('" &Forms![frm_MAIN].NavigationSubform.Form.[mndate] & "') AND DateValue('" & Forms![frm_MAIN].NavigationSubform.Form.[mxdate] & "')")

[June7]

Glad you got it working. However, aside from removing some []'s which should not matter in this case, I don't see any difference from the original syntax.