Full Version: Selecting a min date from several fields
UtterAccess Forums > Microsoft® Access > Access Date + Time
chivveySoft
H'lo all,
Trying to create a module that'll pick the earliest date from many date fields, but I can't seem to find the right method. I'm opening an ADODB recordset and then using "Select Case" to compare each field to the next (ex. c_Date1 < c_Date2 And c_Date1 < c_Date3 And ... etc.) but this doesn't seem to evaulate correctly. Also, since there are nulls in several fields I'm using Nz([DateField1],Date()) in the select statement that opens the recordset so that a null is converted to a date well beyond a typical date in my data. Code snipet below:
c_Date1 = rsDateCrits![FIRST ORDER DATE]
c_Date2 = rsDateCrits![FIRST SHIP DATE]
c_Date3 = rsDateCrits![FIRST ARRIVAL DATE]
c_Date4 = rsDateCrits![FIRST PERFORMANCE DATE]
c_Date5 = rsDateCrits![LUMP_SUM_START_DT]
c_Date6 = rsDateCrits![UWG_START_DT]
c_Date7 = rsDateCrits![CUSTOMER_START_DT]
c_Date8 = rsDateCrits![EARLY PAY BEGIN]

Select Case c_MinDate
'use [FIRST ORDER DATE] and [LAST ...]
Case c_Date1 < c_Date2 And c_Date1 < c_Date3 And c_Date1 < c_Date4 And c_Date1 < c_Date5 And _
c_Date1 < c_Date6 And c_Date1 < c_Date7 And c_Date1 < c_Date8
MinDate = c_Date1
' MaxDate = rsDateCrits![LAST ORDER DATE]
Debug.Print vbTab & MinDate
'Debug.Print vbTab & MaxDate
'use [FIRST SHIP DATE] and [LAST ...]
Case c_Date2 < c_Date1 And c_Date2 < c_Date3 And c_Date2 < c_Date4 And c_Date2 < c_Date5 And _
c_Date2 < c_Date6 And c_Date2 < c_Date7 And c_Date2 < c_Date8
MinDate = c_Date2
R. Hicks
Welcome to Utter Access FOrums ... frown.gif
You stucture is not properly normalized ...
You have what is known a "repeating group" ...
This is the reason you are having your current problem.
RDH
chivveySoft
Thanks for the prompt reply. This term, "repeating group" is new to me (self taught coder, if that's not painfully obvious). Should I understand it as improper (re)use of a set of variables? Or, does the problematic repetition occur in the structure of the "Select Case" statement? Is there a more efficient method of evaluating several date fields against each other. This is actuall a scaled down version, the logic will be much more complex once the earliest date is found, so I really need a concise method for finding that date.
Thanks again.
Josh
This is a "lo-fi" version of UA. To view the full version with more information, formatting and images, please click here.