Solved

access formula

Posted on 2011-03-17
55
745 Views
Last Modified: 2012-05-11
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
Comment
Question by:zachvaldez
  • 27
  • 17
  • 9
  • +1
55 Comments
 
LVL 17

Expert Comment

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

Expert Comment

by:jimyX
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

by:JezWalters
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

by:JezWalters
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

by:JezWalters
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

by:zachvaldez
ID: 35155465
7 Days in the future.
0
 

Author Comment

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

Author Comment

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

Expert Comment

by:jimyX
ID: 35155495
@ JezWalters, good point.
0
 
LVL 17

Expert Comment

by:JezWalters
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

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

Expert Comment

by:JezWalters
ID: 35156180
The SQL for that would be:

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

Expert Comment

by:JezWalters
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

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

Author Comment

by:zachvaldez
ID: 35156652
Datediff has several parms
0
 

Author Comment

by:zachvaldez
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

by:JezWalters
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

by:JezWalters
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

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

Expert Comment

by:JezWalters
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

by:zachvaldez
ID: 35157786
there ae 3 tables in the query
0
 
LVL 17

Expert Comment

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

Author Comment

by:zachvaldez
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

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

Author Comment

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


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


mx
0
 
LVL 17

Expert Comment

by:JezWalters
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
Do You Know the 4 Main Threat Actor Types?

Do you know the main threat actor types? Most attackers fall into one of four categories, each with their own favored tactics, techniques, and procedures.

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

Expert Comment

by:JezWalters
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

by:JezWalters
ID: 35159425
What SQL do you have right now?
0
 
LVL 75
ID: 35159430
seem image for query designer example.

mx
Capture1.gif
0
 
LVL 75
ID: 35159444
"In the date column, I'd like to get all dates that are 7 days from today"
DateAdd("d",7,Date() )
0
 
LVL 17

Expert Comment

by:JezWalters
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

by:JezWalters
ID: 35160271
See below for the Design View for my solution:
Query1.jpg
0
 
LVL 75
ID: 35160335
""7 days from today and beyond"
OK then:

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

mx
0
 

Author Comment

by:zachvaldez
ID: 35160540
WITHIN 7 DAYS ,MEANS STARTING TODAY +  6 DAYS
0
 
LVL 75
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

by:zachvaldez
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

by:DatabaseMX (Joe Anderson - Access MVP)
DatabaseMX (Joe Anderson - Access MVP) earned 100 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

by:zachvaldez
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
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

by:JezWalters
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

by:JezWalters
ID: 35161254
From 35161160 id above, what do you mean by "I'd like to use the formula either or..."?
0
 
LVL 75
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

by:JezWalters
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

by:JezWalters
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

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

Expert Comment

by:JezWalters
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

by:zachvaldez
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

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

Expert Comment

by:JezWalters
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

by:JezWalters
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

by:
JezWalters earned 300 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

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

Author Closing Comment

by:zachvaldez
ID: 35169153
Comprehensive!
0

Featured Post

How to improve team productivity

Quip adds documents, spreadsheets, and tasklists to your Slack experience
- Elevate ideas to Quip docs
- Share Quip docs in Slack
- Get notified of changes to your docs
- Available on iOS/Android/Desktop/Web
- Online/Offline

Join & Write a Comment

I see at least one EE question a week that pertains to using temporary tables in MS Access.  But surprisingly, I was unable to find a single article devoted solely to this topic. I don’t intend to describe all of the uses of temporary tables in t…
Describes a method of obtaining an object variable to an already running instance of Microsoft Access so that it can be controlled via automation.
As developers, we are not limited to the functions provided by the VBA language. In addition, we can call the functions that are part of the Windows operating system. These functions are part of the Windows API (Application Programming Interface). U…
In Microsoft Access, when working with VBA, learn some techniques for writing readable and easily maintained code.

762 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

22 Experts available now in Live!

Get 1:1 Help Now