Results 1 to 9 of 9
  1. #1
    2 Star Lounger
    Join Date
    Aug 2002
    Location
    Minneapolis, MN, Minnesota
    Posts
    162
    Thanks
    1
    Thanked 0 Times in 0 Posts

    Count Function (2000)

    I would like to know if someone could help me with the following problems involving counting:

    1. I would like to count the unique records from tblSchedule where the field strClinicName contains the word
    Easy Access

  2. #2
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Count Function (2000)

    1. Create a query qryCUB that selects unique clinic names from tblSchedule where chkCancelClinic is False:
    <code>
    SELECT DISTINCT strClinicName
    FROM tblSchedule
    WHERE strClinicName Like "CUB*" AND chkCancelClinic=False
    </code>
    Delete the Form_Current code for the form, and set the Control Source of ClinicCounter to
    <code>
    =DCount("*","qryCUB")
    </code>
    2. Change the name of the lngNurseCount text box to lngNurseCnt (to avoid confusion with the field) and set its Control Source to
    <code>
    =DCount("*","tblScheduledEmployees","lngEmployeeID Is Not Null And strEmployeeType='RN' And lngScheduleID=" & [ScheduleID])
    </code>
    No code needed to update it.

  3. #3
    5 Star Lounger
    Join Date
    Nov 2004
    Location
    Wilmington, North Carolina, USA
    Posts
    1,196
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Count Function (2000)

    <img src=/S/whisper.gif border=0 alt=whisper width=29 height=17>I'm glad I checked for new replies before I posted mine...he would have gotten the same solutions twice!

    Hans, you and those 6 arms.... <img src=/S/aflame.gif border=0 alt=aflame width=16 height=16>
    ____________________________
    Jeremy
    "If you spend more on coffee than on IT security, then you will be hacked. What&#39;s more, you deserve to be hacked." -Richard Clarke

  4. #4
    2 Star Lounger
    Join Date
    Aug 2002
    Location
    Minneapolis, MN, Minnesota
    Posts
    162
    Thanks
    1
    Thanked 0 Times in 0 Posts

    Re: Count Function (2000)

    Sorry for the extended interval in responding...I've been on vacation.

    With respect to your answer of #2. The count works correctly, but how does the field lngNurseCount get updated when the value changes since the control source has been changed?
    Each record in tblSchedule needs the lngNurseCount field to be updated as contractors are assigned. Sorry if I didn't make that clear in my original post.
    Easy Access

  5. #5
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Count Function (2000)

    You can put a line

    Me.Recalc

    in the After Update event procedure of the form.

  6. #6
    2 Star Lounger
    Join Date
    Aug 2002
    Location
    Minneapolis, MN, Minnesota
    Posts
    162
    Thanks
    1
    Thanked 0 Times in 0 Posts

    Re: Count Function (2000)

    I followed your suggestion and it did not work. I have attached a copy of what I have so far.
    I have added the field lngNurseCount field to the form for comparison. The field lngNurseCnt updates properly but not lngNurseCount. How can this be done?
    Easy Access

  7. #7
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Count Function (2000)

    No, of course the lngNurseCount field isn't updated. The lngNurseCnt control is a calculated control, it doesn't store its value in a table. But why would you need to store it? As the form demonstrates, it's easy to calculate the nurse count whenever you need it.

  8. #8
    2 Star Lounger
    Join Date
    Aug 2002
    Location
    Minneapolis, MN, Minnesota
    Posts
    162
    Thanks
    1
    Thanked 0 Times in 0 Posts

    Re: Count Function (2000)

    I have another form that needs the value from lngNurseCount to function.
    Is there a way to pass the value from lngNurseCnt to lngNurseCount? or from the form frmContractorSchedulingCUB to another form's control?
    Easy Access

  9. #9
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Count Function (2000)

    You can use a DCount expression in other forms too.

Posting Permissions

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