SQL as recordsource problem - desp. need help
Private Sub mnuVNA_Click()
Dim SearchDate As Date
Dim VisitorCount As Integer
Dim ResultResponse As Integer
On Error GoTo Errhandler
SearchDate = InputBox("Enter the date to search from", "Visitor Number Analysis")
frmVPC.VPCdata.RecordSource = ""
frmVPC.VPCdata.RecordSource = "SELECT * FROM Prospect WHERE [DateEnquired] >=" & Str$(SearchDate)
VisitorCount = frmVPC.VPCdata.Recordset.RecordCount
ResultResponse = MsgBox(VisitorCount & " " & "visited during this period", vbOKOnly + vbInformation, "Visitor Number Analysis")
NoInput = MsgBox("An Error has occured, either you entered data of an incorrect format or none at all, please try again", vbOKOnly + vbExclamation, "Visitor Number Analysis")
Basically, an input box will appear and serve as input for a date variable. the date will then be used in an SQL. I am trying to display all the records in a table that have a date field (DateEnquired) = to or > than the search date. these records are then displayed on a DB grid control and the number of records present counted, folowed by the clearing of the grid. The data control uses the SQL to isolate the necessary records, displaying them in the grid.
What blows is that the SQL syntax is right, the data control accepts the SQL as its recordsource, the input date is recognised etc, but wen i count the records it just gives me the number of records in the whole table (thus it displays all records in the gird, so the SQL retrieves all records) and not the number of records satisfying the condition. Ive used refresh after setting the recordsource, i just dont know wot to do and really need help ASAP
Top DevX Stories
Easy Web Services with SQL Server 2005 HTTP Endpoints
JavaOne 2005: Java Platform Roadmap Focuses on Ease of Development, Sun Focuses on the "Free" in F.O.S.S.
Wed Yourself to UML with the Power of Associations
Microsoft to Add AJAX Capabilities to ASP.NET
IBM's Cloudscape Versus MySQL