Merge Two Pieces of VBA code into one sub

[nixonshaun]

I have a form with a query subform on it and two unbound text boxes.


The goal is to add command buttons each filtering the date Between user input in unbound text boxes and between a specific disability. would be a command button for each of the disabilities we recognize.
The query has several specific fields from the main table: Participant ID, Participant Name, Application Date, Disability Primary.
it allows me to organize my caseload, based on the number of applications in a date range and various different disabilities we used to find someone eligible for service.

I have a code snippet that will filter Between two dates (From and To) based on user added input in the text boxes on the form.
I have another code snippet that will filter by specific disability using Like. e.g Like Cognitive*, Like Physical*, Like Psychosocial* etc.

these both work well individually, but i would like to get them into one snippet of code that would allow me to filter by date, and then by primary disabilities within that date range.

can anybody offer any advice, i am not a programmer, just picking this up from looking at other people's examples.

heres the code:
filter that works on date: (would ultimately like this command button to filter date and then find cognitive disability within that date range)

Private Sub Cognitive_Click()
Dim strFilter As String
strFilter = "([Application Date] Between #" & Me.txtFromDate & "# And #" & Me.txtToDate & "#)"
Me.DisabilityList.Form.Filter = strFilter
Me.DisabilityList.Form.FilterOn = True
End Sub

filter that works on text using Like:
Private Sub psychosocial_Click()
Dim strFilterText As String
strFilterText = "([Disability Primary] Like 'psycho*')"
Me.DisabilityList.Form.Filter = strFilterText
Me.DisabilityList.Form.FilterOn = True
End Sub

[Welshgasman]

Are you always going to ennter dates and diability?
If so simple contactenation is all that is needed.

Otherwise you would need some IF statements

Simple concatenation

strFilter = "[Application Date] Between #" & Me.txtFromDate & "# And #" & Me.txtToDate & "# AND [Disability Primary] Like '" & Me.Disability & "*'"
Debug.Print strFilter

I hope your dates are in USA format?

[nixonshaun]

Thanks Welshgasman,
yes there will always be an application date, as we are only allowed 60 days to review records and make an eligibility determination, then 90 days from eligibility date to develop an individualized plan.
yes date is formatted to USA.
concatenation seems to work, though i am mostly unfamiliar with it, i think i used it once years ago in Excel to merge first name and surname into one cell.

I changed your code slightly so i wouldn't need a text box of combo box for Me.Disability.
looks something like this now: strFilter = "[Application Date] Between #" & Me.txtFromDate & "# And #" & Me.txtToDate & "# AND [Disability Primary] Like 'Cog*'"
Debug.Print strFilter

it works well, thanks alot.
I have one area which is effectively physical and mobility, it includes mobility, both mobility and manipulation, general physical debilitation. is there a way i can get all three of three of these in the same line, i tried to add AND [Disability Primary] Like 'Mobility*' And [Disability Primary] Like 'Both*'"
but it didnt seem to work, my formatting for this line must be off.

[CJ_London]

Just check your logic- how can something be equal to two different things at the same time

[Disability Primary] Like 'Mobility*' And [Disability Primary] Like 'Both*'"

you need an or instead of an and. You are then also into Boolean logic - ands and ors need to be separated in some way

a and b or c

is that
a and b OR c

or

a AND b or c?

as with maths you need to use brackets

either

(a and b) OR c

or

a AND (b or c)

[Welshgasman]

What is the point of hard coding disability?, I would have thought you wanted whatever was in the disability control? regardless of the control type, otherwise you will be changing the code every 10 seconds.

[nixonshaun]

Thank you CJ that provides a more clear understanding of how to proceed.
I tried it using Or and it works flawlessly.

its been a challenge, trying to replicate Query Criteria from query design feature into Basic. But it's good learning something new, thanks again for your help.

[Welshgasman]

For multiple disabilities I would perhaps use IN ( ) and build the string?

[CJ_London]

it’s been a challenge, trying to replicate Query Criteria from query design feature into Basic.

switch to sql view to see the where clause