I tried something like this to have balance of stock at any date or interval between two dates Dim cmdText As String = "SELECT pt.ItemId, pt.Description, SUM(pt.Quantity)AS QuantityPurchased, SUM(st.Quantity) AS QuantitySold, (SUM(pt.Quantity) - SUM(st.Quantity)) AS Balance FROM PurchaseTable pt INNER JOIN SalesTable st ON pt.ItemId=st.ItemId WHERE pt.PDate Between @START and @END GROUP BY pt.ItemId, pt.Description" If con.State = ConnectionState.Closed Then con.Open() Dim cmd As OleDb.OleDbCommand = New OleDb.OleDbCommand(cmdText, con) cmd.CommandType = CommandType.Text cmd.Parameters.AddWithValue("@START", OleDb.OleDbType.Date).Value = TextBox1.Text cmd.Parameters.AddWithValue("@END", OleDb.OleDbType.Date).Value = TextBox2.Text Dim dr As OleDb.OleDbDataReader If con.State = ConnectionState.Closed Then con.Open() dr = cmd.ExecuteReader If Not dr.HasRows Then MessageBox.Show("No Records Found for Date: " & TextBox1.Text) Else MessageBox.Show("Record found for Date: " & TextBox1.Text) ListView1.Items.Clear() ListView1.ForeColor = Color.DarkRed ListView1.GridLines = True While dr.Read Dim ls As New ListViewItem(dr.Item("ItemId").ToString()) ls.SubItems.Add(dr.Item("Description").ToString()) ls.SubItems.Add(dr.Item("QuantityPurchased").ToStr ing()) ls.SubItems.Add(dr.Item("QuantitySold").ToString() ) ls.SubItems.Add(dr.Item("Balance").ToString()) ListView1.Items.Add(ls) End While End If But i could not get the desired results. Please advise what i am doing wrong with this. The problem i am having is as under: Data in PurchaseTable PId PDate ItemId Description Price Quantity Amount 1 28/8/2009 1 Coca Cola Normal 1,00 10 10,00 2 28/8/2009 2 Coca Cola Zero 1,00 5 5,00 3 29/8/2009 1 Coca Cola Normal 1,00 5 5,00 4 29/8/2009 2 Coca Cola Zero 1,00 10 10,00 Data in Sales Table is as under: SId SDate ItemId Description Price Quantity Amount 1 30/8/2009 1 Coca Cola Normal 2,70 2 5,40 2 30/8/2009 2 Coca Cola Zero 2,70 3 7,10 3 31/8/2009 1 Coca Cola Normal 2,70 1 2,70 4 31/8/2009 2 Coca Cola Zero 2,70 2 2,70 The result of query with date range of 28-08-2009 to 29-08-2009 and also with the date range of 28-08-2009 to 31-08-2009 is displayed as under: Coca Cola Normal 30 6 24 Coca Cola Zero 30 10 20 whereas actually for date range of 28-08-2009 to 29-08-2009 the result should be as under: Coca Cola Normal 15 0 15 Coca Cola Zero 15 0 15 And with the date range of 28-08-2009 to 31-08-2009 the result should b

The problem i am having is as under: Data in PurchaseTable PId PDate ItemId Description Price Quantity Amount 1 28/8/2009 1 Coca Cola Normal 1,00 10 10,00 2 28/8/2009 2 Coca Cola Zero 1,00 5 5,00 3 29/8/2009 1 Coca Cola Normal 1,00 5 5,00 4 29/8/2009 2 Coca Cola Zero 1,00 10 10,00 Data in Sales Table is as under: SId SDate ItemId Description Price Quantity Amount 1 30/8/2009 1 Coca Cola Normal 2,70 2 5,40 2 30/8/2009 2 Coca Cola Zero 2,70 3 7,10 3 31/8/2009 1 Coca Cola Normal 2,70 1 2,70 4 31/8/2009 2 Coca Cola Zero 2,70 2 2,70 The result of query with date range of 28-08-2009 to 29-08-2009 and also with the date range of 28-08-2009 to 31-08-2009 is displayed as under: Coca Cola Normal 30 6 24 Coca Cola Zero 30 10 20 whereas actually for date range of 28-08-2009 to 29-08-2009 the result should be as under: Coca Cola Normal 15 0 15 Coca Cola Zero 15 0 15 And with the date range of 28-08-2009 to 31-08-2009 the result should be as under: Coca Cola Normal 15 3 12 Coca Cola Zero 15 5 10 Pleae advise what i am doing wrong with the query. Thanks again for your guidance.

