Results 1 to 14 of 14
  1. #1
    bdaniel is offline Advanced Beginner
    Windows XP Access 2007
    Join Date
    Dec 2009
    Posts
    44

    Dsum Criteria - Invalid Bracketing of Name Error

    Here is my expression:



    HiSKUOH: DSum("[sumofunitsonhand]","onhand-false","[onhand-false.hiskuid]=[replenishment table query.highsku id]")

    When I run the query, get error message Invalid Bracketing of Name '[replenishment table query.highsku id]'

    "replenishment table query" is the name of the query that I am designing and [highsku id] is a field in that query.

    I want the sum of [sumofunitsonhand] in the "onhand-false" table where the [hiskuid] field in the "onhand-false" table is equal to the value of the [highsku id] field in the "replenishment table query". Both the [hiskuid] and [highsku id] fields are text fields.

  2. #2
    ssanfu is offline Master of Nothing
    Windows XP Access 2000
    Join Date
    Sep 2010
    Location
    Anchorage, Alaska, USA
    Posts
    9,664
    When I run the query, get error message Invalid Bracketing of Name '[replenishment table query.highsku id]'
    The error message is referring to missing brackets around the names: '[replenishment table query].[highsku id]

    You could try:
    HiSKUOH: DSum("[sumofunitsonhand]","onhand-false","[hiskuid]=[replenishment table query].[highsku id]")

    or

    HiSKUOH: DSum("[sumofunitsonhand]","onhand-false","[hiskuid]= " & [replenishment table query].[highsku id])

    (sorry, I don't use the DLookup function)


    Also, FYI:
    "onhand-false" this is not a good name because of the dash (a special character)
    "[replenishment table query]" because of the space.

    In naming Access objects, you should only use letters, numbers and the underscore ("onhand_false")

  3. #3
    bdaniel is offline Advanced Beginner
    Windows XP Access 2007
    Join Date
    Dec 2009
    Posts
    44
    Thanks Steve.

    I tried HiSKUOH: DSum("[sumofunitsonhand]","onhand-false","[hiskuid]=[replenishment table query].[highsku id]") and get the message: Microsoft Office Access can't find the name 'replenishment table query.highsku id' you entered in the expression.

    Tried HiSKUOH: DSum("[sumofunitsonhand]","onhand-false","[hiskuid]= " & [replenishment table query].[highsku id]) and get the Enter Parameter Value prompt for replenishment table query.highsku id.

    What do you think?

    Also, I will go back in an change those field descriptions once I get this solved.

  4. #4
    Rod is offline Expert
    Windows 7 32bit Access 2007
    Join Date
    Jun 2011
    Location
    Metro Manila, Philippines
    Posts
    679
    'Scuse me butting in but what exactly is 'replenishment table query?' Is this a catalogued query? Are you expecting the DSum function to run the query? It won't!

    If my suspicions are correct, you first need to define a recordset and set this to your query. Then you can then run the query and use the current value of the recordset field as the variable in the WHERE clause of the DSum function.

    Follow Steve's advice about naming sooner rather than later. I even avoid underscores!

  5. #5
    bdaniel is offline Advanced Beginner
    Windows XP Access 2007
    Join Date
    Dec 2009
    Posts
    44
    The more the merrier!

    What do you mean a "cataloged query"?

    I will be correcting the names.

  6. #6
    Rod is offline Expert
    Windows 7 32bit Access 2007
    Join Date
    Jun 2011
    Location
    Metro Manila, Philippines
    Posts
    679
    I mean a named query that appears in the Access Object List.

  7. #7
    bdaniel is offline Advanced Beginner
    Windows XP Access 2007
    Join Date
    Dec 2009
    Posts
    44
    Yes it is.

    Just looking to sum the total of sumofunitsonhand in the table where the records have the same highsku id that matches the hiskuid in the query.

    Thinking along the same lines as sumif function works in excel (am I allowed to say that in an access forum?)

  8. #8
    bdaniel is offline Advanced Beginner
    Windows XP Access 2007
    Join Date
    Dec 2009
    Posts
    44
    I can get the total sum of the field [sumofunitsonhand] from the table "onhand-false", but as soon as I add a criteria that says only if the hiskuid's match, that's when it bugs out.

  9. #9
    bdaniel is offline Advanced Beginner
    Windows XP Access 2007
    Join Date
    Dec 2009
    Posts
    44
    I can get the total sum of the field [sumofunitsonhand] from the table "onhand-false", but as soon as I add a criteria that says only if the hiskuid's match, that's when it bugs out

  10. #10
    Rod is offline Expert
    Windows 7 32bit Access 2007
    Join Date
    Jun 2011
    Location
    Metro Manila, Philippines
    Posts
    679
    Thinking along the same lines as sumif function works in excel (am I allowed to say that in an access forum?)
    Sacrilege!

    Here's your original expression: DSum("[sumofunitsonhand]","onhand-false","[onhand-false.hiskuid]=[replenishment table query.highsku id]")

    Here's the syntax: DSum(expr, domain [,criteria])

    The expression may be the name of a field or a calculation (sometimes quite complex) operating on a field. In your case the field name is sumofunitsonhand. Do not use brackets otherwise DSum will be searching the TableDef for fields with brackets around the name!

    A domain may be a table name or a query name. Steve has pointed out that a hyphen is not a good character to use in any name.

    The optional criteria is the same as a SQL WHERE clause without the WHERE keyword. In its simplest form this is usually of the form field = value. In fact this is exactly what you are using except that the retrieval of your value is a little more complex. Regarding the field, as you have nominated the domain (table) name there is no need to repeat it in the criteria. So it's OK to simply refer to hiskuid. (Again - no brackets please.)

    There are two syntax variations for coding the comparison. If the field is numeric then the syntax is "FieldName = " & value, whereas if the field is text, the syntax is "FieldName = '" & value & "'". A text/string comparison requires quotation marks around the value. (You can mess around with double double-quotes but most of us prefer to use a single quote; the SQL and code parsers understand a single quote in this context.

    Right, now we come to the 64K$ question: how do you retrieve your value? replenishment table query is a query and highsku id is a column in the result set of that query. Unfortunately you cannot use queries in the criteria part of a DSum function; neither can you use queries in this way in another query.

    However I suspect replenishment table query is this query. Am I correct? I suspect HiSKUOH is the name for the resultant column. So forget my previous comments about recordsets; I was thinking this was taking place inside a procedure but now I see it is an expression you are entering in the design grid of a query - the replenishment table query. I also assume that the comparison is numeric. So try the following:

    HiSKUOH: DSum("sumofunitsonhand","onhand-false","hiskuid = " & [highsku id])


    P.S.
    Do not use brackets otherwise DSum will be searching the TableDef for fields with brackets around the name!
    I'm wrong. The brackets do no harm and are stripped before the Access objects are interrogated.
    Last edited by Rod; 12-12-2012 at 02:21 AM. Reason: Added P.S.

  11. #11
    ssanfu is offline Master of Nothing
    Windows XP Access 2000
    Join Date
    Sep 2010
    Location
    Anchorage, Alaska, USA
    Posts
    9,664
    Thanks Rod. As I said, I don't use the aggregate functions much, if at all. I generally write a UDF because I have more requirements and have better control.
    Good write up. I learned (learnt??) something.

  12. #12
    bdaniel is offline Advanced Beginner
    Windows XP Access 2007
    Join Date
    Dec 2009
    Posts
    44
    Thanks, it all comes down to syntax. It is a comparison of text fields. Used HiSKUOH: DSum("sumofunitsonhand","onhand-false","hiskuid = '" & [highsku id] & "'") and it worked.

    thanks very much, especially for the syntax explanation.

  13. #13
    bdaniel is offline Advanced Beginner
    Windows XP Access 2007
    Join Date
    Dec 2009
    Posts
    44

    Same syntax, new error

    Dsum expression that works (thanks for the help!)

    HiSKUOH: DSum("sumofunitsonhand","on hand-false query","hiskuid = '" & [highsku id] & "'")

    Applied the same syntax and created a new expression in the same query to sum sales units in the Trlg7HiSkuQuery_true query:

    HiSKUTrlg7Sum("sumofSales units","Trlg7HiSkuQuery_true","hiskuid = '" & [highsku id] & "'")

    All looks the same, but when I run the query getting new message:

    Syntax error (missing operator) in query expression 'Sum(sumofSales units)'. Don't understand as the field structure of the target queries in both expressions are identical. first one works, second one syntax error.

    Thought I had this down, but guess not.

  14. #14
    Rod is offline Expert
    Windows 7 32bit Access 2007
    Join Date
    Jun 2011
    Location
    Metro Manila, Philippines
    Posts
    679
    Try placing those brackets around the field name. E.g. ("[sumofSales units]", ...

    If this is the cause please accept my apologies for misleading you. Personally I never use special characters in field names, not even underscores.

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

Similar Threads

  1. Replies: 13
    Last Post: 12-05-2011, 05:10 AM
  2. Dsum criteria
    By bdaniel in forum Queries
    Replies: 4
    Last Post: 11-27-2011, 03:41 PM
  3. Dsum criteria problem
    By leonhuynh2006 in forum Queries
    Replies: 3
    Last Post: 10-04-2011, 03:18 AM
  4. Replies: 3
    Last Post: 04-12-2011, 10:22 AM
  5. DSUM with date criteria from form
    By krutoigoga in forum Reports
    Replies: 4
    Last Post: 07-28-2010, 01:32 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