Results 1 to 6 of 6

Thread: date error

  1. #1
    Join Date
    Jul 2004
    Posts
    214

    Unanswered: date error

    Hello,
    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:

    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 "
          End If
          End If
    
         Next ctl
    
          ' 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 "
        End If
        
        strnewquery = "Select qryProd_Individual.* FROM qryProd_Individual"
    
        If strSQL <> "" Then
            strSQL = Left(strSQL, (Len(strSQL) - 5))
    
            strnewquery = strnewquery & " WHERE " & strSQL & ";"
        End If
    
        Debug.Print strnewquery
        ' Create the recordset
        Set rs = db.OpenRecordset(strnewquery)
        If rs.RecordCount > 0 Then
            DoCmd.OpenReport "rptIndividualReport", acViewPreview, , strSQL
            DoCmd.Close acForm, "frmReportbuiler"
        Else
            MsgBox "There are no records that match your criteria! Please select new criteria.", vbInformation
            Exit Sub
        End If
    
    Exit_cmdPreview_Click:
        Exit Sub
    
    Err_cmdPreview_Click:
        Select Case Err.Number
            Case 2501 'OpenReport action was cancelled
                Resume Exit_cmdPreview_Click
            Case Else
                MsgBox Err.Description
                Resume Exit_cmdPreview_Click
                Resume
            End Select
    End Sub
    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.
    Thanks
    Last edited by slimjen; 05-15-07 at 12:54.

  2. #2
    Join Date
    Feb 2004
    Location
    One Flump in One Place
    Posts
    14,912
    The hashes (or pound signs - depends where you come from - anyway, these "#") indicate to Access that you are concatenating a date into your SQL. By the sounds of it you are not so remove the #s.
    Testimonial:
    pootle flump
    ur codings are working excelent.

  3. #3
    Join Date
    Feb 2004
    Location
    One Flump in One Place
    Posts
    14,912
    Actually - remove them if you are using numbers to represent dates. Replace them with ' if you store the month name.
    Testimonial:
    pootle flump
    ur codings are working excelent.

  4. #4
    Join Date
    Jul 2004
    Posts
    214
    Thank you. I knew it had to be something simple

  5. #5
    Join Date
    Jul 2004
    Posts
    214
    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?

  6. #6
    Join Date
    Feb 2004
    Location
    New Zealand
    Posts
    1,424
    Provided Answers: 8
    the problem is it is text

    are storing the date value in the qryProd_Individual query

    if yes

    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


    StePhan McKillen
    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.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •