Date Query Problems

[carverk]

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 "-"

[Barry]

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 "-"

[Barry]

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 "-"

[carverk]

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 "-"

[carverk]

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 "-"

[Barry]

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 "-"

[Barry]

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 "-"