DAO.recordset filter won't work with preceding wildcard?

[1DMF]

Hi,

I seem be going round in circles at the moment trying to find the most efficient way of getting a recordset and poviding a search facility (filtering) for remote users.

I've just written myself a new helper class for getting a recordset via a stored procedure and apply a filter to it for my GUI form, everything is dandy.

Except I now find that when I apply a filter to the recordset with a wild card to srart with it is ignored ..ie.

rs.Filter = "CompanyName LIKE '*" & sFilter & "*'"

It returns as if the first wildcard wasn't present?

Am i doing something wrong, Or do I need to write a helper method that builds a filter string with...

rs.Filter = "CompanyName LIKE 'a*" & sFilter & "*' OR CompanyName LIKE 'b*" & sFilter & "*' OR CompanyName LIKE 'c*" & sFilter & "*'" ...

you get the picture up to z!

Help with this is appreciated, I was so close to finally geting a lightening fast remote user application, and now DAO has thrown this curve ball at me!

Thanks,
1DMF

"In complete darkness we are all the same, only our knowledge and wisdom separates us, don't let your eyes deceive you."

"If a shortcut was meant to be easy, it wouldn't be a shortcut, it would be the way!"

MIME::Lite TLS Email Encryption - Perl v0.02 beta

 

[HarleyQuinn]

Did your first example work? Because your second example really isn't an attempt at filtering with a wildcard at the start (you've got one mid string and one at the end)...

Andy
---------------------------------
Zebracorn: 50% Zebra, 50% Unicorn = 100% Real.

http://lessthandot.com

 

[1DMF]

Hi Harley, yup got it working thanks.

It wasn't the filtering, there was an error in the SP!

Though I think you mis-read the point of my second example.

Anyway, all sorted, time for a coffe break and do something less taxing!

"In complete darkness we are all the same, only our knowledge and wisdom separates us, don't let your eyes deceive you."

"If a shortcut was meant to be easy, it wouldn't be a shortcut, it would be the way!"

MIME::Lite TLS Email Encryption - Perl v0.02 beta

 

[HarleyQuinn]

Glad you got it working

I'm guessing that your second example was indeed an example then, not meant to illustrate the code you were using?

Andy
---------------------------------
Zebracorn: 50% Zebra, 50% Unicorn = 100% Real.

http://lessthandot.com

 

[1DMF]

yeah, I thought the wildcard wasn't working if used as first character, and the thing that kinda enforced that opinion was searching google. There is a plethora of people saying it doesn't work.

I thought I'd have to create a massive filter with each letter of the alphabet, then the wildcard, then the users input, then another wildcard.

Turns out the records I was trying to filter weren't even in the datasource, as the SP wasn't working right.

So went on a bit of a wild goose chase before I realised the problem.

It's now working really well, I can see massive improvements with my apps for remote users on the horizon, just hope I'm given the time to re-write exisiting apps, but you know what bosses are like, always wanting something new even before a previous project has been finished!

Hey ho, keeps me busy I guess!




"In complete darkness we are all the same, only our knowledge and wisdom separates us, don't let your eyes deceive you."

"If a shortcut was meant to be easy, it wouldn't be a shortcut, it would be the way!"

MIME::Lite TLS Email Encryption - Perl v0.02 beta