Date WHERE & RecordCount

[scott]

My table called "tblData" contains a datetime field called [dtDate]. This is
a sql table. I'm trying to pass a string called "DateString" as the WHERE
condition in my sql statement and return the record count.

The "DateString" variable will always look like 20060509 which stands for
the date 5/9/2006. I've tried 2 different ways in CODE 1 and CODE 2, but
both methods return 1150, the total records in the "tblData" table.

How can I "convert" a string like "20060509" into a date so I can use it as
a date in my where clause? Do I need a delimiter?


CODE 1 ***********

Dim objRS2 As ADODB.Recordset, dtDate As Date, DateString As String
DateString = "20060509"
dtDate = CDate(DateSerial(Val(Left(DateString, 4)), Val(Mid(DateString,
5, 2)), Val(Right(DateString, 2))))


CODE 2 ***********

Dim objRS2 As ADODB.Recordset, dtDate As Date, DateString As String
DateString = "2006-05-09"
dtDate = CDate(DateString)


MAIN CODE *********** (below code works right)

Set objRS2 = New ADODB.Recordset
objRS2.ActiveConnection = CurrentProject.Connection
objRS2.CursorType = adOpenStatic
objRS2.Open "SELECT * from tbldata WHERE dtDate >=" & dtDate

If objRS2.RecordCount > 0 Then
MsgBox "Records Exist: " & objRS2.RecordCount
Else
MsgBox "Records DON'T Exist: " & objRS2.RecordCount
End If

 

[John Nurick]

Hi Scott,

No need to use CDate() or DateSerial() here. Try something like this:

DateString = "2006-05-09" 'or US-style "09/05/2006"
objRS2.Open "SELECT * from tbldata WHERE dtDate >= # _
& DateString & "#;" & dtDate

My table called "tblData" contains a datetime field called [dtDate]. This is
a sql table. I'm trying to pass a string called "DateString" as the WHERE
condition in my sql statement and return the record count.

The "DateString" variable will always look like 20060509 which stands for
the date 5/9/2006. I've tried 2 different ways in CODE 1 and CODE 2, but
both methods return 1150, the total records in the "tblData" table.

How can I "convert" a string like "20060509" into a date so I can use it as
a date in my where clause? Do I need a delimiter?


CODE 1 ***********

Dim objRS2 As ADODB.Recordset, dtDate As Date, DateString As String
DateString = "20060509"
dtDate = CDate(DateSerial(Val(Left(DateString, 4)), Val(Mid(DateString,
5, 2)), Val(Right(DateString, 2))))


CODE 2 ***********

Dim objRS2 As ADODB.Recordset, dtDate As Date, DateString As String
DateString = "2006-05-09"
dtDate = CDate(DateString)


MAIN CODE *********** (below code works right)

Set objRS2 = New ADODB.Recordset
objRS2.ActiveConnection = CurrentProject.Connection
objRS2.CursorType = adOpenStatic
objRS2.Open "SELECT * from tbldata WHERE dtDate >=" & dtDate

If objRS2.RecordCount > 0 Then
MsgBox "Records Exist: " & objRS2.RecordCount
Else
MsgBox "Records DON'T Exist: " & objRS2.RecordCount
End If

 

[Douglas J Steele]

I think you may have mistyped there, John.

You seem to have forgotten the closing quote after the first #, and I have
no idea why you've appended "& dtDate" at the end.

Surely you meant:

DateString = "2006-05-09" 'or US-style "09/05/2006"
objRS2.Open "SELECT * from tbldata WHERE dtDate >= #" _
& DateString & "#;"

(the closing semi-colon is optional, of course, but I left it in for you!)

And just for the record (since others may be reading this in Google years
from now...), it is NOT possible to use dd/mm/yyyy format rather than the
US-style mm/dd/yyyy format.

--
Doug Steele, Microsoft Access MVP

(no e-mails, please!)

Hi Scott,

No need to use CDate() or DateSerial() here. Try something like this:

DateString = "2006-05-09" 'or US-style "09/05/2006"
objRS2.Open "SELECT * from tbldata WHERE dtDate >= # _
& DateString & "#;" & dtDate

My table called "tblData" contains a datetime field called [dtDate]. This is
a sql table. I'm trying to pass a string called "DateString" as the WHERE
condition in my sql statement and return the record count.

