access formula

I have 3 columns on a query. In the date column, I'd like to get all dates that are 7 days from today. What formula is placed in the query?
zachvaldezAsked:
Who is Participating?
 
JezWaltersConnect With a Mentor Commented:
Re-reading your last post, you could used the expression ">= Date() And <= Date() + 6" instead like this (although BETWEEN is arguably clearer):
JoinQuery.jpg
0
 
JezWaltersCommented:
SELECT *
FROM YourTable
WHERE DateDiff("d", YourField, Date()) = 7
0
 
jimyXCommented:
You can use the following SQL code:
SELECT *
FROM Table1
WHERE DateCol=Format(now()+7,"dd/mm/yyyy");
0
Cloud Class® Course: MCSA MCSE Windows Server 2012

This course teaches how to install and configure Windows Server 2012 R2.  It is the first step on your path to becoming a Microsoft Certified Solutions Expert (MCSE).

 
JezWaltersCommented:
DateDiff() is the clearest way to go, but you might like to know that date/time values are actually stored as the number of days (or part thereof) from 30th December 1899.

I DON'T recommend it, but you could therefore just subtract your date value (disregarding the fractional part) from the value returned by the Date() function to determine the elapsed number of days:

SELECT *
FROM YourTable
WHERE Date() - Int(YourField) = 7

Just so you understand what's going on behind the scenes!  :-)
0
 
JezWaltersCommented:
It's not quite clear from your question if "7 days from today" means in the past or in the future.  I've given you the query for 7 days in the past, but to select records for dates 7 days in the future the quey should be:

SELECT *
FROM YourTable
WHERE DateDiff("d", Date(), YourField) = 7
0
 
JezWaltersCommented:
No offense to jimyX but his suggestion isn't independent of locale, so it won't work if you (or any of your users) are using American dates.

When you're working with dates, it's always best to cope with any locale - you never know when you might bump in to an ex-pat user who likes to display dates differently!  :-)
0
 
zachvaldezAuthor Commented:
7 Days in the future.
0
 
zachvaldezAuthor Commented:
I'm doing it in query design. Btw,it's 2010 version .
0
 
zachvaldezAuthor Commented:
I'm doing it in query design. Btw,it's 2010 version .
0
 
jimyXCommented:
@ JezWalters, good point.
0
 
JezWaltersCommented:
Real Access programmers use SQL View!  ;-)  However, if you want to use Design View, then set first Field value to <insert your table name here>.*, set the Table value to <insert your table name here> and tick the Show checkbox.

Next, set the second Field value to:

    DateDiff("d", Date(), <insert your field name here>)

and set the corresponding Criteria to 7.

Joking aside, I'd strongly recommend getting to grips with writing queries directly in SQL - it's ultimately more flexible and will help you when you come to executing SQL in-line from VBA.
0
 
zachvaldezAuthor Commented:
How about in 7 days and thereafter or in day 7 from today and beyond.
0
 
JezWaltersCommented:
The SQL for that would be:

SELECT *
FROM YourTable
WHERE DateDiff("d", Date(), YourField) >= 7
0
 
JezWaltersCommented:
You can create the same query using Design View (though I recommend sticking to SQL View) by changing the Criteria to >=7
0
 
zachvaldezAuthor Commented:
If it is a query then there should be joins among the 3 tables
0
 
zachvaldezAuthor Commented:
Datediff has several parms
0
 
zachvaldezAuthor Commented:
SELECT *
FROM YourTable
WHERE DateDiff("d", Date(), YourField) >= 7

What does this query do? will return data with Tdate field >=7 days?
0
 
JezWaltersCommented:
The query selects all fields (SELECT *) from the table "YourTable" (FROM YourTable) where the date difference between today's date and the field "YourField" is +7 days or more (WHERE DateDiff("d", Date(), YourField) >= 7) .
0
 
JezWaltersCommented:
I can help you with the syntax for JOINing 3 tables, but posting a sample database would make this a lot less error prone!  :-)
0
 
JezWaltersCommented:
Are you looking to modify the records returned, or is this a read-only operation?
0
 
JezWaltersCommented:
Not to put too fine a point on it, but your question mentions 3 columns (i.e. fields), not 3 tables!

If you switch to SQL View and then post the SQL code you're having difficulty with I can help you fix it.
0
 
zachvaldezAuthor Commented:
there ae 3 tables in the query
0
 
JezWaltersCommented:
What is the SQL of your query?
0
 
zachvaldezAuthor Commented:
when I added the datediff formula in the sql view and look at the design it added another column.
I thought it would be criteria driven query?
0
 
zachvaldezAuthor Commented:
Display all the data that would show 7 days from now- is that the query will do?
0
 
zachvaldezAuthor Commented:
or how about show all day today till 7 days... this is what I need, is that possible
0
 
DatabaseMX (Joe Anderson - Microsoft Access MVP)Database ArchitectCommented:


SELECT Table1.*
FROM Table1
WHERE (((Table1.YourDate)=DateAdd("d",7,Date())));


mx
0
 
JezWaltersCommented:
You're getting an extra field because you've ticked the 'Show' checkbox in Design View - untick this and then you'll get the behaviour you're after.

Not wishing to be beligerent, but this sort of thing wouldn't happen if you used SQL View ...
0
 
DatabaseMX (Joe Anderson - Microsoft Access MVP)Database ArchitectCommented:
@ zachvaldez:
Please try the post @ http:#a35159131
0
 
JezWaltersCommented:
No offense to DatabaseMX, but his SQL won't do 'in 7 days from today or later' - although it's easy to tweak to make it do so.

