Search between two Dates in VB6.0?

[wwwwo]

I want to search a table for records between two dates and then print the result of the search. I can't proform the search using an SQL between statement and display.

 

[BobRodes]

Well, there ya go, you took my advice before I gave it! Why don't you post the code that isn't working, so we can get a look at it. Also, tell us what flavor of SQL you're using. Access, Oracle, SQL Server, MySQL???

Thanks,

Bob

 

[wwwwo]

This is code for search between two dates.I use Access 97 database.After compile its display syntax error in date in query expresion.When I go to debug error its display method 'Refresh' of object 'IAdodc' failed
'Declare Variables
Option Explicit

Dim dateFormat As String 'store dateformat value
'the value 0 is for the following format mm/dd/yyyy
'the value 1 is for the following format dd/mm/yyyy


Private Sub SearchbyDateTypeFunc()
'Declare Variables
Dim StartStg As String 'Required to store Start date value (the value from textbox txt_StartDate_mmddyyyy)
Dim EndStg As String 'Required to store end date value (the value from textbox txt_EndDate_mmddyyyy)
Dim SQLStg As String 'Required to store the SQL statement required to perform search

'Set StartStg variables to equal the value in textbox txt_StartDate_mmddyyyy
StartStg = txt_StartDate_mmddyyyy.Text
'Set EndStg variables to equal the value in textbox txt_EndDate_mmddyyyy
EndStg = txt_EndDate_mmddyyyy.Text

'Below is the SQL statement that needed to be perform which will allow you to search between two different dates.
'First we needed to select the fields we would like to perform a search on, & indicate which table they are from.
SQLStg = "select JobID, JobName, StartDate, FinishDate from TblJobDetails WHERE "
'Next we have to set the where condition is true
'What I'm doing here is selecting the startDate row from TblJobDetails where the condition Between StartStg and EndStgis true
SQLStg = SQLStg & "(((TblJobDetails.StartDate) Between # " + StartStg + " # And # " + EndStg + " #) "
SQLStg = SQLStg & " AND "
'What I'm doing here is selecting the FinishDate row from TblJobDetails where the condition Between StartStg and EndStgis true
SQLStg = SQLStg & "((TblJobDetails.FinishDate) Between # " + StartStg + " # And # " + EndStg + " #))"


lblHeading.Caption = "Projects Started between " & StartStg & " - " & EndStg

dcProjects.RecordSource = SQLStg
dcProjects.Refresh
LabRecordCount = dcProjects.Recordset.RecordCount & " Records Found" 'performs a conut of all records on screen and then displays it in a lable

End Sub


Private Sub Command1_Click()
SearchbyDateTypeFunc
End Sub


Private Sub Command2_Click()
'Declare Variables
'Here im just reloading the records
Dim StartStg As String, EndStg As String, SQLStg As String

SQLStg = "select JobID, JobName, StartDate, FinishDate from TblJobDetails "

dcProjects.RecordSource = SQLStg
dcProjects.Refresh

lblHeading.Caption = ""
End Sub


Private Sub Command3_Click()
End 'end application

End Sub


Private Sub Form_Load()
'on form load set date format value to 1 which Equals date format dd/mm/yyyy
dateFormat = 1

End Sub


Private Sub Form_MouseMove(Button As Integer, Shift As Integer, X As Single, Y As Single)
MonthView_txt_StartDate_mmddyyyy.Visible = False
MonthView_txt_EndDate_mmddyyyy.Visible = False

End Sub


Private Sub Option_DDMM_Click()
'the value 1 is for the following format dd/mm/yyyy
dateFormat = "1" 'set dateFormat to 1 which Equals date format dd/mm/yyyy

End Sub


Private Sub Option_MMDD_Click()
'the value 0 is for the following format mm/dd/yyyy
dateFormat = "0" 'set dateFormat to 1 which Equals date format mm/dd/yyyy

End Sub


Private Sub Timer1_Timer()
'Experimenting with the vb Timer control.
'I have used it to automatic set txt_StartDate_mmddyyyy.Text = DTPicker_StartDate
'And
'txt_EndDate_mmddyyyy.Text = DTPicker_EndDate