The "DateString" variable will always look like 20060509 which stands for
the date 5/9/2006. I've tried 2 different ways in CODE 1 and CODE 2, but
both methods return 1150, the total records in the "tblData" table.

How can I "convert" a string like "20060509" into a date so I can use it as
a date in my where clause? Do I need a delimiter?


CODE 1 ***********

Dim objRS2 As ADODB.Recordset, dtDate As Date, DateString As String
DateString = "20060509"
dtDate = CDate(DateSerial(Val(Left(DateString, 4)), Val(Mid(DateString,
5, 2)), Val(Right(DateString, 2))))


CODE 2 ***********

Dim objRS2 As ADODB.Recordset, dtDate As Date, DateString As String
DateString = "2006-05-09"
dtDate = CDate(DateString)


MAIN CODE *********** (below code works right)

Set objRS2 = New ADODB.Recordset
objRS2.ActiveConnection = CurrentProject.Connection
objRS2.CursorType = adOpenStatic
objRS2.Open "SELECT * from tbldata WHERE dtDate >=" & dtDate

If objRS2.RecordCount > 0 Then
MsgBox "Records Exist: " & objRS2.RecordCount
Else
MsgBox "Records DON'T Exist: " & objRS2.RecordCount
End If

 

[Douglas J Steele]

As an alternative to what John's suggested, if your string is 20060509, you
can use the Format function to add the dashes (and the # delimiters too, for
that matter):

Format(DateString, "\#0000-00-00\#")


DateString = "20060509"
objRS2.Open "SELECT * from tbldata WHERE dtDate >= " & _
Format(DateString, "\#0000-00-00\#")

 

[Scott]

thanks guys.

As an alternative to what John's suggested, if your string is 20060509,
you
can use the Format function to add the dashes (and the # delimiters too,
for
that matter):

Format(DateString, "\#0000-00-00\#")


DateString = "20060509"
objRS2.Open "SELECT * from tbldata WHERE dtDate >= " & _
Format(DateString, "\#0000-00-00\#")

--
Doug Steele, Microsoft Access MVP

(no e-mails, please!)

My table called "tblData" contains a datetime field called [dtDate]. This is
a sql table. I'm trying to pass a string called "DateString" as the WHERE
condition in my sql statement and return the record count.

The "DateString" variable will always look like 20060509 which stands for
the date 5/9/2006. I've tried 2 different ways in CODE 1 and CODE 2, but
both methods return 1150, the total records in the "tblData" table.

How can I "convert" a string like "20060509" into a date so I can use it as
a date in my where clause? Do I need a delimiter?


CODE 1 ***********

Dim objRS2 As ADODB.Recordset, dtDate As Date, DateString As String
DateString = "20060509"
dtDate = CDate(DateSerial(Val(Left(DateString, 4)), Val(Mid(DateString,
5, 2)), Val(Right(DateString, 2))))


CODE 2 ***********

Dim objRS2 As ADODB.Recordset, dtDate As Date, DateString As String
DateString = "2006-05-09"
dtDate = CDate(DateString)


MAIN CODE *********** (below code works right)

Set objRS2 = New ADODB.Recordset
objRS2.ActiveConnection = CurrentProject.Connection
objRS2.CursorType = adOpenStatic
objRS2.Open "SELECT * from tbldata WHERE dtDate >=" & dtDate

If objRS2.RecordCount > 0 Then
MsgBox "Records Exist: " & objRS2.RecordCount
Else
MsgBox "Records DON'T Exist: " & objRS2.RecordCount
End If

 

[John Nurick]

Thanks for picking that up, Doug.

I think you may have mistyped there, John.

You seem to have forgotten the closing quote after the first #, and I have
no idea why you've appended "& dtDate" at the end.

Surely you meant:

DateString = "2006-05-09" 'or US-style "09/05/2006"
objRS2.Open "SELECT * from tbldata WHERE dtDate >= #" _
& DateString & "#;"

(the closing semi-colon is optional, of course, but I left it in for you!)

And just for the record (since others may be reading this in Google years
from now...), it is NOT possible to use dd/mm/yyyy format rather than the
US-style mm/dd/yyyy format.