I have been guided to put my question to this forum. I am using vb express and MsAccess as database. I am trying to write a query for the calculation of Stock. My tables are as under: PId PDate ItemId Description Price Quantity Amount 1 28/8/2009 1 Coca Cola Normal 1,00 10 10,00 2 28/8/2009 2 Coca Cola Zero 1,00 5 5,00 3 29/8/2009 1 Coca Cola Normal 1,00 5 5,00 4 29/8/2009 2 Coca Cola Zero 1,00 10 10,00 Data in Sales Table is as under: SId SDate ItemId Description Price Quantity Amount 1 30/8/2009 1 Coca Cola Normal 2,70 2 5,40 2 30/8/2009 2 Coca Cola Zero 2,70 3 7,10 3 31/8/2009 1 Coca Cola Normal 2,70 1 2,70 4 31/8/2009 2 Coca Cola Zero 2,70 2 2,70 I tried something like this to have balance of stock at any date or interval between two dates Dim cmdText As String = "SELECT pt.ItemId, pt.Description, SUM(pt.Quantity)AS QuantityPurchased, SUM(st.Quantity) AS QuantitySold, (SUM(pt.Quantity) - SUM(st.Quantity)) AS Balance FROM PurchaseTable pt INNER JOIN SalesTable st ON pt.ItemId=st.ItemId WHERE pt.PDate Between @START and @END GROUP BY pt.ItemId, pt.Description" If con.State = ConnectionState.Closed Then con.Open() Dim cmd As OleDb.OleDbCommand = New OleDb.OleDbCommand(cmdText, con) cmd.CommandType = CommandType.Text cmd.Parameters.AddWithValue("@START", OleDb.OleDbType.Date).Value = TextBox1.Text cmd.Parameters.AddWithValue("@END", OleDb.OleDbType.Date).Value = TextBox2.Text Dim dr As OleDb.OleDbDataReader If con.State = ConnectionState.Closed Then con.Open() dr = cmd.ExecuteReader If Not dr.HasRows Then MessageBox.Show("No Records Found for Date: " & TextBox1.Text) Else MessageBox.Show("Record found for Date: " & TextBox1.Text) ListView1.Items.Clear() ListView1.ForeColor = Color.DarkRed ListView1.GridLines = True While dr.Read Dim ls As New ListViewItem(dr.Item("ItemId").ToString()) ls.SubItems.Add(dr.Item("Description").ToString()) ls.SubItems.Add(dr.Item("QuantityPurchased").ToStr ing()) ls.SubItems.Add(dr.Item("QuantitySold").ToString() ) ls.SubItems.Add(dr.Item("Balance").ToString()) ListView1.Items.Add(ls) End While End If But i could not get the desired results. Please advise what i am doing wrong with this. Thanks

thanks for your reply I tried something like this: Dim cmdText As String = "SELECT pt.ItemId, pt.Description, SUM(pt.Quantity)AS QuantityPurchased, SUM(st.Quantity) AS QuantitySold, (SUM(pt.Quantity) - SUM(st.Quantity)) AS Balance FROM PurchaseTable pt INNER JOIN SalesTable st ON pt.ItemId=st.ItemId WHERE pt.PDate Between @START and @END GROUP BY pt.ItemId, pt.Description" But it did not give the desired results. it is not making the correct calculations.

I am using vb express and MsAccess as database. I am trying to write a query for the calculation of Stock. My tables are as under: PId PDate ItemId Description Price Quantity Amount 1 28/8/2009 1 Coca Cola Normal 1,00 10 10,00 2 28/8/2009 2 Coca Cola Zero 1,00 5 5,00 3 29/8/2009 1 Coca Cola Normal 1,00 5 5,00 4 29/8/2009 2 Coca Cola Zero 1,00 10 10,00 Data in Sales Table is as under: SId SDate ItemId Description Price Quantity Amount 1 30/8/2009 1 Coca Cola Normal 2,70 2 5,40 2 30/8/2009 2 Coca Cola Zero 2,70 3 7,10 3 31/8/2009 1 Coca Cola Normal 2,70 1 2,70 4 31/8/2009 2 Coca Cola Zero 2,70 2 2,70 I tried something like this to have balance of stock at any date or interval between two dates Dim cmdText As String = "SELECT pt.ItemId, pt.Description, SUM(pt.Quantity)AS QuantityPurchased, SUM(st.Quantity) AS QuantitySold, (SUM(pt.Quantity) - SUM(st.Quantity)) AS Balance FROM PurchaseTable pt INNER JOIN SalesTable st ON pt.ItemId=st.ItemId WHERE pt.PDate Between @START and @END GROUP BY pt.ItemId, pt.Description" If con.State = ConnectionState.Closed Then con.Open() Dim cmd As OleDb.OleDbCommand = New OleDb.OleDbCommand(cmdText, con) cmd.CommandType = CommandType.Text cmd.Parameters.AddWithValue("@START", OleDb.OleDbType.Date).Value = TextBox1.Text cmd.Parameters.AddWithValue("@END", OleDb.OleDbType.Date).Value = TextBox2.Text Dim dr As OleDb.OleDbDataReader If con.State = ConnectionState.Closed Then con.Open() dr = cmd.ExecuteReader If Not dr.HasRows Then MessageBox.Show("No Records Found for Date: " & TextBox1.Text) Else MessageBox.Show("Record found for Date: " & TextBox1.Text) ListView1.Items.Clear() ListView1.ForeColor = Color.DarkRed ListView1.GridLines = True While dr.Read Dim ls As New ListViewItem(dr.Item("ItemId").ToString()) ls.SubItems.Add(dr.Item("Description").ToString()) ls.SubItems.Add(dr.Item("QuantityPurchased").ToStr ing()) ls.SubItems.Add(dr.Item("QuantitySold").ToString() ) ls.SubItems.Add(dr.Item("Balance").ToString()) ListView1.Items.Add(ls) End While End If But i could not get the desired results. Please advise what i am doing wrong with this. Thanks

