Tracking Forums, Newsgroups, Maling Lists
Home Scripts Tutorials Tracker Forums
  Advanced Search
  HOME    TRACKER    Visual Basic

See Related Forum Messages: Follow the Links Below to View Complete Thread

Compare Dates An Find If Dates Are A Week Apart
Hi all,
i would like to compare 2 dates an do an action if they are a week apart. I tried quite a few things to no prevail.
dim date 1 as date
dim date 2 as date
if date1 ARE A WEEK APART date2 then
do some thing
do other thing

early reply would be appreciated.


All Records With Dates Less Than? How To
I am trying to pull all records with dates less than a given date here is the sql I have created

 strSQL = "INSERT INTO ReportTableTemp SELECT * FROM [Billinginfo Query] Where [JobId] = " & JobId & " And [JobDate] < " & MaxDateReport & ""

which generates the following statement

INSERT INTO ReportTableTemp SELECT * FROM [Billinginfo Query] Where [JobId] = 18 And [JobDate] < 11/19/2002

I have records in the table Billinginfo Query   with dates less than 11/19/2002  and these dates are stored in the same format as   '11/19/2002'

but the statement does not work, in other words it does not 'pull' any records out of the table



Records Between Dates
I have an Access 2000 database where a date is stored in a 10 character string "01-01-2006" I need to get the records that are between to ranges. I have tried the codes below and it will not work. I is ok if working with just one year (2005) but not a two year spread (Jan 2005 to Jan 2006)

"ReportDate Between '" & textbox1 & "' And '" & textbox2 & "' "

"ReportDate Between #" & textbox1 & "# And #" & textbox2 & "# "

"CDate(ReportDate) Between #" & textbox1 & "# And #" & textbox2 & "# "

Any ideas?

To Show The Records Between Two Dates

i have a table with one field(edate) is date(format -short date)type in access 2003 database- VB 6.0. i want to search in the table and display the records in between two dates. those dates are picked up from the DTPicker1 and DTPicker2 control. My sql stt., is

strsql="select * from mytable where edate between #" & DTPicker1.value & "# and #" & DTPicker2.value & "# ".

am not getting the exact records as i expecting. is anything wrong in this query. and even i tried >=dtpicker1.value and <=dtpicker2.value. This too not working


Selecting Records Between Two Dates
I want to select records between two dates from a table.

StartDate and EndDate are two Dates but the code below does not seem to work. I get an operator/operand type mismatch.

rstChkdisb.Open "select * from mytable where ck_date >= " & StartDate & " And ck_date <= " & EndDate & " ORDER BY ck_no", Cnn2, adOpenStatic, adLockReadOnly

Rif: Pull Records Between Certain Dates In DBF
Import DBF file in Access ( File - Load External Data - Import ) and then
use appropriate selection query.

If you need to use DBF commands :

Use Time.dbf

Copy to newDBFdatabase for FDDATE >= CTOD("Initial Date") .and. FDDATE<=

Please note :

1) Initial and End Date must be indicated in the format 99/99/99
(day/month/year if American month/day/year)

2) one point before and one point after it with no space

It is a long time since I used DBF files so I dont known if you will have
problems with 2000 years.

Michele Rossi

-------Messaggio originale-------


Data: marted=EC 08 aprile 2003 00.02.24

A: email@removed

Oggetto: [visualbasic-l] Pull Records between certain dates in DBF

Archive Page -

Hi All,

I Have database in DBF , how can i pull record between certain dates.

Field Name that contains dates for example FDDATE, Database Name Time.db

Pls Help



**************FREE* white paper from SPI Dynamics**************

"Outsmart the Top 10 Web Application Attacks!"

************************************************** *************



*Terms of Use:

*Copyright (c) ITtoolbox and message author. No redistribution.


Pull Records Between Certain Dates In DBF
Hi All,
I Have database in DBF , how can i pull record between certain dates.
Field Name that contains dates for example FDDATE, Database Name Time.dbf

Pls Help


My setup is VB6/Access2K.  I have a VB Form (called frmRENTPERIOD) with two txtBoxes 1 and 2 and a cmdButton1.  The user will enter a beginning date and an ending date in the txtBoxes.  Then when the cmdButton1 is clicked, I need to display a second Form that contains a DataGrid1 showing the filtered Records.

