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