Solved Unable to search data in a date criteria

T

Tiprof

Member
Local time
Today, 00:37
Joined
Apr 29, 2022
Messages
43
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

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.
 
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
 
Tgat
Tiprof said:
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
Click to expand...
Thats the filter code I used which isn’t yielding the results I want
 
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 said:
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
Click to expand...
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.
 
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.
 
Tiprof said:
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.
Click to expand...
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. :(
 
Tiprof said:
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.
Click to expand...
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
 
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
 
CJ_London said:
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
Click to expand...
My issue is from the strCriteria. The section for the date is fine
 
The section for the date is fine
Click to expand...
your thread is headed

Unable to search data in a date criteria​

And you haven't answered my other questions
 
Hav
bob fitz said:
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
Click to expand...
Have tried this
But the code turns red and doesn’t compile
 
Tiprof said:
Hav

Have tried this
But the code turns red and doesn’t compile
Click to expand...
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:
 
you should not set Data Entry to Yes.
see and test your form.
 

Attachments

I
bob fitz said:
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:
Click to expand...
rechecked and noticed I didn’t copy it properly
And yes
Your suggestion helped fixed the issue for me
Thanks a lot
 
Tiprof said:
I

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

Similar threads

I
Issues with combobox record selector and filters
Replies
5
Views
398
pdanes
P
raziel3
Solved Form Properties "Order By" and "Filter By" retains parameters.
Replies
6
Views
278
raziel3
raziel3
B
Solved Bring data into form field from a different query
2 3
Replies
47
Views
1,160
Gasman
Gasman
G
Filtering on Date fields problem
Replies
8
Views
262
azhar2006
A
B
Listbox wont search for record
Replies
10
Views
185
Gasman
Gasman

Users who are viewing this thread

Total: 1 (members: 0, guests: 1)
Back
Top Bottom