Solved Unable to search data in a date criteria (1 Viewer)

Tiprof

New member
Local time
Today, 06:09
Joined
Apr 29, 2022
Messages
8
Am having a database which I have a difficulty filtering data in a multiple criteria.

Attached is a sample copy of my database with the issue for analysis.

Any help with this will be highly appreciated
 

Attachments

  • my work.zip
    95.6 KB · Views: 168

Gasman

Enthusiastic Amateur
Local time
Today, 07:09
Joined
Sep 21, 2011
Messages
10,555
I do not want to be downloading files all the time, especially when I find I cannot open them due to my old version of Access.

Start off simple and post the code used (within code tags), see my signature.
Then if we still cannot work it out, then would be the need for a DB.
 

Tiprof

New member
Local time
Today, 06:09
Joined
Apr 29, 2022
Messages
8
Code:
Sub Search()Dim strCriteria, task As String


If Trim(Me.cboIncomeType.Value & vbNullString) = vbNullString Then
MsgBox prompt:="Please select Payment mode.", buttons:=vbInformation, title:="SOFTWARE"
Me.cboIncomeType.SetFocus
Exit Sub

End If

If Trim(Me.cboIncomeType1.Value & vbNullString) = vbNullString Then
MsgBox prompt:="Please select Payment form.", buttons:=vbInformation, title:="SOFTWARE"
Me.cboIncomeType1.SetFocus
Exit Sub

End If



If IsNull(Me.OrderDateFrom) Then
MsgBox "Please enter the Start date", vbInformation, "Date Range Required"
Me.OrderDateFrom.SetFocus
Else
If IsNull(Me.OrderDateTo) Then
MsgBox "Please enter the End date", vbInformation, "End Date Required"
Me.OrderDateTo.SetFocus


Else
strCriteria = "[DATE] >= " & Format(Me.OrderDateFrom, "\#mm\/dd\/yyyy\#") & _
" And [DATE] <= " & Format(Me.OrderDateTo, "\#mm\/dd\/yyyy\#") & _
" And[PAYMENT_FORM] LIKE '" & IIf(IsNull(Me.cboIncomeType1), "*", Me.cboIncomeType1) & _
" And[PAYMENT_MODE] LIKE '" & IIf(IsNull(Me.cboIncomeType), "*", Me.cboIncomeType) & "'"


'task = "select * from PAY_FORM_ALL_EXPENSE where (" & strCriteria & ") order by [DATE]" )

docmd.applyfilter, strCriteria

end if
end if
 

Tiprof

New member
Local time
Today, 06:09
Joined
Apr 29, 2022
Messages
8
Tgat
Code:
Sub Search()Dim strCriteria, task As String


If Trim(Me.cboIncomeType.Value & vbNullString) = vbNullString Then
MsgBox prompt:="Please select Payment mode.", buttons:=vbInformation, title:="SOFTWARE"
Me.cboIncomeType.SetFocus
Exit Sub

End If

If Trim(Me.cboIncomeType1.Value & vbNullString) = vbNullString Then
MsgBox prompt:="Please select Payment form.", buttons:=vbInformation, title:="SOFTWARE"
Me.cboIncomeType1.SetFocus
Exit Sub

End If



If IsNull(Me.OrderDateFrom) Then
MsgBox "Please enter the Start date", vbInformation, "Date Range Required"
Me.OrderDateFrom.SetFocus
Else
If IsNull(Me.OrderDateTo) Then
MsgBox "Please enter the End date", vbInformation, "End Date Required"
Me.OrderDateTo.SetFocus


Else
strCriteria = "[DATE] >= " & Format(Me.OrderDateFrom, "\#mm\/dd\/yyyy\#") & _
" And [DATE] <= " & Format(Me.OrderDateTo, "\#mm\/dd\/yyyy\#") & _
" And[PAYMENT_FORM] LIKE '" & IIf(IsNull(Me.cboIncomeType1), "*", Me.cboIncomeType1) & _
" And[PAYMENT_MODE] LIKE '" & IIf(IsNull(Me.cboIncomeType), "*", Me.cboIncomeType) & "'"


'task = "select * from PAY_FORM_ALL_EXPENSE where (" & strCriteria & ") order by [DATE]" )

docmd.applyfilter, strCriteria

end if
end if
Thats the filter code I used which isn’t yielding the results I want
 

Gasman

Enthusiastic Amateur
Local time
Today, 07:09
Joined
Sep 21, 2011
Messages
10,555
OK, crossposting is not going to endear you to many members on both sites, myself included.
I thought I recognised the code.

Cross-posted at https://www.accessforums.net/showthread.php?t=85845

At least have the courtesy to say you have posted elsewhere.

