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

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

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

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

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

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

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

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

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

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

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

Here is what I use to get data for Access
If I could get a copy of your workbook I might be more help

Private Sub testread()
Dim cnn As ADODB.Connection
Dim rs1 As ADODB.Recordset
Dim strSQL1 As String, strConn
Dim i As Integer
Dim mydate1 As String
Dim mydate2 As String
mydate1 = Sheets(1).Range("F1") 'I use a form with a calander to put these
mydate2 = Sheets(1).Range("F2") 'dates into Cells F1 and F2
i = 1

'Use for Access (jet)
strConn = "Provider=Microsoft.Jet.OLEDB.4.0;" _
& "Data Source=C:\Ilsa\Data\" _
& "Ilsa.mdb;Persist Security Info=False"

'Use for jet
strSQL1 = "SELECT FIELDNAME, FIELDNAME1,FIELDNAME2, FIELDNAME3 " _
& "FROM TABLENAME " _
& "WHERE (((TIME_STAMP) Between #" & mydate1 & "# " _
& "And #" & mydate2 & "#+1)) " _
& "ORDER BY FIELDNAME2; "

Set cnn = New ADODB.Connection
Set rs1 = New ADODB.Recordset
cnn.Open strConn
rs1.Open strSQL1, cnn, adOpenForwardOnly, adLockReadOnly
Do While rs1.EOF = False
Sheets("Sheet1").Range("A" & i) = rs1!FIELDNAME1
Sheets("Sheet1").Range("B" & i) = rs1!FIELDNAME2
Sheets("Sheet1").Range("C" & i) = rs1!FIELDNAME3

i = i + 1
rs1.MoveNext
Loop
rs1.Close
cnn.Close
End Sub

"DaveMZ" wrote:

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