Unbound control causing grief when executing query

[maff811]

Hi,

Hoping someone can assist with this one that has been bugging me.

I have a form on which the user enters a date range before executing a query and showing a report. I have added VBA to ensure the user enters the start date and finish date as well as checking the start date is before the finish date etc., plus a couple of buttons that toggle the dates in the start and finish date to the current and previous financial year.

This is all good to this point, except when the user manually enters the date, then my VBA kicks in a stops me in my tracks. See code below. Any help is appreciated.

Private Sub cmdExpReport_Click()

If IsNull(ExpStartDate) Then
MsgBox "Please enter a start date.", vbOKOnly, "Expenses Report"
ExpStartDate.SetFocus

ElseIf IsNull(ExpFinishDate) Then
MsgBox "Please enter a finish date.", vbOKOnly, "Expenses Report"
ExpFinishDate.SetFocus

ElseIf ExpStartDate > ExpFinishDate Then
MsgBox "Please check the dates - the start date must be on or before the finish date.", vbOKOnly, "Expenses Report"
ExpStartDate.SetFocus

ElseIf DCount("*", "ExpensesQuery") = 0 Then
MsgBox "No records found.", vbOKOnly, "Expenses Report"
ExpStartDate.SetFocus

Else: DoCmd.OpenReport "ExpensesReport", acViewPreview, , , acDialog

End If

End Sub

Private Sub cmdExpCurrentFY_Click()

ExpStartDate = IIf(Date < "01/07/" & (Year(Date)), "01/07/" & (Year(Date)), "01/07/" & (Year(Date) + 1))
ExpFinishDate = IIf(Date < "01/07/" & (Year(Date)), "30/06/" & (Year(Date) + 1), "30/06/" & (Year(Date)))
cmdExpPreviousFY.Enabled = True
cmdExpCurrentFY.Enabled = False
cmdExpPreviousFY.SetFocus

End Sub

Private Sub cmdExpPreviousFY_Click()

ExpStartDate = IIf(Date < "01/07/" & (Year(Date)), "01/07/" & (Year(Date) - 1), "01/07/" & (Year(Date)))
ExpFinishDate = IIf(Date < "01/07/" & (Year(Date)), "30/06/" & (Year(Date)), "30/06/" & (Year(Date)))
cmdExpPreviousFY.Enabled = False
cmdExpCurrentFY.Enabled = True
cmdExpCurrentFY.SetFocus

End Sub

 

[arnelgp]

use DateSerial to convert your expression to
Date type:
...
...

Private Sub cmdExpCurrentFY_Click()
ExpStartDate = IIF(Date < DateSerial(Year(Date),7,1), DateSerial(Year(Date),7,1), DateSerial(Year(Date)+1,7,1))
ExpFinishDate = IIF(Date < DateSerial(Year(Date),7,1), DateSerial(Year(Date)+1,6,30), DateSerial(Year(Date),6,30))

...
...
End Sub


Private Sub cmdExpPreviousFY_Click()
ExpStartDate = IIF(Date < DateSerial(Year(Date),7,1), DateSerial(Year(Date)-1,7,1), DateSerial(Year(Date),7,1))
ExpFinishDate = IIF(Date < DateSerial(Year(Date),7,1), DateSerial(Year(Date),6,30), DateSerial(Year(Date)-1,6,30))
...
...
End Sub

 

[maff811]

Thanks arnelgp, that has done the trick! I just had to tweak it a bit as the dates were the wrong way around, but once I did that it works perfectly.

 

[arnelgp]

Glad to hear that it worked