×
INTELLIGENT WORK FORUMS
FOR COMPUTER PROFESSIONALS

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

(OP)
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

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

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