Filtering records in a form 2

[1DMF]

I seem to have hot a brick wall and don't u8nderstand what's wrong...

i am trying to apply the following filter...

    sSQL = "([Flag_Date] < '" & Format(DateAdd("d", -5, Now()), "dd/mm/yyyy") & "') AND ([Final_Rating] IS NULL OR [Final_Rating] > 1) AND ([Override_By] IS NULL OR [Override_By] = '') AND ([Compliance Officer] IS NULL OR [Compliance Officer] = '')"

    [Forms]![case_checking].[Case_Checking subform].[Form].Filter = sSQL

But I keep getting

The search key was not found in any record

What does that mean and why am i getting it?

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

 

[PHV]

I'd try this:

    sSQL = "Flag_Date<#" & Format(Date - 5, "yyyy-mm-dd") & "# AND (Final_Rating IS NULL OR Final_Rating>1) AND (Override_By IS NULL OR Override_By='') AND [Compliance Officer] IS NULL OR [Compliance Officer]='')"

Note: you really have a space in the name of the field [Compliance Officer] ?

Hope This Helps, PH.
FAQ219-2884
FAQ181-2886

 

[1DMF]

Note: you really have a space in the name of the field [Compliance Officer] ?

Yeah, sorry the DB was developed over many, many years and has some bad practices that aren't all down to me, and some that I now know better!

As long as you use the brackets, it's fine!

"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

 

[dhookom]

1DMF,
Any response regarding the filter suggestion by PH?

If you haven't tried it, you should suggest why you haven't. If you have tried it, then you should provide us with the results.


Duane
Hook'D on Access
MS Access MVP

 

[1DMF]

Hey Duane,

No response yet, I haven't tried it yet!

The reason I haven't tried it yet is because it's 10:20pm, on a Friday night and I've been out with the lads drinking and playing pool.

When I'm back at the day job on Monday, I shall duly try it and report back with my findings.

Have a great weekend

"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

 

[dhookom]

1DMF,
Tip up a cold one for me ;-)


Duane
Hook'D on Access
MS Access MVP

 

[1DMF]

Nice one PHV!

That did the trick, so why does SQL take single qoutes and filters require hashes?

"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

 

[dhookom]

MS Access queries require # delimiters for date/times.

Duane
Hook'D on Access
MS Access MVP

 

[1DMF]

thanks duanne, I get a little confused as in the query design screen you don't use '#' if using a function such as dateadd.

ie.

>=Format(DateAdd("d",-7,Now()),"yyyy/mm/dd")

No '#' is used and it works correctly.



"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