search for date help

[danielw]

Using crstal version 8.5. Have date field in database which lists dates as 7-02-02 for july 2, 2002. How can I search for dates in this field.

 

[dgillz]

Are you asking how do you get a report that returns only records with that date? If so write a record selection formula:

{Datefield} = Date(2002,7,2) Software Training and Support for Macola, Crystal Reports and Goldmine
714-348-0964
dgilsdorf@mchsi.com

 

[synapsevampire]

I'm not sure if you're suggesting that the field isn't a proper date type.

dgillz gave you the proper way to hardcode a date() to limit the rows to.

Perhaps you want a parameter?

This provides a prompt for the date, then you add a record selection criteria something like:

{MyDateField} = {?MyDateParm}

or you can define it as a range, and place something like:

{MyDateField} in {?MyDateParm}

in the record selection criteria.

-k

http://www.informeddatadecisions.com

kai@informeddatadecisions.com

 

[danielw]

I would like to be able to enter the date to search for records for that day. I tried {mydatefield}={?mydateparm}.
When prompted for date I enter 7-01-02 or any date in that format & it returns no records when there is records it should show. I used string value in parameter edit. I can set a default value from the list of dates in parameter setup & it works ok but if I change the date at the prompt it returns no records. Hope this makes some since. I think my problem maybe trying to search for dates as 00-00-00 in that format.

 

[synapsevampire]

{mydatefield} was just a place holder for your date field, replace it with your database date field (you'll see it in the list of fields).

And the parameter type should be a date if it's a date in the database, if it isn't (it's a string/char/number/etc.), you'll need to convert the date field to a real date using a formula, and then use the formula in the record selection criteria.

A string formatted date won't sort correctly.

And remember that you can use the RANGE option on the parameter which will allow users to enter 2 dates to return records between.

-k

http://www.informeddatadecisions.com

kai@informeddatadecisions.com

 

[Naith]

When you change your date in the parameter selection, you're changing the format. If you have default values in your parameter list like MM/DD/YY, you can change the value, but don't change the format.

Naith

 

[Ngolem]

first of all is your "date field" a true field of a type date....or is it just a string field made to look like a date.

You have to match parameter type to field type.

you do have a problem with standardization though in your string format since you show "7" as the day and not "07"
it would have been nice to have the day-month-year in a string format of dd-mm-yy and you could use an input mask in the parameter to guide your user (or better would be to have dd-mm-yyyy but if your database uses a 2 digit year there is not much you can do, and this is what leads me to believe the field is a string).

if it is true that all single digit days are "x" and not "0x" then you will have to instruct your users to use that format (ie. d-mm-yy or dd-mm-yy) in the parameter description

then it is a simple record selection of

{table.stringDate} = {?inputDate}

Jim Broadbent