Date comparison just wont work

[gadjet]

I've got a form with a button that applies a filter to limit the list to records that meet certain criteria, date and status.

This code worked! but now it won't can any one see what I'm doing wrong.

Me.Filter = "[test status]= '" & "not started" & "'" & " AND " & "[planned finish date] < #" & [text30] & "#"

Where [text30] contains a date formatted as dd/mm/yyyy. The first part works filtering on test status = "not started" but the date comparison will not work, i've also tried using Date() and Now() instead of [text30].

Any help would be appreciated

 

[DCrake]

Simple Software Solutions

Few tests to try

First declare a variable to hold the filter string

Dim strSQL as string

strSQL = "[test status]= '" & "not started" & "'" & " AND " & "[planned finish date] < #" & [text30] & "#"

Next debug.print the strSQL to see what it contains. If it looks ok try and create a query based on the underlying table and include the filter to see if you get the desired results.

If it does not you should be able to trace the fault(s)


CodeMaster:

 

[gadjet]

Few tests to try


Next debug.print the strSQL to see what it contains. If it looks ok try and create a query based on the underlying table and include the filter to see if you get the desired results.

If it does not you should be able to trace the fault(s)

CodeMaster:

Thanks, I tried this but I can't find a fault, just that it does not filter correctly, the debug print:
[test status]= 'not started' AND [planned finish date] < #10/03/2008#

This should work but it doesn't.

I tried a query on the table and typed in "not started" and <#10/03/2008# in the query and it worked fine. (see attachment)

I am at a loss as to what to try next other than having seperate forms for each of my filters.

 

[Brianwarnock]

I noticed that you had criteria for the projectid in the query, is that a red herring?

Brian

 

[DCrake]

Simple Software Solutions

Send me a snippet of the app to look at. I am sure it is a simple problem that is easier to detect if I have access to to the nuts and bolts.


David

 

[gadjet]

I noticed that you had criteria for the projectid in the query, is that a red herring?
Brian

When I created the new test query I needed to filter by Project, this was allready done in the original form by its source data query, using the same table data as the test query.

I also tried binding the original form (the one that doesn't work) to the unfiltered table and adding the project ID to the filter along with the status and date but this didn't work.

What I'm doing now is having one form with multiple sub forms each with their own query as per my last post.

So far that seems to work OK.

 

[Mile-O]

Me.Filter = "[test status]= '" & "not started" & "'" & " AND " & "[planned finish date] < #" & [text30] & "#"

Where [text30] contains a date formatted as dd/mm/yyyy.

The date format is only for display purposes and therefore it won't affect your query.

Your Filter is also longwinded. You can shorten it thus:

Me.Filter = "[test status]= 'not started' AND [planned finish date] < #" & [text30] & "#"

Anyway, try changing the Format in the query: i.e.

Me.Filter = "[test status]= 'not started' AND [planned finish date] < #" & Format([text30], "mm/dd/yyyy") & "#"

 

[gadjet]

The date format is only for display purposes and therefore it won't affect your query.

Your Filter is also longwinded. You can shorten it thus:

Me.Filter = "[test status]= 'not started' AND [planned finish date] < #" & [text30] & "#"

Anyway, try changing the Format in the query: i.e.

Me.Filter = "[test status]= 'not started' AND [planned finish date] < #" & Format([text30], "mm/dd/yyyy") & "#"

Thanks for the tidy up info, it's obvious when you see it, I just copied code from a previous project and edited, I'll read it properly in future.

I tried your suggestion but still no joy, I've given up now an redone it as a form with a Tab control and on each tab a different sub form each with its own query, it works and it actually looks a bit better than the previous form.

I still don't understand why it didn't work though ???

 

[gemma-the-husky]

are you sure its not the [test status] thats causing the problem. what values can this take - is "not started" a valid value, or is this a lookup of some sort

 

[gadjet]

are you sure its not the [test status] thats causing the problem. what values can this take - is "not started" a valid value, or is this a lookup of some sort

"not started" is valid, the filter actually applies that part to the form but doesn't apply the second part, the date < today.

The really annoying thing is that when I originally deployed this code it worked...... now it doesn't ????????

 

[gemma-the-husky]

so what is [text30] then - i assume this an unbound field - if so you probably need a me.requery in the after update event of that field

 

[gadjet]

so what is [text30] then - i assume this an unbound field - if so you probably need a me.requery in the after update event of that field

[Text30] is an unbound text box that gets todays date put in it when the form loads.

When the filter is applied it filters using the [test status] = ..... but ignores the date part.

 

[gemma-the-husky]

is [text30] in the header or detail section of the form - it would probably make a difference. try tracing the code, to see what value text30 has when the programe is exceuted. breakpoint and f8 to step thorugh code

 

[gadjet]

is [text30] in the header or detail section of the form - it would probably make a difference. try tracing the code, to see what value text30 has when the programe is exceuted. breakpoint and f8 to step thorugh code

Yep! tried most of that already, post 3 shows the debug.print of the strSQL which shows text30 had the date in it.


Cheers

 

[gemma-the-husky]

in your sample jpeg you have productid = 14 as another criteria - its not that causing the problem is it?

checking again, i see Brian's asked you that already