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.

 

[Pat Hartman]

This is a spreadsheet like solution and that is why you are having such a problem. Instead of unbound controls with hardcoded domain functions, use a subform bound to a query.

Select tblAccLog.tSpecCode, tblSpecCode.SpecCodeDesc, Count(*) As RecCount
From tblAccLog Inner join tblSpecCode On tblAccLog.tSpecCode = tblSpecCode.tSpecCode

That is the simple query I can't figure out your naming scheme so I winged it. The join is to the "lookup" table that has the text values for the code. If you don't have such a table, you need one.

Then all you need to do is to add a where clause to the query and update the RecordSource of the subform. So, you can have an option group with three summarization options. The run button uses the option group to decide which query to use. You can make three queries and save them as querydefs. OR you can save just the summary version and read its SQL string and concatenate the WHERE clause. We can help you with that part if you are willing to give up your spreadsheet.

Your "spreadsheet" solution exemplifies the problems with spreadsheets. You need to modify the calculation three ways for every single "cell". And if you add any additional codes, you need to redesign the entire form and all the code to accommodate the new code. when you use queries and bound forms (the relational database solution), the only thing you need to do to add a new code is to add a row to the SpecCode lookup table to define its name. Then EVERYthing just works

 

[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]?

 

[Pat Hartman]

The two tables are linked by the tAccNoLink field.

If the field doesn't exist in both tables, then you can't link by it. The names don't have to match although it sure is easier if the FK name is the same as the PK it points to. But without a common name, how do we know what any particular FK points to?

The second table doesn't have a PK. It should. If there is no natural unique key, then use an autonumber as the PK

I can't figure out why you have prefixed everything with a "t". Column names should not have prefixes. They just get in the way of intellisense.

Did you try making the query I suggested?

 

[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?

 

[Pat Hartman]

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

In this case it is only one letter. Others use 3+ letters. They interfere in two ways, three for me.
1. you have to type them all before you get to anything meaningful from intellisense
2. When you open a query or table in ds view, they take up room that is better used for meaningful characters.
3. Most people use a prefix that is different for each table (until they run out of meaningful characters and then they get sloppy). That interferes with my documentation tool since I can no longer see all the "city" fields together or the CustID's, etc. The reports I generate help to ensure that like data is defined the same way in each table.

Using a suffix, if they give you comfort, doesn't have the same downsides.

 

[kengooch]

Ken,
What is latest status?

I have still not gotten it to work.

 

[Pat Hartman]

What did you try? Did you consider normalizing your schema and trying the query I suggested?

 

[June7]

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