My first post here, and my mind is pudding at this point (any flavor you like)!
I have been searching for days, and still can't figure out the proper syntax that I require. This is my first foray into databases and forms, and I've managed to put together one that works pretty well, except I can't figure out a date range search, which is vital to the overall project.
What I have is; A database, in Excel, with five fields (Card Number, Date, Amount, Sold By, Payment Type). I have made a user form with VB, with individual searches for all fields above. Using DTPicker for date search. All found records are returned to a ListBox on same form for review or individual selection, then may be exported to worksheet in same workbook for saving or printing. All good to this point.
How can I use 2 DTPickers for start and end date and return all between? Here is how I am conducting search now:
Expand|Select|Wrap|Line Numbers
- Private Sub cmbSearchDate_Click()
- Dim strFind, FirstAddress As String 'what to find
- Dim rSearch As Range 'range to search
- Set rSearch = Sheet1.Range("b5", Range("b65536").End(xlUp))
- strFind = Me.DTPicker1.Value 'what to look for
- Dim f As Integer
- With rSearch
- Set c = .Find(strFind, LookIn:=xlValues)
- If Not c Is Nothing Then 'found it
- c.Select
- With Me 'load entry to form
- .txtCard.Value = c.Offset(0, -1).Value
- .combAmount.Value = c.Offset(0, 1).Value
- .combSoldBy.Value = c.Offset(0, 2).Value
- .combPayment.Value = c.Offset(0, 3).Value
- .cmbAmend.Enabled = True 'allow amendment or
- .cmbDelete.Enabled = True 'allow record deletion
- .cmbAdd.Enabled = False 'don't want to duplicate record
- f = 0
- End With
- FirstAddress = c.Address
- Do
- f = f + 1 'count number of matching records
- Set c = .FindNext(c)
- Loop While Not c Is Nothing And c.Address <> FirstAddress
- If f > 1 Then
- MsgBox "There are " & f & " instances of " & strFind
- cmbFindAllNumber.Visible = False
- cmbFindAllDate.Visible = True
- cmbFindAllAmount.Visible = False
- cmbFindAllSoldBy.Visible = False
- cmbFindAllPayment.Visible = False
- End If
- Else: MsgBox strFind & " not listed" 'search failed
- End If
- End With
- End Sub
I really appreciate your time, Thank you in advance for any help you may provide.