access db sorting

RonR

Well-known member
Joined
Nov 23, 2007
Messages
82
Programming Experience
5-10
I am using an Access 2000 .mdb. what is the fastest way to do this sort job:


all records between a specific year, month, day to a specific year, month, day

and also only get the records with a certain name in the name field of the .mdb and do it fastest way possible??


what would be the best way to store the date of the record? I can easily change it if needed
 
You store dates in a Date/Time column. In code:
VB.NET:
Dim connection As New OleDbConnection("connection string here")
Dim command As New OleDbCommand("SELECT * FROM MyTable WHERE MyDate BETWEEN @StartDate AND @EndDate", connection)

command.Parameters.AddWithValue("@StartDate", startDate)
command.Parameters.AddWithValue("@EndDate", endDate)

connection.Open()

Dim reader As OleDbDataReader = command.ExecuteReader()
Dim table As New DataTable

table.Load(reader)
reader.Close()
connection.Close()
Getting records by Name would be much the same.

If you have created a Data Source for your database then you'll already have a TableAdapter for that table. You can open the DataSet in the designer, right-click the TableAdapter and select Add Query. You can then use the Query Builder to visually build a query as above. Executing the query would then be a matter of calling the appropriate method of your TableAdapter.
 
VB.NET:
Dim startdate As String
Dim enddate As String
startdate = "6/1/2002"
enddate = "7/1/2002"
CMD2.CommandText = "SELECT * FROM incident WHERE newdate BETWEEN @StartDate AND @EndDate"

the above code gives me errors:
no value given for one or more required paramaters
I must not have it correct
 
Last edited by a moderator:
I did change the variables to date rather than string.


I also used the date type in Access.



thanks very much, I have it working now.
 
I am using an Access 2000 .mdb. what is the fastest way to do this sort job:


all records between a specific year, month, day to a specific year, month, day
That's not a sort, it's a search

and also only get the records with a certain name in the name field of the .mdb and do it fastest way possible??
Again, a SELECT with a WHERE clause providing the search criteria

what would be the best way to store the date of the record? I can easily change it if needed
As a date, curiously enough!
 
Back
Top