SOLVED

Date Filter Form (too complex error)

Steel Contributor

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

Tony2021_0-1642728606195.png

 

Tony2021_1-1642728990871.png

 

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.

Tony2021_2-1642729535149.png

 

Why am I getting this error message?  

 

thank you 

 

7 Replies
best response confirmed by Tony2021 (Steel Contributor)
Solution
if the criteria is Single string, you use Between:

Between [Forms]![frmFind_Both]![txtDateFrom] And [Forms]![frmFind_Both]![txtDateTo]

@arnel_gp 

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

 

@ComputerLearningZone 

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.  

Tony2021_1-1642871631640.png

 

 

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

Again, I still don't understand what you're trying to accomplish with the SelStart stuff...???

@Tony2021 

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]

 

thank you Jurgen. Very nice indeed. Have a good day...
1 best response

Accepted Solutions
best response confirmed by Tony2021 (Steel Contributor)
Solution
if the criteria is Single string, you use Between:

Between [Forms]![frmFind_Both]![txtDateFrom] And [Forms]![frmFind_Both]![txtDateTo]

View solution in original post