Select date from multiple dates

[The Bey]

I have a form which outputs multiple date values, dependent on previous criteria.

Is there a way that I can use a textbox to show the date of whichever of these dates is closest to the present day?

i.e. I have 4 due dates (6/30/2012, 6/30/2013, 6/30/2014, 6/30/2015) and I want the text box to look at these 4 boxes and show the soonest (6/30/2012)

Would Switch statements work here and if so, how would I code it?

 

[MStef]

Send a short example of your MDB, (access 2000 or 2002-2003).

 

[MarkK]

What I expect you need to do is write a loop that checks each date to see if the current comparison is more favourable than the last. If so, retain the most favourable date and check the next one.
I think the Switch() function is little too 'hard coded' of a solution for what you want to do.
But how are these dates stored? Are there always four?
Cheers,
Mark

 

[The Bey]

Yes there will always be 4 and they're not stored as such, just the result of a bunch of If statements output in text boxes.

I was thinking a loop would be effective but I don't know how to use them or code them. The code I'm using is along the lines of:

If Me.txtSInsRef = "Strip insulation and inspect" And Me.txtHC = "Yes" Then
Me.txtInsDate = DateAdd("m", 6, Date)
End If

If Me.txtSInsRef = "Repair or replace insulation" And Me.txtHC = "Yes" Then
Me.txtInsDate = DateAdd("m", 18, Date)
End If

If Me.txtSInsRef = "No Action" And Me.txtHC = "Yes" Then
Me.txtInsDate = DateAdd("m", 36, Date)
End If

I know this is long winded but the results will still be the same. How can I get something to pass through all these boxes and decide which is soonest?

 

[The Bey]

Sorry, this only shows one of the sets of answers for one of the boxes; there are another 7 sets of these.

I don't want the loop to look at the code though, just the answer that the code produces.

Could I do a similar job with If's using the max/min function?

 

[MarkK]

"Strip insulation and inspect" should be in a table, not in code. If that event should always happen at 6 month intervals from the current day then that info goes in the same record.
You store the data in a table, and store the process as an abstraction in code. Then your code can process many different subsets of your data very simply.
Cheers,

 

[The Bey]

I don't understand this "abstraction in code" so could you explain what you mean please?

And it doesn't always happen at the same interval, hence why I'm getting it to run through 3 different statements to question which one fits it. Can you not explain your previous code for the loop or do I need to tabulate it? Because that makes things so much tougher for me.

Can you tell me how it could do it simpler if I do it through tables and subsets also?

 

[MarkK]

The code you posted ...

If Me.txtSInsRef = "Strip insulation and inspect" And Me.txtHC = "Yes" Then
  Me.txtInsDate = DateAdd("m", 6, Date)
ElseIf Me.txtSInsRef = "Repair or replace insulation" And Me.txtHC = "Yes" Then
  Me.txtInsDate = DateAdd("m", 18, Date)
ElseIf Me.txtSInsRef = "No Action" And Me.txtHC = "Yes" Then
  Me.txtInsDate = DateAdd("m", 36, Date)
End If

...is a table. You have a linear relationship between an InsRef and how long the InsDate is away from today. Here's the structure of the table...

tYourData
YourDataID (PK)
InsRef
InsDateMonths
HC

... and data is ...

YourDataID/InsRef/InsDateMonths/HC
1/Strip insulation and inspect/6/Yes
2/Repair or replace insulation/18/Yes
3/No Action/36/Yes

So to find InsDate you run a query on the table ...

SELECT InsDateMonths 
FROM tYourData
WHERE InsRef = [txtSInsRef]
  AND HC = Yes

Can you see how one approach is 'abstract' as opposed to the other approach which we might call 'hard-coded?' See how the abstract approach is more flexible? Don't put data in your code. Data goes in tables. Data in table is easily manipulated using code.
Is this making sense? What you want is to design a process that doesn't break even if the data changes.
Let me know,
Mark

 

[The Bey]

I've been tryin for the past hour and a half to do that and because of the work I've done earlier, it's making it incredibly difficult to do what seems like a simple task.

