Question regarding updating dates in specific records within a field (1 Viewer)

[dhop1990]

So I currently have a table that tracks medication activity for patients called tblMedRanges. The Fields are the ID, StartMedication, EndMedication, Med ID/Medication Name.

In the End of Medication, some patients are not currently active so they have a set date/time field property. What I have for right now is the default value is set to

=date()

so that when staff adds a new medication it is set to the current date. These patients are supposed to be active, but it doesn't update the default value. I want it to continue to update to the current date every time the database is open.

I have three options that I know of(or think I know of) to update but can't commit to one in particular :

1)I want these individual records in the field to update the current time for today and was considering using some kind of data macro code using before update. Any suggestions.

2)The other idea I had was splitting up the field into an EndOfMedication field that would display a value only if their inactive, and an Active field (text, value list active/inactive choices) that would be used in a query to generate the current date as med field. This would take some crafty query work with comparisons later on to generate list of drugs that they were on for each visit.

3) set to text field and convert the field to date field for comparison in queries.

I'm a little lost but feel close to a good answer. Any help or suggestions would be great.

PS Sorry for the bulky text.

 

[Cronk]

You could add to your table an Active flag, viz
ID Autonumber
StartMeds Date
EndMeds Date
MedsName Text (good practice not to use spaces and special characters in field names)
Active Yes/No Default set to Active
DeactivateDate Date (optional)

When you determine the record is to be made Unactive, uncheck the Active box

Your daily update query, updates the field to date() for only Active records

 

[dhop1990]

Thanks, that should work out perfectly fine for what I'm doing. Great advice