VBA Code help

[aamer]

How to change the vba code

I was using this code previously


Me.cboFrom = VBA.Date

Before I was using 2/20/2016



What should be the new Code for

Me.cboMonthFrom = VBA.Date

Where the input value will be Month name e.g January, February, March, April etc

[pbaldy]

Are you looking for this?

Format(VBA.Date,"mmmm")

[vicsaccess]

theres probably several ways to do it but try this

Me.Text0 = MonthName(Month(Date))

[aamer]

Thanks pbaldy
this is what i was looking for
Format(VBA.Date,"mmmm")

[pbaldy]

Happy to help.

[aamer]

Am trying to modifying the following but am totally lost.

Private Sub cmdOpenReportSingle_Click()
On Error GoTo Err_Handler

Const REPORTNAME = "1A"
Const MESSAGETEXT = "Both a start and end date must be selected."
Dim strCriteria As String
Dim strDateFrom As String, strDateTo As String

' make sure a customer is selected
If Not IsNull(Me.cboDateFrom) And Not IsNull(Me.cboDateTo) Then
strDateFrom = "#" & Format(Me.cboDateFrom, "yyyy-mm-dd") & "#"
strDateTo = "#" & Format(DateAdd("d", 1, Me.cboDateTo), "mmmm") & "#"
' build string expression to filter report
' to selected data range
strCriteria = "PurDate >= " & strDateFrom & " And PurDate < " & strDateTo

' open report filtered to selected customer
DoCmd.OpenReport REPORTNAME, _
View:=acViewPreview, _
WhereCondition:=strCriteria
Else
MsgBox MESSAGETEXT, vbExclamation, "Invalid operation"
End If

Exit_Here:
Exit Sub

Err_Handler:
MsgBox Err.Description, vbExclamation, "Error"
Resume Exit_Here
End Sub



With the above code I input dates as 19/02/2014 it works (with the Db I am using not referring to this sample 01A).

Now I made some changes in form where I use Start: Month as January With selecting Year 2014
And End Month February 2016, I am not getting the result. I think there is an issue of format & need to add Year in the code.
But I don't know how to do that.

If someone can fix or help me fix the code I will be greatful.


For better understanding I have attached Sample 01A. Please open the form DialogACDC

Sample 01A.zip

[pbaldy]

Well, your combos are only returning a month name, so your formulas don't actually evaluate to valid dates. You aren't using the year textboxes. It would be simpler to have the user enter two valid dates.

[aamer]

how can I do that, can you guide me.

[aamer]

how can I do that, can you guide me.
Basically the function of this report is to give detail by month not between dates & between specific period of year or years

[aamer]

I need Help

[vicsaccess]

Sample 01A1.zip

there's a thousand ways of doing what you are wanting but a few things were necessary. in your code your report name was missing part. added purchase date to your query so that it could have actual dates to pull from, you don't have to display it but it helps with the sort that you wanted. added two new combo boxes to show kind of what paul is asking. you have the actual dates, use them. you don't have to display them so the combo boxes only show the month and year but actually sort by the date.

[aamer]

vicsaccess Thank you for your kind help & specially how beautifully you have explained on the coding. It's helping me a lot understanding better.

The report generated was showing all the descriptions so I added the following in the query 1AB to filter report by Company Title & Item Description to show the details.

added Criteria [Forms]![DialogMonYr].[Cbodcpt] under PurCompanyName
& added Criteria [Forms]![DialogMonYr].[Cboactit] under Purchase Description

Now the Company Title Combo Box is Blank and the report is also blank.

Please see the attached sample.


Sample 01B.zip

[aamer]

I have fixed the problem with combo boxes now its giving me the Company Title & Item Description.
But when I run the report its asking me to "ENTER PARAMETER VALUE" Pur Date

Where as in combo box "cbostart" & "cboend" date range is already given, and report is blank.


Please check this Sample. Sample 01C.zip

[ssanfu]

Look at the query "1AB". Why is the "Show" check box unchecked for the column "PurDate"?

Shouldn't begin object names with numbers. The Access Gnomes get testy and can cause all kinds of random errors.
Also shouldn't use spaces, special characters or punctuation in object names.

For combo box "Cboactit", why are you using a totals query? Better to use the keyword "distinct" (IMO).

SELECT DISTINCT Purchases.PurCompanyName
FROM Purchases
ORDER BY Purchases.PurCompanyName;

You have a row source for the combo box "Cbodcpt", but then you change it in the afterupdate event of combo box "Cboactit"???

Found an error in your logic for the date tests. If you leave the date combo boxes blank, the report still tries to open. Your custom error message does not get displayed.


----- Fixes ------
I checked the "Show" check box for the column "PurDate". No more parameter request.
I changed the row source for the combo box "Cbodcpt" and changed the afterupdate code for combo box "Cboactit".
And I changed the date test when opening the report. (see the code)

[aamer]

I have done what you advised. I entered the following values and in report months are showing values instead of summing them up into a single sum value of month.
Sample Sample 01C is attached.


Sample 01C.zip