Earlier in my DB I couldn't work out how to do coding in one text box, so I instead created 2 text boxes with seperate code and when certain criteria was met, the appropriate box became visible. I've been trying to go back and change this code so that I can put them into one box, but for some reason it's not giving me the correct answers that i'm lookin for.

I agree with my previous code being "hard-coded" and there isn't room for error, but the fact is that that data will not be changed so the error isn't really a problem.

I've managed to get the dates into 4 boxes, rather than 8 (same problem as earlier, but improved) and it would be so much simpler if I could run fresh code on the values in these text boxes rather than on queries and tables, as the code seems to be working better for me... Any thoughts?

 

[MarkK]

What are you trying to do? What's the overview of the process? Are you tracking service calls? What are the basic rules of your system?
Do you keep track of drivers or installers, materials? Maybe we need to back and go from the big picture down to the small in terms of what you are doing.

 

[The Bey]

The database is to categorise various lines on the refinery I work at. The idea is to run a series of questions on the line in question and the answers output an action to be done. There are 4 different categories that can have actions on (insulation, tracing, metallurgy transition and material) and depending on which action is given to each, a date is output (this is the code which I posted previously).

There will always be 4 dates output because regardless of what the outcome is, there will be an action for each of the categories.

What I'm looking to do is take whichever date comes soonest and use that date as the most urgent date, hence why I need to pass the query/code through all four dates. At the moment I'm having a lot of trouble with this, and it seems it's because I don't have tables for each.

Hope this is helping and I appreciate your repsonses very much

 

[MarkK]

Here's code that, if the dates are in an array, can find the nearest to today...

Sub TestFunction()
   Dim var
   var = Array(#1/1/1980#, #12/31/2010#, #7/7/2011#, #7/7/2012#)
   MsgBox FindClosestDateToToday(var)
End Sub

Function FindClosestDateToToday(dates As Variant) As Date
   Dim var
   Dim dFinal As Date
   Dim diff As Long
   
   diff = 2147483647 'maximum long integer
   
[COLOR="Green"]   'traverse array
[/COLOR]   For Each var In dates
[COLOR="Green"]      'check if difference is less than previous least[/COLOR]
      If Abs(Date - var) < diff Then
[COLOR="Green"]         'if so, update our best diff...[/COLOR]
         diff = Abs(Date - var)
[COLOR="Green"]         'and retain this closest date[/COLOR]
         dFinal = var
      End If
   Next
   
   FindClosestDateToToday = dFinal

End Function

If those four dates were in the same column in a table, you could use SQL like ...

SELECT SomeDate
FROM tDateTest
ORDER BY abs(Date() - SomeDate)

...to open a recordset and read the first field in the first record.
Is that useful?
Mark

 

[The Bey]

That looks good. Can I use the dates in my textboxes as a superficial array? Or do I need to formally organize it?

I also don't understand all of the code but I've seen similar sequences elsewhere. With this section "var = Array(#1/1/1980#, #12/31/2010#, #7/7/2011#, #7/7/2012#)" have you just picked random dates? If I wanted to use my dates, how would I put these in their place?

Where's the best place to go to learn about VBA and Access? I think I need a more thorough knowledge to start to understand this...

 

[The Bey]

Solved this one if anybody's interested; used this code:

If Me.txtMatDate < Me.txtInsDate Then
Me.txtNewDate = Me.txtMatDate
ElseIf Me.txtMatDate < Me.txtTraceDate Then
Me.txtNewDate = Me.txtMatDate
ElseIf Me.txtMatDate < Me.txtTraceDate Then
Me.txtNewDate = Me.txtMatDate
ElseIf Me.txtInsDate < Me.txtTransDate Then
Me.txtNewDate = Me.txtInsDate
ElseIf Me.txtInsDate < Me.txtTraceDate Then
Me.txtNewDate = Me.txtInsDate
ElseIf Me.txtTransDate < Me.txtTraceDate Then
Me.txtNewDate = Me.txtTransDate
Else
Me.txtNewDate = Me.txtTraceDate
End If

I literally compared every result. Quick and easy solve once I'd been pointed in the right direction. Saves on tricky coding