If dateFormat = "0" Then
'set txt_EndDate_mmddyyyy textbox to Equals the value displayed in MonthView_txt_StartDate_mmddyyyy
'and use the following date format mm/dd/yyyy
txt_EndDate_mmddyyyy.Text = Format(MonthView_txt_EndDate_mmddyyyy, "mm/dd/yyyy")

'set txt_StartDate_mmddyyyy textbox to Equals the value displayed in MonthView_txt_StartDate_mmddyyyy
'and use the following date format mm/dd/yyyy
txt_StartDate_mmddyyyy.Text = Format(MonthView_txt_StartDate_mmddyyyy, "mm/dd/yyyy")


Label2.Caption = "Current date format is mm/dd/yyyy"

ElseIf dateFormat = "1" Then
'set txt_EndDate_mmddyyyy textbox to Equals the value displayed in MonthView_txt_EndDate_mmddyyyy
'and use the following date format dd/mm/yyyy"
txt_EndDate_mmddyyyy.Text = Format(MonthView_txt_EndDate_mmddyyyy, "dd/mm/yyyy")

'set txt_StartDate_mmddyyyy textbox to Equals the value displayed in MonthView_txt_StartDate_mmddyyyy
'and use the following date format dd/mm/yyyy"
txt_StartDate_mmddyyyy.Text = Format(MonthView_txt_StartDate_mmddyyyy, "dd/mm/yyyy")

Label2.Caption = "Current date format is dd/mm/yyyy"

End If

'Display record count value in LabRecordCount
LabRecordCount = dcProjects.Recordset.RecordCount & " Records Found "
End Sub


Private Sub txt_EndDate_mmddyyyy_MouseMove(Button As Integer, Shift As Integer, X As Single, Y As Single)
'When mouse pointer goes over txt_EndDate_mmddyyyy textbox
'set MonthView_txt_EndDate_mmddyyyy visible to true
MonthView_txt_EndDate_mmddyyyy.Visible = True
End Sub


Private Sub txt_StartDate_mmddyyyy_MouseMove(Button As Integer, Shift As Integer, X As Single, Y As Single)
'When mouse pointer goes over txt_StartDate_mmddyyyy textbox
'set MonthView_txt_StartDate_mmddyyyy visible to true
MonthView_txt_StartDate_mmddyyyy.Visible = True
End Sub

 

[MarkSweetland]

lblHeading.Caption = "Projects Started between " & StartStg & " - " & EndStg

It would seem you logic is a bit off though. If the caption for this label is correct, the data you are trying to retrieve with the SQL statement would not return jobs that "started" within the date span, it would return jobs that started and finished within the date span.

When dealing with starting and ending dates, you have to look at all the possibilities:

[ul][li]The job could start and end within the date span (completed within the date span)[/li]
[li]The job start date could fall in the date span and the end date is outside the span (job started, but not complete before the end date)[/li]
[li]The job finish date could fall in the date span and the start date is before the span (job finished but not started within the date span)[/li]
[li]The job could have started before the start date and ended after the end date (spanned more time than the date span is looking)[/li]
[li]The job started and finished before the start date[/li]
[li]The job started and finished after the end date[/li]
[/ul]

Bear in mind that you may need to look at multiple possibilities in order to retrieve the correct data.

SELECT JobID, JobName, StartDate, FinishDate 
FROM TblJobDetails 
WHERE StartDate between [date1] and [date2]

Would return those jobs that were "started" in the date span identified by date1 and date2


Mark

"You guys pair up in groups of three, then line up in a circle."
- Bill Peterson, a Florida State football coach

 

[wwwwo]

I want to display the job could start and end within the date span (completed within the date span)

 

[vbSun]

Syntax error is because of the formatting issue. If you give the date search condition inside the '#', it is interpreted as default mm/dd/yyyy format. This can lead to confusion sometimes, and if you give #18/04/2005# in this string, it will give this Syntax error because 18 is an invalid month.

Try formatting it using this format string "\#mm\/dd\/yyyy\#" before concatenating with the SQL String.

------------------------------------------
The faulty interface lies between the chair and the keyboard.