SQL query problem

[MattRK]

Ok, i am more of a command line db guy. So i'm not sure how to do what i want to do. I have three differnt querys i want to perform. (i will include these querys in a report)

First Query:
Select all records where the End_Date (a date field in my table) is greater than or equil to now().

Second Query:
Select all records where the Start_Date (a date field in my table) is greater than or equil to now() minus 7 days.

Third Query:
View all records where the End_Date (a date field in my table) is greater than or equil to now() minus 7 days.

If someone could tell me how to do this i would be very pleased. Like i said, i'm new to access and the help files that come with access are way more complicated than they should be. lol Thanks for any help you can provide.

-Matt Keller

 

[scriverb]

Below is the SQL code for the three queries requested. Of course you will have to substitute your table name in place of tblYourTable through the code.

Query1:
SELECT tblYourTable.*
FROM tblYourTable
WHERE (((tblYourTable.End_Date)>=Now()));

Query2:
SELECT tblYourTable.*
FROM tblYourTable
WHERE (((tblYourTable.Start_Date)>=DateAdd("d",7,Now())));

Query3:
SELECT tblYourTable.*
FROM tblYourTable
WHERE (((tblYourTable.End_Date)>=DateAdd("d",-7,Now())));


Now I am not sure if you know how to create the queries from just the SQL. If you don't it is quite simple. First fill in your table name in all three queries. Copy the code and start a New query. When you are prompted for a table selection click cancel. On the toolbar at the top left is a button that gives you three options(Design view, datasheet view, and SQL view) Select the SQL view. Now paste in the code. Save the query and name it. This query is ready to be used with your reports. Do the same with the other SQL code for queries 2 and 3.

If I oversimplified please accept my apologies. Just wasn't sure of your expertise in this area. You Thread indicated you were quite new to query design.

Good luck with your project.

Bob Scriver

 

[MattRK]

Thanks for your quick reply. I appreciate your detail. After I posted this, I got to thinking that maybe it would accept mysql like querys. So I wrote one up that would work in mysql and pasted it into access and it worked. But it wasn’t as complicated as the ones you have down. Tell me if this is correct?

For the first query i requested you said:
SELECT tblYourTable.*
FROM tblYourTable
WHERE (((tblYourTable.End_Date)>=Now()));

but I put:

SELECT *
FROM Customers
WHERE [Customers].[Date]>=now();

The only difference I see is all of the parentheses. lol. The only problem I have is that it only pulls up records that have a greater (>) date than now(). It doesn’t pull up dates that are equal to now(). The same thing happens with your code. I wonder if it is a bug in access or just a mistake in coding?

Thanks again for your help.

-Matt Keller

 

[ldandy]

Hi,

The function Now() returns both date and time, and I guess this is the problem. You should try Date() instead.

Danny.

 

[scriverb]

Yes, I was following your lead with the use of NOW(). When you are making date comparisons you should use Date() as the Now() function has additional value that will never compare properly.

ldandy, is correct about that. You see the Date field is stored as a real number meaning there is an integer number to the left of the decimal that denotes the date and a fractional value to the right of the decimal that denotes the time as it progresses from midnight on through the day in fractions. When you compare Date() = Now() when the date portion of both is the same they will not compare equal. So you must be very careful about using NOW().

Good point ldandy.

Bob Scriver