Query by Form - Working - Get Results in Subform?

[andmunn]

Hi SoS,

One more quick question - instead of having one date field, i have a "from date" and a "to date" field.... I Created the following code:

If Not IsNull(Me.WhatDate) Then
strFilter = strFilter & "([dteAudit] >= " & Format(Me.WhatDate, conJetDate) & ") AND "
End If
 
 
If Not IsNull(Me.WhatDateEnd) Then 'Less than the next day.
strFilter = strFilter & "([dteAudit] < " & Format(Me.WhatDateEnd + 1, conJetDate) & ") AND "
End If

I also added the following code to the beginning of the code:

Const conJetDate = "\#mm\/dd\/yyyy\#"

I must be missing something - as i get an error and the search doesn't work anymore...

Basically, i want to return results between whatever dates are chosen.... (from dteAudit).

What am i missing?

Thanks,
Andrew.

 

[andmunn]

Anyone any idea whats going on thats wrong with the code above?
Thanks
Andrew.

 

[SOS]

What is the exact error? What line does it highlight if you hit the Debug button on the error message?

 

[andmunn]

I get a popup which states " Run Time Error - 13" - Type Mismatch.

It then highlights:

 strFilter = strFilter & "([dteAudit] < " & Format(Me.WhatDateEnd + 1, conJetDate) & ") AND "

 

[andmunn]

Anyone have any advice?

I've attached the most recent version of the database. The problem comes with the "frmsearch" from / to date fields... Doesn't return the correct results..

The first part of the code seems to work (i.e.// it i enter date "2008/04/30" - it will give me everything from that date, to the end), however, if i enter an end date (i.e.// 2008/04/30 to 2009/04/30) the error seems to creep up.

Any advice how to fix this problem?

Andrew.

 

[SOS]

Change this:

    If Not IsNull(Me.WhatDateEnd) Then   'Less than the next day.
        strFilter = strFilter & "([dteAudit] < " & Format(Me.WhatDateEnd + 1, conJetDate) & ") AND "
    End If

to this:

    If Not IsNull(Me.WhatDateEnd) Then           
       strFilter = strFilter & "([dteAudit] [COLOR=red][B]<=[/B][/COLOR]" & Format([COLOR=red][B]Me.WhatDateEnd[/B][/COLOR], conJetDate) & ") AND "
    End If

 

[andmunn]

Thanks so much! Works like a charm (like always). My error makes sense as well...

FYI - is there any way to "sort" these results so that when the filter returns the results, they are sorted first by:

Process, than by Date?

Thanks.