that's true, stupid on my part. meantime, i've got to learn more exporting
dates from access to excel. i'm getting the sense that the code is probably
not the big problem.

thanks for your help,



"Mike" wrote:

Need to remember there are only 30 days in November

"DaveMZ" wrote:

thanks, tried that but it doesn't work. However, putting variables in the SQL
query did work partially with any month but 11:

mydate1 = #10/1/2006#
mydate2 = #10/31/2006#

but apparently, it doesn't like both mydate1 and mydate 2 to be in month 11-
it gives me a compile error. Also, it's totally ignoring the year, 2006. I
don't understand the logic behind the date formatting here in vba and excel.
Could i be missing a Reference or something?

thanks

"Mike" wrote:

try this I have not tested
Sub getData()

Dim cn As New ADODB.Connection
Dim cmd As New ADODB.Command
Dim rs As New ADODB.Recordset

Dim mydate1 As String 'added
Dim mydate2 As String 'added
mydate1 = 11 / 1 / 2006 'added
mydate2 = 11 / 31 / 2006 'added this

'connect to the database
cn.Open "Provider=Microsoft.Jet.OLEDB.4.0;" _
& "Data Source='C:\Documents and Settings\new.mdb'"

'select all tasks that start in july
cmd.ActiveConnection = cn
cmd.CommandText = "SELECT * FROM Tasks " & _
"WHERE (((task_ass_date) Between #" & mydate1 & "# " _
& "And #" & mydate2 & "#)) " _


Set rs = cmd.Execute

rs.MoveFirst

'color index
cl = 5
cl2 = 6

'starting row
i = 1

'color the cells
Do While Not rs.EOF

st = DatePart("d", rs.Fields("task_ass_date"))
due = DatePart("d", rs.Fields("task_ddate"))

Sheets(1).Cells(i, st) = rs.Fields("Task")

For j = st To due
'**these put the calendar days in every cell the task is allocated for
'Sheets(1).Cells(i, j) = _
'Sheets(1).Cells(i, j) & " " & j

Sheets(1).Cells(i, j).Interior.ColorIndex = cl
Sheets(1).Cells(i, st).Interior.ColorIndex = cl2

Next j

rs.MoveNext

i = i + 1
cl = cl + 1
cl2 = cl2 + 1

Loop

End Sub

"DaveMZ" wrote:

hi Mike, I'm new to this forum - I don't see your email address on your
profile yet (i'll post one on my own profile). I've pasted the entire
contents of the workbook below (it's not too big, still an experiment),
although I know this may not duplicate it properly.


Sub getData()

Dim cn As New ADODB.Connection
Dim cmd As New ADODB.Command
Dim rs As New ADODB.Recordset

'connect to the database
cn.Open "Provider=Microsoft.Jet.OLEDB.4.0;" _
& "Data Source='C:\Documents and Settings\new.mdb'"

'select all tasks that start in july
cmd.ActiveConnection = cn
cmd.CommandText = "SELECT * FROM Tasks " & _
"WHERE task_ass_date Between " & _
"#11/1/2006# And #11/31/2006#"

Set rs = cmd.Execute

rs.MoveFirst

'color index
cl = 5
cl2 = 6

'starting row
i = 1

'color the cells
Do While Not rs.EOF

st = DatePart("d", rs.Fields("task_ass_date"))
due = DatePart("d", rs.Fields("task_ddate"))

Sheets(1).Cells(i, st) = rs.Fields("Task")

For j = st To due
'**these put the calendar days in every cell the task is allocated for
'Sheets(1).Cells(i, j) = _
'Sheets(1).Cells(i, j) & " " & j

Sheets(1).Cells(i, j).Interior.ColorIndex = cl
Sheets(1).Cells(i, st).Interior.ColorIndex = cl2

Next j

rs.MoveNext

i = i + 1
cl = cl + 1
cl2 = cl2 + 1

Loop

End Sub






"Mike" wrote:

Could you e-mail me a copy of the workbook

"DaveMZ" wrote:

sorry, still giving that syntax error, although the error now formats the
date as 123099.

"Mike" wrote:

try this
"WHERE (((task_ass_date ) Between #" & Format(11 / 1 / 2006,
"[$-409]mmddyy;@") & "# " _
& "And #" & Format(11 / 31 / 2006, "[$-409]mmddyy;@") & "#))
"DaveMZ" wrote:

Thanks Mike, but that gives me another syntax error as well. However,
something might be working right since the syntax error gives the dates as
numerical values (1.78889696 or something for one) instead of short date
values. why is it not liking these # signs?

thanks,


"Mike" wrote:

try this
"WHERE (((task_ass_date ) Between #" & 11 / 1 / 2006 & "# " _
& "And #" & 11 / 31 / 2006 & "#+1)) " _

"DaveMZ" wrote:

Hello,

I'm trying to get Excel to retrieve records between two dates in an access
table, but get a syntax error when i use pound signs (#). If I take the #s
out, it works partly only if the access dates are in "text" format, instead
of Date/Time.
I'm using Excel 2002, and Access 2000.

code is below:

cmd.ActiveConnection = cn
cmd.CommandText = "SELECT * FROM tasks " & _
"WHERE task_ass_date Between " & _
"#11/1/2006# And #11/31/2006#"

Thanks, any help appreciated