Crystal Report Writer. Using SQL requiring a parameter

Here's the rest of the code to print a Crystal Report (of course, you need to place a Crystal Report control on your form):

CrystalReport1.ReportFileName = "C:\wherever\page01.rpt"
CrystalReport1.Action = 1

There are many other things you can set in VB for a Crystal report, but this is a way to simply print a normal report to the default printer. Let me know if you need more.

The report writer does not list the SQL queries that require a
paramer during design time. How can get I get it into the design
area in the first place.
Steps I took.

Start Crystal report writer.   select the white key under "file"
"Create New Report" window opens.   select "standard" . New window opens.  click "Data file", then "choose database" opens.
I select my Access data base where the SQL query I want to use in my report is designed and stored.
All other tables and SQL queries that do not require parameters are listed but the SQL queries that require a parameter are not listed.
How can I get it listed in order to use it in my report design.
I tried the other key "SQL/ODBC" now every thing is listed but
as a beginner I find more errors that I can't underestand.
I will be grateful if you take a moment and try to explain to me
as a beginner. I'll raise the points.
thanks

I'll have to look into it further. If you select the "Data file", Crystal assumes you are using the normal jet databases. If you select the "SQL/ODBC" button, it assumes you are using ODBC. It shouldn't make a difference to what fields show, but you should probably use the "Data File" one.

However, there are easier ways to place queries in your Crystal report. What I showed in my first answer was an example of that. With that, you place all the fields you want displayed on your report. You don't have to worry about the query yet. In your example, you'd place all the fields from the "customer" table where you want them on the report (not the query fields, just the ones from the table). Now, at run time, when you set the (CrystalReport1.SelectionFormula = "{customer.customer_name} = " &  '" & anyname & "'"), it will only show records that have a customer_name equal to any variable you want in VB (i.e. "anyname").

If you have to display your SQLquery in the report...you might see if anyone else has a solution. In my experience, I've found this method has always been easier when I need to use a user-entered variable. I can walk you through it if it sounds like something you can use.

Your last answer is correct an it does most of what I wanted.
I raised the points to 100 to interest you this topic further.
I plan also to give you an "A" because of you correct answer and
your prompt and clear explaination. However, I would like you to look at this situation. I have the following query

SELECT *
FROM customer INNER JOIN (account INNER JOIN hasAcct ON account.acctnum = hasAcct.accntnum) ON customer.ID = hasAcct.ID
where account.balance > maxBal AND
             account.lastupdate < twomonthsAgo;


The tables are customer=>hasAcct=>account
parameters are "maxBal" and "twomonthsAgo"
Do you have an idea if I can use the information produced by the above query in Crystal Report and if that is posible How?

It can't be done is also a good answer.

Thanks

That's a little more difficult, but I think you can do it. Crystal Reports uses something called "linking" to join 2 or more tables together. This should do what you are looking for. Try this:

In Crystal, click on the "Database" menu choice, and then "Visual Linking Expert". There should be a button called "Tables". Click on that and from there, you can select which tables you want to join. After you finish that, it will display your tables with arrows linking up to your key values. Hopefully, the fields you are joining on are index values. Crystal only allows linking on key values. You can play around on this screen to get your tables joined as you need. You'll notice that you can move, delete, and/or add arrows as needed. Just make sure that arrows are pointing from {account.acctnum} to {hasAcct.accntnum} and from {customer.ID} to {hasAcct.ID}. You probably won't want any more arrows linking up other fields. When you are all finished with that, the report should already be joined based on those values, which leaves only one more thing. Save your report and go to VB. In your VB app, you now need to send the parameters to the report through the control.

CrystalReport1.SelectionFormula = {account.balance} > " & "'" & CStr(maxbal) & "'" & " and {account.lastupdate} < " & "'" & CStr(twomonthsAgo) & "'"

Whew. Get all that? I'm throwing this out by memory, so if you need more help, let me know. That should at least get you in the right direction.

Excellect!
Thanks BergJC, Can you point me anywhere where I can get a reading material about Crystal Report. Like Book, Web tutorial
. It seems I can't find anything.
Hey, more points for you.

That's an excellent question. I wish I had a great answer for you, but Crystal Reports isn't well documented anywhere. Especially in the manual that comes with it, as I'm sure you've found out. The only useful site I know of is the Crystal Reports User Group page (http:\\www.crystaluser.com). There a chat board/BBS feature there that can connect you to other users. There might be a few other things you can check out too.