Solved

working with dates

Posted on 2008-06-09
8
243 Views
Last Modified: 2011-09-20
I'm trying to run a query all new customers in the last three months and I can't remember how it went for that. I was thinking <(date three months ago) but that didn't work... how do i put it so that it looks up from up to three months ago? Thanks.
0
Comment
Question by:StrategicGnomer
  • 3
  • 3
  • 2
8 Comments
 
LVL 120

Assisted Solution

by:Rey Obrero (Capricorn1)
Rey Obrero (Capricorn1) earned 200 total points
ID: 21746364

select * from TableX
where dateField <=DateAdd("m",-3,Date())
0
 
LVL 143

Expert Comment

by:Guy Hengel [angelIII / a3]
ID: 21746367
what about this:
select ...
  from yourtable
 where registration_date >= dateadd("m", -3, date())

Open in new window

0
 
LVL 120

Expert Comment

by:Rey Obrero (Capricorn1)
ID: 21746378
that will return all records before March 10,2008.   is that what you want?
0
The New “Normal” in Modern Enterprise Operations

DevOps for the modern enterprise offers many benefits — increased agility, productivity, and more, but digital transformation isn’t easy, especially if you’re not addressing the right issues. Register for the webinar to dive into the “new normal” for enterprise modern ops.

 

Author Comment

by:StrategicGnomer
ID: 21746386
here is the query, it didn't work...

SELECT tblCustomer.ftxNameFirst, tblCustomer.ftxNameLast, TblServProv.SenttoREP
FROM (tblCustomer LEFT JOIN tblElectricData ON tblCustomer.fidCustomer = tblElectricData.fidCustomer) LEFT JOIN TblServProv ON tblElectricData.fidElectric = TblServProv.ID
WHERE (TblServProv.SenttoREP)<=DateAdd("m",-3,Date());
0
 
LVL 143

Accepted Solution

by:
Guy Hengel [angelIII / a3] earned 300 total points
ID: 21746403
you have to put the condition on left joined table into the join itself:
SELECT tblCustomer.ftxNameFirst, tblCustomer.ftxNameLast, TblServProv.SenttoREP
FROM (tblCustomer LEFT JOIN tblElectricData ON tblCustomer.fidCustomer = tblElectricData.fidCustomer) 
LEFT JOIN TblServProv ON ( tblElectricData.fidElectric = TblServProv.ID AND TblServProv.SenttoREP >= DateAdd("m",-3,Date()));

Open in new window

0
 
LVL 120

Expert Comment

by:Rey Obrero (Capricorn1)
ID: 21746405
try angeIII code,  use >=  instead of <=
0
 

Author Comment

by:StrategicGnomer
ID: 21746831
did that, it says syntax error in join... keep in mind i'm using access 2003 so i think it behaves a little differently then say SQL Server or MySQL but i'm not too sure.
0
 

Author Comment

by:StrategicGnomer
ID: 21746851
oh nm, just a small error, i got it. Thanks
0

Featured Post

Free Tool: Site Down Detector

Helpful to verify reports of your own downtime, or to double check a downed website you are trying to access.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

Question has a verified solution.

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

Occasionally there is a need to clean table columns, especially if you have inherited legacy data. There are obviously many ways to accomplish that, including elaborate UPDATE queries with anywhere from one to numerous REPLACE functions (even within…
A simple tool to export all objects of two Access files as text and compare it with Meld, a free diff tool.
In Microsoft Access, learn the trick to repeating sub-report headings at the top of each page. The problem with sub-reports and headings: Add a dummy group to the sub report using the expression =1: Set the “Repeat Section” property of the dummy…
In Microsoft Access, when working with VBA, learn some techniques for writing readable and easily maintained code.

790 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