thread701-1400589 I added the Ge

[ErinRaquel]

thread701-1400589

I added the GetMaxDate to my Query in the Field box, but am getting error "The expression you entered has a function containing the wrong number of arguments."

I've tried both of these:
MaxDate: GetMaxDate([NOIDate] ,[GarrityAdmDate] ,[OrderForIntDate] )

MaxDate: GetMaxDate( [tblCases]![NOIDate] , [tblCases]![GarrityAdmDate] , [tblCases]![OrderForIntDate] )

I am using Access 2016. Did the expression format change? Thanks for any help.

 

[dhookom]

Can you share the code you used?
Did you save the function in a standard module?
Did you name the module something like "modDateFunctions"?
Do you have those fields in your tables in your query?

Duane
Minnesota
Hook'D on Access
MS Access MVP 2001-2016

 

[ErinRaquel]

I followed all of the instructions in the thread I attached.
Standard module named: basGetMaxDate
Yes the fields exist in the table in my query.

UPDATE: I'VE GOT IT WORKING! NOW, IS IT POSSIBLE TO HAVE THE FIELD LABEL APPEAR, FOR EACH RECORD, SO WE KNOW WHICH DATE FIELD IS THE MAXDATE?

 

[Andrzejek]

The thread (you had mentioned) does not really have anything to do with the data base, as far as I can see. You just have a Function [tt]GetMaxDate[/tt] where you pass 3 dates and this function returns the max date out of the 3 provided.

Getting the max date - out of your table - based on some criteria requires a different approach, IMO. Something like:
[tt]
Select MAX(Date_Field)
From tblMyTable
Where something[/tt]


---- Andy

There is a great need for a sarcasm font.

 

[ErinRaquel]

I have this Module in Access named basGetMaxDate:

Public Function GetMaxDate(dteNOIDate As Date, dteGarrityAdmDate As Date, dteOrderForIntDate As Date) As Date

Dim dteMaxDate As Date

dteMaxDate = dteNOIDate
If dteGarrityAdmDate > dteMaxDate Then dteMaxDate = dteGarrityAdmDate
If dteOrderForIntDate > dteMaxDate Then dteMaxDate = dteOrderForIntDate

GetMaxDate = dteMaxDate

End Function

My query calls that Module with the following:
SELECT GetMaxDate(Nz([NOIDate],0),Nz([GarrityAdmDate],0),Nz([OrderForIntDate],0)) AS MaxDate
FROM tblCases;

It works returning the MaxDate. Problem is, I don't know what field is the MaxDate. I hoped the Code/SQL statement could be tweaked to include the field label with the MaxDate value. The goal is to build a report showing our Professional Standards Unit when and what the last action was with each IA Case.

Thank you for your time. I just joined the Forum. I apologize for any info I am failing to mention.

 

[dhookom]

Your issue is addressed in the thread you posted. Having multiple date fields rather than multiple related records is typically not a good design and leads to issues like this.

Final Final note is that if you are storing dates such as this, you may not be using the best method to store your data - i.e. not fully normalized - depending on the data you are storing. You may want to search this site for some posts on normalization and see if thy might be able to help you out.

I recommend creating a CaseAction table with fields like:

[pre]tblCaseActions
CaseActID
CaseNum (the primary key value from tblCases)
ActionID (the primary key from an action table that contains records for NOI, Garrity Admin, Order For Int, etc)
ActionDate[/pre]

Then finding the max and the most recent action is simple.

BTW: Welcome to Tek-Tips. I hope we aren't too judgmental. We can get a little OCD when it comes to table structures.

Duane
Minnesota
Hook'D on Access
MS Access MVP 2001-2016

 

[ErinRaquel]

I thought to do that, but every case requires a date be inputted for every Case Action until the investigation is complete. Knowing our users, they may skip a step in the process or choose the incorrect Action for a date. We have 13 required dates in the process. Your thoughts?

 

[dhookom]

Hi Erin,

My thoughts are to set up the tables as I suggested and make sure you have a UNIQUE index on the CaseNum and ActionID fields in the tblCaseActions table so you don't get duplicates. You can then run an append query to append every action for the "current" case. This append query will create 13 records in the table.

For the append query you can create a cartesian query like:

SELECT C.CaseNum, A.ActionID
FROM tblCases as C, tblActions As A

This should result in the number of cases times the number of actions. Use this new query LEFT JOINed to tblCaseActions on CaseNum and ActionID in an APPEND query. Display all records from the cartesian query and set a criteria under the tblCaseActions.CaseNum field to Is Null. This will avoid append records already in tblCaseActions.

Run this append query whenever a new Case is added. You can add more field to tblCaseActions to track who updated and comments.

Duane
Minnesota
Hook'D on Access
MS Access MVP 2001-2016

 

[ErinRaquel]

I'll try this tonight. Thank you!

 

[Andrzejek]

Knowing our users, they may skip a step in the process or choose the incorrect Action for a date

That is where your application comes and requires all dates to be filled in, otherwise you cannot close the case (if that's the rule). Another rule may be (just guessing here) Actions have to be done in certain order, so that's how you can control the dates. Date for Action 7 cannot be before the date for Action 2 (for example)

Welcome to TT


---- Andy

There is a great need for a sarcasm font.

 

[ErinRaquel]

Thank you Andy. What method would you use to require fields? The 13 "action dates" are all required fields, and your correct that date13 cannot be before date12, date12 cannot be before date11, and so forth... But I can't imagine the field properties being the best place to require input or they will not be able to save/close the form. AND....is field requirement possible with Duane's suggested method using append/cartesian query? I liked the sounds of it to show incomplete "Case Actions" at-a-glance.

 

[Andrzejek]

I can see a 'base' table, something like:
[pre]
ID Description
1 Start the Case
2 Gather info
3 Talk to Client
...
12 Talk about the Case
13 Have a Pizza
[/pre]
Also, Case table:
[pre]ID CaseDescription ClosedOn
15 Build Something
27 Get a Dog
[/pre]
So every time you need a Case, those records will be added to another table:
[pre]
ID CaseNo DateID CaseDate ProvidedBy ...
123 15 1 1/1/2019 Tom
124 15 2 2/2/2019 Susie
125 15 3
...
135 15 13
136 27 1
137 27 2
...
147 27 13
[/pre]
You can see that [tt]Build Something[/tt] case started by Tom on 1/1/2019 (Start the Case), Susie did the step 2 (Gather info) on 2/2/2019, and the rest of this case is still on-going. Whoever will do step 3 (Talk to Client) will be required to enter the date, but that date needs to be equal to or after 2/2/2019 - that's your application's job.

[tt]Get a Dog[/tt] case has been established, but nobody did anything about it yet.

And you cannot Close the Case unless all dates are filled in.

Just a suggestion...


---- Andy

There is a great need for a sarcasm font.

 

[ErinRaquel]

I understand now. Thank you for the examples. I did not think to append the 13 records to another table (or did I know how to) until Duane's and your suggestions on this type of design. It sounds exactly like what I need to do. Thank goodness for this forum! And thank you both for your time explaining!