×
INTELLIGENT WORK FORUMS
FOR COMPUTER PROFESSIONALS

Log In

Come Join Us!

Are you a
Computer / IT professional?
Join Tek-Tips Forums!
  • Talk With Other Members
  • Be Notified Of Responses
    To Your Posts
  • Keyword Search
  • One-Click Access To Your
    Favorite Forums
  • Automated Signatures
    On Your Posts
  • Best Of All, It's Free!
  • Students Click Here

*Tek-Tips's functionality depends on members receiving e-mail. By joining you are opting in to receive e-mail.

Posting Guidelines

Promoting, selling, recruiting, coursework and thesis posting is forbidden.

Students Click Here

Jobs

thread701-1400589 I added the Ge

thread701-1400589 I added the Ge

thread701-1400589 I added the Ge

(OP)
thread701-1400589: Max Date from Multiple Fields

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.

RE: thread701-1400589 I added the Ge

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

RE: thread701-1400589 I added the Ge

(OP)
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?

RE: thread701-1400589 I added the Ge

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 GetMaxDate 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:

Select MAX(Date_Field)
From tblMyTable
Where something


---- Andy

There is a great need for a sarcasm font.

RE: thread701-1400589 I added the Ge

(OP)
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.

RE: thread701-1400589 I added the Ge

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.

Quote (sgtJarrow)

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:

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 


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

RE: thread701-1400589 I added the Ge

(OP)
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?

RE: thread701-1400589 I added the Ge

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:

CODE --> qcarCasesActions

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

RE: thread701-1400589 I added the Ge

(OP)
I'll try this tonight. Thank you!

RE: thread701-1400589 I added the Ge

Quote (ErinRaquel )

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 wavey3


---- Andy

There is a great need for a sarcasm font.

RE: thread701-1400589 I added the Ge

(OP)
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.

RE: thread701-1400589 I added the Ge

I can see a 'base' table, something like:

ID  Description
1   Start the Case
2   Gather info
3   Talk to Client
...
12  Talk about the Case
13  Have a Pizza
 
Also, Case table:
ID   CaseDescription  ClosedOn
15   Build Something
27   Get a Dog
 
So every time you need a Case, those records will be added to another table:

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
 
You can see that Build Something 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.

Get a Dog 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... smile


---- Andy

There is a great need for a sarcasm font.

RE: thread701-1400589 I added the Ge

(OP)
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!

Red Flag This Post

Please let us know here why this post is inappropriate. Reasons such as off-topic, duplicates, flames, illegal, vulgar, or students posting their homework.

Red Flag Submitted

Thank you for helping keep Tek-Tips Forums free from inappropriate posts.
The Tek-Tips staff will check this out and take appropriate action.

Reply To This Thread

Posting in the Tek-Tips forums is a member-only feature.

Click Here to join Tek-Tips and talk with other members! Already a Member? Login

Close Box

Join Tek-Tips® Today!

Join your peers on the Internet's largest technical computer professional community.
It's easy to join and it's free.

Here's Why Members Love Tek-Tips Forums:

Register now while it's still free!

Already a member? Close this window and log in.

Join Us             Close