Solved Filtering Forms Based on a Calculated Field

[Benginner2212]

In my database, I have a form titled frmEquipTrack that has a calculated field titled txtMainDueDate that is supposed to be the date that a piece of equipment is supposed to be serviced by. When I I am trying to filter the forms by the date in the txtMainDueDate field so that I only get records where the date in the txtMainDueDate is in the current month. So far this is the code that I have:

Private Sub btnOpenMonthlyMaint_Click()
    Dim frmDateCriteria
    Dim fDate As Date
    Dim lDate As Date
    
    fDate = DateSerial(Year(Date), Month(Date), 1)
    lDate = DateSerial(Year(Date), Month(Date) + 1, 0)
    frmDateCriteria = "txtMaintDueDate between #" & fDate & "# AND #" & lDate & "#"
    Debug.Print "fDate  "; fDate
    Debug.Print "lDate  "; lDate
    Debug.Print "frmDateCriteria  "; frmDateCriteria
    DoCmd.OpenForm "frmEquipTrack", acNormal, , frmDateCriteria, acWindowNormal, "MonthlyPM"
    
End Sub

When I run the code, I get a data mismatch error. I am not sure what I am doing wrong. Is it possible to filter forms based on a calculated field? Should this be a filter that is in the On Load Event of the frmEquipTrack?

 

[theDBguy]

What is your Regional Settings? Is it US?

 

[bastanu]

You need to reference the bound field name, not the control name in the Where clause of your OpenForm method call.

Cheers,

 

[Mike Krailo]

If your scheduling maintenance on equipment at regular intervals, you might want to consider using the technique from Allen Browne's demo on Recurring Events. I modified the demo a little bit to incorporate a table of equipment and made a form for adding more equipment and scheduling as many intervals of maintenance as you want in advance. Then each month, you print out the report for equipment that is due for maintenance. I made a simple report that filters by month showing all the equipment for the selected month. You can incorporate this idea into your own database if you wish.

 

[Gasman]

In my database, I have a form titled frmEquipTrack that has a calculated field titled txtMainDueDate that is supposed to be the date that a piece of equipment is supposed to be serviced by. When I I am trying to filter the forms by the date in the txtMainDueDate field so that I only get records where the date in the txtMainDueDate is in the current month. So far this is the code that I have:

Private Sub btnOpenMonthlyMaint_Click()
    Dim frmDateCriteria
    Dim fDate As Date
    Dim lDate As Date
  
    fDate = DateSerial(Year(Date), Month(Date), 1)
    lDate = DateSerial(Year(Date), Month(Date) + 1, 0)
    frmDateCriteria = "txtMaintDueDate between #" & fDate & "# AND #" & lDate & "#"
    Debug.Print "fDate  "; fDate
    Debug.Print "lDate  "; lDate
    Debug.Print "frmDateCriteria  "; frmDateCriteria
    DoCmd.OpenForm "frmEquipTrack", acNormal, , frmDateCriteria, acWindowNormal, "MonthlyPM"
  
End Sub

When I run the code, I get a data mismatch error. I am not sure what I am doing wrong. Is it possible to filter forms based on a calculated field? Should this be a filter that is in the On Load Event of the frmEquipTrack?

I would be formatting the date fields so as to be able to concatenate them, as you appear to be trying to do.
I used to use this constant as I could never remember the correct syntax with backslash etc.

Public Const strcJetDate = "\#mm\/dd\/yyyy\#"  'Needed for dates in queries as Access expects USA format.

Then Format(yourDate,strcJetDate)

 

[Benginner2212]

What is your Regional Settings? Is it US?

The regional setting is US and I am using a short date format.

 

[Benginner2212]

You need to reference the bound field name, not the control name in the Where clause of your OpenForm method call.

Cheers,

This is an unbounded text box and the control source for the text box is an equation that calculates the date for the next required date for maintenance.

 

[theDBguy]

This is an unbounded text box and the control source for the text box is an equation that calculates the date for the next required date for maintenance.

I would try moving the expression to the query and binding the Textbox to that calculated column.

 

[Benginner2212]

I would try moving the expression to the query and binding the Textbox to that calculated column.

I will give that a shot. Now I was warned against saving calculated fields to a field in a table or a form and that is why I am using an unbounded text box to do the calculations. Would I be saving that calculated information to my tables and forms if I moved the calculations to the query that the form is based on?

 

[theDBguy]

Would I be saving that calculated information to my tables and forms if I moved the calculations to the query that the form is based on?

No, it would not be saving the calculation in the tables. The results are recalculated every time you run the query.

 

[Benginner2212]

No, it would not be saving the calculation in the tables. The results are recalculated every time you run the query.

I moved the calculations to the query that I am basing the form on and now I am able to filter the forms based on the correct maintenance due date. For some reason I was not able to bind the calculation in the query to the unbounded text box on my form. I had to drag and drop the calculated field on to the form from the add existing fields tab on the form designer tab.

 

[theDBguy]

I moved the calculations to the query that I am basing the form on and now I am able to filter the forms based on the correct maintenance due date. For some reason I was not able to bind the calculation in the query to the unbounded text box on my form. I had to drag and drop the calculated field on to the form from the add existing fields tab on the form designer tab.

Glad to hear you got it sorted out. Good luck with your project.

 

[iaasiqbal]

If your scheduling maintenance on equipment at regular intervals, you might want to consider using the technique from Allen Browne's demo on Recurring Events. I modified the demo a little bit to incorporate a table of equipment and made a form for adding more equipment and scheduling as many intervals of maintenance as you want in advance. Then each month, you print out the report for equipment that is due for maintenance. I made a simple report that filters by month showing all the equipment for the selected month. You can incorporate this idea into your own database if you wish.

Can I set every six month

 

[Mike Krailo]

Yes, that's semi annual.

 

[iaasiqbal]

Requesting you Please share the file

 

[iaasiqbal]

please help to add semi annual period