filter string question

[CHAOSinACT]

ok i'm stumped here:

this filter list on query ok,

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

but this one isn't:

JobNumber Like '001' Or JobNumber Like '001-*' And ActivityDate Between #1/01/1950# And #30/06/2010#

Can't see it myself. works in QBE grid but when i try to refine in vba no go, first worked fine...

 

[CHAOSinACT]

seems to work on a query where i don't use the between dates. really need them, is there a max number of filter args that can be passed with the .filter method? do i have to filter twice? hope not...

 

[Galaxiom]

In the query designer Access displays dates in the System Date Format.
In sql the date must be #MM/DD/YYYY#

Use Format({date expression},"\#mm/dd/yyyy\#")

 

[CHAOSinACT]

we're souther hemisphere access 2007 seems to be ok with that here. frankly the date was working till i changed the jobnumber thing.

ie:

JobNumber Like '096' Or JobNumber Like '096-*' works fine

JobNumber Like '001*' And ActivityDate Between #1/01/1950# And #30/11/1999# -works fine

JobNumber Like '001' Or JobNumber Like '001-*' And ActivityDate Between #1/01/1950# And #30/11/1999# doesnt work(ofc the one i need)

btw the dates are provided using DateSerial and worked fine before the JobNumber changes....

 

[CHAOSinACT]

btw not sql direct. i'm using the .Filter property/method on a loaded DAO recordset. i'm considering filtering the recordset twice but seems inefficient and shouldn't really have to?

 

[Galaxiom]

I am very surprised that the regional settings would handle dd/mm/yyyy as an sql date. Even in dd/mm/yyyy regions the Access designer input will convert the sql to mm/dd/yyyy. Seems even less likely in a DAO recordset.

I suspect you are not getting what you expect.
Also try using parentheses to group the JobNumber Or conditions together.

I would also use equals operator on the first bit. It is more efficient than Like where there is no variation specified.

 

[CHAOSinACT]

when the date order is wrong i get a vba error so sure they're right and dateserial sends them that way...to elaborate a bit, what i'm showing is the debug.print statement.

it seems unhappy to filter the jobnumber twice and the activity date at same time.

would do 1 job number filter and a date. won't let me double filter...the dates haven't changed at all and can't see the prob in job number.

 

[CHAOSinACT]

LMAO!!! took me forever to check field names in query, esp SINCE THEY WERE FINE BEFORE...how the heck did it run last time???

 

[Galaxiom]

LMAO!!! took me forever to check field names in query, esp SINCE THEY WERE FINE BEFORE...how the heck did it run last time???

The embarrassment of missing the obvious is often tempered by the relief of finding the problem.

How did it happen? AutoCorrect? AKA AutoCorrupt. With it on (default), changing the fieldnames in a table will change in queries and control sources. Works with tablenames and forms too.

Many developers turn it off, especially those once bitten by making copy an object then renaming the original as a backup not realising the implications.

However it does not extend to VBA code.

 

[raskew]

Hi -

This approach might simplify your options:

left(JobNumber,3) = '001' And ActivityDate Between #1/01/1950# And #6/30/2010#

Best wishes - Bob

 

[gemma-the-husky]

i think the reason the date string was ok in either US or UK formats was

1/1/50 is the same in both

30/11/99 is unambiguously not a us date, so it is automatically treated as UK

try 6/12/99, and you probably WILL get the wrong data returned.

 

[CHAOSinACT]

i think the reason the date string was ok in either US or UK formats was

1/1/50 is the same in both

30/11/99 is unambiguously not a us date, so it is automatically treated as UK

try 6/12/99, and you probably WILL get the wrong data returned.

and yet giving the compiler 11/30/99 gives a compile error. is everyone one 2007 and later with this? i will triple check it.

 

[CHAOSinACT]

The embarrassment of missing the obvious is often tempered by the relief of finding the problem.

How did it happen? AutoCorrect? AKA AutoCorrupt. With it on (default), changing the fieldnames in a table will change in queries and control sources. Works with tablenames and forms too.

Many developers turn it off, especially those once bitten by making copy an object then renaming the original as a backup not realising the implications.

However it does not extend to VBA code.

Dude i swear i had a DAO of a query for over 1.5 days working fine with ActivityDate: TransactionsDate in the query but some um, "reverted"? to just TransactionDate. Weird. how it ran all Friday i'll never ever know....

 

[CHAOSinACT]

i think the reason the date string was ok in either US or UK formats was

1/1/50 is the same in both

30/11/99 is unambiguously not a us date, so it is automatically treated as UK

try 6/12/99, and you probably WILL get the wrong data returned.

ARRRRGHHH#$@#$@#$@#$@
somehow YOU ARE RIGHT. now, this sh!ts me on SEVERAL levels.

1. i read about this in advance. doing it right seemed to generate errors. prob my fault so not the biggest issue

2. DateSerial returns dates IN LOCAL FORMAT!!! so i can't use it to pass dates, work out last day of month, WHATEVER!!! wtf? why doesn't dateserial work the way it does in code????(storms off to find a ms rep to beat to death...) this is tragic as it was a very useful function. clearly only to be used by northern hemisphere where it returns AS IT SHOULD.

 

[Galaxiom]

DateSerial returns dates IN LOCAL FORMAT!!! so i can't use it to pass dates, work out last day of month, WHATEVER!!! wtf? why doesn't dateserial work the way it does in code????

I expect underneath the hood DateSerial actually first returns the date as Double then it gets formatted according to the Regional Settings.

Provided you then Format it back to a string into #mm/dd/yyyy# it will work in sql for those purposes you mentioned. Remember that an sql command is nothing but a string. The intepretation of this string to work with a date happens in the engine.

Even US developers should always format dates to a string for sql or their code will fail in other regions. Or worse, it will work until part way through the month potentially leaving a mess to be cleaned up.

The way Access automatically transposes invalid but foreign compatible dates totally sucks and causes more problem than it solves (which is none). Computers can never be trusted to make assumptions.

Many developers concatenate the # characters but I prefer to use the Format function. Of course the Format function is not native sql so that can sometimes be a weakness but it is very tidy in Access.

It is worth including the conversion as custom function to simplify the code if your project uses sql dates a lot.

Incidentally for date and time use:
Format({date expression}, "\#mm/dd/yyyy hh\:nn\:ss\#")

While we are on the topic note that SQL Server uses single quotes instead of the #. So use the single quotes for Passthrough queries etc.