Results 1 to 7 of 7
  1. #1
    Join Date
    Feb 2005
    Posts
    31

    Exclamation Unanswered: Field criteria based on System Date(Month). Is it possible? Please help!

    I hope there’s a solution for this dilemma.

    I have a Form, 2 Tables & 2 Listboxes.

    Tables Layout:

    AgentTable

    AgentID | FirstName | LastName |

    MonitorTable

    MonitorID | Date | Score | AgentID |

    My question is, is there a way to make MonitorListbox display only monitors for the current month? Is there a criteria I could place under Date field based on current System Date(Month in this case)?

    What I've done so far...

    1. I’ve place a SELECT Query into AgentListbox so that it can display a list of all agents(records) stored in AgentTable.

    1.2 I’ve placed an ON CLICK Event behind AgentListbox like this:

    MonitorListbox.Requery

    2. I’ve placed a SELECT Query into MonitorListbox so that it can return all monitors(records) stored in MonitorTable based on selected agent from AgentListbox.

    The problem I encountered is the fact that MonitorListbox will return all monitors done in the agent by their respective supervisor throughout the Year and my goal is to keep it in a Month-To-Date basis. The goal here is to only display monitors dated the current month even know there may exist monitors for many different months. Then, when System Date changes to a new month, shall not retun any results since it is now the begining of a new month.

  2. #2
    Join Date
    Feb 2004
    Location
    Canada
    Posts
    133
    In your query you could add a new field, and in its Field: row put

    fldMonth: Month([YourDateField])

    then in the new field's criteria row put

    =Month(Now())

    If your data spans multiple years this method may include the same month from previous years, so you might wish to incorporate the year as well.

    Also, you should avoid using the word DATE as a field name as it is a reserved word in Access and could be interpreted by Access in more than one way.

  3. #3
    Join Date
    Feb 2005
    Posts
    31
    Brilliant! What an approach! Thank you!

    Out of curisity, is there a way to customize date field to something like:

    Short Date i.e. 0/0/0000 instead of 0.

  4. #4
    Join Date
    Feb 2004
    Location
    Canada
    Posts
    133
    If you want the query itself to show that format, first make sure the properties window is open (View/Properties) then just open the query in design view and click once somewhere in the date field. The properties window should say Field Properties in the title bar. There you can set the Format property to mm/dd/yyyy. Essentially the same procedure applies for a textbox on a form.

  5. #5
    Join Date
    Feb 2005
    Posts
    31
    I tried that which seems logical however Access returns 1/1/1900, weird eh?

    My Last question to you is, can I modify the expressions below to do the same exact thing, only this time, basing themselves in Month(Now())?

    Examples:

    Textbox 1: Counts all completed monitors done in agent so far.

    Code: =DCount("[QualityID]","Quality")

    Textbox 2: Counts all required monitors that must be done in agent. Required 4 per month.

    Code: =DCount("[QualityID]","Quality")*(4)

    Textbox 3: Counts all records containing the word ALL in Score field

    Code: =DCount("[QualityID]","Quality","[QRating] = 'ALL'")

    Textbox 4: Counts all records containing the word SOME in Score field

    Code: =DCount("[QualityID]","Quality","[QRating] = 'SOME'")

    Textbox 5: Counts all records containing the word NONE in Score field

    Code: =DCount("[QualityID]","Quality","[QRating] = 'NONE'")

    Textbox 6: Calculates the Average of all records stored in QAverage field

    Code: =DAvg("[QAverage]","Quality")

  6. #6
    Join Date
    Feb 2004
    Location
    Canada
    Posts
    133
    You must be applying that format to your fldMonth field. I assumed you wanted to apply it to your original date field. Applying it to your month field, which currently being February would return 2, will return 01/01/1900 because it reads the number 2 as the serial number of the date. 1 would return 12/31/1899. I'm not clear on what you want here.

    As for your last question, I think it should work if your form with all the textboxes is based on a query that has a date field in it.

  7. #7
    Join Date
    Feb 2005
    Posts
    29
    Hi just wonder if i want the user to specified which month to filter for the query how should i do it? Thanks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •