Filter Query By Date

[Ripley]

I've searched the forums, and i cant find an answer there.

What i want to do is filter a query field by the current system date, but only using the dateparts Day an Month (no Year).

How would i go about it?

 

[FLM]

if it really is in a query, create an extra field:
selectdate: format (fieldname,"mm/dd")

then in criteria you put format(date,"mm/dd")

 

[TedMartin]

Converting Date to a String

Try this - open Form1 and press the button.

Hopefully you can follow the steps which can all be done within Form 1 by code if you like.

 

[ted.martin]

Of course the table I used has today's day and month in it. For the filter to work on (say) 17th you will have to amend the table.

 

[Ripley]

dam i cant open your database ted, im using Access 2000, could you possibly re-save it and upload it again?

 

[ted.martin]

Sample in v2000 - dates in table changed to 17th August (today)

 

[TedMartin]

Even better solution

Just create two new fields on your form called DateAsString and FilterDate connected to the Query in the sample. Then use the following code. The forms underlying query does require these two additional calculation fields as per the sample db1 provided previously. DateasString:Cstr([DateField]) FilterDate:Left([DateasString],5)

Private Sub Form_Load()

' do you need the next line. If not remove.
Me.AllowAdditions = False

End Sub

Private Sub Form_Open(Cancel As Integer)

Dim datDate As Date
Dim strDate As String

DateasString.Visible = False
FilterDate.Visible = False

'Simplo logic to follow
datDate = Date
'Now convert to a string and extract the first dd/mm part
strDate = Left(datDate, 5)

Me.Filter = "FilterDate = '" & strDate & "'"
Me.FilterOn = True

End Sub

 

[TedMartin]

Further bit of code after the statement Me.FilterOn = True

Private Sub Form_Open(Cancel As Integer)

Dim datDate As Date
Dim strDate As String

DateasString.Visible = False
FilterDate.Visible = False

datDate = Date

strDate = Left(datDate, 5)

Me.Filter = "FilterDate = '" & strDate & "'"
Me.FilterOn = True

'This section prevents the form from opening when the FilterDate field has no matching records
If Me.RecordsetClone.RecordCount = 0 Then
MsgBox "No matching record found", vbInformation, "Nothing found"
DoCmd.Close acForm, "Form"
Else
End If


End Sub