Date Query Problems

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

re: Date Query Problems

Not guaranteed but try just

DateAdd(d, 7, Date)

Or

DateAdd('d', 7, Date)

Or

DateAdd('d', 7, Date())


Rich

*** Sent via Developersdex http://www.developersdex.com ***
Don't just participate in USENET...get rewarded for it!

re: Date Query Problems

Can you do the calculations in Access and just use the resulting dates? The
problem seems to be in the interpretation of DateAdd, which, if MySQL has
that function, may be different.

Larry Linson
Microsoft Access MVP

<carverk@hotmail.com> wrote in message
news:25a214d3.0311241743.2710e901@posting.google.c om...[color=blue]
> 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 "-"[/color]

re: Date Query Problems

Thanks For The Info Rich,

But the first 2 dont work, the 3rd will work (changed 7 to -7)
but it only returns records from 7 days ago instead of from
today and the last 7 days. So I changed the criteris to:

Between Date() And DateAdd('d', 7, Date())

This caused the query to fail, again.

No matter what I do I can't seem to get the correct output.
It either return Nothing,Doesn't Work, or only the records from 7
days ago, but never the records Between today and the last 7 day. :(

Keith


Rich P <rpng123@aol.com> wrote in message news:<3fc2cce4$0$194$75868355@news.frii.net>...[color=blue]
> Not guaranteed but try just
>
> DateAdd(d, 7, Date)
>
> Or
>
> DateAdd('d', 7, Date)
>
> Or
>
> DateAdd('d', 7, Date())
>[/color]