The DataGrid1 is connected via an Adodc1 to the Access Table.  The Table Records are rent payments received and I need the DataGrid to display just the Records between and including the dates the user enters (for example 09/01/04 and 09/30/04 would filter all the Records for the month of September.)  The Date Field from the Table is DatePymtRcvd.

The DataGrid is on a different VB Form (called frmPAYMENTS_FROM_TO) than the Form where the user enters the dates.

I would appreciate any suggestions on accomplishing the above.  It's for my monthly rent roll report.

Query Records Between 2 Dates
Can someone plz, solve my query problem,

1. query to select all the records between 2 dates, using VB6.0 and ms-access.

2. and also all the records before a specific Date.

Thanks in advance,

Problem Retrieving Records Between Dates.
I have a Table in MS Access with a field of following type:

CustDate (Text)

It stores date in DD-MMM-YYYY format.

Now I want to search records between two dates, but the problem is that I declared the field as Text type.

I want to execute following query:

set rs = cn.Execute("Select * from Cust where CustDate between '" & Text1.Text & "' and '" & Text2.Text & "'"
But it is not displaying records. How to retrieve records when I have declared the field as DATE Type

Unable To Filter Records B/w To Dates
Hi friends followings are my code to filter records b/w two dates, i had tried a lot but could't succeed, please check what went wrong. Back end is SQL server 2000.

VB Code:
.Open "select i.description,t.description as TransDes, " & _        "p.partyname,t.rano,t.strano,t.pono,t.trdate,t.documentno, " & _        "t.recqty,pr.orderqty,i.unit,t.rate,t.gst,(t.rate*t.gst/100)+t.rate as GstRate,pr.rate as PoRate, " & _        "pr.gstrate as IncGst,t.transid,t.itemid,p.accountid " & _        "FROM ((trans t LEFT JOIN purchaseorder pr " & _        "ON (t.itemid = pr.itemid " & _        "AND t.pono = pr.pono " & _        "AND t.accountid = pr.accountid)) " & _        "INNER JOIN invmaster i ON t.itemid = i.itemid) " & _        "INNER JOIN parties p ON p.accountid = t.accountid " & _        "WHERE t.rate <> pr.rate or (t.recqty > pr.orderqty) or (t.rate = 0) " & _        "AND t.recqty > 0 and pr.orderqty> null " & _        "AND t.confirmrate = 0 and t.sentforapp = 0 " & _        "AND t.trdate >= convert(smalldatetime,'" & DateFrom.Value & "',103) " & _        "AND t.trdate <= convert(smalldatetime,'" & DateTo.Value & "',103) "

Problem Retrieving Records Between Dates.
I have a Table in MS Access with a field of following type:

CustDate (Text)

It stores date in DD-MMM-YYYY format.

Now I want to search records between two dates, but the problem is that I declared the field as Text type.

I want to execute following query:

set rs = cn.Execute("Select * from Cust where CustDate between '" & Text1.Text & "' and '" & Text2.Text & "'"

But it is not displaying records. How to retrieve records when I have declared the field as DATE Type

Selecting Records In Msacces Between 2 Dates

i need to select records between 2 dates from a table called tblVaccinatie with columns :
- VaccinatieId
- VacDatum
- VacProdukt
- PatId

i am using this code now, but it isn't working

strVaccinatie = "Select * from tblVaccinatie where VacDatum between #" & _
dteVan & "#" & " and " & "#" & dteTot & "#"

dteVan and dteTot are the values of two datepicker controls

if u need more info to solve my problem, please tell me what is lacking

thnx, Koen

How To Display Selected (between Two Dates) Records In Crystal Report.
Hello Sir,

I am new to crystal report section.

I want to know how to display selected (between two dates) records in crystal report.

If user wants to print records between two selected dates (dates selected by DTPicker1 component)

I tried several times to solve the problem. But in crystal report only few fields are displaying.

So please give the suggestion step by step.

Software used:
Front End: VB5
Back End: MS Access 97
Crystal Report: 4.6

Find Whether A Particular Day Is Available Between Two Given Dates
I want a function such that it should be able to give me whether its present between two given dates.

For example i wish to find whether 29 may 2002 is between 2/mar/2002 and 08/jun/2002 .
It should return true
if i give 05 dec 2002 for the above dates it should return false.
Please help me out.

Rs.find Between Dates

I'm updating a project I wrote a few years ago. It's a fairly simple database that keeps track of bookings for a customer. It uses an access database and the user can add/search the records etc.

I now need to use a listview to display all the bookings between 2 dates.

I've been using the following code to find bookings on a single date:


Private Sub cmdSearch_Click()

Set cn = New ADODB.Connection
cn.ConnectionString = "Provider=Microsoft.Jet.OLEDB.4.0;" & _
"Data Source= c:program files abbyhiredatabase.mdb"
Set rs = New ADODB.Recordset
rs.Open "bookings", cn, adOpenKeyset, adLockPessimistic, adCmdTable


rs.Find "bookingstartdate = '39405'"

If (rs.BOF = True Or rs.EOF = True) Then
MsgBox ("not found")


MsgBox ("found it")

End If

End Sub

Can i do something like: rs.find between "bookingstartdate = '39407'" and "bookingstartdate = '39405'" ??

I think i may need to go down the SQL route, but I don't really have time to re-write the rest of the original code as the client needs the update quickly.

Any ideas please? thanks, Pete

Find How Many Weekdays Between Two Dates In Vb

I want to be able to enter a date range on a form or in a query. calculate how many week days (monday - friday) fall within that range. Any ideas?

Please help me?

Thank you

Find Method And Dates
Good Afternoon,
In my spreadsheet I have multiple columns which hold information for each row, which is a "sample". I have created a UserForm in which to be able to search for a "sample" knowing any combination of data in the columns. Say I know column A and C but not the rest, I wish to search for the row which matches what I know. One of these columns is for the date and time of the "sample". I use the find method to search through the particular column and coerce the value of the textbox (to a date) before I use the find method. However the find method never works for dates. I don't understand why it doesn't is anyone aware of any problems associated with using .Find with dates and time???


Find Weekday Between 2 Dates
I have 2 dtpickers on my form, dtpicker1 and dtpicker2 respectively.

suppose dtpicker1 having value '15/01/2008' and

dtpicker2 having value '16/02/2008'

now i want to calculate how many sundays are exist between these 2 dates.

thanks in advance

Find Dates In The Same Week
How do I write a procedure that find all the dates in a field from a table in a Access database that are the same week as today's date, or a selected date. Format is dd/mm/yy.
Please help, Thanks a million

How To Find Difference Between Two Dates Using Vb6
I need to find difference between two dates which one is current date and another one which i am accessing from msaccess database.
I tried my coding is
dim a,b as date
a=Format(Date$, "dd/mm/yyyy")
msgbox datediff("d", a,b)
msgbox a-b
Please help me in coding.
Thanks in advance,

To Find The Last Dates Of 12 Months..
to display the enddates of 12 months
the startdate can be varied

yr-user input for year
For j& = 1 To 12
                startdate(j&) = DateSerial(yr, j&, 1)
                enddate(j&) = DateSerial(yr, j&, Day(DateSerial(Year(startdate(j&)), Month(startdate(j&)) + 1, 1) - 1))
Next j&    

Vb Coding To Find Between Dates
Access database:

I have two fields in a table Loan start and loan end, each of these is in a date format, I need a piece of vb coding that can be used in a command button to check that when u enter a loan date and a end date that it is not between any other records, that has currently been saved.

So far i have been trying the findfirst command but i am getting nowhere with it can anyone help please???

Coding im using at the moment:

Private Sub Command6_Click()
    Dim rs As Object
    Set rs = Me.Recordset.Clone
    rs.FindFirst "[LoanStart] = between #" & Me![LoanStart] & "# And #" & Me![LoanEnd] & "#"
    If rs.NoMatch Then
      MsgBox "You can book this"
      Exit Sub
      Me.Bookmark = rs.Bookmark
      MsgBox "Dates have already been taken sorry"
    End If
    End Sub

Problem With Find Command & Dates
I have a database in Excel that is listed by Date. At the top of the database, I have two cells designated for the user to enter a start date and end date. Then a button where they can graph all the data in the database between these two dates. The code I'm using seems to work fine for ranges up to about a year or year and a half. Past that it messes up that EndDate value. Here is my code:

StartDate = Blad8.Range("P11").Value
EndDate = Blad8.Range("P13").Value

Sheet3.Range("C24").Value = "0"
Sheet3.Range("C25").Value = "0"
For DaDate = StartDate To EndDate
With Range("C21:C" & firstemptyrow)
Set R = .Find(DaDate, LookIn:=xlValues)
If Not R Is Nothing Then
FindAddress = R.Address
Sheet3.Range("C24").Value = FindAddress

Sheet3.Range("C25").Value = Sheet3.Range("c25").Value + 1
Set R = .FindNext(R)
Loop While Not R Is Nothing And R.Address <> FindAddress
End If
End With
Next DaDate
Set R = Nothing

begdate = "R" & Sheet3.Range("D24").Value & "C3"
lastdate = "R" & Sheet3.Range("D25").Value & "C3"
daterange = "='Dates'!" & begdate & ":" & lastdate
I have it set up so that every time it finds an entry with a date between the dates selected by the user, it will add "1" to a cell in another sheet. This lets me know how many entries I have total. Also, it is set so that it starts at the bottom and counts up from the database, and writes the last cell it found a match in to another cell in another spreadsheet. So the final output is two cells in another sheet, one that looks like "$C$35" (the location of the first entry with a matching date) for example, and one that looks like "36" (the number of total entries). Then I have another cell (sheet3.range("D24")) that takes the $C$35 and only reports the numbers after the second $. And another cell ("D25") that adds that number to the total number of entries minus 1. This number should be an effective range for the date data. However, over long time periods it ends up counting too many cells. It gets the EndDate right every time, but overshoots the StartDate. Any ideas on how to fix this or make it less complicated would be appreciated!

Find Dates In Access 2000 Using VB 6
This could be a date problem or my inability to use .EOF or .BOF correctly.

I have an Access 2000 database with two tables - DataA and DataB

DataA will have approx. 20 records of student's names, address, etc. The students are given passwords to clock in & out of the classroom.

DataB consists of a field named "Check_in_Date", "Check_in_Time", "Check_out_time", etc. Check_in_Date is formatted for "Short Date".

The transaction I want to occur is this: On my VB Form I have a command button that reads "Start database". Upon loading, I want VB to check within DataB to see if the system date is in this table. If it's not, this button will be enabled. Once this is clicked, my program runs an append query, perfectly (don't have probems with this). If the append action HAS taken place (as I only want this to happend once per day), the command button will be disabled. The scenario I'm trying to build is similar to a "Daily Labor Exception Report", i.e., all the students are to report everyday Mon thru Fri. This program will track those students who are habitually late, no-shows, etc.

Please review the code that I've come up with so far. I'm pretty sure I'm setting up the date correctly for the .Find (i.e., I'm using the #'s) but for the life of me, this code in NOT finding the date. was so much easier under DAO.....

Private Sub Form_Load()

' Center the form
Me.Move (Screen.Width - frmTimeClock.Width) 2, (Screen.Height - frmTimeClock.Height) 2

Set cn = New adodb.Connection
cn.ConnectionString = "Provider=Microsoft.Jet.OLEDB.4.0;" & _
  "Data Source = c:My Documentscheckin2000.mdb" 'connecting to database

Set MyRstA = New adodb.Recordset 'DataA
Set MyRstB = New adodb.Recordset 'DataB

MyRstB.Open "DataB", cn, adOpenKeyset, adLockPessimistic, adCmdTable 'opening recordset
MyRstA.Open "DataA", cn, adOpenKeyset, adLockPessimistic, adCmdTable 'opening recordset

Today = Date
MyCriteria = "[Check_in_Date] = #" & Today & "#"
With MyRstB
        .Find MyCriteria
        'To see if a recordset is empty, you need to test for both bof (begining of file) and eof (end of file).

If MyRstB.BOF And MyRstB.EOF Then
'date not found(?)
        cmdStart.Enabled = True

'date found
        cmdStart.Enabled = False

End If

End With
End Sub

Subtracting Dates To Find Out A Price! This May B Tricky
I have 2 date variables which holds one start date e.g "17/02/2004" and another end date e.g "17/03/2004" and another currency variable which has the value of 50.

now the situation is the 50 stands for £50 which is meant to represent a price for a hotel room for one night.

What i need to do is calculate the total price by multiplying 50 * no of nights. but the problem is i dont know how to subtract the end date from the start date to find out the no of days difference

does n e 1 know how to do this????

FindFirst Doesnt Find Dates Ealier Than The 12th Day Of The Month!!
I have a MS Access database that contains tables with dates in them, I connect to the database through the regural Data control. The problem is when I try to use the FindFirst fuction it finds dates that are after the 12th day of month but doesnt find dates that are ealier than the 12th day of month eventhough that dates are there in the tables

These are the codes I'm using


dbJournal.Recordset.FindFirst "Date = " & "#23/07/2006#"

This will find the date just find and move to the recordset that contains the date

dbJournal.Recordset.FindFirst "Date = " & "#12/07/2006#"

This wont find anything eventhough there is a recordset with the date 12/07/2006

Any ideas on how to solve this problem??

Thank you so much

Find Records In VB6 Using DAO
good day,

i'm a newbie practicing vb6

can someone help me teach or post some codes to find record
for example : name starts with a

any help would be appreciated


Find Records With ADO

I have one problem, that i want to find one record and move to that record.
1  jonh
2  smith
3  phil
4  sam
in my display i have the record 4
4 jam
and i would like to find the record 2
2 smith

The problem is that if i do a sql query, then when i do next and previous i canĀ“t start from the record 2.
I just have tried the find but i can't put that working.

If there is someone that could help me i apreciate.

Thanks any way.

P.S.-Sorry my English.

Trying To Find Number Of Records.....
i'm using a access database and i'm trying to find out how many records are in a table using vb. does anyone know how i would go on about doing this??

Search Or Find Records
can anybody help me. what i want to know is how i can search for records in a recordset without requesting a new recordset from interbase to populate a combo or a listview.

Find Records With The Same Name In A Recordset
I have a recordset where I need to search for a specific name, and the code I have for that is this:
.Find "M_Nome LIKE '%" & Procura & "%'"

But this only retrieves the first record with that name, but exists more names like that in the recordset.
How can I display all the records with that name or else make something like the F3 to find the next record?

Thanks in advance.

.Find - What If No Records Found?
Hey everyone, simple question.

I'm using the .FIND to try and find records within an ADO recordset.

What I need to say is "If a record is found, do this, else do that".
However, I don't know how to tell if a record is NOT found. What would be the code to say something like....

If the .Find doesn't find a match then
End if

Find Out How Many File-records
Hi Guys,
In VB 4.0, how do I find out how many records there are in a file(type random)?
Some hep would be appreciated.

Using A Filter To Find Records Using ADO
I am using VB6 to connect to an Access 2000 database table. The table - "STMASTER" has a numeric field called "Amount". I want to use a filter to find all records which have "100" in the Amount field i.e. 100, 1000, 2100,3100 etc. The following does not seem to work.

Assuming rs is a recorset object, and the rs RecordSource is STMASTER, rs.filter = "LIKE '%100%' does not work. I get a "Filter cannot be opened" message. The same construct works fine when used in a WHERE condition of an SQL statement.

LIKE '100' works fine and throws up all records with "100" in the Amount field but not 1000, 2100, 3100 etc.

What gives?


Find Records Between 2 Times

I need some help with a problem I am having. I am using VB6 with a MS Access database.
The field in the table I need to search on i a date/time field with the format of DD-MMM-YYYY hh:mm

I need to select all the records in my temp table where the day is Monday, Tuesday etc
In between this, there are 4 time periods:

1. 12:01 AM to 7:00AM
2. 7:01 AM to 11:59 PM
3. 12:00 PM to 17:00 PM
4. 17:00 PM to 22:00 PM

The statement I am using to run through the loop to get all the Monday, Tuesday, etc records is

FROM tmp_12SKU
WHERE WEEKDAY(Transaction_Date)= 1

When I try to get all the records between 1 of the time periods, it returns no records. I've tried
1. AND Transaction_Date>#7:01 AM# And Transaction_Date<#11:59 AM#
2. AND Transaction_Date BETWEEN #7:01 AM# AND#11:59 AM#

And a whole lot of other variations which don't work. Any help with either the correct SQL statement or where to find a possible answer would be much appreciated. Thanks.

Find Total No. Of Rows With Records
How do i check the no. of rows that contains records in excel using VB?

Find Table1 Records Not In Table2 [HELP!]
I have 2 tables in Access. I'm using VB and DAO to get them.
I want to find a set of records that aren't in the 2nd table.

Here is an example of returned records, and what I'd like to do:

OwnerNum = 7A
OwnerNum = 7B
OwnerNum = 7C

OwnNum = 7C

The relationship between tblOwners and tblReceipt is one-to-many, because 1 owner could have many receipts.

I want a set of records, from tblOwners, that are NOT in tblReceipt. in other words, if an Owner has never received a receipt, i want that group.

I've tried the following:

SELECT DISTINCT [tblReceipt].[OwnNum], [tblOwners].[OwnerNum]
FROM tblOwners INNER JOIN tblReceipt ON [tblOwners].[OwnerNum]=[tblReceipt].[OwnNum]
WHERE [tblOwners].[OwnerNum]=[tblReceipt].[OwnNum];

This returns all the owners that are IN tblReceipt.

Is there a way to return all owners that are NOT IN tblReceipt?

Many thanks,

Find Count Of Records And Insert?

How can I find the count of records in a table and then insert that value into a record in a different table?


Find Records That Aren't In Table

i've a table full of customers and a table of orders. They are linked by customerID.

How can i find which customers have no order records with an SQL query?

Many thanks
Nick Swan

Find Records Access Style
I am looking a for a solution for my application that would find records as it is in Office applications for the find command. I cannot seem to find a control that does this.

Anyone know?

Find Records Older Than A Certain Date?
I need to pull up a recordset with records older than 30 days from today's date. How do I do that?

here's my sql so far:

sql2 = "SELECT TOP 10 URL, SubDate, Submitted FROM Sites WHERE Sites.SubDate < '" & strDat + 30 & "' AND Sites.Submitted = '" & strNoSubmit & "' ORDER BY Category"


Find Records Based On Date
Given two dates, start_date and end_date.
How can I retrieve all records from the database, a create_date attribute is specified in the table, that are within the given date interval?

start_date = "8/13/1999"
end_date = "11/12/1999"

records to retrieve should be in the range of the two given date.


Find Records Based On Date
Given two dates, start_date and end_date.
How can I retrieve all records from the database, a create_date attribute is specified in the table, that are within the given date interval?

start_date = "8/13/1999"
end_date = "11/12/1999"

records to retrieve should be in the range of the two given date.


Query To Find Intermediate Records
Dear all,

I need a solution to find intermediate records in a table.

i.e. A table consists of 1 Lakh records.

I need to write a Query to find intermediate records (i need
100-110th record in the table).

Usage of Primary key, Id is not allowed.
(i.e Select * from <TableName> where id between 100 and 110)

Using any SQL functions, it should be written.

Expecting a reply,



ADODB How To Find And Update Records
I have a visual basic 6 application that saved data in the wrong Access fields.
Ex. Table: Expenses
    form field "personal" saved in Expenses.Business
    form field "business" saved in Expenses.Personal

I need to create an executable to send to customers to correct this problem.  I read that RecordSets and Cursors shouldn't be used (multi-user concerns).  How can I do this?  (ADODB)

I thought I would do the following but don't know how to code it.
-query table for records where personal or business are not blank
-save data into temporary variable
-update correct field
-loop thru the rest of the records
Thanks for the help.

Find Records In Non Primary Keys.
Hi, Im havng a problem with trying to search for a record.

I know that Recordset.rows.find only works for if your searching in a primary key, but im not and i need to know if there is a way i could search out side of the primary key and in non primary key coloums.

Hope someone can help.


Help! How Do You Find Out How Many Records A Access DB Table Has?
I need some help on finding out, how to find out the number of records a specific Table in an Access Database has

Help - SQL LIKE Condition With Wildcards Can't Find Records
Hi all. I am writing a program where it will search a column in the DB by last name only. The column itself has both first name & last name combined, so I am using the LIKE condition in my SQL as follows:

ls_sql = "SELECT Name FROM NameColumn WHERE Name LIKE '%" & ls_LastName & "'"

I get no errors and it always finds 0 records when I look at RecordCount.

Can someone please tell me what else I am missing? Thanks!

Copyright © 2005-08, All rights reserved