Page 1 of 2 12 LastLast
Results 1 to 15 of 22
  1. #1
    5 Star Lounger
    Join Date
    Nov 2004
    Location
    Wilmington, North Carolina, USA
    Posts
    1,196
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Display a record count in text box (A2k)

    I would like to display on a text box the number of current awards handed out (1) this month, and (2) this year. I've built a query to get the SQL, however I don't know how to display it on my form.

    To display the number of records for the current month I have:

    SELECT Count(*) AS TTL
    FROM tbl16Awards
    WHERE ((Year([AWARD_SIGNED_DATE])=Year(Now()) And Month ([AWARD_SIGNED_DATE])=Month(Now());

    For current year, I have:

    SELECT Count(*) AS TTL
    FROM tbl16Awards
    WHERE Month ([AWARD_SIGNED_DATE])=Month(Now());
    ____________________________
    Jeremy
    "If you spend more on coffee than on IT security, then you will be hacked. What's more, you deserve to be hacked." -Richard Clarke

  2. #2
    5 Star Lounger
    Join Date
    Jan 2001
    Location
    Jacksonville,NC, USA
    Posts
    705
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Display a record count in text box (A2k)

    Jeremy,

    Have you tried setting the "control source" of the textbox properties, to your query? Like ="SELECT COUNT(*) FROM somequery"


    Nannette
    NMP <img src=/S/cool.gif border=0 alt=cool width=15 height=15>

    If you can't convince them, confuse them. - Harry Truman <img src=/S/scratch.gif border=0 alt=scratch width=25 height=29>

  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: Display a record count in text box (A2k)

    Thanks Hans, that works great. I was trying to avoid having a query at all costs...

    <img src=/S/whisper.gif border=0 alt=whisper width=29 height=17>You have an apostrophe instead of quotation before tbl16Awards

    Thanks again. It works perfect. <img src=/S/cheers.gif border=0 alt=cheers width=30 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
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Display a record count in text box (A2k)

    Edited by HansV to correct error

    You can use DCount. For the current month count, create a text box with Control Source

    =DCount("*","tbl16Awards","Year([AWARD_SIGNED_DATE])=Year(Date()) And Month([AWARD_SIGNED_DATE])=Month(Date())")

    For the current year, I suspect you intended to use Year instead of Month:

    =DCount("*","tbl16Awards","Year([AWARD_SIGNED_DATE])=Year(Date())")

    (I used Date() instead of Now() since you're only interested in the date, not in the time)

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

    Re: Display a record count in text box (A2k)

    > You have an apostrophe instead of quotation before tbl16Awards

    Thanks, now corrected.

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

    Re: Display a record count in text box (A2k)

    Unfortunately, setting the control source to an SQL statement won't work.

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

    Re: Display a record count in text box (A2k)

    Sure won't! I tried...after that my subform wouldn't show up on the tab I had it nested in...good thing I had a backup <img src=/S/grin.gif border=0 alt=grin width=15 height=15> Thanks for all your help, and Nannette, thank you for your help!
    ____________________________
    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

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

    Re: Display a record count in text box (A2k)

    I am now trying to count records in a table with the following criteria:
    <>"approved"
    <>"disapproved"
    Is Not Null

    However I get an error message "Data type mismatch in criteria expression." Eventually I will need to use DCount, but until then, I'd just like to apply this criteria to a totals query.
    ____________________________
    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

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

    Re: Display a record count in text box (A2k)

    Can you provide some information about the query, for example its SQL?

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

    Re: Display a record count in text box (A2k)

    SELECT Count(tblCareerPlanner.PACKAGE_LOCATION) AS CountOfPACKAGE_LOCATION
    FROM tblCareerPlanner
    HAVING (((Count(tblCareerPlanner.PACKAGE_LOCATION)) Is Not Null));
    ____________________________
    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

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

    Re: Display a record count in text box (A2k)

    Umm, I meant including the criteria that don't work.

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

    Re: Display a record count in text box (A2k)

    Whenever I try to add <>"approved" or <>"disapproved", I get the error.
    ____________________________
    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

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

    Re: Display a record count in text box (A2k)

    You're trying to apply the criteria to the Count instead of to the field itself.
    Remove the criteria from the PACKAGE_LOCATION field.
    Add the PACKAGE_LOCATION field a second time to the query grid, but this time set the Total option to Where, and add the criteria to this column.

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

    Re: Display a record count in text box (A2k)

    Hans, I tried that, however it didn't work.
    ____________________________
    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

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

    Re: Display a record count in text box (A2k)

    By placing 3 criteria below each other, they are "Or"ed, so you should get all records except those for which the field is Null. What exactly do you want? Records for which the field is neither equal to APPROVED not equal to DISAPPROVED? If so, use only one criteria line:
    <>"APPROVED" And <>"DISAPPROVED"

Page 1 of 2 12 LastLast

Posting Permissions

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