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?
15 4416
Hi,
Use This Formula : -
Report1.RecordSelectionFormula = _
-
& "UpperCase (MonthName (Month ({SALES.DATE}))) ='" _
-
& UCase(Format(cboMonth.Text, "MMMM") ) & "'"
-
Regards
Veena
it wont work because my Sales.Date format is dd/mm/yyyy
do i need to convert it first?
thanks...
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
they are 1 to 12
what i mean is 1 refers to January, and so on
Hi,
Then Keep it simple, try this : -
Report1.RecordSelectionFormula = _
-
& "Month ({SALES.DATE}) =" & Val(cboMonth.Text)
-
Regards
Veena
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
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
I made it like this: -
Dim m_Report As CRAXDDRT.report
-
Dim m_Application As New CRAXDDRT.Application
-
-
Set m_Report = Nothing
-
Set m_Report = m_Application.OpenReport(App.Path + "Report.rpt", 1)
-
-
m_Report.RecordSelectionFormula = "Month ({SALES.DATE}) =" & Val(cboMonth.Text) & " And Year ({SALES.DATE}) =" & Val(cboYear.Text)
-
-
'it doesnt work when i put this 2 parameters
-
m_Report.ParameterFields.Item(1).AddCurrentValue Format(Val(cboMonth.Text), "MMMM")
-
m_Report.ParameterFields.Item(2).AddCurrentValue cboYear.Text
-
-
-
With CRViewer1
-
.EnableExportButton = True
-
.EnableCloseButton = True
-
.ReportSource = m_Report
-
.ViewReport
-
End With
-
For both parameters, i put them as string.
did i make any mistake?
Thanks so much
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
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 =(
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.
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
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
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..............
=))
your month year report guide for wonderful thanks for that.........
Sign in to post your reply or Sign up for a free account.
Similar topics
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...
|
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 ...
|
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...
|
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...
|
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...
|
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...
|
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...
|
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...
|
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 ...
|
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...
|
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...
|
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...
|
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...
|
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...
|
by: Defcon1945 |
last post by:
I'm trying to learn Python using Pycharm but import shutil doesn't work
|
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....
|
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...
|
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...
| |