Simple question, I want to prompt the user for a start date and an end

[JasSim]

Here is my query, I have created the parameters, but when I run it I get a message box that says they are not defined. Here is the query as it stands:
SELECT Orderlog.Proc, Orderlog.Order, Orderlog.OpenDate
FROM Orderlog
WHERE Orderlog.OpenDate Between {?Pm-@FirstD} and {?Pm-@SecondD}
ORDER BY Orderlog.Order;
Please let me know where I am going wrong on this. I am new to Crystal and have always used Access for reports. While the query looks similar I am unsure how to make Crystal prompt for input. Thanks for any help!

 

[s2001]

Hi,

Define the parameter fields in crystal reports. Click on Insert -> field Object. Field explorer will pop up. Right click on parameter field and select New to create parameter field. (crystal report 8.5)

Hope this helps. Murali Bala

 

[Nuffsaid]

Hi JasSim,

I'm no SQL expert but, I don't think you can include,
WHERE Orderlog.OpenDate Between {?Pm-@FirstD} and {?Pm-@SecondD} in your SQL statement. SQL doesn't understand Crystal parameters.

Make Orderlog.OpenDate a date range you know will cover all possibilities that your Crystal user might enter.

That way the server returns all possibilites and Crystal filters them out.

I'm sure there might be a better way (and your sure to hear about it here, great site!), as I said, I'm not a SQL expert.

Nuffsaid.

 

[Hm786]

Hi,

If you are using the query designer to create the query, then you need to create the parameters from clicking the edit-Parameter-new from menu. It gives a box where You need to fill the parameter type and prompting text. Then add the parameters in your sql statement. Unless you didn't create the parameter as above said method you will get the error.


Thanks,
Hamida

 

[JasSim]

S2001,
Since I did that in the report designer do I need to query for all records and have Crystal filter it for me? Also, it doesn't prompt me for the variables, should I put this in a formula or something? I am unsure where I would put the filtering part if I get it to prompt for the data. So currently I have the query returning all data and two parameter fields. It doesn't prompt, and if it did I don't see how it is supposed to know I want all data in between the two dates Thanks very much for your help.

 

[GaryWilsonCPA]

I wanted to add to an earlier post.
"Define the parameter fields in crystal reports. Click on Insert -> field Object. Field explorer will pop up. Right click on parameter field and select New to create parameter field. (crystal report 8.5)"

After you do this go to the select expert and select the field in question - next select equal to and pick from the drop down box the paramter you created. It should look lik this {?DATAENTRYDATES}

To further the process edit the selection criteria and remove the word equal and replace with "like"
Now when your parameter comes up a person can enter "*" to
override the selection with all records.

Thanks and may your adventure be fun, and the lord take a liking to ya.

 

[Ngolem]

Just the form of the parameter leads me to believe that this might be a subreport...is this true?...Maybe you have a linking problem if this is correct

 

[s2001]

hi JasSim

u don't need to filter in the query part ..crystal can handle it through the parameter field. If you want to make prompt for the date "Insert it in prompting text"..U can follow the instruction of "GaryWilsonCPA". He is correct.. Murali Bala

 

[JasSim]

I am at my witts end....
I know no one wants to hold my hand and walk me through this, but I think that is the best way to get this stinking report out of my hair.
Can anyone send me an example of querying a table, looking for data between two dates. It needs to prompt for the start and end date. Or, if you know of an example on the net point it to me.

I have tried working w/ the posts but the best results I have gotten are that it prompts but doesn't show the data in the range. Thanks!!

 

[Ngolem]

WAIT A MINUTE>>>>

I don't think you have described the problem to us properly:

Where is this query??
1. a query made in Crystal SQL Designer that you are
using as a data source?
2. composed by Crystal itself from a record selection
formula?
3. Designed by yourself and dropped into
"Show SQL Query Box"

You haven't told us

If it is
1. then there is an issue of how you linked the SQL
Designer Query to the report....try using set
location to relink it.

2. The complexity of this query is so simple...THIS
IS ALL THAT IS REQUIRED AND SETTING UP PROPER
Parameters

3. You can't do that....You can affect the Where
clause not not the rest...AND IT IS NOT NECESSARY
Given the simplicity of this query