[RESOLVED] A Question About Crystal Reports

[xperator]

Hi guys,
I am making a report using CR for the first time. Everything went smooth till now.
There is something i want to do but don't know how. First thing: In my table, there is a field that return numbers from 1 to 6 . Which one suppose to mean a letter type. I can't show the numbers in the report. I have to write a code for it which shows which number means what.
I figure out that i can use this simple code to make changes to rows:

 Dim row As DataRow
        For Each row In DaftarDataSet.Tables("tbldaftar").Rows
            row("FldSdate") = PersianDate(row("FldSDate"))
        Next row

This code works for changing values if type of the variable on the both side are equal. For example if i want to change the field I have mentioned above, It returns the error 'Couldn't Store <blabla> .... expected type is int32'.
So I thought I have to use a special field, unbound field or something which i am not familiar with any or how to give them values.

How should I solve this ?

[controlguy]

In Crystal why not just create a formula in the detail section of the report. I created this one. Make sure you use "Basic" instead of Crystal for the formula type:

Select Case {Table.Field}
   Case  1
        formula = "A"
    Case  2
        formula = "B"
    Case  3
        formula = "C"
    Case  4
        formula = "D"
    Case  5
        formula = "E"
    Case  6
        formula = "F"
   Case Else
      formula = "Out Of Bounds"
End Select

This will create a string based on the field being a certain number.

HTH

[MonkOFox]

You could also do a case statement in your sql code.

Justin

[xperator]

In Crystal why not just create a formula in the detail section of the report. I created this one. Make sure you use "Basic" instead of Crystal for the formula type:

Select Case {Table.Field}
   Case  1
        formula = "A"
    Case  2
        formula = "B"
    Case  3
        formula = "C"
    Case  4
        formula = "D"
    Case  5
        formula = "E"
    Case  6
        formula = "F"
   Case Else
      formula = "Out Of Bounds"
End Select

This will create a string based on the field being a certain number.

HTH

Actually the letter types are stored in another in the database. So i don't know which number indicates which one. But I have already saved them in an array in the main form. Is it possible to use that ?
Or maybe a way to do it using codes in the report form load event?

objRpt.DataDefinition.FormulaFields("LetterType1").Text = "'" & frmmain.LetterTypesList(, 1) & "'"

This is incomplete and I don't how to use the database field as the first argument in the array.

[MonkOFox]

Join that table in your query then. Select the numeric value as "LetterNumericValue" and the actual letter as "LetterValue".

select twn.number as "LetterNumericValue", twl.letter as "LetterValue"
from tablewithnumber as twn join tablewithletters as twl on twn.number = twl.number

Then you can use that in your vb.code.

me.combobox1.DataSource = mydataSource
me.combobox1.displaymember = "LetterNumericValue"
me.combobox1.valuemember = "LetterValue"

Justin

[xperator]

Then you can use that in your vb.code.

me.combobox1.DataSource = mydataSource
me.combobox1.displaymember = "LetterNumericValue"
me.combobox1.valuemember = "LetterValue"

Justin

How should i pass values to crystal report textbox field ? the code you write is for normal controls on forms. but as for crystal reports they are database fields. Can you explain please?

EDIT : Sorry man i didn't get what you mean. your sql query is what should i try. i am trying to work on that now.

[MonkOFox]

Can't you bind the report to a datasource? Then set all the crystal report fields data members (field in the source to bind to)?

Justin

[xperator]

LOL Sorry man but I can't write down the sql query.I don't know how sql join works.
Can you please help me ?
This is the original query i used to extract report data:

Dim strSQL As String = "SELECT * FROM tbldaftar WHERE Fldno_post = '" & frmmain.txtPPostCode.Text & "'"

I will give you the name details:

- main table : tbldaftar
letter number field : FblPayvast

- table with letter values : TblPayvast
letter number field : FldNO1
letter values field : FldPayvast

I will really be thankful to you

[MonkOFox]

Select tbldafter.*, TblPayvast.FldPayvast
From tbldafter join TblPayvast on tbldafter.FblPayvast = TblPayvast.FldNO1

That query will return rows containing all of tbldafter's fields and the letter field from TblPayvast that corresponds with tbldafter's FblPayvast field.

Justin

[xperator]

Select tbldafter.*, TblPayvast.FldPayvast
From tbldafter join TblPayvast on tbldafter.FblPayvast = TblPayvast.FldNO1

That query will return rows containing all of tbldafter's fields and the letter field from TblPayvast that corresponds with tbldafter's FblPayvast field.

Justin

What about the WHERE clause. I need that because the report has to get all records in tbldafter that the post num match textbox

Thank you man for keeping reply to me.

EDIT : I tried your code but it gives 'Syntax error in FROM clause'

EDIT 2 : I run this code but no luck it returns error : Dim strSQL As String = "SELECT tbldaftar.*, TblPayvast.FldPayvast FROM tbldaftar JOIN TblPayvast ON tbldaftar.FldPayvast = TblPayvast.FldNO1"

EDIT 3 : It is working now, I just added INNER before JOIN. THANKS man I don't know how can I return the favor. You really helped me

[MonkOFox]

Just add a where clause to the end of that query.

Select tbldafter.*, TblPayvast.FldPayvast
From tbldafter join TblPayvast on tbldafter.FblPayvast = TblPayvast.FldNO1
Where tblafter.postnummatch = whatevervalueisIntheTextbox

Are you calling stored procedures? Or are you creating the sql string in code?

No problem : ). Glad it's kind of working for you lol.

Justin

[xperator]

Just add a where clause to the end of that query.

Select tbldafter.*, TblPayvast.FldPayvast
From tbldafter join TblPayvast on tbldafter.FblPayvast = TblPayvast.FldNO1
Where tblafter.postnummatch = whatevervalueisIntheTextbox

Are you calling stored procedures? Or are you creating the sql string in code?

No problem : ). Glad it's kind of working for you lol.

Justin

It's in code. And for Where clause I already found out. Thank you.

Crystal Reports seems to be very tough for people. Usually when I start a new thread, my question get answered and solved in a few hours but this one took 2 days. I asked this on another forum but they never did any reply