Results 1 to 12 of 12
  1. #1
    Join Date
    Jun 2010
    Posts
    9

    Unanswered: how can i run this query in vb

    Hi all
    i have a query in database and it works good .now i need to use this query in vb6 .
    any body can write this query in vb6 so that run this query in vb6?
    i attached access database(mdb) .please take a look .
    sorry for my bad english
    thank's all
    Attached Files Attached Files

  2. #2
    Join Date
    Nov 2004
    Location
    out on a limb
    Posts
    13,692
    Provided Answers: 59
    the query (the SQL) should be the same
    chose which db object model you want to use in VB6 (DAO or ADO)
    there is no a lot of difference in this area between VB6 and VBA
    I'd rather be riding on the Tiger 800 or the Norton

  3. #3
    Join Date
    Jun 2010
    Posts
    9
    Quote Originally Posted by healdem View Post
    the query (the SQL) should be the same
    chose which db object model you want to use in VB6 (DAO or ADO)
    there is no a lot of difference in this area between VB6 and VBA
    i do copy sql of query in vb6 to run, but i faced error "Undefined function 'DSUM' in expression"

    are you look at query in db ?
    Code:
    SELECT  '' AS DocNo, '' AS  Date_, 'From pre date' AS Narration_,  " "  AS  Credit_, " " AS Deptor_, DSum("credit-deptor","acc","c_date<'" & nz([forms]![form1].[txtmindate],"0") & "'") AS Balance_
    from acc
    UNION SELECT DocNo, c_date,narration,credit,deptor, DSum("credit-deptor","acc","Docno<=" & [docNo]) AS Balance_
    FROM acc
    WHERE (((acc.c_date) Between nz([forms]![form1].[txtmindate],0) And nz([forms]![form1].[txtmaxdate],9) ))
    ORDER BY DocNO;
    DSUM function is not define in vb6 . can you modify this sql to run in vb6 ?
    thanks

  4. #4
    Join Date
    Jun 2005
    Location
    Richmond, Virginia USA
    Posts
    2,763
    Provided Answers: 19
    You might try SUM instead of DSUM. Of course, you'd probably do better to pose this question in a VB6 forum.

    Linq ;0)>
    Last edited by Missinglinq; 06-29-14 at 10:57.
    Hope this helps!

    The problem with making anything foolproof...is that fools are so darn ingenious!

    All posts/responses based on Access 2003/2007

  5. #5
    Join Date
    Jun 2010
    Posts
    9
    Hi Missinglinq
    i use sum instead Dsum but ,it not work
    the query in db access is :
    Code:
    SELECT  '' AS DocNo, '' AS  Date_, 'From pre date' AS Narration_,  " "  AS  Credit_, " " AS Deptor_, DSum("credit-deptor","acc","c_date<'" & nz([forms]![form1].[txtmindate],"0") & "'") AS Balance_
    from acc
    UNION SELECT DocNo, c_date,narration,credit,deptor, DSum("credit-deptor","acc","Docno<=" & [docNo]) AS Balance_
    FROM acc
    WHERE (((acc.c_date) Between nz([forms]![form1].[txtmindate],0) And nz([forms]![form1].[txtmaxdate],9) ))
    ORDER BY DocNO;
    can you convert this query to sql code ,so that work in vb6 . it's vital for me.
    thanks

  6. #6
    Join Date
    Mar 2009
    Posts
    5,442
    Provided Answers: 14
    You'll have several problems actually:

    1. As Missinglinq points out, DSUM() and other VBA domain functions are undefined in VB6. Use a subquery with the aggregate function SUM(), instead.
    DSUM([Column], [Table], Criteria) can be translated in:
    Code:
    SELECT SUM([Column]) FROM [Table] WHERE Criteria;
    2. Nz() is also undefined. You can use an IIF() function in the query:
    Code:
    IIf(IsNull(Value), ValueIfNull, Value)
    or create your own function:
    Code:
    Public Function Nz(Value As Variant, ValueIfNull As Variant) As Variant
    
        If IsNull(Value) then
            Nz = ValueIfNull 
        Else
            Nz = Value
        End If
    
    End Function
    Have a nice day!

  7. #7
    Join Date
    Jun 2010
    Posts
    9
    i could not solve my problem
    i could change a little sql code (convert Dsum to Sum) :
    Code:
    SELECT 
     '' AS docnum,
     '' AS  c_date,
     'previous balance' AS narration,
     " "  AS  credit,
     " " AS deptor,
    Sum(credit-deptor),"c_date< ' "  &  2014/06/10  & " '  "   AS Balance  FROM Cash
    UNION SELECT
     doc_num,
     c_date,
    narration,
    credit,
    deptor,
    Sum(credit-deptor),"doc_num<=" & doc_num AS Balance FROM Cash
    WHERE (((Cash.c_date) Between ("2014/06/10  ") And ("2014/06/20 ") ))
    ORDER BY docnum;
    but show error " you tried to execute query that dose not include the specified expression 'doc_num' as part of an aggregate.
    i want to get result like this :
    Code:
    -------------------------------------------------------------------
    doc_num        date         credit        deptor       balance
    -------------------------------------------------------------------
         1       2014/06/10       5000          0              5000
         2       2014/06/12          0          2000           3000
    please change this code as correct code.
    thanks

  8. #8
    Join Date
    Mar 2009
    Posts
    5,442
    Provided Answers: 14
    Quote Originally Posted by unforgiven747 View Post
    i want to get result like this :
    Code:
    -------------------------------------------------------------------
    doc_num        date         credit        deptor       balance
    -------------------------------------------------------------------
         1       2014/06/10       5000          0              5000
         2       2014/06/12          0          2000           3000
    I'm not sure to understand what you try to achieve. Can you provide a data sample (csv is ok)?
    Have a nice day!

  9. #9
    Join Date
    Jun 2010
    Posts
    9
    i have a one table as Cash,it has a 5 Columns: doc_num,narration,date,credit,deptor
    now i want to show sum(credit-debtor) as balence for each rows.also show previous balance
    result like this :
    Code:
    --------------------------------------------------------------------------------------
    doc_num        date          narration          credit        debtor       balance
    --------------------------------------------------------------------------------------
                                     previous balance                                2000    
         1       2014/06/10           remit             5000          0              5000
         2       2014/06/12           bar                  0          2000           3000
         3       2014/06/15           remit             1000          0              4000
    thanks

  10. #10
    Join Date
    Jun 2010
    Posts
    9
    Finally, i do write this code to calculate and show balance of each rows.now
    i need to show previous balance according date .
    please help me to do that
    Code:
    SELECT '' AS doc_num, '' AS c_date, 'previous balance' AS narration, " " AS credit, " " AS debtor,''  AS ["BALANCE"] FROM Cash 
    UNION SELECT doc_num, cur_date,narration, credit, debtor, (select sum(credit-debtor) from cash
    where  doc_num<=a.doc_num) AS ["BALANCE"]FROM cash AS a WHERE cur_date between '2014/06/10' and '2014/06/15' ORDER BY doc_num;
    something like this :
    Code:
    --------------------------------------------------------------------------------------
    doc_num        date          narration          credit        debtor       balance
    --------------------------------------------------------------------------------------
                                     previous balance                                2000    
         1       2014/06/10           remit             5000          0              7000
         2       2014/06/12           bar                  0          2000           5000
         3       2014/06/15           remit             1000          0              6000
    thanks again

  11. #11
    Join Date
    Nov 2004
    Location
    out on a limb
    Posts
    13,692
    Provided Answers: 59
    do you need to do this in a query or can you do this in the vb form/report
    I'd rather be riding on the Tiger 800 or the Norton

  12. #12
    Join Date
    Jun 2010
    Posts
    9
    Quote Originally Posted by healdem View Post
    do you need to do this in a query or can you do this in the vb form/report
    yes,i need to do this in query .i want to run above code to show result in grid with vb6 .
    can you write SQL code to show previous balance,according my SQL code ?
    thanks
    Last edited by unforgiven747; 07-03-14 at 10:17.

Posting Permissions

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