Solved

Filtering Dynamic current 1/4 From Microsoft Acccess

Posted on 2009-07-15
8
196 Views
Last Modified: 2013-11-28
I had 2 questions.

 I know how to filter something in but how do I filter something out where if I had table and wanted so show everything but a certain customers How do i do a not equal too

I also had a question on dates. I am trying to run this report dynamically for a metric on the current quarter and was wonder if there is a SQL line from grabbing the current date figuring out the current quarter?
0
Comment
Question by:gotti777
[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
  • 5
  • 3
8 Comments
 
LVL 34

Expert Comment

by:jefftwilley
ID: 24861210
1) Select * from Table1 Where [Field] <> 'Some Value'

or

Select * from Table1 Where [Field] Not IN ('Some string of values')


2) Where [Date] >= DateDiff("mm",3,Date())


But how you define your quarters may be different.

You can use DateAdd or DateDiff depending on your needs. Check the Help for syntax and tricks.

J
0
 

Author Comment

by:gotti777
ID: 24861474
Thanks #1 work ..
but on number #2  Our Quarters are standard
jan, feb ,march q1
april, may, june, q2 and so..
i tryied  >= DateDiff("mm",3,Date())  and got a
Invalid Procdedure call
am i not replacing somthing?
0
 
LVL 34

Expert Comment

by:jefftwilley
ID: 24862168
Using something like this as a function

Function ReturnQuarter(MyDate As Date) As Boolean
If DatePart("q", MyDate) = DatePart("q", Date) Then ReturnQuarter = True
End Function

and using it in your query like this

Field = ReturnQuarter([DateField]
Show = False
Criteria = True

OR

you could use an IIF statement as part of your query

YourField: iif(DatePart("q", [DateField]) = DatePart("q", Date) , [SomeValue],0)

J
0
Ransomware-A Revenue Bonanza for Service Providers

Ransomware – malware that gets on your customers’ computers, encrypts their data, and extorts a hefty ransom for the decryption keys – is a surging new threat.  The purpose of this eBook is to educate the reader about ransomware attacks.

 

Author Comment

by:gotti777
ID: 24862497
I am not an exprt on functions or how to add them to Access
i tried adding the iif statement iif(DatePart("q", [DateField]) = DatePart("q", Date) , [SomeValue],0)
 to the criteria field but it ask for date? trying to make it dynamic
SELECT Invoice.WrittenBy AS [INS Rep], [Invoice Line Detail-View].CustomerName AS Customer, Invoice.SONumber AS [Order], [Invoice Line Detail-View].Item, [Invoice Line].DateRequired AS [Required Date], [Invoice Line].DatePromised, Invoice.DateShipped
FROM ([Invoice Line Detail-View] LEFT JOIN [Invoice Line] ON ([Invoice Line Detail-View].InvoiceNumber = [Invoice Line].InvoiceNumber) AND ([Invoice Line Detail-View].InvoiceLineNumber = [Invoice Line].InvoiceLineNumber)) LEFT JOIN Invoice ON [Invoice Line Detail-View].InvoiceNumber = Invoice.InvoiceNumber
WHERE ((([Invoice Line Detail-View].CustomerName)<>"Dwight Long") AND ((Invoice.DateShipped)=IIf(DatePart("q",[DateField])=DatePart("q","Date"),[SomeValue],0)) AND ((Invoice.InvoiceType)="SHIP"));

Open in new window

0
 
LVL 34

Expert Comment

by:jefftwilley
ID: 24862535
iif(DatePart("q", [DateField]) = DatePart("q", Date()) , [SomeValue],0)


Make sure that you have no Missing References. To do this, go to the VBA window and go to Tools/References

You will need the Microsoft Object Library included in the list to make the Date() function work. It is a built in function within Access.

J
0
 
LVL 34

Expert Comment

by:jefftwilley
ID: 24862545
Also, you're not adding the IIF statement to a criteria of a field. it becomes the source field for whatever it is you're totaling.

Where I have [SomeValue]..that represents a $$ field inside your query where you're trying to total Metrics.

J
0
 

Author Comment

by:gotti777
ID: 24862815
Well I am alittle lost becuase I am sure my Reference are there cause I added this to one of my other query under criteria to exclude sat and sunday and works great (Date()-Choose(Weekday(Date()),2,3,1,1,1,1,1))

So i am not adding the iif stament to creteria ?  
0
 
LVL 34

Accepted Solution

by:
jefftwilley earned 500 total points
ID: 24863677
If you want to use as Criteria, it will be something more along these lines:


SELECT Invoice.WrittenBy AS [INS Rep],
[Invoice Line Detail-View].CustomerName AS Customer, Invoice.SONumber AS [Order], [
Invoice Line Detail-View].Item, [Invoice Line].DateRequired AS [Required Date], [Invoice Line].DatePromised, Invoice.DateShipped
FROM ([Invoice Line Detail-View] LEFT JOIN [Invoice Line] ON ([Invoice Line Detail-View].InvoiceNumber = [Invoice Line].InvoiceNumber) AND ([Invoice Line Detail-View].InvoiceLineNumber = [Invoice Line].InvoiceLineNumber)) LEFT JOIN Invoice ON [Invoice Line Detail-View].InvoiceNumber = Invoice.InvoiceNumber
WHERE [Invoice Line Detail-View].CustomerName <>"Dwight Long"
AND DatePart("q",[DateShipped])= DatePart("q",Date())
AND Invoice.InvoiceType ="SHIP";

Note, I used [DateShipped] in the expression. You may want to use another date as required.
J
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

Describes a method of obtaining an object variable to an already running instance of Microsoft Access so that it can be controlled via automation.
It’s been over a month into 2017, and there is already a sophisticated Gmail phishing email making it rounds. New techniques and tactics, have given hackers a way to authentically impersonate your contacts.How it Works The attack works by targeti…
Familiarize people with the process of retrieving data from SQL Server using an Access pass-thru query. Microsoft Access is a very powerful client/server development tool. One of the ways that you can retrieve data from a SQL Server is by using a pa…
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.

729 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