Forum Discussion

Tony2021's avatar
Tony2021
Steel Contributor
Jan 21, 2022
Solved

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 [Tr...
access
  • arnel_gp's avatar
    arnel_gp
    Jan 21, 2022
    if the criteria is Single string, you use Between:

    Between [Forms]![frmFind_Both]![txtDateFrom] And [Forms]![frmFind_Both]![txtDateTo]
Tony2021's avatar
Tony2021
Steel Contributor
Jan 21, 2022

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

ComputerLearningZone's avatar
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's avatar
    Tony2021
    Steel 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's avatar
      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]

       

      • Tony2021's avatar
        Tony2021
        Steel Contributor
        Jan 24, 2022
        thank you Jurgen. Very nice indeed. Have a good day...
    • ComputerLearningZone's avatar
      ComputerLearningZone
      Brass Contributor
      Jan 22, 2022
      Again, I still don't understand what you're trying to accomplish with the SelStart stuff...???

Resources