By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
434,779 Members | 1,291 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 434,779 IT Pros & Developers. It's quick & easy.

Monthly report

P: 44
I'm kinda stuck of making this

Expand|Select|Wrap|Line Numbers
  1. 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?
Jan 12 '08 #1
Share this Question
Share on Google+
15 Replies


QVeen72
Expert 100+
P: 1,445
Hi,

Use This Formula :

Expand|Select|Wrap|Line Numbers
  1. Report1.RecordSelectionFormula =  _
  2. & "UpperCase (MonthName (Month ({SALES.DATE}))) ='" _
  3. & UCase(Format(cboMonth.Text, "MMMM") ) & "'"
  4.  
Regards
Veena
Jan 12 '08 #2

P: 44
it wont work because my Sales.Date format is dd/mm/yyyy
do i need to convert it first?
thanks...
Jan 12 '08 #3

QVeen72
Expert 100+
P: 1,445
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
Jan 12 '08 #4

P: 44
they are 1 to 12
what i mean is 1 refers to January, and so on
Jan 12 '08 #5

QVeen72
Expert 100+
P: 1,445
Hi,

Then Keep it simple, try this :

Expand|Select|Wrap|Line Numbers
  1. Report1.RecordSelectionFormula =  _
  2. & "Month ({SALES.DATE}) =" & Val(cboMonth.Text)
  3.  
Regards
Veena
Jan 12 '08 #6

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

Expand|Select|Wrap|Line Numbers
  1. .RecordSelectionFormula = "Month ({SALES.DATE}) =" & Val(cboMonth.Text) And "Year ({SALES.DATE}) =" & cboYear.Text
Jan 12 '08 #7

QVeen72
Expert 100+
P: 1,445
Thanks Ms. Veena =)
Can i add year as well to that code?
Maybe like this?

Expand|Select|Wrap|Line Numbers
  1. .RecordSelectionFormula = "Month ({SALES.DATE}) =" & Val(cboMonth.Text) And "Year ({SALES.DATE}) =" & cboYear.Text
Yes You can,
Check this :

Expand|Select|Wrap|Line Numbers
  1. .RecordSelectionFormula = "Month ({SALES.DATE}) =" & Val(cboMonth.Text)  & " And Year ({SALES.DATE}) =" & Val(cboYear.Text) 

Regards
Veena
Jan 12 '08 #8

P: 44
I made it like this:

Expand|Select|Wrap|Line Numbers
  1. Dim m_Report As CRAXDDRT.report
  2. Dim m_Application As New CRAXDDRT.Application
  3.  
  4. Set m_Report = Nothing
  5. Set m_Report = m_Application.OpenReport(App.Path + "Report.rpt", 1)
  6.  
  7.     m_Report.RecordSelectionFormula = "Month ({SALES.DATE}) =" & Val(cboMonth.Text) & " And Year ({SALES.DATE}) =" & Val(cboYear.Text)
  8.  
  9.    'it doesnt work when i put this 2 parameters
  10.     m_Report.ParameterFields.Item(1).AddCurrentValue Format(Val(cboMonth.Text), "MMMM")
  11.     m_Report.ParameterFields.Item(2).AddCurrentValue cboYear.Text
  12.  
  13.  
  14.     With CRViewer1
  15.     .EnableExportButton = True
  16.     .EnableCloseButton = True
  17.     .ReportSource = m_Report
  18.     .ViewReport
  19.  End With
  20.  
For both parameters, i put them as string.
did i make any mistake?

Thanks so much
Jan 12 '08 #9

QVeen72
Expert 100+
P: 1,445
Hi,

Comment these lines and check :

m_Report.ParameterFields.Item(1).AddCurrentValue Format(Val(cboMonth.Text), "MMMM")
m_Report.ParameterFields.Item(2).AddCurrentValue cboYear.Text


Regards
Veena
Jan 12 '08 #10

P: 44
Hi,

Comment these lines and check :

m_Report.ParameterFields.Item(1).AddCurrentValue Format(Val(cboMonth.Text), "MMMM")
m_Report.ParameterFields.Item(2).AddCurrentValue 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 =(
Jan 12 '08 #11

P: 44
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.
Jan 12 '08 #12

Expert 100+
P: 446
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

Expand|Select|Wrap|Line Numbers
  1. MsgBox Month ({SALES.DATE}) & "  -  "  & Val(cboMonth.Text)
  2. ' and 
  3. 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
Expand|Select|Wrap|Line Numbers
  1. 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
Jan 12 '08 #13

QVeen72
Expert 100+
P: 1,445
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.FormulaFields(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
Jan 13 '08 #14

P: 44

m_Report.FormulaFields(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..............
=))
Jan 13 '08 #15

P: 1
your month year report guide for wonderful thanks for that.........
Dec 8 '13 #16

Post your reply

Sign in to post your reply or Sign up for a free account.