Comparing dates with Select Case + Nulls

[Xproterg]

Private Sub Form_Current()
 
Dim todaydate As Date
 
Dim masl1 As Date
Dim masl2 As Date
Dim masl3 As Date
 
todaydate = Date
 
masl1 = gradONE.Value
masl2 = gradTWO.Value
masl3 = gradTHR.Value

If IsEmpty(masl1) Then masl1 = todaydate - 100 Else todaydate = Date
If IsEmpty(masl2) Then masl2 = todaydate - 100 Else todaydate = Date
If IsEmpty(masl3) Then masl3 = todaydate - 100 Else todaydate = Date
Select Case todaydate
    Case Is < masl1
        Text70.Value = DLookup("COURSE_NAME", "Courses_Info", "MASL = maslONEtxt")
        Text72.Value = DLookup("COURSE_ID", "Courses_Info", "MASL = maslONEtxt")
        Text74.Value = DLookup("SQUADRON", "Courses_Info", "MASL = maslONEtxt")
        Text76.Value = DLookup("BUILDING", "Courses_Info", "MASL = maslONEtxt")
        Text78.Value = DLookup("PH_EXT", "Courses_Info", "MASL = maslONEtxt")
        Text80.Value = DLookup("RM", "Courses_Info", "MASL = maslONEtxt")
        Text84.Value = DLookup("SHIFT", "Courses_Info", "MASL = maslONEtxt")
        Text91.Value = DLookup("TIME", "Courses_Info", "MASL = maslONEtxt")
    
    Case Is < masl2
        Text70.Value = DLookup("COURSE_NAME", "Courses_Info", "MASL = maslTWOtxt")
        Text72.Value = DLookup("COURSE_ID", "Courses_Info", "MASL = maslTWOtxt")
        Text74.Value = DLookup("SQUADRON", "Courses_Info", "MASL = maslTWOtxt")
        Text76.Value = DLookup("BUILDING", "Courses_Info", "MASL = maslTWOtxt")
        Text78.Value = DLookup("PH_EXT", "Courses_Info", "MASL = maslTWOtxt")
        Text80.Value = DLookup("RM", "Courses_Info", "MASL = maslTWOtxt")
        Text84.Value = DLookup("SHIFT", "Courses_Info", "MASL = maslTWOtxt")
        Text91.Value = DLookup("TIME", "Courses_Info", "MASL = maslTWOtxt")
        
    Case Is < masl3
        Text70.Value = DLookup("COURSE_NAME", "Courses_Info", "MASL = maslTHRtxt")
        Text72.Value = DLookup("COURSE_ID", "Courses_Info", "MASL = maslTHRtxt")
        Text74.Value = DLookup("SQUADRON", "Courses_Info", "MASL = maslTHRtxt")
        Text76.Value = DLookup("BUILDING", "Courses_Info", "MASL = maslTHRtxt")
        Text78.Value = DLookup("PH_EXT", "Courses_Info", "MASL = maslTHRtxt")
        Text80.Value = DLookup("RM", "Courses_Info", "MASL = maslTHRtxt")
        Text84.Value = DLookup("SHIFT", "Courses_Info", "MASL = maslTHRtxt")
        Text91.Value = DLookup("TIME", "Courses_Info", "MASL = maslTHRtxt")
    
    Case Else
        Text70.Value = "Unknown"
        Text72.Value = "Unknown"
        Text74.Value = "Unknown"
        Text76.Value = "Unknown"
        Text78.Value = "Unknown"
        Text80.Value = "Unknown"
        Text84.Value = "Unknown"
        Text91.Value = "Unknown"
        
End Select
        
End Sub

I'm using the code above to attempt to populate unbound fields based on three date boxes. It populates those fields based on which of the three dates is closest to today's date (except those that have passed).

The issue with the code, which is the issue I've had with vba and access time and time again, is that i cannot seem to populate nulled variables so that they meet the requirements of the "Case Else." In fact, the code doesn't want to populate the variables if the date fields are empty/null at all. Any expertise would be of great benefit to me. I can't wait to learn how to deal with these things in all cases.

Thank you in advance for your help.

 

[boblarson]

A couple of things:

1. Use a VARIANT datatype instead of DATE. It can handle nulls.

2. Don't use DLookups the way you are doing. Use a recordset object instead. It is more efficient and, depending on the number of records, will be faster.

 

[Xproterg]

Can you show me an example of how i would use a record set for that?


The reason I used the dlookup was to populate that set of textboxes in the form_current event. So when a user navigates to the next or previous record, information is determined in real-time.

 

[boblarson]

Looks like you didn't set up the tables in a normalized fashion too. With the proper setup it would be a bit easier.

 

[MarkK]

What I would do is name the textboxes the same as the fields in the table. Then you can open a recordset and assign values as follows ...

  dim rst as dao.recordset
  dim fld as dao.field

[COLOR="Green"]  'open the recordset[/COLOR]
  set rst = currentdb.openrecordset( _
    "SELECT Course_Name, Course_ID, Squadron, Building, PH_Ext, RM, [Shift], [Time] " & _
    "FROM Courses_Info " & _
    "WHERE MASL = " & me.maslONEtxt)
[COLOR="Green"]  'traverse the fields collection[/COLOR]
  for each fld in rst.fields
[COLOR="Green"]    'for each field find the same-named control and assign the value from the rst[/COLOR]
    me.controls(fld.name).value = fld.value
  next
  rst.close

All you need to do in each select case option is define the where clause, since that's the only part that differs, and pass it to a subroutine like the one above.
Cheers,
Mark