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 3 Years Ago by mike_2000_17: Fixed formatting

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 [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>

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

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

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

strqry = "Select * from tablename
where fieldnamefordate between #" & Format(StartDate,"MM/dd/yyyy") & "# And #" & Format(EndDate,"MM/dd/yyyy") & "#"
This article has been dead for over six months. Start a new discussion instead.