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 CI.name,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 two dtpicker one for from date and the other todate..when the user slects a range from the dtpicker fromdtpiker and todtpicker the records in the data grid shud be within that range(i e only those transactions shud be seen).can anyone 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
any :idea:

Edited by mike_2000_17 because: Fixed formatting
  • 4 Contributors
  • 5 Replies
  • 306 Views
  • 1 Day Discussion Span
  • Latest Post Latest Post by jireh

Dani AI

Generated

This thread shows the usual symptoms: Access date-format/locale mismatches, a Date/Time field that actually stores time-of-day, and SQL built from strings. ’s schema is fine, and the Between/CDate and Format ideas from and will work in simple cases, but they can fail when the stored times are non-zero or when regional settings change. ’s suggestion to aggregate with SQL is valid — parameterized queries are safer and simpler.

Prefer a parameterized JOIN and a date-range that treats the end date as exclusive (avoids missing rows with a non‑midnight time). Use DateTimePicker.Value.Date and add one day to the end value, then pass both as parameters (OleDb uses positional ? parameters). Example pattern (VB.NET / OleDb):

Dim startDate As Date = DateTimePicker1.Value.Date
Dim endExclusive As Date = DateTimePicker2.Value.Date.AddDays(1)

Dim sql As String =
  "SELECT CI.Name, I.ServiceID, P.Packagename, P.Duration, P.Price, I.[Date], I.TimeIn, I.TimeOut " &
  "FROM (CustInfo CI INNER JOIN InDate I ON CI.CustID = I.CustID) " &
  "INNER JOIN PakInfo P ON I.ServiceID = P.ServiceID " &
  "WHERE I.[Date] >= ? AND I.[Date] < ? ORDER BY CI.Name"

' Add parameters in the same order as the placeholders
cmd.Parameters.AddWithValue("p1", startDate)
cmd.Parameters.AddWithValue("p2", endExclusive)

To show the total, either run a separate SELECT SUM(P.Price) ... with the same parameters or sum the grid in code. Example summing the DataGridView Price column:

Dim total As Decimal = 0D
For Each r As DataGridViewRow In DataGridView1.Rows
  If Not r.IsNewRow Then
    Dim v = r.Cells("Price").Value
    If v IsNot Nothing AndAlso Not IsDBNull(v) Then total += Convert.ToDecimal(v)
  End If
Next
LabelTotal.Text = total.ToString("F2")

Quick troubleshooting checklist: confirm the Access field type is Date/Time (not Text); avoid naming a column Date (or always reference it as [Date]); ensure OleDb parameter order matches ? positions; check DateTimePicker values in the debugger; and add an index on the date column for large tables.

Recommended Answers

All 5 Replies

Member Avatar for cometburn

Select ,I.ServiceID,P.packagename,P.duration,P.price,[I.date],I.timeIn,I.timeOut
From CustInfo CI, InDate I, PakInfo P Where [Date] >= <DatePicker1> and [Date] <= <DatePicker2>....

Now to sum up a Column, u can use... Select Sum(<ColumnName>) as Sumof where <You can put the condition here>

Member Avatar for QVeen72

Hi,

Try this :

"Select CI.name,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 I.Date Between #" & DtPicker1.Value & "# And #" & DtPicker2.Value & "#"

OR

" And I.Date Between CDate('" & DtPicker1.Value & "') And CDate('" _
& DtPicker2.Value & "') "

Regards
Veena

Member Avatar for ITech

Still its not working it is not filtering to date range specified

Member Avatar for QVeen72

Hi,

Both the queries should work.. we are using it in all our real-time projs..

What is the Regional Date setting..? and date filed format in access...?

Keep a Breakpoint and find in Debug Window, what is the SQL Query after selecting the date.. Post it here..

REgards
Veena

Member Avatar for jireh 
strqry = "Select * from tablename
where fieldnamefordate between #" & Format(StartDate,"MM/dd/yyyy") & "# And #" & Format(EndDate,"MM/dd/yyyy") & "#"
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.