You have had some decent advice on that forum, but seem reluctant to to follow it? :(
Perhaps the DB might come in useful here, if someone chooses to debug that for you, but that will not be me.

I will say however that you are using Like but not any * when those controls are populated?
If you had debug printed as advised on the other forum, that would be evident? :(
 

Tiprof

New member
Local time
Today, 06:09
Joined
Apr 29, 2022
Messages
8
Am having a database which I have a difficulty filtering data in a multiple criteria.

Attached is a sample copy of my database with the issue for analysis.

Any help with this will be highly appreciated
Yes
I did cross post as you said. Sorry about that.
Needed quick response because I don’t understand the debug print procedure so making it difficult to do that.

And it seemed the help wasnt coming as expected so needed to post here too.

Maybe someone might be of help.
 

bob fitz

AWF VIP
Local time
Today, 07:09
Joined
May 23, 2011
Messages
4,444
Why not simply use:
Code:
Else
    strCriteria = "[DATE] >= " & Format(Me.OrderDateFrom, "\#mm\/dd\/yyyy\#") & _
                  " And [DATE] <= " & Format(Me.OrderDateTo, "\#mm\/dd\/yyyy\#") & _
                  " And [PAYMENT_FORM] = '" & Me.cboIncomeType1 & "'" & _
                  " And [PAYMENT_MODE] = '" & Me.cboIncomeType & "'"
I see no need for using LIKE or IIF as your other code compels users to make a selection in the combo boxes.
 

Gasman

Enthusiastic Amateur
Local time
Today, 07:09
Joined
Sep 21, 2011
Messages
10,555
Yes
I did cross post as you said. Sorry about that.
Needed quick response because I don’t understand the debug print procedure so making it difficult to do that.

And it seemed the help wasnt coming as expected so needed to post here too.

Maybe someone might be of help.
So say you do not know how to debug.print or walk through the code. :(
You have been on that site since Jan 2020, so presumably using Access since then?
That should be one of the first things you learn, it will help you so much in the future, even now. :)

Don't just ignore the suggestions offered, even if you do not like them. :(
 

bob fitz

AWF VIP
Local time
Today, 07:09
Joined
May 23, 2011
Messages
4,444
Yes
I did cross post as you said. Sorry about that.
Needed quick response because I don’t understand the debug print procedure so making it difficult to do that.

And it seemed the help wasnt coming as expected so needed to post here too.

Maybe someone might be of help.
Whatever your reason for cross-posting it is still considered by many to be good practice to post a link to the other post(s)
Please read: https://www.excelguru.ca/content.php?184
 

CJ_London

Super Moderator
Staff member
Local time
Today, 07:09
Joined
Feb 19, 2013
Messages
14,390
sorry to be critical, but you don't make it easy for people to help - do you not use indenting? What result do you want? what result do you get?

other observations -

1. Date is a reserved word (it is a function that returns today) and should not be used as a field name

2. [DATE] >= " & Format(Me.OrderDateFrom, "\#mm\/dd\/yyyy\#") & _
" And [DATE] <= " & Format(Me.OrderDateTo, "\#mm\/dd\/yyyy\#") & _

could be expressed as

[DATE] between " & Format(Me.OrderDateFrom, "\#mm\/dd\/yyyy\#") & _
" And " & Format(Me.OrderDateTo, "\#mm\/dd\/yyyy\#") & _

3. If [DATE] includes a time element, any records with a date of Me.OrderDateTo will not be returned
 

Tiprof

New member
Local time
Today, 06:09
Joined
Apr 29, 2022
Messages
8
sorry to be critical, but you don't make it easy for people to help - do you not use indenting? What result do you want? what result do you get?

other observations -

1. Date is a reserved word (it is a function that returns today) and should not be used as a field name

2. [DATE] >= " & Format(Me.OrderDateFrom, "\#mm\/dd\/yyyy\#") & _
" And [DATE] <= " & Format(Me.OrderDateTo, "\#mm\/dd\/yyyy\#") & _

could be expressed as

[DATE] between " & Format(Me.OrderDateFrom, "\#mm\/dd\/yyyy\#") & _
" And " & Format(Me.OrderDateTo, "\#mm\/dd\/yyyy\#") & _

3. If [DATE] includes a time element, any records with a date of Me.OrderDateTo will not be returned
My issue is from the strCriteria. The section for the date is fine
 

CJ_London

Super Moderator
Staff member
Local time
Today, 07:09
Joined
Feb 19, 2013
Messages
14,390
The section for the date is fine
your thread is headed

Unable to search data in a date criteria​

And you haven't answered my other questions
 

Tiprof

New member
Local time
Today, 06:09
Joined
Apr 29, 2022
Messages
8
Hav
Why not simply use:
Code:
Else
    strCriteria = "[DATE] >= " & Format(Me.OrderDateFrom, "\#mm\/dd\/yyyy\#") & _
                  " And [DATE] <= " & Format(Me.OrderDateTo, "\#mm\/dd\/yyyy\#") & _
                  " And [PAYMENT_FORM] = '" & Me.cboIncomeType1 & "'" & _
                  " And [PAYMENT_MODE] = '" & Me.cboIncomeType & "'"
I see no need for using LIKE or IIF as your other code compels users to make a selection in the combo boxes
Have tried this
But the code turns red and doesn’t compile
 

bob fitz

AWF VIP
Local time
Today, 07:09
Joined
May 23, 2011
Messages
4,444
Hav

Have tried this
But the code turns red and doesn’t compile
Perhaps you should check that you copied and pasted it correctly (as I have just done) because it compiles fine in the db that you posted :unsure:
 

arnelgp

..forever waiting... waiting for jellybean!
Local time
Today, 14:09
Joined
May 7, 2009
Messages
16,408
you should not set Data Entry to Yes.
see and test your form.
 

Attachments

  • my work.zip
    99.2 KB · Views: 79

Tiprof

New member
Local time
Today, 06:09
Joined
Apr 29, 2022
Messages
8
I
Perhaps you should check that you copied and pasted it correctly (as I have just done) because it compiles fine in the db that you posted :unsure:
rechecked and noticed I didn’t copy it properly
And yes
Your suggestion helped fixed the issue for me
Thanks a lot
 

bob fitz

AWF VIP
Local time
Today, 07:09
Joined
May 23, 2011
Messages
4,444
I

rechecked and noticed I didn’t copy it properly
And yes
Your suggestion helped fixed the issue for me
Thanks a lot
Glad you have a solution. I hope you'll take the time to read the link that I posted about cross-posting.
 

Users who are viewing this thread

Top Bottom