Access & Crystal dates

[lous]

I have a field set up in Access 97 that is "Short Date," so that it looks like 8/22/03.

I have made a report using Crystal that needs to limit the records by a range of dates, but for some reason, I can't get it to pick up the correct data. I've tried lots of combinations and nothing works. It's either all or nothing.

Can anyone offer any insight as to what formula I should be using to get the correct information from Access 97?

Thanks!

 

[Madawc]

I don't know Access 97, but I'd reckon you need to convert your 'Short Date'into something Crystal can follow, and use that to select. Crystal 8.5 will allow formula fields in the selection statement.

Madawc Williams
East Anglia, Great Britain

 

[pgtek]

hi
in your report
format your date field
click on the field -> format -> custom
and pick the format you want it to be


cheers

pgtek

 

[mbarron]

What is the formula you are trying to use.

pgtek,
lous is having problems bringing back data, not with how the data is being displayed

Mike

 

[synapsevampire]

Please share what you tried.

If you right click the field in CR and select browse, what does it show as the data type and format?

As Mike stated, you may have to convert the fieldin some fashion, though I find it unlikely.

In the record selection formula, try something like:

{table.shortdatefield} >= currentdate-30

Does that work?

Are you using ODBC?

You can also build a Query in Access which alters the data type and then base your report on that.

-k

 

[lous]

The field data looks like this:
8/22/2003 12:00:00AM

I can get it to work using this selection formula:
{Data.Date}>= DateTime (2003, 07, 03,00, 00, 00) and
{Data.Date}<= DateTime (2003, 07, 03,00, 00, 00)

Now my problem is Crystal won't let a person actually pick their own dates. It just runs with whatever is in there. Any ideas how to set up a parameter for this?

 

[mbarron]

Go to Insert / Field object...

Create a new parameter. Set the type to DateTime. Choose whether you want it to be discrete, range, or both. If you choose range, when you run the report, just set both dates to the same thing for a single day.

For your seletction criterea use the formula:
{your.datetime.field}={?your_parameter}

Mike

 

[synapsevampire]

You don't have to set the date parameter to a datetime, you can make it a date type so that the users don't have to enter the time.

But make sure that what is being entered is being passed to the database (Database->Show SQL Query).

If it isn't, convert the parameter dates entered to datetimes in formulas, and reference the formulas in the record selection formula.

-k