asked on

Microsoft Access Form Code not Working

I am not sure where to place my coding. I have a text box is being used as a less or greater than statement to pull values from two fields to filter data on the next page or form.

I put the following statement in the new page where it suppose to filter the data, written as following and I have criteria set in query that is making the list box display blank.

Private Sub Form_LOad()
        If Len(Form_002_Criteria.TxtExp & vbNullString) > 0 Then
            Me.Filter = "ExpMin <= " & Form_002_Criteria.TxtExp & " AND ExpMax >= " & Form_002_Criteria.TxtExp
            Me.FilterOn = True
        Else
            Me.Filter = ""
            Me.FilterOn = False
        End If
End Sub

Open in new window

Gustav Brock

Sounds like you are entering dates. If so:

Private Sub Form_Load()

    If Len(Form_002_Criteria.TxtExp & vbNullString) > 0 Then
        Me.Filter = "#" & Format(Form_002_Criteria.TxtExp, "yyyy\/mm\/dd") & "# Between ExpMin And ExpMax"
        Me.FilterOn = True
    Else
        Me.Filter = ""
        Me.FilterOn = False
    End If

End Sub

Open in new window

Joseph S

ASKER

@Gustav, they are not dates just numbers. I need help with a less than or greater than.

Gustav Brock

OK. Then it could be:

Private Sub Form_Load()

    If Len(Form_002_Criteria.TxtExp & vbNullString) > 0 Then
        Me.Filter = "" & CDbl(Form_002_Criteria.TxtExp) & " Between ExpMin And ExpMax"
        Me.FilterOn = True
    Else
        Me.Filter = ""
        Me.FilterOn = False
    End If

End Sub

Open in new window

Joseph S

ASKER

@Gustavo, Do I put this code in the with the textbox itself form2 or should I have it on the form3 where the information be filter? Another thing, ExpMIN and ExpMax, when I set the criteria in the query to point at the textbox Forms![Criteria]![TxtExp]. It causes the listbox to display nothing.

Gustav Brock

It is code for the code-behind module.

You should post your code.

Joseph S

ASKER

@Gustav, Where do you recommend posting it?

Joseph S

ASKER

@Gustav,

I put the coding on Form 3 between Private Sub Form_Load() End Sub, and it didn't filter the data. I also put the coding on Form 2 with the textbox between Private Sub TxtExp_AfterUpdate(Cancel As Integer), and still didn't filter anything.

Joseph S

ASKER

@Gustav,

When I set the query criteria for the list box, pointing to ExpMin and ExpMax unchecked it causes my data to not display.

Gustav Brock

It is not easy to follow your doings, not to say what you are trying to do.
Perhaps you could attach your database - with anonymised data, of course.

Joseph S

ASKER

I could that for you, would have to email you the file if that is alright?

Gustav Brock

You could attach it to a Private Message.

This question needs an answer!

Become an EE member today

7 DAY FREE TRIAL

Members can start a 7-Day Free trial then enjoy unlimited access to the platform.

View membership options

Learn why we charge membership fees

We get it - no one likes a content blocker. Take one extra minute and find out why we block content.