Help with a DCount question (1 Viewer)

[gojets1721]

See the attached DB. I have really specific DCount question.

In the complaintfollowup field, users input dated notes and each note is separated by a line break. I'm trying to figure out a way to capture the number of records via DCount where the very last dated note in any given record is older than 30 days and the complaint status is 'open'. In my example, it would show one record.

Any suggestions?

 

[Josef P.]

A lack of normalization makes itself felt at some point. Now the time has come.
Imagine you had a separate record for each date value and the respective note in that record. How simple the required evaluation would be.

 

[plog]

Agree with Josef. You don't have a database, you have a spreadsheet with a form.

When you feel the need to jam a bunch of information into one field, it's time for a new table. You are doing this 2 ways in ComplaintFollowup. 1. Each line represents a new piece of data. 2. Each line itself contains 3 pieces of data (date, note & author). You need a ComplaintFollowup table. It would contain 5 fields and look like this:

tblFollowups
fu_ID, autonumber, primary key
ID_Complaint, number, foreign key back to tblComplaints.ID
fu_Date, date, date of followup
fu_Note, short text, note of complaint
fu_Author, short text, name of person writing follow up

You do that and what you want to find out becomes trivial.

 

[gojets1721]

Gotcha. I was dreading having to do this but you’re certainly right. I appreciate the help!

 

[Josef P.]

The alternative an evaluation with a calendar table and the hope that the date values were always entered in the same format is out of proportion to the clean solution by normalization.

 

[gojets1721]

The issue I've always had is there's thousands of complaints going back years. So it'd be quite a process to convert everything over to a separate table.

 

[Josef P.]

So it'd be quite a process to convert everything over to a separate table.

Why?
Work through the text via VBA and save it in the new table.
Maybe split the date blocks with RegEx.