Access: Code for adding a date is not working

[gheyman]

I have a combo box on a form. When the user changes selection I want a date to populate another field on the same form as the combo box. this code is not working. Nothing happens when I change the combo selection

Private Sub Combo4_Change()

If TextQuoteExpDate01 = "All Quotes" Then
TextQuoteExpDate01 = "*"
Else

If TextQuoteExpDate01 = "Only Valid Quote" Then
TextQuoteExpDate01 = Now()
Else

If TextQuoteExpDate01 = "Current and Expired within 30 Days" Then
TextQuoteExpDate01 = Now() - 29
Else

If TextQuoteExpDate01 = "Current and Expired within 60 Days" Then
TextQuoteExpDate01 = Now() - 59
Else

If TextQuoteExpDate01 = "Current and Expired within 90 Days" Then
TextQuoteExpDate01 = Now() - 89
Else

If TextQuoteExpDate01 = "Current and Expired within 180 Days" Then
TextQuoteExpDate01 = Now() - 179
Else

If TextQuoteExpDate01 = "Current and Expired within 360 Days" Then
TextQuoteExpDate01 = Now() - 359
Else


End If
End If
End If
End If
End If
End If
End If

End Sub

Help. Please!

 

[gheyman]

Option Compare Database

Private Sub Combo4_Change()

If ComboQuoteValidity01 = "All Quotes" Then
TextQuoteExpDate01 = "*"
Else

If ComboQuoteValidity01 = "Only Valid Quote" Then
TextQuoteExpDate01 = Now() - 1
Else

If ComboQuoteValidity01 = "Current and Expired within 30 Days" Then
TextQuoteExpDate01 = Now() - 29
Else

If ComboQuoteValidity01 = "Current and Expired within 60 Days" Then
TextQuoteExpDate01 = Now() - 59
Else

If ComboQuoteValidity01 = "Current and Expired within 90 Days" Then
TextQuoteExpDate01 = Now() - 89
Else

If ComboQuoteValidity01 = "Current and Expired within 180 Days" Then
TextQuoteExpDate01 = Now() - 179
Else

If ComboQuoteValidity01 = "Current and Expired within 360 Days" Then
TextQuoteExpDate01 = Now() - 359
Else


End If
End If
End If
End If
End If
End If
End If

End Sub

But still not working. uhg!

 

[Joe4]

A few things:
- When checking one field for so many different values, use a Case statement instead of a bunch of IFs - much cleaner, and easier to read (see: https://www.techonthenet.com/access/functions/advanced/case.php).
- Note that "Now()" returns current date AND time. "Date" will return just the current date.
- When referencing fields on the Form, preface all of them with "Me.".

See if this works for you:

Option Compare Database

Private Sub Combo4_Change()

    Select Case Me.ComboQuoteValidity01
        Case "All Quotes"
            Me.TextQuoteExpDate01 = "*"
        Case "Only Valid Quote"
            Me.TextQuoteExpDate01 = Date - 1
        Case "Current and Expired within 30 Days"
            Me.TextQuoteExpDate01 = Date - 29
        Case "Current and Expired within 60 Days"
            Me.TextQuoteExpDate01 = Date - 59
        Case "Current and Expired within 90 Days"
            Me.TextQuoteExpDate01 = Date - 89
        Case "Current and Expired within 180 Days"
            Me.TextQuoteExpDate01 = Date - 179
        Case "Current and Expired within 360 Days"
            Me.TextQuoteExpDate01 = Date - 359
        Case Else
            '
    End Select

End Sub

 

[gheyman]

Once again, Thank You!

 

[Joe4]

You are welcome.