Building Select Statement with Dates

[marnieg]

I have a form that has many fields and when the user enters data and tabs off the field a select statement is dynamically built to query the table(s). I have one problem where I have a date range the user can enter, and the fields on the screen are of type date. They are going against a column in the table that is of date/time. The column contains some data that if manually entered stores a short date, but in some cases it gets timestamped with the currrent datetime. Here is my dilemma. Lets say I have 4 records in this table with the following dates.

10/01/2009 09:10:25
10/01/2009
10/01/2009 12:12:34
10/01/2009

If the user enters 10/01/09 and 10/01/09 they only get the 2,4 records.
If they enter 10/01/09 and 10/02/09 they get all four records.

Here is my dynamic code for adding the date fields to my select statement in my vb code.

If Not IsNull(Me.SrchLastCDate1) And Not IsNull(Me.SrchLastCDate2) Then
'ChangeSearchType
Cond = Cond & " AND LeadContacts.ContactDate BETWEEN '" & Me.SrchLastCDate1 & "' AND '" & Me.SrchLastCDate2 & "'"
End If

The LeadContacts.ContactDate is the field in the database. the two Me. fields are on the screen. What do I need to add to make this work without user having to enter one future date. Rember this is a dynamic string that I'm building called "Cond" for my select. I have tried adding functions to the database column to just reference a short date, but keep getting compile and debug errors.

 

[pbaldy]

This type of thing works:

Between [Forms]![frmReportDates]![txtFromDate] And [Forms]![frmReportDates]![txtToDate] & " 23:59:59"

 

[marnieg]

This is not the syntax I need, remember I am coding a string statement to be added to my whole select statement.

If Not IsNull(Me.SrchLastCDate1) And Not IsNull(Me.SrchLastCDate2) Then
'ChangeSearchType
Cond = Cond & " AND LeadContacts.ContactDate BETWEEN '" & Me.SrchLastCDate1 & "' AND '" & Me.SrchLastCDate2 & "'"
End If

I tried adding the 23:59:59 like this:

If Not IsNull(Me.SrchLastCDate1) And Not IsNull(Me.SrchLastCDate2) Then
'ChangeSearchType
Cond = Cond & " AND LeadContacts.ContactDate BETWEEN '" & Me.SrchLastCDate1 & "' AND '" & Me.SrchLastCDate2 & "' 23:59:59"
End If

I get an Invalid SQL STatement error

 

[pbaldy]

In Access the date/time values need to be surrounded by #, not '. You'd also need it around the entire date/time value, not just the date portion as you have in the second value.

 

[marnieg]

This statement is working the way I had it for those records with no timestamp. I'm just trying to include those with a timestamp. Again remember I'm just building SQL statement here not executing it.

 

[raskew]

Hi -

Try playing with this:

? dateadd("s", -1, date()+1)
11/19/2009 11:59:59 PM

HTH - Bob

 

[pbaldy]

Are you passing this to SQL Server? Try

Cond = Cond & " AND LeadContacts.ContactDate BETWEEN '" & Me.SrchLastCDate1 & "' AND '" & Me.SrchLastCDate2 & " 23:59:59'"

 

[marnieg]

This is how I ended up fixing it.

If Not IsNull(Me.SrchLastCDate1) And Not IsNull(Me.SrchLastCDate2) Then
'ChangeSearchType
If Me.SrchLastCDate1 = Me.SrchLastCDate2 Then
Me.SrchLastCDate2 = Me.SrchLastCDate2 & " 23:59:59"
End If
Cond = Cond & " AND LeadContacts.ContactDate BETWEEN '" & Me.SrchLastCDate1 & "' AND '" & Me.SrchLastCDate2 & "'"
End If
If Not IsNull(Me.SrchLastCDate1) And Not IsNull(Me.SrchLastCDate2) Then
'ChangeSearchType
Cond = Cond & " AND LeadContacts.ContactDate.ToString('d') BETWEEN '" & Me.SrchLastCDate1 & "' AND '" & Me.SrchLastCDate2 & "'"
End If

Just added the code in the if to adjust the value of SrchLastCDate2

 

[marnieg]

Sorry wrong posted of code.

If Not IsNull(Me.SrchLastCDate1) And Not IsNull(Me.SrchLastCDate2) Then
'ChangeSearchType
If Me.SrchLastCDate1 = Me.SrchLastCDate2 Then
Me.SrchLastCDate2 = Me.SrchLastCDate2 & " 23:59:59"
End If
Cond = Cond & " AND LeadContacts.ContactDate BETWEEN '" & Me.SrchLastCDate1 & "' AND '" & Me.SrchLastCDate2 & "'"
End If

 

[pbaldy]

Actually I don't think you want the If/Then in there. If you search on dates of 10/1/09 through 10/31/09, you will miss records with a date of 10/31/09 that have a time component.

 

[marnieg]

Thanks for the reply. You are right I always want to add the 23:59:59 to the from date.

This forum always gets me to the solution I need.

Thanks