Monthly report

[WhiteShore]

I'm kinda stuck of making this

Report1.RecordSelectionFormula = "{Sales.Date} In (" & Format(cboMonth.Text, "MMMM") & ")"

it wont work to display monthly report based on combobox (january, february, etc)

im using vb6 and ms access and CR as for report

do i miss anything?

[QVeen72]

Hi,

Use This Formula :

[code=vb]
Report1.RecordS electionFormula = _
& "UpperCase (MonthName (Month ({SALES.DATE})) ) ='" _
& UCase(Format(cb oMonth.Text, "MMMM") ) & "'"
[/CODE]

Regards
Veena

[WhiteShore]

it wont work because my Sales.Date format is dd/mm/yyyy
do i need to convert it first?
thanks...

[QVeen72]

it wont work because my Sales.Date format is dd/mm/yyyy
do i need to convert it first?
thanks...

No, I have already converted In my selection formula..
can you post the data as in the ComboBox....?

Regards
Veena

[WhiteShore]

they are 1 to 12
what i mean is 1 refers to January, and so on

[QVeen72]

Hi,

Then Keep it simple, try this :

[code=vb]
Report1.RecordS electionFormula = _
& "Month ({SALES.DATE}) =" & Val(cboMonth.Te xt)
[/code]

Regards
Veena

[WhiteShore]

Thanks Ms. Veena =)
Can i add year as well to that code?
Maybe like this?

.RecordSelectionFormula = "Month ({SALES.DATE}) =" & Val(cboMonth.Text) And "Year ({SALES.DATE}) =" & cboYear.Text

[QVeen72]

Thanks Ms. Veena =)
Can i add year as well to that code?
Maybe like this?

.RecordSelectionFormula = "Month ({SALES.DATE}) =" & Val(cboMonth.Text) And "Year ({SALES.DATE}) =" & cboYear.Text

Yes You can,
Check this :

.RecordSelectionFormula = "Month ({SALES.DATE}) =" & Val(cboMonth.Text)  & " And Year ({SALES.DATE}) =" & Val(cboYear.Text)

Regards
Veena

[WhiteShore]

I made it like this:

[CODE=vb]
Dim m_Report As CRAXDDRT.report
Dim m_Application As New CRAXDDRT.Applic ation

Set m_Report = Nothing
Set m_Report = m_Application.O penReport(App.P ath + "Report.rpt ", 1)

m_Report.Record SelectionFormul a = "Month ({SALES.DATE}) =" & Val(cboMonth.Te xt) & " And Year ({SALES.DATE}) =" & Val(cboYear.Tex t)

'it doesnt work when i put this 2 parameters
m_Report.Parame terFields.Item( 1).AddCurrentVa lue Format(Val(cboM onth.Text), "MMMM")
m_Report.Parame terFields.Item( 2).AddCurrentVa lue cboYear.Text


With CRViewer1
.EnableExportBu tton = True
.EnableCloseBut ton = True
.ReportSource = m_Report
.ViewReport
End With
[/CODE]

For both parameters, i put them as string.
did i make any mistake?

Thanks so much

[QVeen72]

Hi,

Comment these lines and check :

m_Report.Parame terFields.Item( 1).AddCurrentVa lue Format(Val(cboM onth.Text), "MMMM")
m_Report.Parame terFields.Item( 2).AddCurrentVa lue cboYear.Text


Regards
Veena

[WhiteShore]

Hi,

Comment these lines and check :

m_Report.Parame terFields.Item( 1).AddCurrentVa lue Format(Val(cboM onth.Text), "MMMM")
m_Report.Parame terFields.Item( 2).AddCurrentVa lue cboYear.Text


Regards
Veena

Yup, that's where i put the comments on.
because of that parameters, the report cant show the currently picked month =(

[WhiteShore]

I need to pass the parameters to CR to show the picked month and year to the report.
But because of that code, the report doesnt show correctly.

[sierra7]

Sorry to butt in but a number of things caught my eye here;

Why are you using curly braces ({) round {SALES.DATE} not square brackets [SALES.DATE] ? If it's because CR (Crystal Reports?) requires it, then my apologies because I don't do CR.

DATE is a reserved word and I would not use it as a field name because strange thing happen. . .

"MMMM" will return 'January' in US/UK keyboard/regional settings but 'janv' if in French. As I detect that English may not be your first language, are your regional settings causing the problem?

Veena has moved you on by using the Month() and Year() functions which should return numbers, so the above should not now be causing a problem but when I am faced with problems like this I find it useful to add a temporary command button to pop a message box to display the criteria (I've never mastered the Debug Window!) e.g. something like

MsgBox Month ({SALES.DATE}) & "  -  "  & Val(cboMonth.Text)
' and 
MsgBox  Year ({SALES.DATE})  & "  -  " & Val(cboYear.Text)

Although I don't think that will work because of the curly braces and you may have to do a DLookup() to find a particular SALES.DATE to display

MsgBox Month (DLookUp("[DATE]", "SALES", "A Criteria ") & "  -  "  & Val(cboMonth.Text)

If you don't put a criteria in the DLookUp I believe that you just return data from the first record, which might be OK just to check the formatting of the data.

Hope this has been some help.

S7

[QVeen72]

Hi Sierra,

Yes, Curly Brackets are for CR..

Hi WhiteShore,

You want to pass the parameters, Try this:
Delete the parameters, Add a New Formula Field in CR Designer and place it wherever required. IN Formula editor give:
'Monthly Sales Report'
Save the Report.
Count the Index of the Formula, Pass this Formula from Front End:

m_Report.Formul aFields(i).Text = "'Monthly Sales Report For : " & cboMonth.Text & "/" & cboYear.Text & "'"

If you Dont have any other formulas, then in above syntax, use 1 instead of i

Regards
Veena

[WhiteShore]

m_Report.Formul aFields(i).Text = "'Monthly Sales Report For : " & cboMonth.Text & "/" & cboYear.Text & "'"

It works!!!
Unbelieveable!!
You're really great, Ms. Veena =))
I was stucked with the parameters and you came up with formulas.. I never think of that way before...
Thank you so much for your help........... ...
=))