-
I made a database in Access with ID, Firstname, Lastname , and StartDate . I want to SQL the database by ID and Startdate where Startdate are between two give dates by user. The user places the ID and two dates in to textbox on a form then click a button to start the code of the SQL. When I run the SQL it debug ok but it will skip the Do Until because dbs.EOF = true. I try change it to dbs.BOF . I try try placing dbs.MoveFirst. still does not work. It will show all the records on the table in the MSFlexgrid no mater what date or ID.
‘txt Start is the first texr5box the user enter the Start Date
‘ txtEnd is the textbox the user enter the end date
‘ txtId is the textbox that the user enter the Member ID number
Dim last as String
Dim db as Database
dim dbs as Recordset
last = txtID
strstartdate = "#" & txtStart & "#"
strendate = "#" & txtEnd & "#"
SQL = "select * FROM " & "Time1 where id = '" & LAST & "' and startdate between " & strstartdate & " and " & strendate & " ;"
Set db = OpenDatabase(Form1.Text1)
Set dbs = db.OpenRecordset(SQL)
Do Until dbs.EOF
Data1.Refresh
Data1.RecordSource = SQL
dbs.MoveNext
Loop
-
Firstly, what you have written in the DO UNTIL..LOOP seems a bit pointless, because you are making a data control fetch the same records as your initial SQL statement, for every record returned by the SQL statement.
Secondly, just change the code so that you build up your sql statement (as is, but drop the ";", you don't need it when using SQL queries from VB) and then use your DATA1.RECORDSOURCE = <your SQL statement here>
DATA1.REFRESH
This will automatically make the grid see the data you request.
*IF* there are no records returned, then check your SQL statement, in particular the dates. Being a brit, if you do searches like 01/12/1999, the SQL statement may assume you mean the 12th January 1999 instead of 1st December 1999.
So always use format(<date>, "dd/mmm/yyyy") wherever you are adding dates to the query.
Another problem you may need to be aware of, if you are updating records and then trying to query those updated records, you may need to flush the cache because sometimes the changes aren't recorded immediately. To solve this kind of problem, do DBENGINE.IDLE(dbRefreshCache).
Hope it helps.
