I have a form with a combo box for a "to month" and a "from month" that a user can filter between months they want to appear on a report. The source type is a value list with the names of the month. Ex. user chooses frmMth: January and toMth: April. They want only the information from between January and April to appear on the report. I use a query for the source of the report that has a month field.
This is my code:
Private Sub cmdPreview_Click()
On Error GoTo Err_cmdPreview_Click
Dim strSQL As String, intCounter As Integer
Dim ctl As Control, strname As String, strnewquery As String
Dim strRptSel As String
Dim stMessage As String
Set db = CurrentDb
'Build SQL String
For Each ctl In Me.Form
If ctl.Tag = "input" Then
strname = "me." & ctl.Name
If ctl.Value > "" Then
strSQL = strSQL & "[" & ctl.Name & "] " & " like " & Chr(34) & ctl.Value & Chr(34) & " And "
' Set the value of the parameter.
If Me.frmMth & vbNullString <> "" And Me.toMth & vbNullString <> "" Then
strSQL = strSQL & " [Month] BETWEEN #" & Me.frmMth & "# And #" & Me.toMth & "# And "
strnewquery = "Select qryProd_Individual.* FROM qryProd_Individual"
If strSQL <> "" Then
strSQL = Left(strSQL, (Len(strSQL) - 5))
strnewquery = strnewquery & " WHERE " & strSQL & ";"
' Create the recordset
Set rs = db.OpenRecordset(strnewquery)
If rs.RecordCount > 0 Then
DoCmd.OpenReport "rptIndividualReport", acViewPreview, , strSQL
DoCmd.Close acForm, "frmReportbuiler"
MsgBox "There are no records that match your criteria! Please select new criteria.", vbInformation
Select Case Err.Number
Case 2501 'OpenReport action was cancelled
It worked before I put a date filter on the form. Can someone tell me what I'm doing wrong? The error is "Syntax error in date in query expression" I am not storing a date. I am storing the name of the month and the user needs to select a to and from date on a form to filter the data they need.
Ok. This worked but apparently the months are sorted in alpha order and it keeps coming up if the user ask for months between feb and april it gives me Jan also. It's not sorted in the query. What can I do?
are storing the date value in the qryProd_Individual query
then change the combo so it have 2 Column the first is hidden
which will hold the month number in it (coloums widths)
then change the SQL to some like
" where month([datefeildname])= " & me.combo.Column(1) & " And month([otherdatefeildname])= " & me.othercombo.Column(1) & " And ......" the rest of the SQL
don't forget to put the (()) in
Last edited by myle; 05-15-07 at 16:29.
hope this help
See clear as mud
the aim is store once, not store multiple times
Remember... Optimize 'til you die!
Progaming environment: Access based on my own environment: DAO3.6/A97/A2000/A2003/A2007/A2010 VB based on my own environment: vb6 sp5 ASP based on my own environment: 5.6 VB-NET based on my own environment started 2007 SQL-2005 based on my own environment started 2008 MYLE YOUR PASSWORD IS JUST LIKE YOUR TOOTHBRUSH DON'T SHARE IT.