Jan 20 2022 05:49 PM
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
Jan 20 2022 07:40 PM
SolutionJan 21 2022 03:41 AM - edited Jan 21 2022 03:46 AM
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
Jan 21 2022 07:29 AM
Jan 22 2022 09:19 AM
Hi Richard,
Thank you for the tip on between. Its good to know that since I do have a time formatted column however I am not filtering on that column but I could in the future so that is a excellent tip. I did get an error of "Too Complex to Evaluate" using the CDate though (see pic in first post). I reverted back to the between code provided by Arnel.
I took the On Change code out and replaced it with an AfterUpdate: Me.subformJPMDates.requery and after I enter the dates in the unbound field the subfrmJPMDates filters on the dates entered. I wanted to use the On Change code but I think it would have to be tweaked since I am using a DateFrom and DateTo and not sure if then I would need a set a SelPos and SelPos1. I get a #Name in subfrmJPMDates when using the OnChange codes of unbound fields txtDateFrom and txtDateTo and I assume it is because it updates after entering a single number and I need it to only update after I finish entering the full date.
Private Sub txtDateFrom_AfterUpdate()
Me.subfrmJPMDates.Requery
End Sub
Private Sub txtDateTo_AfterUpdate()
Me.subfrmJPMDates.Requery
End Sub
The below On Change is just a nice to have. The above AfterUpdate works (it filters the subfrmJPMDates for the dates I enter in the 2 unbound fields txtDateTo and txtDateFrom). I changed selPos to selPos1 for only the txtDateFrom unbound field. I am not a coder so my approach is probably wrong.
thank your for your help. if you happen to see where the On Change code could be wrong please let me know but I am having good success with the AfterUpdate though.
Private Sub txtDateFrom_Change()
Dim selPos1 As Integer
selPos1 = Me.txtdatefrom.SelStart
Me.Refresh
' Me.subfrmJPMDates.Requery
If Len(Me.txtdatefrom.Value) = selPos1 Then
Me.txtdatefrom.SelStart = selPos1
Else
Me.txtdatefrom = Me.txtdatefrom & " "
Me.txtdatefrom.SelStart = selPos1
End If
End Sub
Private Sub txtDateTo_Change()
Dim selPos As Integer
selPos = Me.txtDateTo.SelStart
Me.Refresh
' Me.subfrmJPMDates.Requery
If Len(Me.txtDateTo.Value) = selPos Then
Me.txtDateTo.SelStart = selPos
Else
Me.txtDateTo = Me.txtDateTo & " "
Me.txtDateTo.SelStart = selPos
End If
End Sub
Jan 22 2022 10:02 AM
Jan 23 2022 11:15 AM
You can try this:
Add two more input fields on your filter form (frmFind_Both) and name it txtDateFrom2 and txtDateTo2.
Add this code to your filter form:
Private Sub txtDateFrom_Change()
If IsDate(txtDateFrom.Text) Then
txtDateFrom2 = txtDateFrom.Text
Me.subfrmJPMDates.Requery
End If
End Sub
Private Sub txtDateTo_Change()
If IsDate(txtDateTo.Text) Then
txtDateTo2 = txtDateTo.Text
Me.subfrmJPMDates.Requery
End If
End Sub
Add this in your subform query (subfrmJPMDates):
Between [Forms]![frmFind_Both]![txtDateFrom2] And [Forms]![frmFind_Both]![txtDateTo2]
Jan 24 2022 03:34 AM
Jan 20 2022 07:40 PM
Solution