INTELLIGENT WORK FORUMS
FOR COMPUTER PROFESSIONALS

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.

Jobs

Wildcard (*) use for a date field?

Wildcard (*) use for a date field?

(OP)
Designing a report that has several different date fields, for example Manufacturing Date, Ship Date, and Install Date.

The user wants to be able to provide a date range and they also want to be able to be prompted for all 3 date fields. Depending on which date field they select, one date range could influence the other.

For example, we are looking for products shipped between 2 dates. The date provided for the Install or Manufacturing prompt could limit the results. To eliminate that possibility, we would like to be able to enter a wildcard so it considers all dates. Since this is a date field it will not accept an *.

Would it be possible to provide a wildcard to get all dates?

If this is not possible, I think the user would be able to set an extremely low beginning date and extremely high ending date on the 2 date fields they are not filtering on but provide the actual date range for the field they do want to filter on. For example, for the Manufacturing and Install date prompt, they could enter 1/1/1970 to 5/31/2017 and for the Ship Date enter the specific date range they are looking for 2/1/2017 to 4/15/2017.

Occasionally the date fields will not be populated so this needs to be considered in the solution.

Thanks

RE: Wildcard (*) use for a date field?

First, the dates ARE related to each other, since they exist within the same record for a particular item, so if you select a date range for one date, the record might be excluded from consideration for other date range parameters. I can't imagine the scenario, but if you wanted the date selections to be truly independent I think you'd have to use separate subreports OR you could use a command which uses union statements to return the dataset three times, with a particular parameter limiting one of three sets of data.

If you are just aiming to make selection by one or more parameters optional, you can do something like the following. Also note that higher versions of CR allow for optional parameters, but I have never made use of them and am not sure how they work with date fields.

Try a selection formula like this which basically makes a particular date range optional:

(
(
minimum({?ship date})<> date(9999,09,09) and
(
isnull({Orders.Ship Date}) or
{Orders.Ship Date}={?ship date}
)
) or
minimum({?ship date})= date(9999,09,09)
) and
(
(
minimum({?order date})<>date(9999,09,09) and
(
isnull({Orders.Order Date}) or
{Orders.Order Date}={?order date}
)
) or
minimum({?order date})=date(9999,09,09)
) and
(
(
minimum({?required date})<>date(9999,09,09) and
(
isnull({Orders.Required Date}) or
{Orders.Required Date}={?required date}
)
) or
minimum({?required date})=date(9999,09,09)
)

The parameter prompts would have to instruct the user to enter 9999-09-09 (or the format specific to your version of CR) to select all dates for that parameter.

-LB

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!

Resources

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