Filter a form with unbound text boxes that count values in a field based on the dates

[kengooch]

I have a From that shows a quick list of totals for various aspects of Patient data Table and it's child records. See image below.

They are asking now to be able to filter these totals by month or by quarter, and by Pathologist. So my thought is to provide a start and end date and tally the values that meet the criteria of the data range using an AND function to tie that two the dates in the vDateSt and vDateEnd and the vPathologist unbound text boxes. When the user clicks the Run Report button, it sets that filter and recalculates totals based upon the dates and pathologists. If not dates are selected and no pathologist selected, or if dates are selected and no pathologist is selected or if pathologist are selected and no dates, they want the numbers to be correct for those combinations.

The Unbound Text boxes that currently tally records are reading data from the Accession Log (tAccLog) and doing a simple DCount function. The equation I use in the Total Count box is.

=DCount("*","tAccLog","tSpecCode =22")

So my thinking is that I can modify that equation... I have tried many combinations... Here is my last attempt...

=DCount("*",tAccLog","tSpecCode =22 and Between (IIf(IsNull([vDateSt]),#1/1/1900#,[vDateSt])) And (IIf(IsNull([vDateEnd]),#12/31/3100#,[vDateEnd])) and tPathologist =4")

I use the "Between IIF(IsNull([vDatest]) string' in a query that prompts the user and it works perfectly, but the syntax is not working in my unbound text box.
Thanks in advance for your help!

 

[June7]

Try Nz instead of IsNull. It will be a shorter expression.
Need date field in the expression.
Are vDateSt and vDateEnd controls on form? Concatenate variable inputs.
Also, you show missing quote mark in front of tAccLog.

=DCount("*", "tAccLog", "tSpecCode=22 AND [your date field] BETWEEN " & Nz([vDateSt],#1/1/1900#) & " AND " & Nz([vDateEnd],#12/31/3100#) & " AND tPathologist=4")

 

[jdraw]

Ken,
Can you post the design of e "tAccLog"?
I agree with June re NZ.

 

[Cronk]

If you really must have an unbound form, open a recordset with all the values rather than having all those lookups.

 

[kengooch]

Here is the tAccLog structure. It holds the basic patient record. There is a Child Table that holds the specific details for each encounter called tAccSpc

The two tables are linked by the tAccNoLink field. Each item in the tAccLog may or may not have records in the tAccSpc table.
I have a query that houses both tables and shows all records and sub-records, which means I get multiple lines for items in the tAccID as they have 2 or 3 or more related records in the tAccSpc table.

Here is an image of a dummy patient record (Green is the tAccLog and Child record is in the Pink Box.

 

[kengooch]

Try Nz instead of IsNull. It will be a shorter expression.
Need date field in the expression.
Are vDateSt and vDateEnd controls on form? Concatenate variable inputs.
Also, you show missing quote mark in front of tAccLog.

=DCount("*", "tAccLog", "tSpecCode=22 AND [your date field] BETWEEN " & Nz([vDateSt],#1/1/1900#) & " AND " & Nz([vDateEnd],#12/31/3100#) & " AND tPathologist=4")

I added the vDateStart and vDateEnd as unbound text boxes onto the form with all of the totals, (see picture in earlier post) in hopes of being able to use those two fields to isolate the records by the date range and then be able to count the totals bound by the date range. vDateStart shows on the form as "Start Date" and vDateEnd shows as "End Date"

 

[jdraw]

And what was the result?
Did you use the set up shown by June?

=DCount("*", "tAccLog", "tSpecCode=22 AND [your date field] BETWEEN " & Nz([vDateSt],#1/1/1900#) & " AND " & Nz([vDateEnd],#12/31/3100#) & " AND tPathologist=4")

 

[kengooch]

And what was the result?
Did you use the set up shown by June?

=DCount("*", "tAccLog", "tSpecCode=22 AND [your date field] BETWEEN " & Nz([vDateSt],#1/1/1900#) & " AND " & Nz([vDateEnd],#12/31/3100#) & " AND tPathologist=4")

Well I put this equation in and adjusted it to use tDateAcc and the form opens and flashes the field on and off really fast and shows a #Error in the field. I just got back to my desk again, so I am going to look more closely at the equation and make sure I have it correct.

 

[June7]

What did you use in place of [your date field]?

 

[June7]

Why would a "t" prefix get in the way of intellisense? It's just another character in field name.

 

[kengooch]

What did you use in place of [your date field]?

tDateAcc from the parent table tAccLog

 

[jdraw]

Ken,
What is latest status?

 

[kengooch]

Ken,
What is latest status?

I have still not gotten it to work.

 

[June7]

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