By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
459,866 Members | 1,765 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 459,866 IT Pros & Developers. It's quick & easy.

Date Query Problems

P: n/a
Hello All

I'm in the middle of moving a MS Access DB to a MySql backend.
I have figured out about 90% of the problems I have faced, execpt
for this one.

I have 3 Queries, which pull records depending on a date range.
(Today,Last 7 Days,Next 7 Days) The one I'm having problems with
is the "Last 7 Days" Query.

Here's the Criteria I'm Using: Between Date() And DateAdd("d",-7,Date())

When the tables are in access things work Fine, but when the tables
are in MySql the Query doesn't work. Changing things a little and
playing around with the criteria, it looks like MySql/MyODBC or
something doesn't like the "-7" part, as when I make the # a non
negitive value the query works.

I have tried for the last 2 days changing the Criteria to work
but I can't seem to get it working. Can any of your wonderfull
people help?

PS: The "Next 7 Days" query is the same as above only removing the "-"
Jul 19 '05 #1
Share this Question
Share on Google+
6 Replies


P: n/a
You need to use DATE_SUB(Date(), INTERVAL 7 DAY)
ca*****@hotmail.com wrote:
Hello All

I'm in the middle of moving a MS Access DB to a MySql backend.
I have figured out about 90% of the problems I have faced, execpt
for this one.

I have 3 Queries, which pull records depending on a date range.
(Today,Last 7 Days,Next 7 Days) The one I'm having problems with
is the "Last 7 Days" Query.

Here's the Criteria I'm Using: Between Date() And DateAdd("d",-7,Date())

When the tables are in access things work Fine, but when the tables
are in MySql the Query doesn't work. Changing things a little and
playing around with the criteria, it looks like MySql/MyODBC or
something doesn't like the "-7" part, as when I make the # a non
negitive value the query works.

I have tried for the last 2 days changing the Criteria to work
but I can't seem to get it working. Can any of your wonderfull
people help?

PS: The "Next 7 Days" query is the same as above only removing the "-"


Jul 19 '05 #2

P: n/a
You need to use DATE_SUB(Date(), INTERVAL 7 DAY)
ca*****@hotmail.com wrote:
Hello All

I'm in the middle of moving a MS Access DB to a MySql backend.
I have figured out about 90% of the problems I have faced, execpt
for this one.

I have 3 Queries, which pull records depending on a date range.
(Today,Last 7 Days,Next 7 Days) The one I'm having problems with
is the "Last 7 Days" Query.

Here's the Criteria I'm Using: Between Date() And DateAdd("d",-7,Date())

When the tables are in access things work Fine, but when the tables
are in MySql the Query doesn't work. Changing things a little and
playing around with the criteria, it looks like MySql/MyODBC or
something doesn't like the "-7" part, as when I make the # a non
negitive value the query works.

I have tried for the last 2 days changing the Criteria to work
but I can't seem to get it working. Can any of your wonderfull
people help?

PS: The "Next 7 Days" query is the same as above only removing the "-"


Jul 19 '05 #3

P: n/a
Hello,

Thanks for the Info. However when I past this into the
Access Query Windows "Critera" field and try to run the
query I get "Undefined "DATE_SUB" In Expression".


Barry <ba***@nospamppsolutions.co.uk> wrote in message news:<3F**************@nospamppsolutions.co.uk>...
You need to use DATE_SUB(Date(), INTERVAL 7 DAY)
ca*****@hotmail.com wrote:
Hello All

I'm in the middle of moving a MS Access DB to a MySql backend.
I have figured out about 90% of the problems I have faced, execpt
for this one.

I have 3 Queries, which pull records depending on a date range.
(Today,Last 7 Days,Next 7 Days) The one I'm having problems with
is the "Last 7 Days" Query.

Here's the Criteria I'm Using: Between Date() And DateAdd("d",-7,Date())

When the tables are in access things work Fine, but when the tables
are in MySql the Query doesn't work. Changing things a little and
playing around with the criteria, it looks like MySql/MyODBC or
something doesn't like the "-7" part, as when I make the # a non
negitive value the query works.

I have tried for the last 2 days changing the Criteria to work
but I can't seem to get it working. Can any of your wonderfull
people help?

PS: The "Next 7 Days" query is the same as above only removing the "-"

