Results 1 to 5 of 5

Thread: Dcount problem

  1. #1
    Join Date
    Mar 2014
    Posts
    3

    Unanswered: Dcount problem

    Hello everybody,

    I have problems with the Dcount function in Access. The function returns #Error as a result.

    DCount("Shares";"All_Operations_Sorted";"[Instrument] ='" & [Instrument] & "'" And [ID]<=" & [ID] &" And [SubaccountID]=" & [SubaccountID] &")

    [Instrument] is text
    [ID] is number
    [SubaccountID] is also number

    I suppose I have a syntax problem somewhere in the quotation marks or apostrophes or ampersands?

    The general idea is a running count but only for the specific [SubaccountID] and [Instrument].

    Thanks in advance for the assistance.

  2. #2
    Join Date
    Nov 2004
    Location
    out on a limb
    Posts
    13,692
    Provided Answers: 59
    DCount("Shares";"All_Operations_Sorted";"[Instrument] ='" & [Instrument] & "'" And [ID]<=" & [ID] &" And [SubaccountID]=" & [SubaccountID] &")


    replace the semicolon with a comma
    remove the additonal " in & "'" And
    ...so it reads & "' And
    remove the &") at the end of the statement
    ...so it reads )

    this sort of error is a breeze to resolve if you assign the value to a variable and either debug it or display it in a message box

    if its text literals it can be in within a single set of speech marks ", if you have to insert a value from a variable, control or vba function then you have to drip out of direct text and glue together the fragments using an ampersand

    so if you value is supplied as a literal this works
    "anumericolumn = 1234"
    whereas if the value is coming from a control
    "anumericolumn = " & mynumericcontrol

    "atextcolumn = 'abcdef'"
    "atextcolumn = '" & mytextcontrol & "'"
    I'd rather be riding on the Tiger 800 or the Norton

  3. #3
    Join Date
    Mar 2014
    Posts
    3
    Thanks a lot! It worked!

    I forgot to mention that I use the formula in a query not in VBA, so the semicolons were OK.

    I gathered the impression that the whole criteria part should be embraced with quotation marks just as the expression and the domain part but it seems I was wrong.

    Could you also please explain why is the ampersand and quotation marks needed in the end of [ID]<=" & [ID] &" but are not needed in the end of [SubaccountID]=" & [SubaccountID] since both are numeric?

    DCount("Shares";"All_Operations_Sorted";"[Instrument] ='" & [Instrument] & "' And [ID]<=" & [ID] &" And [SubaccountID]=" & [SubaccountID])

    Sorry if the question looks stupid but I am trying to figure out the logic of the syntax.

    Thanks once again!

  4. #4
    Join Date
    Nov 2004
    Location
    out on a limb
    Posts
    13,692
    Provided Answers: 59
    I forgot to mention that I use the formula in a query not in VBA, so the semicolons were OK.
    ..uou are using a domain function INSIDE a query. although I don't know it to be true Im pretty certain the domain functions are a macro wrapper around a recordset. they are usefull if you need a single isolated value but they are not smart or clever inside a query (essentially you are opening a query inside a query. years ago by removing domain functions inside someone else's query the query time was reduced from over 6 minutes to less than 10 seconds

    in vba & 'joins' two fragments together

    eg
    'assuem the following are varaibles
    title = "Mr"
    forename = "Blah-di-blah"
    surname = "Blaj"

    usersname = title & " " & forename & " " & surname
    'so usersname would be
    'Mr Blah-di-blah Blaj

    usersname = "Users name:-" & title & " " & forename & " " & surname

    when you use " or ' to delimit string values you are dropping in or out of VBA string 'mode'
    the & " " means add a space between these variables when building the new string
    string delimiters should be in matched pairs (starting and ending a string). so there is no need for a trailing " in your code as there shoudl be no closing delimter required

    im guessing the ide inserted the extra " at the end of the linme to ensure the delimiters were matched pairs
    FWIW if you need to use a " or ' inside a string literal then you need to escape the symbol
    I'd rather be riding on the Tiger 800 or the Norton

  5. #5
    Join Date
    Mar 2014
    Posts
    3
    Thanks! This was helpful.
    Cheers

Posting Permissions

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