Filtering Dynamic current 1/4 From Microsoft Acccess

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

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?

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

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"));

Select allOpen in new window

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

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

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 ?