Date Filter Form (too complex error)

Tony2021

Iron Contributor

Jan 21, 2022

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

ComputerLearningZone
Brass Contributor
Jan 21, 2022
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
- Tony2021
  Iron Contributor
  Jan 22, 2022
  
  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.
  
  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
  - JurgenGeelen
    Brass Contributor
    Jan 23, 2022
    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]

Forum Discussion

Resources