All months versus one

[DarrenBoyer]

I've searched for a solution but have come up short so far.

What I would like to do is open a form 'frmUsedMargins' based on input from the unbound 'frmSalesReports'. In frmSalesReports I created 4 drop down boxes. Two of the drop down boxes are based on date input. The first drop down box is based on months - I list Jan - Feb and it defaults to the current month. The second one is year and again it defaults to the current year. Once the user has chosen their inputs the next form would open based on the On Click event for a cmd button.

How would I pull up all twelve months if say the user chose a 13th option in the combo box 'cmbMonthChosen'that titled year to date.?
The field that this would filter results against is called TransactionDate from the Manual-Vehicles table. My current Date format is 14-Sep-04.

I'd appreciate any direction someone could offer to get past this hurdle. Thanks,

 

[Elysium]

I would base it off of a Select Case statement such as:

     Select Case cbxIndex
          Case 1 'January
              ... Do some action
          .
          .
          .
          Case 12 'December
              ... Do some action
          Case Else
              ... Action for Year-To-Date
     End Select

Randy

 

[DarrenBoyer]

I suppose I could work on it that way. Being new to VBA it would be faster than my recent progress.

However, I'm wondering if there might be another way because I also need to do the same thing for the year combo box as well -cmbYearChosen (1995-2004). This would create a large # of Case statements would it not? Plus I was hoping to limit the Case's based on the first combo box - dept. (New, Used, or Fleet)

I know about the date part function and originally thought about getting a part of the date such as month and/or year and then adding it to an SQL statement that populates the form. DatePart seems to be VBA and I need it to perform it while doing an SQL action.

 

[Zion7]

Darren, it sounds, like you have the first part working. You're able to open a filtered form, based on the choices from, 'frmSalesReports'.

Assuming this is correct, and that you are building a WHERE statement, in order to do so....

Not sure how you build the SQL, but here's an example...

Dim SQL, sMonth, sYear As String

sMonth = cmbMonthChosen

If sMonth = "month to date" Then sMonth = "*"
sYear = IIF(sYear = "Year To Date", "*", cmbYearChosen)

SQL = "Dept. = '" & cboDept & "' AND TransactionDate Like '" & sMonth & "' AND TransactionYear Like '" & sYear & "' AND ...

DoCmd.OpenForm "frmUsedMargins",,, SQL


Is this what you were asking?

Hope this helps, good luck!

 

[DarrenBoyer]

Thanks for your input Zion. Interesting Handle.

I've spent several hours working on a solution and really appreciated the little kickstart your input gave me.

I have two of the combo boxes working perfectly. However, I'm having a real struggle with the date options.
cmbMonthChosen - currently a value list with col 1 # 0-12
col 2 - Year To Date & Jan - Feb

cmbYearChosen I would like to base on a callable fcn but for now am happy working off of the Default - 2004 '=Year(Date())'.

Here is what works so far:

Private Sub cmdOpenMarginForm_Click()
On Error GoTo Err_cmdOpenMarginForm_Click

    Dim strSQL As String
    Dim stDept As String
    Dim stDocName As String
    Dim sMonth As String
          
    'Open Form based on Department'
    stDept = (Me.cmbDepartment)
    Select Case stDept
        Case "New"
            stDocName = "frmNewMargins"
        Case "Used"
            stDocName = "frmUsedMargins"
        Case Else
            MsgBox "Please chose a department from the 1st drop down box."
    End Select

    'Four combo boxes filter query '
    strSQL = "SELECT * FROM qryMarginsUsed "
    strSQL = strSQL & "WHERE("
    
    strSQL = strSQL & "((InventorySource) = '" & Me.cmbDepartment & "') AND "
    
    If Not IsNull(Me.cmbEmployeeChosen) Then
        strSQL = strSQL & "((EmpName) = '" & Me.cmbEmployeeChosen & "') AND "
    End If
    
    'Removes last AND in SQL statement at the end of WHERE clause.'
    If Right(strSQL, 4) = "AND " Then
        strSQL = Left(strSQL, Len(strSQL) - 5)
    End If
    
    strSQL = strSQL & ")"
    strSQL = strSQL & " ORDER BY TransactionDate "
    strSQL = strSQL & ";"

    DoCmd.OpenForm "frmUsedMargins"
    Forms!frmUsedMargins.RecordSource = strSQL

Exit_cmdOpenMarginForm_Click:
    Exit Sub

Err_cmdOpenMarginForm_Click:
    MsgBox Err.Description
    Resume Exit_cmdOpenMarginForm_Click
    
End Sub

How would I get the dates to work the way I want to? I've tried to come up with a solution using the DateSerial or DatePart functions but come up blank.

 

[Zion7]

Darren,
So your cboMonth gives a value of 1-12?
And cboYear just a 4 digit value, also.(neither one are true DATE values)
I hope this doesn't appear too convoluted, but here's an example, from one of my modules...

....
If Me.cboYear.Value = "" And Me.cboMonth.Value = "" Then
stDate = "Like '*/*/*') OR ((tblProjects.txtInvoiceDate) Is Null)"
ElseIf Me.cboYear.Value <> "" And Me.cboMonth.Value <> "" Then
stDate = "Like '" & cboMonth & "/*/" & cboYear & "')"
ElseIf Me.cboYear.Value = "" And Me.cboMonth.Value <> "" Then
stDate = "Like '" & cboMonth & "/*/*')"
ElseIf Me.cboYear.Value <> "" And Me.cboMonth.Value = "" Then
stDate = "Like '*/*/" & cboYear & "')"
End If

