sql statement

[Zach Ahmed]

Hi:
Can someone please tell me what is I am doing wrong with this statement. The statement is returning 0 and I know there are at least 100 records in my table.


I think the problem is with the date. I am not be converting the date to its proper datatype.

lcsql = "Select * from TaxName Where Meeting_ID= " & _
Chr(39) & Trim(Str(iMeeting_ID)) & Chr(39) & _
" And Event_Date =" & "02/02/2004"


oRecData.Open lcsql, oDataConn
x= oRecData.RecordCount

[RobDog888]

Probably because you are not specifing the cursor type and lock
type. The default cursor type is FowardOnly when it is not
specified. This will make the RecordCount property reflect a -1
because the RecordCount property is not supported with a
FowardOnly cursor type. Change to Keyset or Static.
To test for records with any type of cursor, use this...

oRecData.Open lcsql, oDataConn, adOpenKeyset, adLockOptimistic, adCmdText
If oRecData.BOF = False and oRecData.EOF = False Then
    'Do record manipulation or ???
Else
    MsgBox "No Records Returned!", vbOkOnly+vbInformation, App.ProductName
End If

[Zach Ahmed]

The statement below does return records if I do not include date. For example:

lcsql = "Select * from TaxName Where Meeting_ID= " & _
Chr(39) & Trim(Str(iMeeting_ID)) & Chr(39)

The problem is when I include the date. Why does it not like the date?


lcsql = "Select * from TaxName Where Meeting_ID= " & _
Chr(39) & Trim(Str(iMeeting_ID)) & Chr(39) & _
" And Event_Date =" & "02/02/2004"

Thanks

[Zach Ahmed]

Thanks all:
I figured out what was the issue. I needed to add extra digit if the month value 2 needed to be (02).

Thanks again

[RobDog888]

What is the field definition for Event_Date?
If it is text then enclose the date with apostrophies. If it is
formatted as a Date/Time filed, then try adding the pound
character around your date value.

lcsql = "Select * from TaxName Where Meeting_ID= " & _
Chr(39) & Trim(Str(iMeeting_ID)) & Chr(39) & _
" And Event_Date ='02/02/2004'"
 
'OR
 
lcsql = "Select * from TaxName Where Meeting_ID= " & _
Chr(39) & Trim(Str(iMeeting_ID)) & Chr(39) & _
" And Event_Date =#02/02/2004#"
 
oRecData.Open lcsql, oDataConn, adOpenKeyset, adLockOptimistic, adCmdText
If oRecData.BOF = False and oRecData.EOF = False Then
    'Do record manipulation or ???
Else
    MsgBox "No Records Returned!", vbOkOnly+vbInformation, App.ProductName
End If