good day, is there a way to put date values in a variable, that is coming fromo the database?
something like

dim dates as dates
(select * from table1 where id = id   )
dates = date BETWEEN collumn1 and collumn2 of table1

well its not working of course, its just im looking for something like that,,
i want to store dates in between that two collumn in a variable, is it posible?
any help ?

4 Years
Discussion Span
Last Post by Reverend Jim

Use a Date variable like

Dim mydate As Date
Dim con As New ADODB.Connection
Dim rec As New ADODB.Recordset

con.Open("Driver={SQL Server};Server=.\SQLEXPRESS;Database=mydb;Trusted_Connection=yes;")
rec.Open("select * from attendance where EmployeeID = 1", con)

mydate = rec("LoginDateTime").Value



thank you for commenting sir, my database is ms access,
sir my table have 2 date collumn, say collumn1 is datehired and collumn2 is dateend,
i want to get the date between collumn1 and collumn2 and put the value in a single variable?
is it possible sir?

example if collumn1 = 7/16/2012 and collumn2 = 8/16/2012
my variable in my form should contain dates between collumn1 and collumn2
something like
mydate = dates between collumn1 ( 7/16/2012) and collumn2 ( 8/16/2012)

kindly tell me if my explanation is confusing.
thanks in advanced

Edited by saphiro


if you want all of the dates between two dates you will need an array or collection to house them, a single variable will not do.

use the DateAdd function to iterate through the dates

aDay = dateadd("d",1,datehired)
do until datediff("d",aDay, dateend) <=0
    'add date to array or collection
    aDay =dateadd("d",1,aDay)

I posted a complete code sample yesterday but it seems to have disappeared. I've already deleted the code so I'll try to do it from memory:

Dim con As New ADODB.Connection
Dim rec As New ADODB.Recordset

con.open("Driver={Microsoft Access Driver (*.mdb)};Dbq=d:\temp\db1.mdb;Uid=;Pwd=;")
rec.Open("select * from table1 where date1 between #06/01/2012# and #06/30/2012#", con, CursorTypeEnum.adOpenStatic)

Do Until rec.EOF
    TextBox1.AppendText(rec("date1").Value & vbCrLf)


I can't help but notice that the date format between the #s must be mm/dd/yyyy even though my system format is yyyy-mm-dd. It might be possible to change this but I'm not an Access user so I don't knoow the details.

This article has been dead for over six months. Start a new discussion instead.
Have something to contribute to this discussion? Please be thoughtful, detailed and courteous, and be sure to adhere to our posting rules.