By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
432,188 Members | 832 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 432,188 IT Pros & Developers. It's quick & easy.

Date Range search with VB UserForm

P: 1
Greetings all!

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
  1. Private Sub cmbSearchDate_Click()
  2. Dim strFind, FirstAddress As String   'what to find
  3.     Dim rSearch As Range  'range to search
  4.     Set rSearch = Sheet1.Range("b5", Range("b65536").End(xlUp))
  5.     strFind = Me.DTPicker1.Value    'what to look for
  6.     Dim f As Integer
  7.     With rSearch
  8.         Set c = .Find(strFind, LookIn:=xlValues)
  9.         If Not c Is Nothing Then    'found it
  10.             c.Select
  11.             With Me    'load entry to form
  12.                 .txtCard.Value = c.Offset(0, -1).Value
  13.                 .combAmount.Value = c.Offset(0, 1).Value
  14.                 .combSoldBy.Value = c.Offset(0, 2).Value
  15.                 .combPayment.Value = c.Offset(0, 3).Value
  16.                 .cmbAmend.Enabled = True     'allow amendment or
  17.                 .cmbDelete.Enabled = True    'allow record deletion
  18.                 .cmbAdd.Enabled = False      'don't want to duplicate record
  19.                 f = 0
  20.             End With
  21.             FirstAddress = c.Address
  22.             Do
  23.                 f = f + 1    'count number of matching records
  24.                 Set c = .FindNext(c)
  25.             Loop While Not c Is Nothing And c.Address <> FirstAddress
  26.             If f > 1 Then
  27.                 MsgBox "There are " & f & " instances of " & strFind
  28.                 cmbFindAllNumber.Visible = False
  29.                 cmbFindAllDate.Visible = True
  30.                 cmbFindAllAmount.Visible = False
  31.                 cmbFindAllSoldBy.Visible = False
  32.                 cmbFindAllPayment.Visible = False
  33.             End If
  34.         Else: MsgBox strFind & " not listed"    'search failed
  35.         End If
  36.     End With
  37. End Sub
What is, and where do I add the extra parameter? I feel as though it's on the tip of my brain, but I can't grasp it.

I really appreciate your time, Thank you in advance for any help you may provide.
Jan 11 '08 #1
Share this question for a faster answer!
Share on Google+

Post your reply

Sign in to post your reply or Sign up for a free account.