X   Site Message
(Message will auto close in 2 seconds)

Welcome to UtterAccess! Please ( Login   or   Register )

Custom Search
   Reply to this topicStart new topic
> Unable To Figure Out How To Return The Next Date From Today Pulling From Multiple Fields, Access 2016    
post May 21 2019, 05:01 PM

Posts: 8
Joined: 21-May 19

I'm having an issue that I swear would have been simple to implement but the programming logic is not clicking for me.

I have searched google to no avail and also used the search box in this forum.

I'm working off of an existing database that I did not program, and so I know it's sort of awkward how the problem presents itself.

I have a single, large table that has data that needs to be returned in a report.

each record has a car VIN number, and a series of dates for maintenance all in a row. What I'm trying to do is print a report that shows a list of all the VINS, and the next upcoming maintenance date from today.

I was going to attempt doing a today() function minus a year or some other set interval, but the maintenance periods vary, could be 6 months, could be a year. These are all entered by the user into fields all in a row up to 12 deep.

I would gladly appreciate any help you could offer me. I have toyed with having a hidden box that does a calcuation on form update that looks at all the fields with data (not all 12 are filled out at any given time) and fills itself in with the next date, then just have the report pull that, but I'm not a programmer by trade and so I'm at a dead stop.

Thank you so much!
Go to the top of the page
post May 21 2019, 05:13 PM

UA Admin
Posts: 35,135
Joined: 20-June 02
From: Newcastle, WA

Welcome to UtterAccess.

"...a series of dates for maintenance all in a row.""
You mean like in a spreadsheet, across the page?

If so, that's one problem you might want to try to correct. How much control do you have? Can you alter the tables to make them more appropriate for a Relational Database? If so, we ought to make that the first priority.

You also need a way to record the maintenance intervals. This statement does sound like it would be the relevant info, but maybe it's also not designed correctly. "...these are all entered by the user into fields all in a row up to 12 deep."

If we can see the tables, we'll have a better idea of how to help.

My Real Name Is George. Grover Park Consulting is where I do business.
How to Ask a Good Question
Beginning SQL Server
Go to the top of the page
post May 21 2019, 06:54 PM

Posts: 8
Joined: 21-May 19

Thank you for the quick response! I do appreciate your assistance greatly.

Yes this particular table is basically a giant spreadsheet.

I do have the ability to alter the tables, however none of the database is normalized and there is already a metric TON of data in the tables. At this point I really don't have the time to redo it all.

More information:

There is a form that has entry fields in this style:

VIN: Enter VIN Here

Maintenance 1 - Maintenance 2 - Maintenance 3 -> etc to 12
Enter date here - Enter date here - Enter date here -> etc to 12


They then go into a table like this

VIN - Mdate1 - Mdate2 - Mdate3 -> etc to 12

I'm thinking the way to do it is to have some function read all 12 of the Maintenance Dates on the form (on update or after update on the form properties maybe?) compare it to Today() and then take the one that is the smallest but greater than 0. I'm not sure if that function even exists though. All date manipulation functions dont' look like they can handle that.
Go to the top of the page
post May 22 2019, 02:17 PM

Access Wiki and Forums Moderator
Posts: 75,525
Joined: 19-June 07
From: SunnySandyEggo

Hi. We do have a function in the Wiki that might be able to help with this. Check out GetMinArrayValue()

Just my 2 cents... "And if I claim to be a wise man, it surely means that I don't know" - Kansas
Access Website | Access Blog | Email
Go to the top of the page

Custom Search

RSSSearch   Top   Lo-Fi    18th June 2019 - 02:17 PM