Query results using several textbox values separated by commas (1 Viewer)

dlambert

Member
Local time
Today, 07:26
Joined
Apr 16, 2020
Messages
42
Hello,
I have the following situation:
I have a a table (tblJobs) of job numbers
I have a table (tblWorkHours) of work hours, each of which is tied to a set job number.

I would like a query to select the work hours based on the job number i select, but i would like to do so using a textbox in a form, separated by commas to be able to select multiple job numbers.

For example, if i want to see the hours for job number 2 and 4 and 5 i would like to type in: "3,4,5" into a textbox and have the query return the corresponding WorkHours accordingly.

Also it would be great is i could type "ALL" into the textbox and have it display the WorkHours for all the job numbers

Any suggestions on how i can achieve this?
 

cheekybuddha

AWF VIP
Local time
Today, 06:26
Joined
Jul 21, 2014
Messages
2,237
What is the RecordSource of your form? Is it tblWorkHours?

You can use something in your AfterUpdate of your textbox like:
Code:
Private Sub txtFilterJobNumbers_AfterUpdate()

  With Me
    If Len(.txtFilterJobNumbers & vbNullString) Then
      .Filter = "[JobNumber] IN (" & .txtFilterJobNumbers & ")"
      .FilterOn = True
    Else
      .FilterOn = False
    End If
  End With

End Sub
You will need to adjust the control/field names as required.

Also, it assumes your job numbers are numeric datatype - if they are strings then a little extra adjustment will be necessary.

hth,

d
 

Users who are viewing this thread

Top Bottom