Wildcard in Date Prompts

Thanks,
  But then it must be able to switch from one condition to the other, right?
 your suggestion will bring everything regardless of what we enter in the prompt, right?

I think Jim was suggesting to enter those values when prompted.  Utilizes what you have in place already but sends a range that should encompass all your data.

However, you can try this as an alternative as you would need to check for the * first, otherwise you will try to convert an incorrect date and get errors.

( '*' IN (@Prompt('1 Kit Version Start Date','A',,MONO,FREE)  AND '*' IN @Prompt('2 Kit Version End Date ','A',,Mono,FREE)) OR (DATE(@Select(Kit\Kit Creation Date)) BETWEEN  DATE(@Prompt('1 Kit Version Start Date','A',,MONO,FREE))  AND DATE(@Prompt('2 Kit Version End Date ','A',,Mono,FREE))) 

                                              

1:

And you will most likely want an OR in between the two checks for '*' as if you get only one it will fail the first condition and try to convert one to a date and you will end up with failure again.

Hi,

 '*' doesn't work because of the datatype mismatch.
 I tried a default date like '1/1/1900' and that works.But again, as you said, this value holds good only when i enter it in both the prompts.If i enter 1/1/1900 for the end date and 8/24/2009 for the start date, doesn't work.Can you suggest any workaround?

Thanks!

(DATE(@Select(Kit\Kit Creation Date)) BETWEEN  DATE(@Prompt('A- Kit Creation Date Start','A',,MONO,FREE))  AND DATE(@Prompt('B- Kit Creation Date End','A',,MONO,FREE))) OR ( DATE('1/1/1900') BETWEEN DATE(@Prompt('A- Kit Creation Date Start','A',,MONO,FREE))  AND DATE(@Prompt('B- Kit Creation Date End','A',,MONO,FREE)))

                                              

1:

The Between clause, in most flavors of SQL, expects the earliest date first as the first one.

This is a common restriction.

Yes, but my intention is to get all the values if '1/1/1900' is entered. How can i achieve it?

Just test the first one then.  And whenever you enter that value in the first that means you want all dates.  Again you have to put that as the first check.

(( DATE('1/1/1900') = DATE(@Prompt('A- Kit Creation Date Start','A',,MONO,FREE)) OR (DATE(@Select(Kit\Kit Creation Date)) BETWEEN  DATE(@Prompt('A- Kit Creation Date Start','A',,MONO,FREE)) AND DATE(@Prompt('B- Kit Creation Date End','A',,MONO,FREE))))

                                              

1:

Type-o:

(( DATE('1/1/1900') = DATE(@Prompt('A- Kit Creation Date Start','A',,MONO,FREE))) OR 
(DATE(@Select(Kit\Kit Creation Date)) 
BETWEEN  DATE(@Prompt('A- Kit Creation Date Start','A',,MONO,FREE)) 
AND DATE(@Prompt('B- Kit Creation Date End','A',,MONO,FREE))))

                                              

1:
2:
3:
4:

Thanks, tried that. But i have Kit Creation Date End also.If i try something like this, it gets back all the data regardless of what i type in. Do you see the problem?

(( DATE('1/1/1900') = DATE(@Prompt('A- Kit Creation Date Start','A',,MONO,FREE))) OR
(( DATE('1/1/1900') = DATE(@Prompt('B- Kit Creation Date End','A',,MONO,FREE))) OR
(DATE(@Select(Kit\Kit Creation Date))
BETWEEN  DATE(@Prompt('A- Kit Creation Date Start','A',,MONO,FREE))
AND DATE(@Prompt('B- Kit Creation Date End','A',,MONO,FREE))))

Just use one to flag or put braces around first two logical.

Won't work, since all are 'OR' conditions. Correct me if i'm wrong.

I just skimmed through this question, but initially...my suggestion would be to create a SEPARATE TABLE in the Database that contains the DATES that you need for the prompt...and use the DB TABLE to populate the PROMPT.

Hope it makes sense....

M

Thanks! But, can you please explain how that would solve my problem?
   Let me mention to you, my requirement here.
  I have a Date Range "Start Date' And "End Date". If the user enters '1/1/1900' in the start date and '8/24/2009' (just an example) in the end date, it should get all the dates upto '8/24/2009'.
  If i enter '8/24/2009' as start date and '1/1/1900' as end date, it should start from '8/24/2009 and get all the dates in the database that are greater than 8/24/2009.

   1/1/1900 is my wildcard prompt value here.

SO,..... what will the user be inputting a MAJORITY of the time.

Will they be entering a literal "REAL" START DATE...or will they be doing the silly '1/1/1900' in either the START or END date.

Sorry was on my mobile, was harder to type.

(( DATE('1/1/1900') = DATE(@Prompt('A- Kit Creation Date Start','A',,MONO,FREE))) OR
(DATE(@Select(Kit\Kit Creation Date))
BETWEEN  DATE(@Prompt('A- Kit Creation Date Start','A',,MONO,FREE))
AND DATE(@Prompt('B- Kit Creation Date End','A',,MONO,FREE))))

Should work as you are just using to wildcard your data.  Pick a date that is never a valid start date, so when you see '1/1/1900', it doesn't matter what is entered for the second prompt as your logical expression will evaluate as true without ever hitting the date conversion code which is why that has to be first in the list of logical conditions.

if both fields need to be set, then you are basically doing what Jim suggested earlier.  So again I support his thoughts.

What's wrong with just this:
DATE(@Select(Kit\Kit Creation Date)) BETWEEN  DATE(@Prompt('1 Kit Version Start Date','A',,MONO,FREE))  AND DATE(@Prompt('2 Kit Version End Date ','A',,Mono,FREE))

Then just pass in '1/1/1900' and '12/31/2999' into the dates or like Jim showed '5/5/55' and '2/2/22'.

CRXIuser2005: I see you are here now, so I will leave this to you. :)

Why not have the prompt to DEFAULT to the following:

STARTDATE DEFAULT: 01/01/1900

ENDDATE DEFAULT: 12/31/2099 (or whatever calendar end date the system allow)

And have the user change ONLY the dates needed.....????

How can we give default dates?  don't know how to do it.

Thanks for your help!

I like CRXI's idea for a simple solution. If your users do not like this, do you have the option to add additional parameters? It might clarify it for your users.
First Parameter could contain two values: "Return All Data and Ignore Date Range Entered Below" and "Use Date Range Entered Below".

You can have a conditional where clause.

If @Parameter = 1 then Date > 1/1/1900
Else Date between @BeginDate and @EndDate

This will work as long as @BeginDate and @EndDate both have default values (they can be any value, this is just so the user isn't prompted at run time).

I have done this a lot for reports where we will try to make it easier for users to pull standard (MTD, WTD, YTD) but then also give them the option to choose the date range.

Good luck.

Default Dates are set in your parameter settings.

Right click to Edit Parameter, and there will be a field for "Default Value". This looks a little different in each version of Crystal, but it will be in there somewhere.

Thanks LinInDenver! I'm working on BOXI R2.But i'll try and see if your idea works here.

The tests you posted in message 25190089 seem OK logically.  1/1/1900 entered for either the start or end date should give you every date, which may not be what you want, but I don't see any reason why you would get "all the data regardless of what i type in".  Actual dates, like 08/20/09 to 08/25/09, should work.

 If I misunderstood and the problem was actually that 1/1/1900 for either the start or end date was giving you every date, and you wanted 1/1/1900 for the start date to mean no start date and 1/1/1900 for the end date to mean no end date, you should be able to change the tests to handle that.

 Are those your only tests, or are there others (other parameters or other fields that you're checking)?  If there are other tests, then there could be problems if the tests are not combined properly, but, offhand, I'd expect a problem like that to exclude records, not include extra records.

 James

Thanks James! Upon rethinking about the prompts, i settled down with the idea that Jimpen gave in the first post,which is typing 1/1/1900 as start date and 12/31/2050 as enddate. This works fine but, has a problem too.There are no date entries on some rows,meaning they are all blanks.
 For this situation ,i couldn't bring back those rows. Can anyone suggest a workaround for this?

Thanks everyone,for all the help!

That's a nice Idea!! Thanks CRXIuser2005! Let me try that idea.I have a bunch of dates that need to be changed.
 Will keep you posted.

Thanks!

That's a great idea again! Thanks much! I'm having it implemented.