How do I retrieve records using a filter for between two dates

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

I have a datetime field in my database(SQL Server) and I need to retrieve
records for between two dates using a filter query in my visual basic code?
What is the exact syntax to do this because it depends on what the user
enters in for a date to be like the records. How do I create a between and a
like in the filter or is that possible? Thanks.
 
Hi,
so if you have a textboxes with dates like text1 and text2 then where
portion of pass-through query to SQL server will look like:

"Where MyDateField between '" & format(me.textbox1,"yyyymmdd") & "' and '" &
format(me.textbox1,"yyyymmdd") & "'"

so dates should be in 'yyyymmdd' format
 
I have a datetime field in my database(SQL Server) and I need to
retrieve records for between two dates using a filter query in my
visual basic code?
Click to expand...

' dates from the user
Dim dateLower as Date
Dim dateUpper as Date

' a sql command and database objects: this is all
' DAO and Jet code. You need to change things for ADO
' or ODBC etc etc.
Dim jetSQL as String
Dim db as DAO.Database
Dim rst as DAO.Recordset

' this one is _not_ regionally-negotiable!!
Const fmtJetDate as String = "\#yyyy\-mm\-dd\#"

' you may have some other method of getting the
' limit dates
dateLower = GetDateFromUser("Start")
dateUpper = GetDateFromUser("Final ending date")

' Create the inline SQL command. Note correct method
' of getting date intervals depends on the values to be
' found. This will FAIL if the SomeDate field any time
' values.
jetSQL = "SELECT ALL Something " & vbNewLine & _
"FROM SomeTable " & vbNewLine & _
"WHERE " & Format(dateLower, fmtJetDate) & "<= SomeDate " & _
" AND SomeDate <= " & Format(dateUpper, fmtJetDate)" & vbNewLine & _
"ORDER BY Something ASC"

' always get to see what a mess you have made of the
' command, until you know it's right
Debug.Assert vbYes=MsgBox(jetSQL, vbYesNo, "Is this OK?")

' now you can pass the thing off to the database
Set db = CurrentDB()
Set rst = db.OpenRecordset(jetSQL, dbOpenSnapshot, dbForwardOnly)

' and do something with the recordset
Do While Not rst.EOF
Debug.Print rst!Something
rst.MoveNext
Loop

' tidy up
rst.Close

Hope that helps


Tim F
 
Thanks for your help.
This is some of the code that I'm trying to do
sqlquery= sqlquery+ " [DateField] Between '" & Format(start, "mmddyyyy") & "
' and ' " & Format(end, "mmddyyyy") & " ' "
start - is the value coming from the text field in the form and end is the
other value.
The user would just enter like 08/16/05 but the field in the database is
8/16/2005 1:10:00PM
I'm using this filter with creating a report so I need to it to get
everything between the two dates but my database field type is date time.
Any more suggestions?

Thanks.
 
sqlquery= sqlquery+ " [DateField] Between '" & Format(start,
"mmddyyyy") & " ' and ' " & Format(end, "mmddyyyy") & " ' "
Click to expand...

Okay: my bad... I missed your reference to SQL Server and I gave you the
Jet date format. According to my documents, the SQL date format is

'yyyymmdd'

which is only logical seeing as it's going to be a text comparison.

Another minor point is the use of + as a concatenation operator instead
of & -- it's legal but can have undesired consequences if there are any
nulls in the area.

Another minor point is that End is a reserved word in VBA (and most
basic-like languages) and is a Bad Thing to use for a variable.

Try this:

Const sqlDateFmt = "\'yyyymmdd\'"

sqlQuery = sqlQuery & vbNewLine & _
"WHERE "Format(startDate, sqlDateFmt) & " <= DateField " & _
" AND DateField <= " & Format(endDate, sqlDateFmt)

' don't forget this line!!
MsgBox sqlQuery



Hope that helps


Tim F
 
Back
Top