MSQuery date criteria using 'today's' date

[matpj]

Is there a way I can code 'today()' into MSQeury?

I need to have a criteria that give all data that is today minus 4 weeks or more

>=Today - <4 weeks>

is there a way to write this in SQL using MSQuery?


Matt
London (UK)

 

[xlbo]

I think that

>=Date-28

should do it

In code, you might have

mySQL = "Select * From myTable Where TestDate >=" & Date - 28

Rgds, Geoff

"Three things are certain: Death, taxes and lost data. Guess which has occurred"

Please read FAQ222-2244 before you ask a question

 

[matpj]

that doesn;t seem to work. it complanis of invalid column name 'Date'.
SQL as follows (see the last line for the criteria)

SELECT ppa_WIP.TransNo, ppa_WIP.ApplyTo, ppa_WIP.SourceModule, ppa_WIP.TransType, ppa_WIP.BatchNo, ppa_WIP.EntryUser, ppa_WIP.EntryDate, ppa_WIP.LastUpdateUser, ppa_WIP.LastUpdateDate, ppa_WIP.ExternalBatchID, ppa_WIP.ExternalTransNo, ppa_WIP.LocationID, ppa_WIP.DepartCode, ppa_WIP.Entity, ppa_WIP.TransDate, ppa_WIP.Client, ppa_WIP.Project, ppa_WIP.CostCode, ppa_WIP.Variation, ppa_WIP.EmplyCode, ppa_WIP.ProjSite, ppa_WIP.Quantity, ppa_WIP.BillRate, ppa_WIP.Price, ppa_WIP.Amount, ppa_WIP.FactorAmount, ppa_WIP.Burden, ppa_WIP.Overhead, ppa_WIP.TotalAmount, ppa_WIP.AmountRemaining, ppa_WIP.ActualCost, ppa_WIP.StdCost, ppa_WIP.TotalCost, ppa_WIP.CostRuleCode, ppa_WIP.BurdenCode, ppa_WIP.OverheadCode, ppa_WIP.OnSite, ppa_WIP.Chargeable, ppa_WIP.Status, ppa_WIP.Notes, ppa_WIP.InvoiceNo, ppa_WIP.EmplyHomeLocation, ppa_WIP.EmplyHomeDepart, ppa_WIP.EmplyActualCost, ppa_WIP.EmplyStdCost, ppa_WIP.ProjectType, ppa_WIP.ClientClass, ppa_WIP.ProjectClass, ppa_WIP.CostClass, ppa_WIP.TransClass, ppa_WIP.EmplyClass, ppa_WIP.WIPClass, ppa_WIP.WorkInt, ppa_WIP.WorkBuf, ppa_WIP.GLPosted, ppa_WIP.GLPeriod
FROM DBSfpmi.dbo.ppa_WIP ppa_WIP
WHERE ppa_WIP.EntryDate = Date - 28

Matt
London (UK)

 

[ETID]

A cheap (but effective)way...

You could add a bracket prompt...then link that paramter to a cell that comtains the today() function

 

[xlbo]

Should it not be:

WHERE ppa_WIP.EntryDate = #Date - 28#

Rgds, Geoff

"Three things are certain: Death, taxes and lost data. Guess which has occurred"

Please read FAQ222-2244 before you ask a question

 

[matpj]

If I use #Date -28# I get an error saying "incorrect syntax near #"



Matt
London (UK)

 

[xlbo]

Sorry - it's GetDate() that returns current date - but that is SQL Server 7 - you will need to use the correct syntax for your database - I think it is SysDate() for Oracle etc...

Rgds, Geoff

"Three things are certain: Death, taxes and lost data. Guess which has occurred"

Please read FAQ222-2244 before you ask a question

 

[matpj]

so if it was SQL Server I would write:

WHERE ppa_WIP.EntryDate >= GetDate() - 28

?

Matt
London (UK)

 

[xlbo]

Well, I use SQL Server 7 via Business Objects - I just set up a bog standard query and it gave me:

SELECT dbo.Calendar.Cal_Date
FROM dbo.Calendar
WHERE (dbo.Calendar.Cal_Date  >=  GETDATE())

Being as dates increment in 1s per day, you 'should' be able to write

GETDATE()-28

Rgds, Geoff

"Three things are certain: Death, taxes and lost data. Guess which has occurred"

Please read FAQ222-2244 before you ask a question

 

[matpj]

The Sybase syntax is also GetDate(), although when I entered >= GetDate() - 28, it came up with an error saying:
"Operand Type Clash: INT is incompatible with DATETIME"

:-(



Matt
London (UK)

 

[xlbo]

Try this then - straight from my SQL for dummies book !
>= GetDate() - INTERVAL '28' DAY

Rgds, Geoff

"Three things are certain: Death, taxes and lost data. Guess which has occurred"

Please read FAQ222-2244 before you ask a question

 

[matpj]

I should go and buy myself that book!!


Matt
London (UK)

 

[xlbo]

Did it work ??

Rgds, Geoff

"Three things are certain: Death, taxes and lost data. Guess which has occurred"

Please read FAQ222-2244 before you ask a question