Jul 19 '05 #4

P: n/a
Hello,

Thanks for the Info. However when I past this into the
Access Query Windows "Critera" field and try to run the
query I get "Undefined "DATE_SUB" In Expression".


Barry <ba***@nospamppsolutions.co.uk> wrote in message news:<3F**************@nospamppsolutions.co.uk>...
You need to use DATE_SUB(Date(), INTERVAL 7 DAY)
ca*****@hotmail.com wrote:
Hello All

I'm in the middle of moving a MS Access DB to a MySql backend.
I have figured out about 90% of the problems I have faced, execpt
for this one.

I have 3 Queries, which pull records depending on a date range.
(Today,Last 7 Days,Next 7 Days) The one I'm having problems with
is the "Last 7 Days" Query.

Here's the Criteria I'm Using: Between Date() And DateAdd("d",-7,Date())

When the tables are in access things work Fine, but when the tables
are in MySql the Query doesn't work. Changing things a little and
playing around with the criteria, it looks like MySql/MyODBC or
something doesn't like the "-7" part, as when I make the # a non
negitive value the query works.

I have tried for the last 2 days changing the Criteria to work
but I can't seem to get it working. Can any of your wonderfull
people help?

PS: The "Next 7 Days" query is the same as above only removing the "-"

Jul 19 '05 #5

P: n/a
DATE_SUB is a function of MySQL so won't work in Access. I thought you
were moving from Access to MySQL so assumed you would need the MySQL
function.

ca*****@hotmail.com wrote:
Hello,

Thanks for the Info. However when I past this into the
Access Query Windows "Critera" field and try to run the
query I get "Undefined "DATE_SUB" In Expression".


Barry <ba***@nospamppsolutions.co.uk> wrote in message news:<3F**************@nospamppsolutions.co.uk>...

You need to use DATE_SUB(Date(), INTERVAL 7 DAY)
ca*****@hotmail.com wrote:
Hello All

I'm in the middle of moving a MS Access DB to a MySql backend.
I have figured out about 90% of the problems I have faced, execpt
for this one.

I have 3 Queries, which pull records depending on a date range.
(Today,Last 7 Days,Next 7 Days) The one I'm having problems with
is the "Last 7 Days" Query.

Here's the Criteria I'm Using: Between Date() And DateAdd("d",-7,Date())

When the tables are in access things work Fine, but when the tables
are in MySql the Query doesn't work. Changing things a little and
playing around with the criteria, it looks like MySql/MyODBC or
something doesn't like the "-7" part, as when I make the # a non
negitive value the query works.

I have tried for the last 2 days changing the Criteria to work
but I can't seem to get it working. Can any of your wonderfull
people help?

PS: The "Next 7 Days" query is the same as above only removing the "-"


Jul 19 '05 #6

P: n/a
DATE_SUB is a function of MySQL so won't work in Access. I thought you
were moving from Access to MySQL so assumed you would need the MySQL
function.

ca*****@hotmail.com wrote:
Hello,

Thanks for the Info. However when I past this into the
Access Query Windows "Critera" field and try to run the
query I get "Undefined "DATE_SUB" In Expression".


Barry <ba***@nospamppsolutions.co.uk> wrote in message news:<3F**************@nospamppsolutions.co.uk>...

You need to use DATE_SUB(Date(), INTERVAL 7 DAY)
ca*****@hotmail.com wrote:
Hello All

I'm in the middle of moving a MS Access DB to a MySql backend.
I have figured out about 90% of the problems I have faced, execpt
for this one.

I have 3 Queries, which pull records depending on a date range.
(Today,Last 7 Days,Next 7 Days) The one I'm having problems with
is the "Last 7 Days" Query.

Here's the Criteria I'm Using: Between Date() And DateAdd("d",-7,Date())

When the tables are in access things work Fine, but when the tables
are in MySql the Query doesn't work. Changing things a little and
playing around with the criteria, it looks like MySql/MyODBC or
something doesn't like the "-7" part, as when I make the # a non
negitive value the query works.

I have tried for the last 2 days changing the Criteria to work
but I can't seem to get it working. Can any of your wonderfull
people help?

PS: The "Next 7 Days" query is the same as above only removing the "-"


Jul 19 '05 #7

This discussion thread is closed

Replies have been disabled for this discussion.