Multiple Crystal Reports recordselection formula in VB help

[msay]

I am using VB 6. I want my end user to select a date from a calendar and a name from a combobox and create a Crystal report. The VB code below does not work. I know it's because I do not know the proper syntax for this formula. Basically, I want the formula to select a name and a date from the "reservations" table.
Any help would be welcomed. Thanks!


B = Combo2.Text

Report.RecordSelectionFormula = "{Reservations.instructor}_ =" & Chr(34) & B & Chr(34) and {Reservations.rsdate} ="+ "'" + Calendar1.SelectedDays(0).Date + "'"

 

[kenhamady]

You can get the correct CR syntax by using the select expert in the CR designer. Select a name and a date as a sample and then click the "show formula" button to see the syntax CR is using. Then make sure that what you are passing matches exactly, including all quotes and other punctuation. Ken Hamady, On-site/Phone Crystal Reports Training/Consulting
Quick Reference Guide to using Crystal in VB

http://www.kenhamady.com/

 

[msay]

Creating CR formulas is the easy part. But when you use a CR formula in a VB application, your VB code will have to be modified. For example if you pass a VB string to CR it will have to have embeded quotes in it for CR to recognize it. When you use the Select Expert it only creates a CR formula which can't directly be used in a VB app. I am having trouble with where the punctuation belongs when I code in VB. Also, I haven't been successful using the AND operator in these formulas either. I need specific help. Thanks!

 

[kenhamady]

Your code assembles a string that you are trying to pass to CR.

If you can post the current (incorrect) result of your code (ie the string that it is currently building), as opposed to just the code that generates the string, I think I can tell you the problem with your syntax and how to change the code to fix it.

It will be quicker if you also post the formula that works in the report designer, which is the string you are trying to assemble. Ken Hamady, On-site/Phone Crystal Reports Training/Consulting
Quick Reference Guide to using Crystal in VB

http://www.kenhamady.com/

 

[msay]

Code being used:
A = Calendar1.SelectedDays(0).Date
Report.RecordSelectionFormula = "{Reservations.rsdate} = # " & Format(A, "yyyy,mm,dd&quot & " # And {Reservations.instructor} =" & Chr(34) & B & Chr(34)

Result of Code:
Report.RecordSelectionFormula = "{Reservations.rsdate} = # 2001,08,19 # And {Reservations.instructor} = "JOE PILOT"

 

[kenhamady]

You aren't far off, but the date syntax you are passing isn't going to fly when it gets to CR.

In any version of CR you can use: Date(2001,8,19)
In CR Version 8 you could use: #8/19/2001#

This assumes that CR reads the field as a date, and not a dateTime or DateTimeString, etc.

To know what the syntax should be we need an example of the actual formula that works in the CR designer. Ken Hamady, On-site/Phone Crystal Reports Training/Consulting
Quick Reference Guide to using Crystal in VB

http://www.kenhamady.com/

 

[msay]

Thank you for being patient! I think I'm almost there. This formula works in the Cr Designer. Now, to translate that to VB code...
{reservations.rsdate} = Date (2001,08,20) And {reservations.instructor} = "PILOT JOE"

I want to use a variable for the "Date (2001,08,20)" function. That would be:
RsDate = Calendar1.SelectedDays(0).Date

Variable for "reservations.instructor" will be:
x = Combo2.text

 

[kenhamady]

You can't use a VB date value directly. You have to use the VB date value to build a string that matches one of the CR patterns, put that into the formula and pass the formula the way CR wants it.

1) You could capture your VB date, pull out the year, month, and day values as three numerics, and reassemble them as a string with the word DATE, the parentheses and commas.

2) If you can capture it with slashes you can put it between two pound signs. Ken Hamady, On-site/Phone Crystal Reports Training/Consulting
Quick Reference Guide to using Crystal in VB

http://www.kenhamady.com/

 

[msay]

I believe the following is what you mean. The result of my "recordselection" is:
{reservations.rsdate}='Date(2001,8,20)' and {reservations.instructor}="Pilot Joe"
With this I get the error: "A date is required here."
In the report designer under report options, I have the convert date-time field to: Date

Dim Report As New CrystalReport1, A As String, rsday As Integer, rsmonth As Integer, rsyear As Integer

A = Calendar1.SelectedDays(0).Date
B = Combo2.Text

rsday = Format(A, "dd&quot
rsmonth = Format(A, "mm&quot
rsyear = Format(A, "yyyy&quot
DATESTART = "Date" & "(" & rsyear & "," & rsmonth & "," & rsday & &quot"

Report.RecordSelectionFormula = "{Reservations.rsdate} =" + "'" + DATESTART + "' and {Reservations.instructor} =" & Chr(34) & B & Chr(34)

 

[msay]

Well, I'm very close. The following code works as separate formulas.

Report.RecordSelectionFormula = "{Reservations.rsdate} =" & DATESTART (returns: "{Reservations.rsdate} =Date(2001,8,20)"

Report.RecordSelectionFormula = "{Reservations.instructor}= " & Chr(34) & B & Chr(34) (returns: "{Reservations.instructor}= "Pilot Joe"

If I run each of these formulas separately they pull up the correct reports. When I combine the two formulas with the word "And" so as to try to create a report with a specific date and name I get an error. My problem is punctuation syntax. If I write a formula like this:
Report.RecordSelectionFormula = "{Reservations.rsdate} =" & DATESTART And "{Reservations.instructor}= " & Chr(34) & B & Chr(34)
This makes my date a string and I get a error.

 

[kenhamady]

Sticking the word AND between two strings is not the same as concatenating the word AND into the string:

Try something like this:

Report.RecordSelectionFormula = "{Reservations.rsdate} =" & DATESTART &
" And {Reservations.instructor}= " & Chr(34) & B & Chr(34) Ken Hamady, On-site/Phone Crystal Reports Training/Consulting
Quick Reference Guide to using Crystal in VB

http://www.kenhamady.com/

 

[msay]

That worked! Thank you for all your help!