473,322 Members | 1,347 Online
Bytes | Software Development & Data Engineering Community
Post Job

Home Posts Topics Members FAQ

Join Bytes to post your question to a community of 473,322 software developers and data experts.

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 "-"
Nov 12 '05 #1
3 12010
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!
Nov 12 '05 #2
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

<ca*****@hotmail.com> wrote in message
news:25**************************@posting.google.c om...
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 "-"

Nov 12 '05 #3
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 <rp*****@aol.com> wrote in message news:<3f*********************@news.frii.net>...
Not guaranteed but try just

DateAdd(d, 7, Date)

Or

DateAdd('d', 7, Date)

Or

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

Nov 12 '05 #4

This thread has been closed and replies have been disabled. Please start a new discussion.

Similar topics

6
by: carverk | last post by:
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...
4
by: Russell | last post by:
I'm having a fit with a query for a range of dates. The dates are being returned from a view. The table/field that they are being selected from stores them as varchar and that same field also...
3
by: Mike Dundee | last post by:
I am importing data into a new database (the database still has to be set up) and have a problem. The comma delimited text files I am importing have four fields containing date and date/times. ...
14
by: Alan | last post by:
Hi everyone! I'm trying to produce a periodic financial report on projects from various departments. My database is set up with the tables tblDepartment, tblProjects, tblPeriods, and tblBudgets...
7
by: Nicolae Fieraru | last post by:
Hi All, I have a table tblProducts where I have four fields:\ Index, ProductName, EnterDate (as Date/Time - Medium Date), PurchaseDate (Date/Time - Medium Date) The EnterDate is automatically...
2
by: Julie Wardlow | last post by:
Help! I am calculating a future date using the DateAdd function in a query (the calculation also involves an IIf statement), and have managed to get this formula to produce the required result....
7
by: Jerome | last post by:
Hallo, I know a lot has already been told about date/time fields in a database but still confuses me, specif when dealing with SQLserver(Express). It seems that sqlserver only accepts the date in...
14
by: BurtonBach | last post by:
I constantly have problems with this and I am hoping someone can explain it to me. Does Access always output text into a query regardless of what the formating of the data is in the table? I am...
2
by: thewilldog | last post by:
Hello, I've reviewed the archives here to address the issue, but I'm still running into problems. I've got a table field populated with the record date in text "YYYYMMDD" To convert it into a...
9
by: Millie18 | last post by:
Hi, I’m trying to set up a query to find all dates on or between a start and end date. Table name and field names I’ve used: Tbl_bookings Booking No Boarding Arrival Date Boarding Departure...
0
by: DolphinDB | last post by:
Tired of spending countless mintues downsampling your data? Look no further! In this article, you’ll learn how to efficiently downsample 6.48 billion high-frequency records to 61 million...
0
isladogs
by: isladogs | last post by:
The next Access Europe meeting will be on Wednesday 6 Mar 2024 starting at 18:00 UK time (6PM UTC) and finishing at about 19:15 (7.15PM). In this month's session, we are pleased to welcome back...
1
isladogs
by: isladogs | last post by:
The next Access Europe meeting will be on Wednesday 6 Mar 2024 starting at 18:00 UK time (6PM UTC) and finishing at about 19:15 (7.15PM). In this month's session, we are pleased to welcome back...
0
by: ArrayDB | last post by:
The error message I've encountered is; ERROR:root:Error generating model response: exception: access violation writing 0x0000000000005140, which seems to be indicative of an access violation...
1
by: PapaRatzi | last post by:
Hello, I am teaching myself MS Access forms design and Visual Basic. I've created a table to capture a list of Top 30 singles and forms to capture new entries. The final step is a form (unbound)...
1
by: CloudSolutions | last post by:
Introduction: For many beginners and individual users, requiring a credit card and email registration may pose a barrier when starting to use cloud servers. However, some cloud server providers now...
1
by: Defcon1945 | last post by:
I'm trying to learn Python using Pycharm but import shutil doesn't work
0
by: af34tf | last post by:
Hi Guys, I have a domain whose name is BytesLimited.com, and I want to sell it. Does anyone know about platforms that allow me to list my domain in auction for free. Thank you
0
isladogs
by: isladogs | last post by:
The next Access Europe User Group meeting will be on Wednesday 3 Apr 2024 starting at 18:00 UK time (6PM UTC+1) and finishing by 19:30 (7.30PM). In this session, we are pleased to welcome former...

By using Bytes.com and it's services, you agree to our Privacy Policy and Terms of Use.

To disable or enable advertisements and analytics tracking please visit the manage ads & tracking page.