Go Premium for a chance to win a PS4. Enter to Win

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 199
  • Last Modified:

Filtering Dynamic current 1/4 From Microsoft Acccess

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
gotti777
Asked:
gotti777
  • 5
  • 3
1 Solution
 
jefftwilleyCommented:
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
 
gotti777Author Commented:
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
 
jefftwilleyCommented:
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
Veeam Task Manager for Hyper-V

Task Manager for Hyper-V provides critical information that allows you to monitor Hyper-V performance by displaying real-time views of CPU and memory at the individual VM-level, so you can quickly identify which VMs are using host resources.

 
gotti777Author Commented:
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
 
jefftwilleyCommented:
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
 
jefftwilleyCommented:
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
 
gotti777Author Commented:
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
 
jefftwilleyCommented:
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

What is SQL Server and how does it work?

The purpose of this paper is to provide you background on SQL Server. It’s your self-study guide for learning fundamentals. It includes both the history of SQL and its technical basics. Concepts and definitions will form the solid foundation of your future DBA expertise.

  • 5
  • 3
Tackle projects and never again get stuck behind a technical roadblock.
Join Now