It's just my humble opinon, but using DateDiff() seems more logical for what you're trying to do (see id 35156180 above):

SELECT *
FROM YourTable
WHERE DateDiff("d", Date(), YourField) >= 7
0
 
JezWaltersCommented:
What SQL do you have right now?
0
 
DatabaseMX (Joe Anderson - Microsoft Access MVP)Database ArchitectCommented:
seem image for query designer example.

mx
Capture1.gif
0
 
DatabaseMX (Joe Anderson - Microsoft Access MVP)Database ArchitectCommented:
"In the date column, I'd like to get all dates that are 7 days from today"
DateAdd("d",7,Date() )
0
 
JezWaltersCommented:
DatabaseMX,

It would appear that the requirement is now to return records "7 days from today and beyond" - see id 35156086 above).
0
 
JezWaltersCommented:
See below for the Design View for my solution:
Query1.jpg
0
 
DatabaseMX (Joe Anderson - Microsoft Access MVP)Database ArchitectCommented:
""7 days from today and beyond"
OK then:

SELECT Table1.*
FROM Table1
WHERE Table1.YourDate >= DateAdd("d",7,Date())

mx
0
 
zachvaldezAuthor Commented:
WITHIN 7 DAYS ,MEANS STARTING TODAY +  6 DAYS
0
 
DatabaseMX (Joe Anderson - Microsoft Access MVP)Database ArchitectCommented:
You previously stated differently:

"How about in 7 days and thereafter or in day 7 from today and beyond."

Which scenario do you really want ?

mx
0
 
zachvaldezAuthor Commented:
Something like
"return me the data starting today and the next 6 days"
       Sorry for the misuse of prepositions.
0
 
DatabaseMX (Joe Anderson - Microsoft Access MVP)Connect With a Mentor Database ArchitectCommented:
Here you go then:

SELECT Table1.*
FROM Table1
WHERE (((Table1.YourDate) Between Date() And DateAdd("d",6,Date())));



Capture1.gif
0
 
zachvaldezAuthor Commented:
Here's table structure
First
Tid
 name
Tickdate

Secondtable
Tid
Pid
Pickdate

Third table
Pid
Document

First & second are joined by Tid
Second and third joined by Pid.

In both fields pickdate and tickdate
I'd like to use the formula either or...

0
 
DatabaseMX (Joe Anderson - Microsoft Access MVP)Database ArchitectCommented:
3 tables ??

Seems the question keeps escalating.

With all due respect, I provide the answer to the question:

 "return me the data starting today and the next 6 days"

in my last post.

mx
0
 
JezWaltersCommented:
Given that dates are stored as days (or part thereof) since 30th December 1899, you could just have:

    SELECT *
    FROM YourTable
    WHERE YourField Between Date() AND Date() + 6
0
 
JezWaltersCommented:
From 35161160 id above, what do you mean by "I'd like to use the formula either or..."?
0
 
DatabaseMX (Joe Anderson - Microsoft Access MVP)Database ArchitectCommented:
"Given that dates are stored as days "
?

Dates (date/time datatypes) are stored interally Double Precision numbers, where the Date part is to the Left of the Decimal point, and the Time part is to the Right of the decimal point.

mx
0
 
JezWaltersCommented:
Bear in mind that the returned records won't be updateable (see id 35157638 above), but the following SQL should get you started:

    SELECT *
    FROM FirstTable, SecondTable, ThirdTable
    WHERE SecondTable.Tid = FirstTable.Tid AND
    ThirdTable.Pid = SecondTable.Pid AND
    (FirstTable.Tickdate BETWEEN Date() AND Date() + 6 OR
    SecondTable.Pickdate BETWEEN Date() AND Date() + 6)
0
 
JezWaltersCommented:
Or, if you prefer using JOINs

    SELECT *
    FROM (FirstTable INNER JOIN SecondTable
    ON SecondTable.Tid = FirstTable.Tid) INNER JOIN ThirdTable
    ON ThirdTable.Pid = SecondTable.Pid
    WHERE (FirstTable.Tickdate BETWEEN Date() AND Date() + 6 OR
    SecondTable.Pickdate BETWEEN Date() AND Date() + 6)
0
 
JezWaltersCommented:
The latter JOIN query looks like this in Design View:
JoinQuery.jpg
0
 
JezWaltersCommented:
DatabaseMX

"Given that dates are stored as days (or part thereof) since 30th December 1899" - this is just a recap of id 35155265 id above.
0
 
zachvaldezAuthor Commented:
Would this formula work by placing inthe criteria on both date fields.
>date()and < date()plus(can't find the symbol)
0
 
JezWaltersCommented:
Sorry zachvaldez, but I don't understand your question!  :-(
0
 
JezWaltersCommented:
At the risk of repeating myself, this question would be a lot easier to answer if you'd switch to SQL View and write the query directly in SQL.

As I've already mentioned, learning to write queries in SQL will put you in good stead for future problems, and will also help when you need to execute queries from code.
0
 
JezWaltersCommented:
Queries that return particular records are known as SELECT queries, the syntax for which is descibed here:

http://office.microsoft.com/en-us/access-help/access-sql-basic-concepts-vocabulary-and-syntax-HA010256402.aspx
http://msdn.microsoft.com/en-us/library/bb208930(v=office.12).aspx

If it helps, SQL is not difficult to understand - even for a novice.
0
 
zachvaldezAuthor Commented:
"Great contributions from great minds!"
0
 
zachvaldezAuthor Commented:
Comprehensive!
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.