Results 1 to 26 of 26

Thread: Serial number in Data Report is not starting from 1,2...

  1. #1

    Thread Starter
    Lively Member
    Join Date
    May 2009
    Posts
    110

    Serial number in Data Report is not starting from 1,2...

    Code:
    Private Sub cmdPrint_Click()
    Set Record = New ADODB.Recordset
    Record.Open "SELECT InvoiceNo,Qty,Price,TotalAmount,DateCreated,CustomerName,ProductCode,ContactNo,Address,City,Tin,Dist,Packing,(SELECT COUNT(*) FROM SalesInvoice C2 WHERE C2.ProductCode <= C.ProductCode) AS SerNo FROM SalesInvoice C where InvoiceNo = '" & txtInvNo.Text & "' ORDER BY ProductCode asc ", Connect, 1, 3
        If Record.RecordCount > 0 Then
    
            With rptSalesInvoice
                Set rptSalesInvoice.DataSource = Record
                    .Sections("Section4").Controls("Label17").Caption = Record!DateCreated
                    .Sections("Section4").Controls("Label18").Caption = Record!InvoiceNo
                    .Sections("Section4").Controls("Label19").Caption = Record!CustomerName
                    .Sections("Section4").Controls("Label28").Caption = Record!Address
                    .Sections("Section4").Controls("Label29").Caption = Record!City
                    .Sections("Section4").Controls("Label31").Caption = Record!ContactNo
                    .Sections("Section4").Controls("Label33").Caption = Record!Tin
                    .Sections("Section4").Controls("Label34").Caption = Record!Dist
                    .Show 1
                Set Record = Nothing
             End With
            Exit Sub
        End If
    
    End Sub
    Serial number in Data Report is not starting from 1,2...?

  2. #2
    PowerPoster ChrisE's Avatar
    Join Date
    Jun 2017
    Location
    Frankfurt
    Posts
    3,046

    Re: Serial number in Data Report is not starting from 1,2...

    what do you mean with Serial number

    in your SQL-Statement you use ...
    Code:
    C2.ProductCode <= C.ProductCode) AS SerNo FROM SalesInvoice
    SerNo is not in use within your DataReport...??

    regards
    Chris

  3. #3

    Thread Starter
    Lively Member
    Join Date
    May 2009
    Posts
    110

    Re: Serial number in Data Report is not starting from 1,2...

    Name:  sr.jpg
Views: 434
Size:  58.8 KB
    Here i am using SerNo ..???

  4. #4
    PowerPoster
    Join Date
    Feb 2012
    Location
    West Virginia
    Posts
    14,205

    Re: Serial number in Data Report is not starting from 1,2...

    Look at the code you posted, there is no reference to the serno field related to the report. So if the field is used then it is assigned somewhere else.

    You also have it setup to return a count so serno seems an odd name for it.
    Last edited by DataMiser; Jun 24th, 2017 at 08:12 AM.

  5. #5

    Thread Starter
    Lively Member
    Join Date
    May 2009
    Posts
    110

    Re: Serial number in Data Report is not starting from 1,2...

    Code:
    (SELECT COUNT(*) FROM SalesInvoice C2 WHERE C2.ProductCode <= C.ProductCode) AS SerNo FROM SalesInvoice C
    Here i am using count for Serno

  6. #6
    PowerPoster
    Join Date
    Feb 2012
    Location
    West Virginia
    Posts
    14,205

    Re: Serial number in Data Report is not starting from 1,2...

    As I said you must be assigning it somewhere else as it is not assigned to the report in that code. It is in the select statement but seems rather strange as named and used. A serial number is usually a unique number but you are using a count where the product code <= the current product.
    Since I have no idea what is in your database I can't say exactly what result you should be getting from that but I would guess that it is often going to be a number much higher than 1

    What is it that you are actually trying to do?
    What should that number represent?

  7. #7

    Thread Starter
    Lively Member
    Join Date
    May 2009
    Posts
    110

    Re: Serial number in Data Report is not starting from 1,2...

    actually i need serial number count like 1,2 for records displayed in Report.
    But i am getting 13,14 like this
    Name:  report.jpg
