Filter with date - problem

[johndoomed]

Hi,

I have problems with a date filter. I've tried searching, but could not find the answer.

My code:

Private Sub filter_Click()
On Error GoTo Err_filter_Click

Statid = Me.Status_ID
MyDate = Date

Dim stDocName As String
Dim stLinkCriteria As String

stDocName = "Faktura_oversikt"

stLinkCriteria = "[Status ID]=" & Statid
DoCmd.OpenForm stDocName, , , stLinkCriteria

MsgBox (MyDate)

Forms!Faktura_oversikt.filter = "[Action dato] =< MyDate"

Exit_filter_Click:
Exit Sub

Err_filter_Click:
MsgBox Err.Description
Resume Exit_filter_Click

End Sub

The two lines are highlighted.

I want the form to show the posts with has [Action dato] equal or larger then todays date.

 

[jkl0]

I am assuming that when this runs it shows a single record.

Change the first line to:
MyDate = Date()

stLinkCriteria is the filter that is automatically being applied when the form is opened, so it needs to change to the following (Also assuming MyDate is dimensioned as a string)

stLinkCriteria = "[Action dato] >= '" & MyDate & "'"

If you switch to design view after the form is opened the filter should look like:
[Action dato] >= '7/20/06' (Or however your date is formated to)

If Action dato and Mydate are formatted differently then you will neet to change the format of MyDate to match so that the comparison can be made.

 

[johndoomed]

Thank you for your reply!

However.. Access (2000) does not allow me to set Mydate = Date(). The "()" is removed when I save.

And the stLinkCriteria has to use the [Status ID] field. Can I use two criterias with stLinkCriteria?

 

[johndoomed]

Since nobody has come up with a solution to this by now, I assume the it is either difficult or imposible.

Then to work around the problem : Can I, with VBA, open a form and select which query this form should be based on?

 

[jkl0]

MyDate = Date will work OK.

and

stLinkCriteria = "[Status ID]=" & Statid & ";[Action dato] >= '" & MyDate & "'"

 

[johndoomed]

Thanks, but this line gives me "Syntaks error".

 

[jkl0]

My mistake, try

stLinkCriteria = "[Status ID]=" & Statid & " And [Action dato] >= '" & MyDate & "'"

 

[raskew]

Hi -

Suspect you need to dimension Statid and MyDate.

Bob

 

[johndoomed]

A large thanks to both of you!

However, this still dont work. My code:

Private Sub filter_Click()
On Error GoTo Err_filter_Click

    Dim Statid As Integer
    Dim MyDate As Integer

    Statid = Me.Status_ID
    MyDate = Date
    
    Dim stDocName As String
    Dim stLinkCriteria As String

    stDocName = "Faktura_oversikt"
        
    stLinkCriteria = "[Status ID]=" & Statid & " And [Action dato] >= '" & MyDate & "'"
    DoCmd.OpenForm stDocName, , , stLinkCriteria

Exit_filter_Click:
    Exit Sub

Err_filter_Click:
    MsgBox Err.Description
    Resume Exit_filter_Click
    
End Sub

This code gives only one word for error: "Overflow"...

 

[RV]

Private Sub filter_Click() 
On Error GoTo Err_filter_Click 
     
Dim stDocName As String Dim stLinkCriteria As String 

    stDocName = "Faktura_oversikt" 
         
    stLinkCriteria = "[Status ID]=" & Me.Status_ID & " And [Action dato] >= #"  & Format(Date, "mm-dd-yyyy") & "#" 
    DoCmd.OpenForm stDocName, , , stLinkCriteria 

Exit_filter_Click: 
    Exit Sub 

Err_filter_Click: 
    MsgBox Err.Description 
    Resume Exit_filter_Click 
     
End Sub

RV

 

[johndoomed]

Thanks! Thats it!!!