Filter Question

[frankos]

Hi guys.

Currently I have an application which holds orders. To search through the orders, I use a filter which the user activates to limit the orders he can move through in the dataset. The code to do so is as follows:

Private Sub btn_filter_Click()

Dim str_filter As String
str_filter = ""

'Put the filter on depending on what fields are filled out
If (Trim(txt_find_Order) <> "") Then
str_filter = str_filter + "Order_no like '*" & txt_find_Order & "*' And "
End If
If (Trim(cmb_find_status) <> "") Then
str_filter = str_filter + "cur_status like '*" & cmb_find_status & "*' And "
End If

'remover the last ' And '
If (str_filter <> "") Then
str_filter = Left(str_filter, (Len(str_filter) - 5))
End If
MsgBox str_filter
ado_rmOrder.Filter = str_filter
Else ....

So when the filter is on, the user moves through records set in the filter text boxes. the beauty of this is that the wildcards (*) mean you can only enter part of a number and it leave any orders in the recordset that have part of it match the number. For example if the order number filter is set to "123" then order numbers "12345", "45123", "3412363" and "123" will all pass through the filter.

The problem is I also want to filter on a time / date field. This requires hases (#) as follows:

If (Trim(txt_find_date) <> "") Then
str_filter = str_filter + "Order_Date like #" & txt_find_date & "# And "
End If

The problem is that I cannot seem to insert wildcards (*) in this statement. This means that the filter will discard all records that dont EXACTLY match the txt_find_date field. This means if txt_find_date = 12/12/03, then a record with "12/12/03 12:47:32 PM" with be rejected by the filter, as it is not an EXACT match. As you can imagine, this is not much good to me. So my question is, is there any way to get some form of wildcards working with date fields so that I can filter records based on only part of the date field?

Cheers
Frank

[Ecniv]

Use two textboxes and a Between statement...

Box 1 - after update fills box two with same date.
Filter : where datefield between #<box1> & 00:00:00# and #<box2> 23:59:59#


One way perhaps.

Another more coding way is to look where the * is and amend the filter accordingly.

Example:
*/03/2004
Datefield between 01/03/2004 and 31/03/2004
Or
Month(datefield) = 03 and year(datefield) =2004

If you have time, try both and experiment to see which performs best/gives better results


Vince