Views: 407
Size:  27.3 KB
    Last edited by rasheedraj@gmail.com; Jun 24th, 2017 at 09:01 AM.

  8. #8
    PowerPoster
    Join Date
    Feb 2012
    Location
    West Virginia
    Posts
    14,205

    Re: Serial number in Data Report is not starting from 1,2...

    What do you mean by serial number count? Serial Numbers are by definition unique so the count would always be 1

    Did you understand what I said about the way you are selecting them and that will in many if not most cases give you a higher number than you want?
    In other words if you have a product with the product code 12345 and you have sold that product 10 different times and now you create an invoice where you sell product 23456 the count you are using would show 11 because you have 11 sales where product code <=23456


    Perhaps what you are actually looking for is not a count of anything but the line number where it appears on the report?

  9. #9
    PowerPoster ChrisE's Avatar
    Join Date
    Jun 2017
    Location
    Frankfurt
    Posts
    3,046

    Re: Serial number in Data Report is not starting from 1,2...

    Quote Originally Posted by DataMiser View Post
    Perhaps what you are actually looking for is not a count of anything but the line number where it appears on the report?
    I think you are wright DataMiser

    rascheed if you are trying to add a running number you will have to refrence in you SQL-Statement something
    with ......+1 AS SerNo...

    Code:
            
    "SELECT InvoiceNo,Qty,Price,TotalAmount,DateCreated,CustomerName,ProductCode,ContactNo,Address,City,Tin,Dist,Packing,(SELECT COUNT(*) FROM SalesInvoice C2 WHERE C2.ProductCode <= C.ProductCode)+1 AS SerNo FROM SalesInvoice C where InvoiceNo
    just guessing

    regards
    Chris

  10. #10
    PowerPoster ChrisE's Avatar
    Join Date
    Jun 2017
    Location
    Frankfurt
    Posts
    3,046

    Re: Serial number in Data Report is not starting from 1,2...

    here a Sample with a running number

    open the Northwind Database and add a new Query
    in the new Query add this as Sql
    Code:
    SELECT Products.ProductID, Products.ProductName, (Select Count (*) FROM [Products] as X WHERE [X].[ProductName] >"c" AND [X].[ProductName] < [Products].[ProductName])+1 AS SerNo
    FROM Products
    WHERE (((Products.ProductName) Like "c*"))
    ORDER BY Products.ProductName;
    then you will see the Products have a number 1 to ......

  11. #11

    Thread Starter
    Lively Member
    Join Date
    May 2009
    Posts
    110

    Re: Serial number in Data Report is not starting from 1,2...

    Chrise sir
    i am using MS Access 2003 database
    is the command is same???

  12. #12
    PowerPoster
    Join Date
    Feb 2012
    Location
    West Virginia
    Posts
    14,205

    Re: Serial number in Data Report is not starting from 1,2...

    Why not try it and see?

  13. #13

    Thread Starter
    Lively Member
    Join Date
    May 2009
    Posts
    110

    Re: Serial number in Data Report is not starting from 1,2...

    Code:
    SELECT InvoiceNo,Qty,Price,TotalAmount,DateCreated,CustomerName,ProductCode,ContactNo,Address,City,Tin,Dist,Packing, (Select Count (*) FROM [SalesInvoice] as X WHERE [X].[ProductCode] >"c" AND [X].[ProductCode] < [SalesInvoice ].[ProductCode])+1 AS SerNo FROM SalesInvoice WHERE (((ProductCode) Like "c*")) ORDER BY ProductCode
    Error on "c*"

  14. #14
    PowerPoster ChrisE's Avatar
    Join Date
    Jun 2017
    Location
    Frankfurt
    Posts
    3,046

    Re: Serial number in Data Report is not starting from 1,2...

    do you have productnames with c ?

  15. #15

    Thread Starter
    Lively Member
    Join Date
    May 2009
    Posts
    110

    Re: Serial number in Data Report is not starting from 1,2...

    No
    What should be C

  16. #16
    PowerPoster ChrisE's Avatar
    Join Date
    Jun 2017
    Location
    Frankfurt
    Posts
    3,046

    Re: Serial number in Data Report is not starting from 1,2...

    I see in your Report there is a Product SG1321

    change c to s

  17. #17

    Thread Starter
    Lively Member
    Join Date
    May 2009
    Posts
    110

    Re: Serial number in Data Report is not starting from 1,2...

    i have products with different codes
    then what to do???

  18. #18
    PowerPoster ChrisE's Avatar
    Join Date
    Jun 2017
    Location
    Frankfurt
    Posts
    3,046

    Re: Serial number in Data Report is not starting from 1,2...

    the sample I gave you was search for all products beginning with the Letter c
    you will have to adapt the code to your requirements

    did you get it working ?

    regards
    Chris

  19. #19

    Thread Starter
    Lively Member
    Join Date
    May 2009
    Posts
    110

    Re: Serial number in Data Report is not starting from 1,2...

    Not working
    Even after i change C to S
    same error
    Sample for Ms Access plz

  20. #20
    PowerPoster ChrisE's Avatar
    Join Date
    Jun 2017
    Location
    Frankfurt
    Posts
    3,046

    Re: Serial number in Data Report is not starting from 1,2...

    did you try the sample with the Northwind Database

  21. #21
    PowerPoster
    Join Date
    Feb 2006
    Posts
    24,482

    Re: Serial number in Data Report is not starting from 1,2...

    Here is another free-standing example if it helps. This one creates a sample database on first run:

    Code:
    Option Explicit
    
    Private DbHandler As DbHandler
    
    Private Sub Main()
        ChDrive App.Path
        ChDir App.Path
        Set DbHandler = New DbHandler
        DbHandler.OpenConnection "Gobbler's.mdb"
        With DataReport1
            Set .DataSource = _
                DbHandler.Query("SELECT " _
                              & "(SELECT COUNT(*) FROM [Turkeys] WHERE [Age] BETWEEN 4 AND 18 " _
                              & "AND [ID] <= [T].[ID]) AS [RowCount], " _
                              & "* FROM [Turkeys] [T] WHERE [Age] BETWEEN 4 AND 18")
            .Show
        End With
    End Sub
    Name:  sshot.png
