Solved

working with dates

Posted on 2008-06-09
8
248 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
[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
  • Learn & ask questions
  • 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 Eight Noble Truths of Backup and Recovery

How can IT departments tackle the challenges of a Big Data world? This white paper provides a roadmap to success and helps companies ensure that all their data is safe and secure, no matter if it resides on-premise with physical or virtual machines or in the cloud.

 

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

Migrating Your Company's PCs

To keep pace with competitors, businesses must keep employees productive, and that means providing them with the latest technology. This document provides the tips and tricks you need to help you migrate an outdated PC fleet to new desktops, laptops, and tablets.

Question has a verified solution.

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

I'm trying, I really am. But I've seen so many wrong approaches involving date(time) boundaries I despair about my inability to explain it. I've seen quite a few recently that define a non-leap year as 364 days, or 366 days and the list goes on. …
The Windows Phone Theme Colours is a tight, powerful, and well balanced palette. This tiny Access application makes it a snap to select and pick a value. And it doubles as an intro to implementing WithEvents, one of Access' hidden gems.
Show developers how to use a criteria form to limit the data that appears on an Access report. It is a common requirement that users can specify the criteria for a report at runtime. The easiest way to accomplish this is using a criteria form that a…
Learn how to number pages in an Access report over each group. Activate two pass printing by referencing the pages property: Add code to the Page Footers OnFormat event to capture the pages as there occur for each group. Use the pages property to …

739 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