Results 1 to 14 of 14
  1. #1
    tagteam is offline Competent Performer
    Windows 7 64bit Access 2010 32bit
    Join Date
    Mar 2013
    Posts
    486

    best way to find out if a set of records has a specific value in a continuous form

    I have a continuous form that displays a summary of line items. I would like to be able to run a query or dcount to display how many of those line items have a specific value in one of the fields.

    I have created a query that has RailcarID and UpdatedResponsibilityCode and I am trying to get it to work with a dcount.

    =DCount("*","q_TestforResp7","[UpdatedResponsibilityCode] = 7 And [BillingInvoiceID]=' & Me!BillingInvoiceID'")

    The query i use to test is very simple
    Code:
    SELECT tbl_ImportedRepairs.BillingInvoiceID, tbl_ImportedRepairs.UpdatedResponsibilityCode
    FROM tbl_ImportedRepairs
    WHERE (((tbl_ImportedRepairs.UpdatedResponsibilityCode)=7));
    I manually set one of these fields to 7 so I could test but it still shows 0, not an error just a 0

  2. #2
    ranman256's Avatar
    ranman256 is offline VIP
    Windows Vista Access 2010 32bit
    Join Date
    Apr 2014
    Location
    Kentucky
    Posts
    9,521
    right click on the field,
    say FILTER = 7
    the count will be in the record nav control at the bottom.

  3. #3
    tagteam is offline Competent Performer
    Windows 7 64bit Access 2010 32bit
    Join Date
    Mar 2013
    Posts
    486
    Sorry, I did not explain enough.

    Each line in the continuous form is a summary of that invoices dollar amount. In those lines there will be records with UpdatedResponsibility (URC) code of 1,3,6 or 9. In each summary line I want to be able to say how many of the total records are URC of 7

    so a line might look like
    invoice 1 Carnumber 5 $3,500

    I want it to look like this if there were 5 records that were URC 7
    invoice 1 Carnumber 5 $3500 5

    Thanks

  4. #4
    June7's Avatar
    June7 is online now VIP
    Windows 10 Access 2010 32bit
    Join Date
    May 2011
    Location
    The Great Land
    Posts
    52,825
    I am confused. You say there could be code of 1,3,6 or 9 then you say want to know how many records were code 7.

    Consider:

    =Count(IIf([URC]=7,[URC],Null))
    or
    =Sum(IIf([URC]=7,1,0))
    How to attach file: http://www.accessforums.net/showthread.php?t=70301 To provide db: copy, remove confidential data, run compact & repair, zip w/Windows Compression.

  5. #5
    tagteam is offline Competent Performer
    Windows 7 64bit Access 2010 32bit
    Join Date
    Mar 2013
    Posts
    486
    Sorry for the confusion, i meant to say that in addition to 7 you could have 1,3,6,9.

    The basic sum or count wont work because i need to be able to group the query by Invoice.

    The basic workflow is:
    I receive an invoice that has several line items
    I import the invoice into my database
    In my continuous form, I summarize that invoice and show the basic data like total charge, vendor, etc
    On that summary line, in the background, I want to look at the details for that invoice to see if any of the lines were a 7
    I would then like to display in the field the number of lines that were a 7.


    A basic sum or count won't work because I need to be able to specify the invoice number to be checking (since it is a continuous form)
    So I created a query that will look for that invoice ID and the URC. (the query criteria is set to look for only the 7's

    This simple query works, I just need to figure out how to pass the Invoice number from a particular line in the continuous form to that query and return the result, or do something with dcount.

  6. #6
    orange's Avatar
    orange is offline Moderator
    Windows 10 Access 2010 32bit
    Join Date
    Sep 2009
    Location
    Ottawa, Ontario, Canada; West Palm Beach FL
    Posts
    16,716
    tagteam,

    I have read your posts several times and am still confused.
    It seems you are telling us HOW to do what you want , but haven't clearly told us WHAT.
    I suggest you tell/show us what you need with an example - very clear example (not SQL or query).

    This is part of the confusion:
    I have created a query that has RailcarID and UpdatedResponsibilityCode and I am trying to get it to work with a dcount.

    =DCount("*","q_TestforResp7","[UpdatedResponsibilityCode] = 7 And [BillingInvoiceID]=' & Me!BillingInvoiceID'")
    Where is RailcarID in any of this? Where/How does it fit?
    Is [BillingInvoiceID]
    a numeric data type? If yes, then you don't need the ' around me!BillingInvoiceID.

  7. #7
    June7's Avatar
    June7 is online now VIP
    Windows 10 Access 2010 32bit
    Join Date
    May 2011
    Location
    The Great Land
    Posts
    52,825
    Build a report and use its Sorting & Grouping features with aggregate calcs.
    How to attach file: http://www.accessforums.net/showthread.php?t=70301 To provide db: copy, remove confidential data, run compact & repair, zip w/Windows Compression.

  8. #8
    tagteam is offline Competent Performer
    Windows 7 64bit Access 2010 32bit
    Join Date
    Mar 2013
    Posts
    486
    Ok, sorry if my explanations are not good. The method I posted is not working, that is why i posted. Just trying to let people know what I have tried.

    Lets see if this is a better explanation.

    I have a continuous form where each line is a summary of an invoice.
    In that invoice are several lines (for example, invoice 1 may have 10 line items)
    I need a way to notify the user if any of the line items on the invoice have a URC of 7
    since each line on the continuous form is a summary of the invoice i cant see the details - that is why i need a field or something to tell me if there was a URC of 7

  9. #9
    tagteam is offline Competent Performer
    Windows 7 64bit Access 2010 32bit
    Join Date
    Mar 2013
    Posts
    486
    I am also trying to do it with just Dcount and not using a query

    =DCount("UpdatedResponsibilityCode","tbl_ImportedR epairs","[BillingInvoiceID] = " & [Me].[BillingInvoiceID] & "[UpdatedResponsibilityCode]= 7")

    But I get the #Name? errror.
    I need to have two where criteria because I need to match the current lines BillingInvoiceID so I dont count everything in the table and or course the 7 because I am just looking for the number of 7's.

  10. #10
    tagteam is offline Competent Performer
    Windows 7 64bit Access 2010 32bit
    Join Date
    Mar 2013
    Posts
    486
    Update, I can get it to count the number of 7's with this
    =DCount("UpdatedResponsibilityCode","tbl_ImportedR epairs","[UpdatedResponsibilityCode] =7")

    But the problem is that it counts the 7's in the whole table. I need it to count the 7's that only have a specific BillingInvoiceID.
    Thanks

  11. #11
    June7's Avatar
    June7 is online now VIP
    Windows 10 Access 2010 32bit
    Join Date
    May 2011
    Location
    The Great Land
    Posts
    52,825
    Include as many criteria in the WHERE argument as needed.

    Can't use Me. in textbox expression. Me. is used only in VBA. Just drop the Me. from the expression.

    =DCount("UpdatedResponsibilityCode", "tbl_ImportedRepairs","[BillingInvoiceID]=" & [BillingInvoiceID] & " AND [UpdatedResponsibilityCode]= 7")

    Why not use Sorting & Grouping in report with aggregate calcs?
    How to attach file: http://www.accessforums.net/showthread.php?t=70301 To provide db: copy, remove confidential data, run compact & repair, zip w/Windows Compression.

  12. #12
    tagteam is offline Competent Performer
    Windows 7 64bit Access 2010 32bit
    Join Date
    Mar 2013
    Posts
    486
    Thank you for your patience, but that did not work. I am sure because I need it to have the BillingInvoiceID of that particular line and I dont see how that would know which BillingInvoiceID to look for.
    I cant use grouping and sorting because I have buttons that do things and input that is needed on the line.

    Click image for larger version. 

Name:	Dcountsmall.png 
Views:	11 
Size:	18.8 KB 
ID:	35850

  13. #13
    June7's Avatar
    June7 is online now VIP
    Windows 10 Access 2010 32bit
    Join Date
    May 2011
    Location
    The Great Land
    Posts
    52,825
    It's a dynamic expression. It will use the BillingInvoiceID of each record. #Name? error usually means Access can't find a name used in expression.
    How to attach file: http://www.accessforums.net/showthread.php?t=70301 To provide db: copy, remove confidential data, run compact & repair, zip w/Windows Compression.

  14. #14
    CJ_London is online now VIP
    Windows 10 Access 2010 32bit
    Join Date
    Mar 2015
    Posts
    11,399
    there is an assumption that

    have a continuous form where each line is a summary of an invoice.
    In that invoice are several lines (for example, invoice 1 may have 10 line items)
    will include a field called BillingInvoiceID

    so still don't see why you can't use a group by query

    SELECT BillingInvoiceID, Count(billingID) AS Count7
    FROM tbl_ImportedRepairs
    WHERE UpdatedResponsibilityCode=7
    GROUP BY BillingInvoiceID

    then either link to it in your recordsource query on BillingInvoiceID or use dlookup against it

    if those lines do not include a BillingInvoiceID field, you either need to bring it through or you need a different field which is common to both recordsource and tbl_ImportedRepairs. Just a thought but is billinginvoiceID the id to the invoice or the individual line (and therefore not available in your summary data in the recordsource)? If this is the case and you have a different field to identify the invoice, you need to be able to attach that to your tbl_ImportedRepairs data with a query.

    I could be wrong but this looks like the sort of issue you can get when using lookups in tables

Please reply to this thread with any new information or opinions.

Similar Threads

  1. Replies: 1
    Last Post: 03-09-2018, 01:26 PM
  2. Replies: 2
    Last Post: 06-14-2016, 03:01 PM
  3. Replies: 4
    Last Post: 06-02-2016, 09:03 AM
  4. Replies: 8
    Last Post: 02-09-2014, 07:25 PM
  5. Replies: 1
    Last Post: 07-16-2012, 01:57 PM

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  
Other Forums: Microsoft Office Forums