Still celebrating National IT Professionals Day with 3 months of free Premium Membership. Use Code ITDAY17

x
Solved

# access formula

Posted on 2011-03-17
Medium Priority
790 Views
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?
0
Question by:zachvaldez
[X]
###### Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

• Help others & share knowledge
• Earn cash & points
• 27
• 17
• 9
• +1

LVL 17

Expert Comment

ID: 35155180
SELECT *
FROM YourTable
WHERE DateDiff("d", YourField, Date()) = 7
0

LVL 24

Expert Comment

ID: 35155220
You can use the following SQL code:
SELECT *
FROM Table1
WHERE DateCol=Format(now()+7,"dd/mm/yyyy");
0

LVL 17

Expert Comment

ID: 35155265
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

LVL 17

Expert Comment

ID: 35155307
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

LVL 17

Expert Comment

ID: 35155342
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

Author Comment

ID: 35155465
7 Days in the future.
0

Author Comment

ID: 35155475
I'm doing it in query design. Btw,it's 2010 version .
0

Author Comment

ID: 35155476
I'm doing it in query design. Btw,it's 2010 version .
0

LVL 24

Expert Comment

ID: 35155495
@ JezWalters, good point.
0

LVL 17

Expert Comment

ID: 35155611
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

Author Comment

ID: 35156086
How about in 7 days and thereafter or in day 7 from today and beyond.
0

LVL 17

Expert Comment

ID: 35156180
The SQL for that would be:

SELECT *
FROM YourTable
WHERE DateDiff("d", Date(), YourField) >= 7
0

LVL 17

Expert Comment

ID: 35156208
You can create the same query using Design View (though I recommend sticking to SQL View) by changing the Criteria to >=7
0

Author Comment

ID: 35156412
If it is a query then there should be joins among the 3 tables
0

Author Comment

ID: 35156652
Datediff has several parms
0

Author Comment

ID: 35157401
SELECT *
FROM YourTable
WHERE DateDiff("d", Date(), YourField) >= 7

What does this query do? will return data with Tdate field >=7 days?
0

LVL 17

Expert Comment

ID: 35157612
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

LVL 17

Expert Comment

ID: 35157628
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

LVL 17

Expert Comment

ID: 35157638
Are you looking to modify the records returned, or is this a read-only operation?
0

LVL 17

Expert Comment

ID: 35157669
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

Author Comment

ID: 35157786
there ae 3 tables in the query
0

LVL 17

Expert Comment

ID: 35157790
What is the SQL of your query?
0

Author Comment

ID: 35157803
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

Author Comment

ID: 35158137
Display all the data that would show 7 days from now- is that the query will do?
0

Author Comment

ID: 35158390
or how about show all day today till 7 days... this is what I need, is that possible
0

LVL 75

Expert Comment

ID: 35159131

SELECT Table1.*
FROM Table1

mx
0

LVL 17

Expert Comment

ID: 35159382
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

LVL 75

Expert Comment

ID: 35159419
@ zachvaldez:
Please try the post @ http:#a35159131
0

LVL 17

Expert Comment

ID: 35159424
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

LVL 17

Expert Comment

ID: 35159425
What SQL do you have right now?
0

LVL 75

Expert Comment

ID: 35159430
seem image for query designer example.

mx
Capture1.gif
0

LVL 75

Expert Comment

ID: 35159444
"In the date column, I'd like to get all dates that are 7 days from today"
0

LVL 17

Expert Comment

ID: 35160264
DatabaseMX,

It would appear that the requirement is now to return records "7 days from today and beyond" - see id 35156086 above).
0

LVL 17

Expert Comment

ID: 35160271
See below for the Design View for my solution:
Query1.jpg
0

LVL 75

Expert Comment

ID: 35160335
""7 days from today and beyond"
OK then:

SELECT Table1.*
FROM Table1

mx
0

Author Comment

ID: 35160540
WITHIN 7 DAYS ,MEANS STARTING TODAY +  6 DAYS
0

LVL 75

Expert Comment

ID: 35160572
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

Author Comment

ID: 35160895
Something like
"return me the data starting today and the next 6 days"
Sorry for the misuse of prepositions.
0

LVL 75

Assisted Solution

DatabaseMX (Joe Anderson - Microsoft MVP, Access and Data Platform) earned 400 total points
ID: 35160941
Here you go then:

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

Capture1.gif
0

Author Comment

ID: 35161160
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

LVL 75

Expert Comment

ID: 35161190
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

LVL 17

Expert Comment

ID: 35161233
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

LVL 17

Expert Comment

ID: 35161254
From 35161160 id above, what do you mean by "I'd like to use the formula either or..."?
0

LVL 75

Expert Comment

ID: 35161259
"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

LVL 17

Expert Comment

ID: 35161303
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

LVL 17

Expert Comment

ID: 35161305
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

LVL 17

Expert Comment

ID: 35161320
The latter JOIN query looks like this in Design View:
JoinQuery.jpg
0

LVL 17

Expert Comment

ID: 35161339
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

Author Comment

ID: 35162404
Would this formula work by placing inthe criteria on both date fields.
>date()and < date()plus(can't find the symbol)
0

LVL 17

Expert Comment

ID: 35164004
Sorry zachvaldez, but I don't understand your question!  :-(
0

LVL 17

Expert Comment

ID: 35164018
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

LVL 17

Expert Comment

ID: 35164041
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

LVL 17

Accepted Solution

JezWalters earned 1200 total points
ID: 35164097
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

Author Comment

ID: 35169133
"Great contributions from great minds!"
0

Author Closing Comment

ID: 35169153
Comprehensive!
0

## Featured Post

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Access custom database properties are useful for storing miscellaneous bits of information in a format that persists through database closing and reopening.  This article shows how to create and use them.
We live in a world of interfaces like the one in the title picture. VBA also allows to use interfaces which offers a lot of possibilities. This article describes how to use interfaces in VBA and how to work around their bugs.
In Microsoft Access, learn how to use Dlookup and other domain aggregate functions and one method of specifying a string value within a string. Specify the first argument, which is the expression to be returned: Specify the second argument, which â€¦
Add bar graphs to Access queries using Unicode block characters. Graphs appear on every record in the color you want. Give life to numbers. Hopes this gives you ideas on visualizing your data in new ways ~ Create a calculated field in a query: â€¦
###### Suggested Courses
Course of the Month7 days, 14 hours left to enroll