unbound control on form show count of certain items

Posted on 2011-10-21
Last Modified: 2012-05-12
I have a form that shows number of claims.  The users want, at the bottom of the form, controls that reflect the count of claim for the prior month and then a count of claim for MTD.  Since these are unaboug, I tried making a select query as the source for one of the count control, but it did not work.  So. how can I get two controls to count items in the underlying table based on different critieria?  The counts are separate from the individual detail data as reflected on the form, which shows only the detail for one clailm record at a time.  This is an ACCESS 2003 database.

SELECT TblClaims.ClaimACHATM, TblClaims.ClaimMonth, TblClaims.ClaimYear, Count(TblClaims.ClaimDateReceived) AS CountOfClaimDateReceived
FROM TblClaims
GROUP BY TblClaims.ClaimACHATM, TblClaims.ClaimMonth, TblClaims.ClaimYear
HAVING (((TblClaims.ClaimACHATM)="ACH") AND ((TblClaims.ClaimMonth)=Month(Date())) AND ((TblClaims.ClaimYear)=Year(Date())));

Open in new window

Question by:ssmith94015
    LVL 119

    Accepted Solution

    have tried using the dcount() function?
       dCount(expr, domain, [criteria])

    Author Comment

    Let me try that, I was just wondering about the multiple criteria.  Be right back.

    Author Comment

    I've requested that this question be closed as follows:

    Accepted answer: 0 points for ssmith94015's comment http:/Q_27409359.html#37008318

    for the following reason:

    Next issue will be to adjust for DEC/JAN, but I wil work on that for a bit yet. &nbsp;thank you for the answer.<br /><br />Sandra
    LVL 119

    Expert Comment

    by:Rey Obrero
    what ?

    Author Comment

    OK, I messed up adn hit the wrong button.  Let me get assistance, sorry about that!

    Author Comment

    Capricon should get the points, I hit the wrong button when closing the questions.

    Author Closing Comment

    Ok, I think this will assgin the points now.

    Write Comment

    Please enter a first name

    Please enter a last name

    We will never share this with anyone.

    Featured Post

    IT, Stop Being Called Into Every Meeting

    Highfive is so simple that setting up every meeting room takes just minutes and every employee will be able to start or join a call from any room with ease. Never be called into a meeting just to get it started again. This is how video conferencing should work!

    In a multiple monitor setup, if you don't want to use AutoCenter to position your popup forms, you have a problem: where will they appear?  Sometimes you may have an additional problem: where the devil did they go?  If you last had a popup form open…
    User Beware!  This is a rather permanent solution to removing your email from an exchange server.  The only way to truly go back is to have your exchange administrator restore your mailbox from backups.  This is usually the option of last resort.  A…
    Basics of query design. Shows you how to construct a simple query by adding tables, perform joins, defining output columns, perform sorting, and apply criteria.
    In Microsoft Access, when working with VBA, learn some techniques for writing readable and easily maintained code.

    760 members asked questions and received personalized solutions in the past 7 days.

    Join the community of 500,000 technology professionals and ask your questions.

    Join & Ask a Question

    Need Help in Real-Time?

    Connect with top rated Experts

    8 Experts available now in Live!

    Get 1:1 Help Now