Views: 359
Size:  7.8 KB
    Attached Files Attached Files

  22. #22

    Thread Starter
    Lively Member
    Join Date
    May 2009
    Posts
    110

    Re: Serial number in Data Report is not starting from 1,2...

    Thanks alot or kind help
    Its working

  23. #23

    Thread Starter
    Lively Member
    Join Date
    May 2009
    Posts
    110

    Re: Serial number in Data Report is not starting from 1,2...

    Code:
    ("SELECT " _
                              & "(SELECT COUNT(*) FROM [Customer]," _
                              & "AND [ID] <= [T].[ID]) AS [SerNo],"), Connect, 1, 3
    Syntax Error

  24. #24
    PowerPoster
    Join Date
    Feb 2012
    Location
    West Virginia
    Posts
    14,205

    Re: Serial number in Data Report is not starting from 1,2...

    That is an invalid SQL statement Looks like you are missing a line that would contain the first part of the where clause

  25. #25

    Thread Starter
    Lively Member
    Join Date
    May 2009
    Posts
    110

    Re: Serial number in Data Report is not starting from 1,2...

    i don't want any filter, i want all records of table show in report

  26. #26
    PowerPoster
    Join Date
    Feb 2012
    Location
    West Virginia
    Posts
    14,205

    Re: Serial number in Data Report is not starting from 1,2...

    Then you need to remove part of that last line the And portion is part of a where clause.
    You may want to consider reading some basic tutorials on SQL

Posting Permissions

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



Click Here to Expand Forum to Full Width