Page 1 of 2 12 LastLast
Results 1 to 15 of 16
  1. #1
    Join Date
    Jan 2005
    Location
    Nanded, India
    Posts
    397

    Unanswered: DCount help need...

    Hi,

    Thru VBA, I am trying to count records from a footer table with criteria using Dcount function. But it retruns all the records which are total 14 while it should return only 2 records based on my criteria.

    OBPurTrans = Nz(DCount("*", "T_PurInvFoot", "ProductCode = ProductCode"), 0)

    Where might have gone wrong?

    With kind regards,
    Ashfaque

  2. #2
    Join Date
    Jan 2007
    Location
    California, USA
    Posts
    520
    Your criteria.
    OBPurTrans = Nz(DCount("*", "T_PurInvFoot", "ProductCode=" & ProductCode)
    With ProductCode=ProductCode inside of the quotes, you were just saying the same as 2 = 2, or 4 = 4. By putting the second ProductCode outside the quotes, and concatenating them together with the "&". The second ProductCode outside of the quotes will use the value from the ProductCode field in your VBA code which means it could also be on the form.
    HTH,

  3. #3
    Join Date
    Jan 2005
    Location
    Nanded, India
    Posts
    397
    Thanks Vic,

    I already used following way but it won't work.
    OBPurTrans = Nz(DCount("*", "T_PurInvFoot", "ProductCode=" & ProductCode),0)

    Then I changed to
    OBPurTrans = Nz(DCount("*", "T_PurInvFoot", "ProductCode = ProductCode"), 0)
    Which is returning 14 records as OBPurTrans

    I am triggering abouve on AfterUpdate of PurQty of subform for further calculations. The productcode field on the subform is showing the productcode before entering PurQty.

    Please advice.

    With kind regards,
    Ashfaque
    Last edited by Ashfaque; 04-21-07 at 04:20.

  4. #4
    Join Date
    Dec 2002
    Location
    Préverenges, Switzerland
    Posts
    3,740
    a restatement of Vic's comment.

    consider what happens if you use your criteria in SQL:
    SELECT * FROM T_PurInvFoot WHERE ProductCode = ProductCode
    ...your WHERE expression is always True so you get all records.

    similarly:
    SELECT * FROM T_PurInvFoot WHERE ProductCode <> ProductCode
    ...your WHERE expression is always False so you get zero records.

    what is the datatype of ProductCode in T_PurInvFoot ??

    izy
    currently using SS 2008R2

  5. #5
    Join Date
    Jan 2005
    Location
    Nanded, India
    Posts
    397
    Thanks Izy,

    Datatype ProductCode is Number (Double)

    I had tested it with Long Integer also but the result is same.

    Please advice..

    With regards,
    Ashfaque

  6. #6
    Join Date
    Jan 2007
    Location
    California, USA
    Posts
    520
    Ashfaque,

    Where ever you are actually using this line of code, please do a copy and paste of the code into the forum. There is something missing when you type it into the forum. I think I have done too many of these to be missing it so badly. And, when Izy agrees, I know I can't be too far off. (Thanks for the rewording, it was good!)

    Thanks,

  7. #7
    Join Date
    Jan 2005
    Location
    Nanded, India
    Posts
    397
    The idea was to get average price from sum of old price and new price of the same item.

    Here is my code.

    Dim OBPurTrans As Long
    Dim SumCurrPrice As Long
    Dim OBPrice As Long
    Dim AvgPrice As Long

    'Count Total Pur transactions till date + 1 transaction of Opening balance
    'Following 3 lines already tried out of which 1 returns 14 Recs


    OBPurTrans = DCount("*", "T_PurInvFoot", "ProductCode = ProductCode") + 1

    OBPurTrans = DCount("*", "T_PurInvFoot", "ProductCode=" & ProductCode)

    OBPurTrans = Nz(DCount("*", "T_PurInvFoot", "ProductCode=" & ProductCode),0)


    Also happening the same in below code. (Summing of all records instead of using criteria)

    'Sum of total purchase price of the item.
    SumCurrPrice = Nz(DSum("CurrPurPrice", "T_PurInvFoot", "Productcode =ProductCode"), 0)

    'Bring Old purchase price of opening balance(This one is OK)
    OBPrice = Nz(DLookup("OldPurPrice", "Product_master", "ProductCode = ProductCode"), 0)

    AvgPrice = (SumCurrPrice + OBPrice) / OBPurTrans

    With kind regards,
    Ashfaque

  8. #8
    Join Date
    Jan 2007
    Location
    California, USA
    Posts
    520
    I am assuming this code is being done on a form. But do you have ProductCode included in the RecordSource for this form/report and do you have it as a field, hidden or visible anywhere on the form/report? If not, make the field available on the form/report. If it is already on the form/report, then can you tell me what the value of the field is just before you run this code?

  9. #9
    Join Date
    Dec 2002
    Location
    Préverenges, Switzerland
    Posts
    3,740
    'Bring Old purchase price of opening balance(This one is OK)
    OBPrice = Nz(DLookup("OldPurPrice", "Product_master", "ProductCode = ProductCode"), 0)

    is NOT at all OK.
    i believe it will return a random OldPurPrice, possibly the same OldPurPrice every time, possibly by total chance the one you wanted in your test-case, but not one constrained by a form-field ProductCode (is equivalent to DLookup() without the criteria part)

    have a go with

    OBPurTrans = DCount("*", "T_PurInvFoot", "ProductCode=" & Forms!YourFormsRealName.ProductCode)


    if
    a/ you have a form named 'YourFormsRealName'
    b/ the form is open at the time the DCount() executes
    c/ that form holds a textbox named 'ProductCode' containing a numeric value.
    then the DCount() absolutely WILL work correctly!


    if that works, but
    OBPurTrans = DCount("*", "T_PurInvFoot", "ProductCode=" & ProductCode)
    doesn't work...
    ...where is this code running ?
    ...where is the field ProductCode ?


    it might be useful to check that the form-field actually has a value at the time DCount() executes. add a
    msgbox Forms!YourFormsRealName.ProductCode,,"This is the form-field value'
    just before the DCount() call.

    izy
    Last edited by izyrider; 04-21-07 at 12:25. Reason: typo in ProductCode
    currently using SS 2008R2

  10. #10
    Join Date
    Dec 2002
    Location
    Préverenges, Switzerland
    Posts
    3,740
    with a slight risk of over-emphasising:

    Danything("anything", "anything", "ProductCode = ProductCode")
    is plain simple WRONG

    izy
    currently using SS 2008R2

  11. #11
    Join Date
    Jan 2005
    Location
    Nanded, India
    Posts
    397
    The code is running on AfterUpdate even of PurQty on subform called 'F_PurInvFoot' and header form name is 'F_PurInvHead'. Hence I tried this way:

    OBPurTrans = DCount("*", "T_PurInvFoot", "ProductCode=" & Forms!F_PurInvHead!F_PurInvFoot.Form!ProductCode)

    It produces OBPurTrans=0.

    And this won't work. "& Forms!F_PurInvFoot.ProductCode)"

    Regards,

  12. #12
    Join Date
    Jan 2005
    Location
    Nanded, India
    Posts
    397
    a/ you have a form named 'YourFormsRealName'
    The subform name is 'F_PurInvFoot'

    b/ the form is open at the time the DCount() executes

    Yes

    c/ that form holds a textbox named 'ProductCode' containing a numeric value.
    Yes its number data type.

    then the DCount() absolutely WILL work correctly!

    Not working !!!

  13. #13
    Join Date
    Jan 2005
    Location
    Nanded, India
    Posts
    397
    Wow....Finally it worked out gentlemen,

    Excellent response from your end.

    What I did is just set the focus over the field and off course your '& ProductCode.Text)

    Me.ProductCode.SetFocus
    'Count Total Pur transactions till date + 1 transaction of Opening balance
    OBPurTrans = DCount("*", "T_PurInvFoot", "ProductCode=" & Me.ProductCode.Text)

    My special thanks to Vic and Izy.

    With kind regards,
    Ashfaque

  14. #14
    Join Date
    Dec 2002
    Location
    Préverenges, Switzerland
    Posts
    3,740
    maybe your diagnosis is faulty!

    experiment:
    make a new form
    save as frmTest
    open frmTest in design
    add a textbox
    name the textbox 'myBox'
    close/save
    run the form
    type into the textbox a valid ProductCode (do NOT hit return/enter, do NOT move to another control on the form)
    type Ctrl-G
    in the resulting immediate window, type
    ? dcount("*", "t_purinvfoot", "productcode = " & forms!frmtest.mybox)
    hit return - you get an error!
    still in the immediate window, type
    ? dcount("*", "t_purinvfoot", "productcode = " & forms!frmtest.mybox.text)
    hit return - you do not receive an error!

    what happened?
    there is no myBox.Value because you are still editing it. myBox.Text exists but has not yet become a .Value

    don't get distracted and start using the .Text property incorrectly. it is a very valuable property if you want to obtain a .Value under edit, but it is a complete PITA to use it all over the place when it is not necessary.

    repeat the above experiment but this time hit return/enter after you have entered the valid productcode ...no errors any more ;p

    izy
    Last edited by izyrider; 04-21-07 at 13:57.
    currently using SS 2008R2

  15. #15
    Join Date
    Jan 2007
    Location
    California, USA
    Posts
    520
    Izy,

    GREAT explaination!
    (I wanted to under emphasize)

    I'm sure glad I was shopping this morning so I did not get involved. You handled it VERY nicely!

Posting Permissions

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