Member Avatar for ITech

hi

i aam trying to develop a project using vb and access

i have got a problem

i have 3 tables in access

1.Cust info table(fields are listed below)
cust id,name,age,sex,country...etcxc(primary key cust id)

2,indate(fields are listed below)
custid ,packageid,date,intime,outime(no primary key)

3.package info table(fields are listed below
package id, package name,duration ,price(primary key packageid)


tthese three tables are connected...the attachment shows how it is connected

i want to write a query to select custid, custname,packageid,packagename,duration,price,date,timein,timeout from these tables in vb..n in a data grid to display these.

this has been solved by the following query

Select ,I.ServiceID,P.packagename,P.duration,P.price,I.date,I.timeIn,I.timeOut
From CustInfo CI, InDate I, PakInfo P Where CI.CustID = I.CustID And I.ServiceID = P.ServiceID Order By CI.Name

now the problem is

i want to put a combo box and inthat combo box i have put months from jan to december...if the user selects jan it shud show filter the records from that query respect to date....if it is jan all the transactions in jan.it shud show in the data grid..can you tell me how to do that,,and one more thing there is a textbox to display the total. in that label it shud sum up all the price column listed in side the datagrid respect to the month selected by the user

can any one help me with this

  • 2 Contributors
  • 1 Reply
  • 201 Views
  • 15 Hours Discussion Span
  • Latest Post Latest Post by QVeen72

Dani AI

Generated

: the goal is to show the joined rows for a selected month and display the total price for that month. 's idea to test the month value will work, but it has two practical drawbacks: using Month([Date]) in the WHERE clause prevents the database from using an index on the date column (slower on large tables) and can mis-handle times if you try inclusive endpoints. Use a date-range filter instead and pass real Date/DateTime values as parameters.

Compute the month boundaries in VB, then pass them to a parameterized query. Example (VB-style date math shown — adapt to VB6 or VB.NET as needed):

monthStart = DateSerial(selectedYear, selectedMonth, 1)
nextStart  = DateAdd("m", 1, monthStart)

Use a query that filters I.Date >= ? AND I.Date < ? (or named parameters) so you get all rows whose date/time falls in the month, and the database can use indexes. Prefer parameterized commands to concatenating date strings; if you must embed literals for Access, date literals use #mm/dd/yyyy# and are locale-sensitive.

For the total, you can either run a second SQL that uses SUM(price) with the same two parameters, or compute the sum client-side after filling the grid. Example client-side (ADO.NET DataTable) one-liner:

total = CDec(dt.Compute("SUM(price)", String.Empty))

Troubleshooting notes: confirm I.Date is stored as a Date/Time type (not text), make sure price is numeric (handle NULLs with Nz or coalesce), and include year selection if you need multi-year data. In short: keep the join logic you already have, replace Month(...) filtering with a start/end date range, and use SUM with the same parameters (or compute on the client) for the total — this gives correct results and scales much better than extracting month numbers in SQL.

Member Avatar for QVeen72

Hi,

After Selection of ComboBox, To filter out records use this query:

"Select ,I.ServiceID,P.packagename,P.duration,P.price,I.date,I.timeIn,I.timeOut
From CustInfo CI, InDate I, PakInfo P Where CI.CustID = I.CustID And I.ServiceID = P.ServiceID And Month(I.Date) = " & (Combo1.ListIndex+1) & " Order By CI.Name"

Assuming, Your ComboBox is not sorted and it is in the Order "Jan","Feb","Mar"...

To Select Sum of price use this query:

"Select Sum(P.price)
From CustInfo CI, InDate I, PakInfo P Where CI.CustID = I.CustID And I.ServiceID = P.ServiceID "

Open recordset like above and populate the label..

Regards
Veena

commented: very good touch in vb +1
Be a part of the DaniWeb community

We're a friendly, industry-focused community of developers, IT pros, digital marketers, and technology enthusiasts meeting, networking, learning, and sharing knowledge.