473,320 Members | 1,993 Online
Bytes | Software Development & Data Engineering Community
Post Job

Home Posts Topics Members FAQ

Join Bytes to post your question to a community of 473,320 software developers and data experts.

Date Range search with VB UserForm

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
0 2107

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

Similar topics

5
by: Greg | last post by:
I have a page that searches a database by a repairman's name and by a date range. It pulls info by the repairman's name but pulls all info in the database regardless of the date. Below is the code...
4
by: Tony | last post by:
Hey guys, I use Google Groups quite a bit as it is an enormous wealth of information, and now I need some help. I have created a query using parameters to capture a range of date, the date is...
0
by: szymon_k | last post by:
Hi. How to set the userform as subform of some access form? What I need to do is to insert some userform into the form, so that it will be under the same conditions of resize, move and other...
3
by: gw.boswell | last post by:
I have created a userform with textboxes for data entry. I also have a command button that calls a second useform. A user enters certain information in the first useform but if he/she wants to do...
6
by: fonzie | last post by:
Is there any way to include two unbound text boxes (for a start date and end date) to a filter-by-form? The users may want to filter by several different fields and they may want to include a date...
2
by: williamyeow | last post by:
Hi i am new to excel VB. can anyone tell me how to link userform to another userform? My concept for my program I have set up a userform with a couple of option. after the user has chose the...
1
by: brenty66 | last post by:
I have a userform that is used as an interface for information stored on excel spreadsheets. The user enters bits of information into fields on the userform which are place on the corresponding...
12
by: jamieboy86 | last post by:
Hi Everyone, I'm new here and to access as well, The problem I'm having right now is that I made a Query that upon the input of a date, it will search for the last purchase I made and at what...
5
by: jazznojive | last post by:
I've created a textbox control on a userform and I am trying to figure out how to make the text populate to a worksheet range in sequence automatically. I've figued out how to get the text that the...
0
by: ryjfgjl | last post by:
ExcelToDatabase: batch import excel into database automatically...
0
isladogs
by: isladogs | last post by:
The next Access Europe meeting will be on Wednesday 6 Mar 2024 starting at 18:00 UK time (6PM UTC) and finishing at about 19:15 (7.15PM). In this month's session, we are pleased to welcome back...
0
by: Vimpel783 | last post by:
Hello! Guys, I found this code on the Internet, but I need to modify it a little. It works well, the problem is this: Data is sent from only one cell, in this case B5, but it is necessary that data...
0
by: jfyes | last post by:
As a hardware engineer, after seeing that CEIWEI recently released a new tool for Modbus RTU Over TCP/UDP filtering and monitoring, I actively went to its official website to take a look. It turned...
0
by: ArrayDB | last post by:
The error message I've encountered is; ERROR:root:Error generating model response: exception: access violation writing 0x0000000000005140, which seems to be indicative of an access violation...
0
by: CloudSolutions | last post by:
Introduction: For many beginners and individual users, requiring a credit card and email registration may pose a barrier when starting to use cloud servers. However, some cloud server providers now...
1
by: Shællîpôpï 09 | last post by:
If u are using a keypad phone, how do u turn on JavaScript, to access features like WhatsApp, Facebook, Instagram....
0
by: Faith0G | last post by:
I am starting a new it consulting business and it's been a while since I setup a new website. Is wordpress still the best web based software for hosting a 5 page website? The webpages will be...
0
isladogs
by: isladogs | last post by:
The next Access Europe User Group meeting will be on Wednesday 3 Apr 2024 starting at 18:00 UK time (6PM UTC+1) and finishing by 19:30 (7.30PM). In this session, we are pleased to welcome former...

By using Bytes.com and it's services, you agree to our Privacy Policy and Terms of Use.

To disable or enable advertisements and analytics tracking please visit the manage ads & tracking page.