# sign doesn't work with between-date searching

G

Guest

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
 
G

Guest

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

Guest

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,
 
G

Guest

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

Guest

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

Mike said:
try this
"WHERE (((task_ass_date ) Between #" & Format(11 / 1 / 2006,
"[$-409]mmddyy;@") & "# " _
& "And #" & Format(11 / 31 / 2006, "[$-409]mmddyy;@") & "#))
DaveMZ said:
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,
Click to expand...
Click to expand...
 
G

Guest

Could you e-mail me a copy of the workbook

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

Mike said:
try this
"WHERE (((task_ass_date ) Between #" & Format(11 / 1 / 2006,
"[$-409]mmddyy;@") & "# " _
& "And #" & Format(11 / 31 / 2006, "[$-409]mmddyy;@") & "#))
DaveMZ said:
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,


:

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

:

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
Click to expand...
Click to expand...
Click to expand...
 
G

Guest

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 said:
Could you e-mail me a copy of the workbook

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

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

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,


:

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

:

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
Click to expand...
Click to expand...
Click to expand...
 
G

Guest

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 said:
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 said:
Could you e-mail me a copy of the workbook

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

:

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

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,


:

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

:

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
Click to expand...
Click to expand...
Click to expand...
 
G

Guest

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 said:
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 said:
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 said:
Could you e-mail me a copy of the workbook

:

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

:

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

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,


:

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

:

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
Click to expand...
Click to expand...
Click to expand...
 
G

Guest

Need to remember there are only 30 days in November

DaveMZ said:
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 said:
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 said:
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






:

Could you e-mail me a copy of the workbook

:

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

:

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

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,


:

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

:

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
Click to expand...
Click to expand...
Click to expand...
 
G

Guest

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 said:
Need to remember there are only 30 days in November

DaveMZ said:
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 said:
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

:

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






:

Could you e-mail me a copy of the workbook

:

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

:

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

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,


:

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

:

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
Click to expand...
Click to expand...
Click to expand...
 
G

Guest

Here is what I use to get data for Access
If I could get a copy of your workbook I might be more help
(e-mail address removed)
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 said:
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 said:
Need to remember there are only 30 days in November

DaveMZ said:
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

:

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

:

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






:

Could you e-mail me a copy of the workbook

:

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

:

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

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,


:

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

:

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
Click to expand...
Click to expand...
Click to expand...
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Similar Threads

COUNT BETWEEN A SPECIFIC DATE RANGE 4
Automating Import of Date Information 2
SQL query 5
Date Ranges with Gaps - Finding Corresponding Maximum 0
Add value if date is in an interval... tricky?! 1
Dates in SQL query won't match 1
LOOKUP function between two dates in Excel 2007 6
MATCH command does not work with dates 3

Top