SQL as recordsource problem - desp. need help

[VB6noobz]

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)
frmVPC.VPCdata.Refresh
VisitorCount = frmVPC.VPCdata.Recordset.RecordCount
frmVPC.VPCDBGrid.ClearFields
ResultResponse = MsgBox(VisitorCount & " " & "visited during this period", vbOKOnly + vbInformation, "Visitor Number Analysis")
Errhandler:
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")
Exit Sub
End Sub

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

much appreciated,

VB6noobz

[kashif_82]

I dont see anything wrong up here... so just try stepping through your code...Most likely the problem is with your SQL statement.. so instead of getting the data, pring your SQL on the form or in an input box, copy that SQL statement and paste it in your database (which ever DB you are using) and then compare the results with your form if its the same then just break your sql down to two where clauses:
SELECT * FROM Prospect WHERE [DateEnquired] =" & Str$(SearchDate) & " OR [DateEnquired] > " & Str$(SearchDate)

[VB6noobz]

can u tell me how to do this thing step by step - i dont know, my DB is an access one, access 95 but i converted it so i can edit its properties, wot next, ive never used access before and its complex

much appreciated

VB6noobz

[kashif_82]

SearchDate = InputBox("Enter the date to search from", "Visitor Number Analysis")
frmVPC.VPCdata.RecordSource = ""

After these lines, instead of doing this:
frmVPC.VPCdata.RecordSource = "SELECT * FROM Prospect WHERE [DateEnquired] >=" & Str$(SearchDate)

Do

MsgBox("SELECT * FROM Prospect WHERE [DateEnquired] >=" & Str$(SearchDate))

copy that SQL statement that will be printed out on a message box.

Open Microsoft Access and open the database where your "Prospect" table is located. from main window, click on Queries from left hand side. (see db1.jpg attached)..

Then select new query, design view, no need to add tables. now under the File from top menu before the save icon, you will see another button which will say "SQL" with a small arrow right next to it. Click on arrow and select SQL view (see attachment db2.jpg). and paste the sql statement copied from your form.
Then compare those results. If the results are the same as your results from your VB form, then try the sql statement that I wrote earlier. (sorry I dont have photoshop or anyother image program which is why the attachments are huge).