I am using vb express and MsAccess as database. I am trying to write a query for the calculation of Stock. My tables are as under: Data in PurchaseTable PId PDate ItemId Description Price Quantity Amount 1 28/8/2009 1 Coca Cola Normal 1,00 10 10,00 2 28/8/2009 2 Coca Cola Zero 1,00 5 5,00 3 29/8/2009 1 Coca Cola Normal 1,00 5 5,00 4 29/8/2009 2 Coca Cola Zero 1,00 10 10,00 Data in Sales Table is as under: SId SDate ItemId Description Price Quantity Amount 1 30/8/2009 1 Coca Cola Normal 2,70 2 5,40 2 30/8/2009 2 Coca Cola Zero 2,70 3 7,10 3 31/8/2009 1 Coca Cola Normal 2,70 1 2,70 4 31/8/2009 2 Coca Cola Zero 2,70 2 2,70 I want a query for stockcalculation to show data as under: PDate,ItemId, QuantityPurchased, QuantitySold, Balance(QuantityPurchased - QuantitySold) Please advise how I can get the above information through a query on the above two tables to be displayed in a Listview.

thanks for your reply. i tried the sites you suggested but there is code for vb only. i could not find code for vb express. please adivse.

I am working in Vb Express 2008 with MsAccess as database. Please advise code for transfer of data records from ListView to a table in MsAccess. ListView shows like this: FirstName LastName City Peter Anderson NewYork John Pierce Lansdale

Private Sub Button1_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles Button1.Click Dim total As Integer total = txtQuantity.Text * txtPrice.Text txtAmount.Text = total If ListBox1.Items.Count <> 0 Then ListBox1.Items.RemoveAt(ListBox1.Items.Count - 1) End If ListBox1.Items.Add(txtId.Text + vbTab + txtDescription.Text + vbTab + txtQuantity.Text + vbTab + txtPrice.Text + vbTab + txtAmount.Text) total = total + Integer.Parse(txtAmount.Text) ListBox1.Items.Add("Total " + vbTab + vbTab + vbTab + total.ToString()) End Sub Private Sub Form1_Load(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles MyBase.Load ListBox1.Items.Add("Id " + vbTab + "Description " + vbTab + "Quantity " + vbTab + "Price " + vbTab + "Amount ") End Sub Private Sub btnTransData_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles btnTransData.Click Dim desc, qnty, price, amount As String Dim Cmd As OleDb.OleDbCommand Dim SQL As String Dim objCmd As New OleDb.OleDbCommand Dim Con = New OleDb.OleDbConnection ""Provider=Microsoft.Jet.OLEDB.4.0;Data Source=C:\Users\Propriet rio\Desktop\bd2.mdb") Dim id As Integer = 0 Con.Open() For Each item As String In ListBox1.Items If id <> 0 Then Dim parts() As String = item.Split(" ") SQL = "INSERT INTO TestTable (*) VALUES ('" & id.ToString & "', '" & parts(0) & "', " & parts(1) & ", '" & parts(2) & "', '" & parts(3) & "')" Cmd = New OleDb.OleDbCommand(SQL, Con) objCmd = New OleDb.OleDbCommand(SQL, Con) objCmd.ExecuteNonQuery() End If id = id + 1 Next Con.Close() End Sub it is giving the error ""IndexOutOfRangeExecption was unhandled"" kindly advise the correction.

I want to transfer the data displayed in listbox to a table and assign an IdNumber to each data transfer. And then to retrieve that data from that table for the relevant Id and after more additions to that data in list box, transfer again to table and save it permanently. I am using MsAccess as database. i have following data in list box in vb express 2008: Description Quantity Price Amount Order1 2 3 6 Order2 3 4 12 Order3 2 3 6 Order4 4 2 8 Total 32 So how i can transfer this data to a table giving it one Id and then retrieve that data in listbox from that table using the same id and after addition of more two Orders( Order5 and Order6) in the list box, transfer it back to that table for permanent save. Kindly advise me the code for this requirement.

so there is no option for alignment of data in columns in listbox. to add spaces is difficult. because the data is being transferred from tables and there is text of different in each row and column. like: Coca Cola Normal Sprite Fanta Pepsi Coca Cola Zero so it is not possible to add spaces. can anybody have any idea to fix the problem of aligning the data in columns in listbox in vb2008

thanks for your advice. I tried that font of COURIER NEW. but the result is same. the columns data is not formatted. kindly give some other idea. or is there any option in listbox properties to align the data in columns.

I am new to vb 2008. i have four textboxes. and the data is displayed in a listbox in four columns. but the columns are not formatted. like the data shows like this. Order1 23 3 23.2 new order1 25 3 54 Order4 54 4 52.3 as you see the columns are not formatted. kindly advise me how to format columns in listbox so that data is aligned in a proper format.