Contact US

Log In

Come Join Us!

Are you a
Computer / IT professional?
Join Tek-Tips Forums!
  • Talk With Other Members
  • Be Notified Of Responses
    To Your Posts
  • Keyword Search
  • One-Click Access To Your
    Favorite Forums
  • Automated Signatures
    On Your Posts
  • Best Of All, It's Free!

*Tek-Tips's functionality depends on members receiving e-mail. By joining you are opting in to receive e-mail.

Posting Guidelines

Promoting, selling, recruiting, coursework and thesis posting is forbidden.

Students Click Here

Create a VB Data Report with a user input Date Range

Create a VB Data Report with a user input Date Range

Create a VB Data Report with a user input Date Range

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;

RE: Create a VB Data Report with a user input Date Range


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

Red Flag This Post

Please let us know here why this post is inappropriate. Reasons such as off-topic, duplicates, flames, illegal, vulgar, or students posting their homework.

Red Flag Submitted

Thank you for helping keep Tek-Tips Forums free from inappropriate posts.
The Tek-Tips staff will check this out and take appropriate action.

Reply To This Thread

Posting in the Tek-Tips forums is a member-only feature.

Click Here to join Tek-Tips and talk with other members! Already a Member? Login

Close Box

Join Tek-Tips® Today!

Join your peers on the Internet's largest technical computer professional community.
It's easy to join and it's free.

Here's Why Members Love Tek-Tips Forums:

Register now while it's still free!

Already a member? Close this window and log in.

Join Us             Close