....part of the SQL where statement...
"AND (((tblProjects.txtInvoiceDate)" & stDate & ") " & _


So in your case, I may do Something like this...

....
strSQL = strSQL & "((InventorySource) = '" & Me.cmbDepartment & "') AND "
    
    If Not IsNull(Me.cmbEmployeeChosen) Then
        strSQL = strSQL & "((EmpName) = '" & Me.cmbEmployeeChosen & "') AND "
    End If
    
     
        If Me.cboMonthChosen = "MonthToDate" Then sMonth = "*" Else sMonth = Me.cboMonthChosen.Column(1)
    
        If Me.cboYearChosen = "YearToDate" Then sYear = "*" Else sYear = Me.cboYearChosen.Column(1)
     
     If Not IsNull(Me.cboMonthChosen) And Not IsNull(Me.cboYearChosen) Then
        strSQL = strSQL & "((TransactionDate) Like '" & sMonth & "/*/" & sYear & "') AND "
    
    
    'Removes last AND in SQL statement at the end of WHERE clause.'
    If Right(strSQL, 4) = "AND " Then
        strSQL = Left(strSQL, Len(strSQL) - 5)
    End If
...

...even though, transaction date, is formatted MedDate, the table values, are in ShortDate format.
my column values for cboYear & cboMonth, may be off. You want a 12/27/2004 format, for the SQL to work.
If code is viable, don't forget to declare variables...
Dim sMonth, sYear As String

Hope this helps, good luck!

 

[DarrenBoyer]

This is almost working!

I have come up with a seperate SQL statement that works after modifying my combo boxes.

SELECT * FROM qryMarginsUsed
WHERE (((InventorySource) = 'Used') AND Year(TransactionDate) LIKE "2004" AND Month(TransactionDate) LIKE "9" AND  ((EmpName) = 'Rene Aubin'))
ORDER BY TransactionDate;

All I have to do is get VBA to do the same thing. If cmbMonthChosen or cmbYearChosen are blank than I want to use that to select all the records (*) for that part of the date.

Here is what I have right now

Private Sub cmdOpenMarginForm_Click()
On Error GoTo Err_cmdOpenMarginForm_Click

    Dim strSQL As String
    Dim stDept As String
    Dim stDocName As String
    Dim stMonth As String
    Dim stYear As String
          
    'Open Form based on Department'
    stDept = (Me.cmbDepartment)
    Select Case stDept
        Case "New"
            stDocName = "frmNewMargins"
        Case "Used"
            stDocName = "frmUsedMargins"
        Case Else
            MsgBox "Please chose a department from the 1st drop down box."
    End Select

    'Four combo boxes filter query '
    strSQL = "SELECT * FROM qryMarginsUsed "
    strSQL = strSQL & "WHERE("
    
    strSQL = strSQL & "((InventorySource) = '" & Me.cmbDepartment & "') AND "
    
    stYear = Me.cmbYearChosen
    If stYear & " " = " " Then
        stYear = "*"
    End If
    
    strSQL = strSQL & "Year (TransactionDate) LIKE stYear AND "

    stMonth = Me.cmbMonthChosen
    If stMonth & " " = " " Then
        stMonth = "*"
    End If
    
    strSQL = strSQL & "Month(TransactionDate) LIKE stMonth AND "
    
    If Not IsNull(Me.cmbEmployeeChosen) Then
        strSQL = strSQL & "((EmpName) = '" & Me.cmbEmployeeChosen & "') AND "
    End If
    
    'Removes last AND in SQL statement at the end of WHERE clause.'
    If Right(strSQL, 4) = "AND " Then
        strSQL = Left(strSQL, Len(strSQL) - 5)
    End If
    
    strSQL = strSQL & ")"
    strSQL = strSQL & " ORDER BY TransactionDate "
    strSQL = strSQL & ";"

    DoCmd.OpenForm "frmUsedMargins"
    Forms!frmUsedMargins.RecordSource = strSQL

Exit_cmdOpenMarginForm_Click:
    Exit Sub

So when I step through this code:
stMonth "9"
stYear "2004'
strSQL
"SELECT * FROM qryMarginsUsed WHERE(((InventorySource) = 'Used') AND Year (TransactionDate) LIKE stYear AND Month(TransactionDate) LIKE stMonth AND ((EmpName) = 'Rene Aubin')) ORDER BY TransactionDate ;" Any suggestions?

 

[DarrenBoyer]

Whoops!

Just when I thought I'd tried everything I got the VBA to output the same as my working SQL statement.

This is what comes up in the Locals window:
"SELECT * FROM qryMarginsUsed WHERE(((InventorySource) = 'Used') AND Year(TransactionDate) LIKE "2004" AND Month(TransactionDate) LIKE "9" AND ((EmpName) = 'Rene Aubin')) ORDER BY TransactionDate;"

To me it's the same as my SQL that works:
SELECT * FROM qryMarginsUsed WHERE (((InventorySource) = 'Used') AND Year(TransactionDate) LIKE "2004" AND Month(TransactionDate) LIKE "9" AND ((EmpName) = 'Rene Aubin'))
ORDER BY TransactionDate;

(9 is my bound column, September is in column 2)

 

[Zion7]

You have double quotes, around 2004 & 9, Should be No quotes or, single quotes... .

"SELECT * FROM qryMarginsUsed WHERE(((InventorySource) = 'Used') AND Year(TransactionDate) LIKE '2004' AND Month(TransactionDate) LIKE 9 AND ((EmpName) = 'Rene Aubin')) ORDER BY TransactionDate;"

...This includes examples of both, just to show, you have the option.

You NEED single quotes, if including asterix.

Good Luck!