SQL Statement Expression

[mans]

Hello,

I am using VB6 with Access2000. Can someone please let me know what code I can use to get information within the last 3 months eg. Select revenue.payment From revenue Where revenue.paydate between Date() and (Date (0 – 3 months)).

I understand the 3 months expression above is the wrong format but it shows you what I am trying to do. Is there a simple expression that I can use to look at data within the last week (s) and month (s).

Thank You

 

[Genimuse]

Use DateAdd in VB to calculate your two dates and then put them in the WHERE clause:

'Assumes date in question is ThisDate

Dim ThatDate as Date

ThatDate = DateAdd("m", -3, ThisDate)

Then your SQL would look like:

&quot;SELECT revenue.payment FROM revenue WHERE revenue.paydate >= #&quot; & ThatDate & &quot;# AND revenue.paydate <= #&quot; & ThisDate & &quot;#&quot;

Change

<=

to

<

and change

>=

to

>

if desired (not sure if you want the dates to be inclusive or not).

 

[johnwm]

BETWEEN is a standard SQL operator, and is recognised by JET4:

SELECT myField FROM myTable WHERE myDate BETWEEN myFirstDate AND myLastDate

Format(DateDiff("d",7, Date()),"\#mm\/dd\/yyyy\#&quot will return a date 7 days in the past ready to go into Access. If you need SQL syntax, just remove the 2 # signs

For months difference, substitute "m" for "d"


________________________________________________________________
If you want to get the best response to a question, please check out FAQ222-2244 first

'People who live in windowed environments shouldn't cast pointers.'

 

[Genimuse]

Hmm, sorry. I originally wrote my sample using BETWEEN but I did a quick Help search for Access and didn't see it as an option so I used > and <. Glad it's a default part of the Jet engine.

Which makes me ask: where can I find documentation on what Jet does and doesn't support?