Create a VB Data Report with a user input Date Range 1

[canpross]

I have a data report created and it works fine. I need to be able to have the user select a date range for the set of records thay want to view. This is similar to prompting the user in access for the start date and end date before a report opens. I Think I need to add a where clause in my SQL statement, but I'm not sure of the syntax. Can this be done? This is the query:

SELECT Master.[Last Name], Master.Date, Master.Week, Master.[Activity Code], Master.[Air-Amex], Master.[Hotel-Amex], Master.[Breakfast-Amex], Master.[Lunch-Amex], Master.[Dinner-Amex], Master.[Auto Rental-Amex], [Ground Xport-Amex]+[Parking-Amex]+[Gas-Amex]+[Toll-Amex] AS [Transportation-Amex], Master.[Phone-Amex], [Miscellaneous1-Amex]+[Miscellaneous21-Amex]+[Miscellaneous3-Amex] AS [Miscellaneous-Amex], Master.[Misc1Name-Amex], Master.[Misc2Name-Amex], Master.[Misc3Name-Amex], Master.[Entertainment-Amex], Master.[Air-Cash], Master.[Hotel-Cash], Master.[Breakfast-Cash], Master.[Lunch-Cash], Master.[Dinner-Cash], Master.[Auto Rental-Cash], [Ground Xport-Cash]+[Gas-Cash]+[Parking-Cash]+[Toll-Cash] AS [Transportation-Cash], Master.[Phone-Cash], [Miscellaneous1-Cash]+[Miscellaneous21-Cash]+[Miscellaneous3-Cash] AS [Miscellaneous-Cash], Master.[Misc1Name-Cash], Master.[Misc2Name-Cash], Master.[Misc3Name-Cash], Master.[Entertainment-Cash], IIf([date]<#4/1/2000#,[mileage]*0.31,[mileage]*0.325) AS [Mileage Reimbursement], [Air-Amex]+[Hotel-Amex]+[Breakfast-Amex]+[Lunch-Amex]+[Dinner-Amex]+[Auto Rental-Amex]+[Ground Xport-Amex]+[Parking-Amex]+[Gas-Amex]+[Phone-Amex]+[Toll-Amex]+[Miscellaneous1-Amex]+[Miscellaneous21-Amex]+[Miscellaneous3-Amex]+[Entertainment-Amex] AS [Total Amex], [Air-Cash]+[Hotel-Cash]+[Breakfast-Cash]+[Lunch-Cash]+[Dinner-Cash]+[Auto Rental-Cash]+[Ground Xport-Cash]+[Parking-Cash]+[Gas-Cash]+[Phone-Cash]+[Toll-Cash]+[Miscellaneous1-Cash]+[Miscellaneous21-Cash]+[Miscellaneous3-Cash]+[Entertainment-Cash] AS [Total Cash], Master.Currency, Master.[Hotel-Nonreimbursable], Master.[Air-Nonreimbursable], Master.[MiscName-Nonreimbursable], Master.[Miscellaneous-Nonreimbursable], [Air-Amex]+[Air-Cash] AS Air, [Hotel-Amex]+[Hotel-Cash] AS Hotel, [Auto Rental-Amex]+[Auto Rental-Cash] AS Auto, [Transportation-Amex]+[Transportation-Cash] AS [Ground Xport], [Phone-Amex]+[Phone-Cash] AS Phone, [Miscellaneous-Amex]+[Miscellaneous-Cash] AS Miscellaneous, [Breakfast-Amex]+[Breakfast-Cash] AS Breakfast, [Lunch-Amex]+[Lunch-Cash] AS Lunch, [Dinner-Amex]+[Dinner-Cash] AS Dinner, [Entertainment-Amex]+[Entertainment-Cash] AS Entertainment, [Air-Nonreimbursable]+[Hotel-nonreimbursable]+[Miscellaneous-Nonreimbursable] AS [Total Non-Reimbursable]
FROM Master
ORDER BY Master.Date;

 

[Guest_imported]

Have a look here:

http://vb.digitalroutes.co.uk/index.shtml?datareport

the project &quot;connect a datareport to an ADO recordset&quot; should give you exactly what you need.

 

[varnit]

hello..

u can input the values to create a view for this table and then display that view according to ur requirement in a Data Report through a data environment.

the syntax which i know is for Oracle as it is-

create or replace view master_view as (select name, age, address from master);

and u can use this query through VB to create it, just pass the control name as the arguments and ur work is done.
Good Luck
Varnit