473,322 Members | 1,425 Online
Bytes | Software Development & Data Engineering Community
Post Job

Home Posts Topics Members FAQ

Join Bytes to post your question to a community of 473,322 software developers and data experts.

Monthly report

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
15 4416
QVeen72
1,445 Expert 1GB
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
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
1,445 Expert 1GB
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
they are 1 to 12
what i mean is 1 refers to January, and so on
Jan 12 '08 #5
QVeen72
1,445 Expert 1GB
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
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
1,445 Expert 1GB
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
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
1,445 Expert 1GB
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
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
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
sierra7
446 Expert 256MB
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
1,445 Expert 1GB
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

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
your month year report guide for wonderful thanks for that.........
Dec 8 '13 #16

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

Similar topics

18
by: Jeremy Weiss | last post by:
I'm trying to build a database that will handle the monthly billing needs of a small company. I'm charting everything out and here's what I see: table for customers sub table to track payments...
2
by: karmaverma | last post by:
I need help with this apparently simple problem. I have a table with the following records: Effective_Date Commodity Price 10/1/2005 0 5/1/2006 2750 10/1/2006 ...
2
by: Bob | last post by:
I've searched this group and have not found any posts since 2001, so I'm hoping that now there may be a better way. I have a need to generate a report that looks like a outlook monthly calendar...
9
by: MikeSA | last post by:
Hi I a trying to create a chart that reflects monthly cumulative totals from a query. The query fields show sales opportunities forecasted invoice date (OppForInvDate), Opportunity Description and...
1
by: azimid | last post by:
Hello everyone, I need your help with a query. Currently I display the data in a daily format. I have a form that the users submits the date range (from say 20061224 to 20070530) and the...
1
by: cbellew | last post by:
Hi guys, i'm looking to create a report with a table showing totals (running and cumulative) of education sessions attend by the staff at a hospital. I'm trying to get the table to show something...
1
by: shaggawoo | last post by:
Hello from Nova Scotia Canada. Hi there, Im struggling a bit here and I thought I might seek out some advice. Nice to meet you all. My situation is that im running a database that records...
0
by: FMS Development Team | last post by:
Hi Everyone, I just published a paper with a sample database describing how to create a monthly summary report without VBA code. Basically, a financial crosstab showing 12 months of summaries...
1
by: creative1 | last post by:
Hi Everyone, I need help in one more thing. I my report that I want to print under follwoing criteria: Weekly ---------------- Set start and end date automatically to last week Bi-Weekly ...
0
by: DolphinDB | last post by:
Tired of spending countless mintues downsampling your data? Look no further! In this article, you’ll learn how to efficiently downsample 6.48 billion high-frequency records to 61 million...
1
isladogs
by: isladogs | last post by:
The next Access Europe meeting will be on Wednesday 6 Mar 2024 starting at 18:00 UK time (6PM UTC) and finishing at about 19:15 (7.15PM). In this month's session, we are pleased to welcome back...
0
by: Vimpel783 | last post by:
Hello! Guys, I found this code on the Internet, but I need to modify it a little. It works well, the problem is this: Data is sent from only one cell, in this case B5, but it is necessary that data...
0
by: jfyes | last post by:
As a hardware engineer, after seeing that CEIWEI recently released a new tool for Modbus RTU Over TCP/UDP filtering and monitoring, I actively went to its official website to take a look. It turned...
0
by: ArrayDB | last post by:
The error message I've encountered is; ERROR:root:Error generating model response: exception: access violation writing 0x0000000000005140, which seems to be indicative of an access violation...
1
by: Defcon1945 | last post by:
I'm trying to learn Python using Pycharm but import shutil doesn't work
1
by: Shællîpôpï 09 | last post by:
If u are using a keypad phone, how do u turn on JavaScript, to access features like WhatsApp, Facebook, Instagram....
0
by: Faith0G | last post by:
I am starting a new it consulting business and it's been a while since I setup a new website. Is wordpress still the best web based software for hosting a 5 page website? The webpages will be...
0
isladogs
by: isladogs | last post by:
The next Access Europe User Group meeting will be on Wednesday 3 Apr 2024 starting at 18:00 UK time (6PM UTC+1) and finishing by 19:30 (7.30PM). In this session, we are pleased to welcome former...

By using Bytes.com and it's services, you agree to our Privacy Policy and Terms of Use.

To disable or enable advertisements and analytics tracking please visit the manage ads & tracking page.