Forum Discussion
Date Filter Form (too complex error)
Experts,
I am building a form to filter a few different subforms.
I add 2 unbound fields to this form:
txtDateFrom
txtDateTo
The problem I am having is this WHERE CLAUSE I put on the [Transaction Date] in the subform query:
>=[forms]![frmFind_Both]![txtDateFrom] And <[forms]![frmFind_Both]![txtDateTo]+1
When I open the subform I dont know why I get this error after I enter the dates in the msgboxs.
I enter 9/15/2021 in the msgbox for txtDateFrom hit enter and then 10/15/2021 is entered in the next msgbox txtDateTO and I hit enter and I get the error msg box above.
I use the same filter logic on other forms in my db and it works fine.
[Transaction Date] is a date field short date type.
Why am I getting this error message?
thank you
- if the criteria is Single string, you use Between:
Between [Forms]![frmFind_Both]![txtDateFrom] And [Forms]![frmFind_Both]![txtDateTo]
- if the criteria is Single string, you use Between:
Between [Forms]![frmFind_Both]![txtDateFrom] And [Forms]![frmFind_Both]![txtDateTo]HI Arnel,
that was it. I do not have an error when I open the subform by itself and it filters.
When I open it from the filter form I think the issue is something to do with the following On Change code (I think Integer shoudl be a Date and I made that chnage but there is still something wrong with it. The subform updates to #Name for all fields so I assume there should be a tweak in the following to adjust for a Date and not an Integer.
Do you happen to see where the codes are wrong: Not sure if .Value should be something that handles a date but I dont see .Date as a choice?
FYI: the filter form filters for Integers but now I am adding a date in addition to Integers but only for the txtDateFrom and txtDateTo. I have 7 subforms on the filter form.thank you very much!
Private Sub txtDateFrom_Change()
Dim selPos As Integer ' I changed this to Date but still have a #Name error on Change
selPos = Me.txtdatefrom.SelStart
Me.Refresh
If Len(Me.txtdatefrom.Value) = selPos Then ' Does this need to be adj. for Date?
Me.txtdatefrom.SelStart = selPos
Else
Me.txtdatefrom = Me.txtdatefrom & " "
Me.txtdatefrom.SelStart = selPos
End If
End Sub
Private Sub txtDateTo_Change()Dim selPos As Integer ' I changed this to Date but still have a #Name error on Change
selPos = Me.txtDateTo.SelStart
Me.Refresh
If Len(Me.txtDateTo.Value) = selPos Then ' Does this need to be adj. for Date?
Me.txtDateTo.SelStart = selPos
Else
Me.txtDateTo = Me.txtDateTo & " "
Me.txtDateTo.SelStart = selPos
End If
End Sub- You don't need "me." or ".value" as those are both assumed. Your original criteria should have worked just fine. However, if you're going to do math like that, you may want to convert the value to a date.
>=CDate(Forms!MyForm!BeginDate) AND <CDate(Forms!MyForm!EndDate)+1
Also, be careful when using the BETWEEN keyword. If you have TIMES in your date values, then an order placed at 5pm on 2/1/2022 will not show up if you're looking BETWEEN 1/1/2022 AND 2/1/2022. See: https://599cd.com/Between
SelPos should be an Integer (preferably Long). What exactly are you trying to accomplish with these events?
Richard Rost
Access MVP 2014-15
AccessLearningZone.com
