Solved

working with dates

Posted on 2008-06-09
8
244 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
Space-Age Communications Transitions to DevOps

ViaSat, a global provider of satellite and wireless communications, securely connects businesses, governments, and organizations to the Internet. Learn how ViaSat’s Network Solutions Engineer, drove the transition from a traditional network support to a DevOps-centric model.

 

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

Independent Software Vendors: We Want Your Opinion

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

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. …
Access developers frequently have requirements to interact with Excel (import from or output to) in their applications.  You might be able to accomplish this with the TransferSpreadsheet and OutputTo methods, but in this series of articles I will di…
In Microsoft Access, learn different ways of passing a string value within a string argument. Also learn what a “Type Mis-match” error is about.
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 …

749 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