assign date values on a variable

[saphiro]

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 ?

[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

rec.Close()
con.Close()

MsgBox(mydate)

[saphiro]

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

[ChrisPadgham]

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)
loop

[Reverend Jim]

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)
    rec.MoveNext()
Loop

rec.Close()
con.Close()

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.