Error in selection formular for date field

[prettyangel]

Hi All,

I'm using crystal reports version 7.0
I passed a selection formula to crystal reports from visual basic 6 but it's telling me "error in formula record selection"

The vb code is below
-----------------------------------------------------------
Report.SelectionFormula = "{Tablename.date} = Date ('" & Me.combobox.Value & "')"

-----------------------------------------------------------

where did I go wrong?

 

[IanWaterman]

'" & Me.combobox.Value & "' is a string and not a date.

You will need to pass a parameter from Visual Basic to the Report. You will need to create a date parameter in Crystal and then get your application to populate it with a date. Alternatively you can use a String Parameter, and your application will have to pass a date time string, in the format

2000/01/13 11:30:15

In select statement it will then look like

"{Tablename.date} = dtstoDate (String Parameter)"

Ian

 

[prettyangel]

Thanks Ian for your response I must apologize for giving half information.

Actually, the combobox is being populated by a table in my ms sql 7 database and the field data type is datetime.

So I want users to specify report to be viewed based on date selected in the combo box.

Thanks for your response.

 

[IanWaterman]

I am not a VB programmer so I do not know how you pass the data from your combo box. All I know about VB integration is that you will need to create a Crystal Parameter (defined as date time) and then somehow code the inteface so that when your user selects a date time from your combo box it is then passed the Crystal Parameter.

select statement it will then look like

{Tablename.date} = Date (DateTime Parameter)

Ian

 

[MisterMo]

try

Report.SelectionFormula = "{Tablename.date} = Date ('" & format(Me.combobox.Value,"DD/MM/YYYY") & "')"


Mo

 

[prettyangel]

Thanks MisterMo for your response.

I tried your suggestion and I got this error message

------------------------------------------------------------
Error in formula <Record_selection>.
'
'
The remaining text does not appear to be part of the formula.
------------------------------------------------------------

I'm at a loss for what to do.

 

[MisterMo]

remove ' from both sides

Report.SelectionFormula = "{Tablename.date} = Date (" & format(Me.combobox.Value,"DD/MM/YYYY") & ")"


Mo

 

[prettyangel]

Thanks MisterMo,

I removed the single quotes and it still gave me the same error.

when I put the same selection formula directly on crystal reports design view I got the desired result but I noticed that it formatted the date as "YYYY, MM, DD" BUT EVEN WHEN I TRIED this in VB I still got the same error.

 

[MisterMo]

try this

dim iDate as date

iDate = format(Me.combobox.Value,"DD/MM/YYYY")

go in debug and see what is the value of iDate



Mo

 

[prettyangel]

MisterMo this is what I saw in debug view
The date below is displayed exactly as it is in the database. "22-Sep-05"

meanwhile crystal reports will take Date(2005, 09, 05)

directly from design view. But my problem now is how to pass it from vb end.

Formatting it to look like Date(2005, 09, 05) is not a problem but i'm still getting the same error message.

 

[MisterMo]

OK then set it as string and then pass it to crystal

dim iDate as date

iDate = format(Me.combobox.Value,"YYYY, MM, DD")

Report.SelectionFormula = "{Tablename.date} = Date (" & iDate & ")"

Mo

 

[prettyangel]

MisterMo,

I've tried it to no avail, I'm stuck.

I keep getting the same error message.

Or could the error be due to the fact that the crystal report control on my vb is version 8 while the report file was designed with version 7?

 

[MisterMo]

this is an old line of code that use to work for me with CR7 VB6 and oracle
"{TABLE.FIELD_DATE_CREATED} >= Date (" & Format(MyForm.pkrDate(0).value, "yyyy,mm,dd") & ")"


have you got any report at all that works in a VB program?

are you using Global.bas supplyed by crystal?

is you report integrated with your project or disconnected?





Mo

 

[prettyangel]

Thanks MisterMo for your response.

Yes all my reports are integrated with VB and yes all my reports are working.

In fact I've been able to view reports based on other sort criterias I'm only having problems with Date Criteria.

 

[MisterMo]

OK then try and create the formula in Crystal using a fixed date make it work and then copy it onto your VB code.



the code should look like this

"{Tablename.date} = Date(2005/12/22)"

see if that still fail

Mo

 

[prettyangel]

Thanks MisterMo,

It failed. Crystal reports said "Not enough arguments have been given to this function"

{Tablename.date} = Date(2005/12/22)

but when it is in this format it displays result correctly.
{Tablename.date} = Date(2005, 12, 22)

 

[MisterMo]

in that case

dim myStr as string

myStr = "{Tablename.date} = date(" & format(Me.combobox.Value,"yyyy,mm,dd") & ")"


Report.SelectionFormula = myStr

Mo

 

[prettyangel]

MisterMo,

Thanks for your response but did you try replicating this on your own system? Because I'm still getting the same error message.

 

[MisterMo]

unfortunately i don't work with that system anymore

but you say that

Report.SelectionFormula = "{Tablename.date} = Date(2005, 12, 22)
"

then by logic

dim myStr as string

myStr = "{Tablename.date} = date(" & format(Me.combobox.Value,"yyyy,mm,dd") & ")"


Report.SelectionFormula = myStr

can you read the value of myStr in debug mode?

if so what is the value?


Mo

 

[prettyangel]

MisterMo,

It's still the same error I'm getting, though the value is correct.