Solved

working with dates

Posted on 2008-06-09
8
242 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 142

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
3 Use Cases for Connected Systems

Our Dev teams are like yours. They’re continually cranking out code for new features/bugs fixes, testing, deploying, testing some more, responding to production monitoring events and more. It’s complex. So, we thought you’d like to see what’s working for us.

 

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 142

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

Comprehensive Backup Solutions for Microsoft

Acronis protects the complete Microsoft technology stack: Windows Server, Windows PC, laptop and Surface data; Microsoft business applications; Microsoft Hyper-V; Azure VMs; Microsoft Windows Server 2016; Microsoft Exchange 2016 and SQL Server 2016.

Question has a verified solution.

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

Suggested Solutions

Title # Comments Views Activity
Exporting Access Tables as CSV 3 24
Email Header Detail 12 55
Run SQL Server Proc from Access 11 31
Problem copying record details to a new record 5 9
Overview: This article:       (a) explains one principle method to cross-reference invoice items in Quickbooks®       (b) explores the reasons one might need to cross-reference invoice items       (c) provides a sample process for creating a M…
'Between' is such a common word we rarely think about it but in SQL it has a very specific definition we should be aware of. While most database vendors will have their own unique phrases to describe it (see references at end) the concept in common …
In Microsoft Access, learn how to “cascade” or have the displayed data of one combo control depend upon what’s entered in another. Base the dependent combo on a query for its row source: Add a reference to the first combo on the form as criteria i…
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 …

773 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