between date to date in vba(syntax)

[CHAOSinACT]

couple of quick questions....(same project as last time to those who helped) all going great, trying to sort the (now working) results a little more, first was by job number, now needs to be by job number and a DATE RANGE.

was originally filtering my recordset (successfully) with this (the debug.print return from the vba statement):

JobNumber Like '001*'

trying to work this into it:

JobNumber Like '001*' And ActivityDate = Between '#1/01/2000#' And '#06/30/2010#'

but not too sure on the syntax with the between statement in vba (its clear enough in the UI)

we're on a European date system (day/month/year) and use that in the UI, i've read i need to use (month/day/year) in vba, am doing so (let me know if i'm wrong there).
the above included date range gives me a missing operator error, i've tried a few variations on the way. i assume that having multiple filters in the .filter property of a recordset is ok?

thanks to all

 

[vbaInet]

Without the equal to sign and without the single quotes:

JobNumber LIKE '001*' AND ActivityDate BETWEEN #1/01/2000# And #06/30/2010#

 

[CHAOSinACT]

thanks always nice when they are relatively simple

 

[vbaInet]

I see you like them simple

 

[CHAOSinACT]

*gasp* did i detect sarcasm there??

lol....

 

[SteveSchapel]

Chaos,

However, in practice it is unusual to be using hard-coded actual dates in your VBA code. It is more common to be referring to the value of controls on forms, or to variables, or whatnot.

 

[vbaInet]

Just a wee bit of sarcasm Chaos

I see Steve is on. Must be afternoon in NZ now

 

[SteveSchapel]

Must be afternoon in NZ now

Yes, it is. 4:50pm. Whereas for you, shouldn't you be sleepy-byes?

 

[vbaInet]

It was sleepy-byes, I missed it due to staying awake being busy. So I will soon retire to bed

 

[CHAOSinACT]

Just a wee bit of sarcasm Chaos

I see Steve is on. Must be afternoon in NZ now

its very ok, tough but fair lol...
i'm a southern hemisphere too, in Australia

as for the non hard coded dates, ya i know. we're doing a report/spreadsheet that will calculate the Work In Progress to end financial year, then thereafter break following expenditure into months. this is the first half which IS static...but thanks, as always.

 

[SteveSchapel]

... calculate the Work In Progress to end financial year, then thereafter break following expenditure into months. this is the first half which IS static.

Sorry, Chaos, I beg to differ here. "End financial year" should be handled by a variable. Otherwise you have to go editing your code next year, which may not seem like a big deal, but is in fact regarded (quite correctly so) as poor form.

 

[CHAOSinACT]

i've reworked the code to be more object oriented date wise - i needed it for the month to month stuff anyway...that kind of functionality to the end user will have to come after this hurdle though....

I've done some access stuff over the years... every time seems to be a very different project, all stuff i've picked up as i went. i knew enough to know access would solve a GREAT MANY of the problems here (though i hadn't implemented all the things i've had to do for this i did know they were there). we're a mid range construction company that grew quick - they're still wrapping their heads around managing all the data. i'm the only one here that even gets that this is a solution. my boss went on holidays and gave me 4 weeks to prove we can do this - after we get base functionality and he's impressed i can work on this indefinitely....

but point well taken, asap end of financial year will be adjustable. i aprreciate the input always

On that note:

many people here have helped me greatly, Bob Larson in particular has given quite freely of his time often and i say thanks again. many many more but i can't remember all your names just now.

 

[boblarson]

Just in case it hadn't come out elsewhere, you can get

the first half of year by using:

Between DateSerial(Year(Date()), 1, 1) And DateSerial(Year(Date()), 6, 30)

And second half:

Between DateSerial(Year(Date()), 7, 1) And DateSerial(Year(Date()), 12, 31)


And Monthly (assuming prior to the current month):

Between DateSerial(Year(Date()), Month(Date()) - 1, 1) And DateSerial(Year()), Month(Date()),0)

So, DateSerial can be your friend.

 

[CHAOSinACT]

i don't even know what to say bob - you just ANTICIPATED todays question (and believe it or not i work hard to look myself and not ask RIGHT away).

breaking things into months was todays scripting exercise
your a legend as always

*runs off to read about DateSerial...*

 

[boblarson]

And a quick explanation about the last one (the month ending).

In that one since we are looking for the last day of last month, we use day 0 of this month. Day one is the first and day 0 actually subtracts a day so it turns the date into a day before the first of the month. Does that make sense? When I finally understood that formula (courtesy of RuralGuy's help), it opened up a whole new world for me. And believe it or not, it was just a couple of years ago that I finally got that one.

 

[CHAOSinACT]

And a quick explanation about the last one (the month ending).

In that one since we are looking for the last day of last month, we use day 0 of this month. Day one is the first and day 0 actually subtracts a day so it turns the date into a day before the first of the month. Does that make sense? When I finally understood that formula (courtesy of RuralGuy's help), it opened up a whole new world for me. And believe it or not, it was just a couple of years ago that I finally got that one.

cheers, yes makes PERFECT sense and just the kind of thing you can soldier through and not know with more code (no doubt the road i would have been on)

i may not be the best access developer (lol) but i've done autocad management and application development for 20 years and i delight in the fact that you can always learn more. access seems like autocad in that way - ALWAY something new to learn, a knew, better way to do it.

 

[CHAOSinACT]

holy @#$. that is awesome!!!! exactly what i need, and will make programming it so easy. just tried it now

 

[boblarson]

Yeah, makes things very dynamic and keeps you from having to make changes or code in dates, use forms for selecting dates, etc. (doesn't eliminate that completely but does quite a bit for you depending on what you need)

 

[CHAOSinACT]

i've read (not sure) that since we use the day/month/year format here in the UI i still need to code my dates month/day/year *US format* - DateSerial returns (to my surprise) day/month/year (my local format) i wonder if 2007 is now smart enough to run both code and UI on same date settings?

 

[CHAOSinACT]

my information simply must be wrong or out of date, as the date() functions returns local format in debug.print....