Results 1 to 6 of 6
  1. #1
    Thompyt is offline Expert
    Windows 8 Access 2010 32bit
    Join Date
    Sep 2014
    Location
    El Paso, TX
    Posts
    839

    My denseness is exceeding


    DCount("strField","strTable","Not isNull(NumField) & AND ID<" & [ID] +1)

    Its the dang quotes again - gives me a type conversion failure

    Examles used:
    For numerical values:

    DLookup("FieldName" , "TableName" , "Criteria = n")
    For numerical values:

    DLookup("FieldName", "TableName", "Criteria = " & forms!FormName!ControlName)
    From
    http://access.mvps.org/access/general/gen0018.htm


    I am trying to use - Not isNull(NumField) - as a qualifier for the count. Such as Not isNull(NumField) and isNull(AnotherNumField)

  2. #2
    Micron is offline Virtually Inert Person
    Windows 10 Access 2016
    Join Date
    Jun 2014
    Location
    Ontario, Canada
    Posts
    12,737
    - when concatenating, the ampersand (&) is never within quotes
    - if strTable and strField are variables, I'd say also no quotes, otherwise you're passing literals; i.e. the table is named "strTable"
    - not sure what to say about ID vs [ID]. If ID is a variable, same could apply. However in the case of one is variable and the other is field name, the variable name is a poor choice given that it would be the same name as a field. Might also expect ID to always be < ID+1 if they're the same thing. Anything you add to [ID] will always make it greater.

    P.S. as for trying to use IsNull, if it helps, this works
    dcount("Dte","tblMyTable","Not Isnull(dte) and [ID] < 4")
    The more we hear silence, the more we begin to think about our value in this universe.
    Paraphrase of Professor Brian Cox.

  3. #3
    June7's Avatar
    June7 is online now VIP
    Windows 10 Access 2010 32bit
    Join Date
    May 2011
    Location
    The Great Land
    Posts
    52,816
    Where are you using this DCount? Are you trying to generate a sequence number in a query? Example:

    SELECT DCount("*", "OrderDetail", "OrderID=" & [OrderID] & " AND OrderDetailID<=" & [OrderDetailID]) AS GrpSeq, OrderID, OrderDetailID, ProductID, Quantity, Price FROM OrderDetails ORDER BY OrderID, OrderDetailID;

    Domain aggregates in query can cause slow performance. This sequence number can be dynamically generated in report using textbox RunningSum property.
    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.

  4. #4
    Thompyt is offline Expert
    Windows 8 Access 2010 32bit
    Join Date
    Sep 2014
    Location
    El Paso, TX
    Posts
    839
    All,
    Attached is what I am trying to do. I am attempting to count by filtering down to the sub-component Level.
    Parent Node ID
    platform_id
    unique_id

    Step - Example
    1. Find all items of Parent Node ID - 1222126
    2. Further Filter down to platform_id - 3589359
    3. Where [Row Type]='MEQUIP' - Find all the unique_id that have a parent_equipment_id equal to the platform_id - 3589359 - 3589384 (Count = 1)
    4. Where [Row Type]='MEQUIP' -Find all the unique_id with a parent_equipment_id equal to the unique_id in Step 3 - 3589384 - 3589385 (Count = 2)
    5. Where [Row Type]='MEQUIP' -Repeat step 4. until there are no more parent_equipment_id equal to the unique_id in Step 3 - 1 Additional Record - 3589384 - 3589386 (Count = 3)
    6. Where [Row Type]='MEQUIP' -Run step 3 again for any other unique_id that with a parent_equipment_id equal to the unique_id in Step 3 - (if there are no more then do Step 7.)
    7. Where [Row Type]='MEQUIP' -Find all the unique_id that have a parent_equipment_id equal to the platform_id, but no Sub Components - 3589359 - 3589375 (Count = 4)
    8. Where [Row Type]='MEQUIP' -Repeat step 7. until there are no more parent_equipment_idequal to the platform_id but no Sub Components - 3589359 - 3589393 (Count = 4)

    9 Repeat Step 1 through Step 9 process until Parent Node ID records are exhausted, then move to next Parent Node ID.

    In the attachment is the dB and a flowchart.


  5. #5
    ssanfu is offline Master of Nothing
    Windows 7 32bit Access 2010 32bit
    Join Date
    Sep 2010
    Location
    Anchorage, Alaska, USA
    Posts
    9,664
    There is no flowchart in the Zip file.

    Can't find a "Parent Node ID", nor a value of 1222126 in the table.
    Can't find a field "unique_id"

    I would use VBA to do this..........



    You really need to fix your naming convention.
    - Object names should only be letters and numbers.
    - Do not begin an object name with a number.
    - NO spaces, punctuation or special characters (exception is the underscore) in object names

    You have a field name "Role / FE / Node Name"; spaces AND special characters! Better would be "RoleFENodeName" or "Role_FE_Node_Name".

  6. #6
    Thompyt is offline Expert
    Windows 8 Access 2010 32bit
    Join Date
    Sep 2014
    Location
    El Paso, TX
    Posts
    839
    ssanfu,
    I know about the naming conventions and I didn't make up the names.

    Parent Node ID,
    unique_id
    are in table1.

    I thought I had it attached, now corrected in the attached file.

    I would prefer in vba, but not all that sure how to start, the relationships being offset in the columns, unique_id and parent_equipment_id to platform_id has been screwing with me for a while. I can dig through it in my head to find an instance, but not how to make it generic to hit all the fields.

    I think its a very convoluted if then statement within more if then statements using Dlookup. Thus I was trying to get the Dcount correct and go from there. I was thinking there could be a possibility of arrays?

    The end is me trying to get the data into Visio and linking the data to a stencil and connecting that to a stencil design.


    Attached Files Attached Files

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

Similar Threads

  1. Replies: 5
    Last Post: 05-18-2011, 11:02 AM
  2. Exceeding the 255 field limit
    By Tolli Birgisson in forum Import/Export Data
    Replies: 1
    Last Post: 03-18-2011, 08:37 AM

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