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...?
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.
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?
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?
Re: Serial number in Data Report is not starting from 1,2...
Originally Posted by DataMiser
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
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 ......
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
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