Results 1 to 11 of 11
  1. #1
    gestroup is offline Novice
    Windows 10 Access 2016
    Join Date
    Dec 2020
    Posts
    5

    Question VBA SQL Query Start/End Date Variables

    Hello there! I am going crazy. I am writing a VBA module in my Access Database to query down a Table showing in a Subform. The one issue I am having right now is getting the Date Range part of the SQL Query to work. What happens now is it acts like it doesn't recognize the "StartDate" and "EndDate" variables, and instead, prompts me twice for each. WHAT AM I DOING WRONG? JUST USE MY VARIABLES! Lol.



    Private Sub runQueryBtn_Click()



    'On Error GoTo runQueryBtn_Click_Err



    Dim SearchUpdate As String
    Dim WUC As String
    Dim TMS As String
    Dim StartDate As Date
    Dim DateCalcMonth As Date
    Dim EndDate As Date




    whatTMS.SetFocus
    TMS = whatTMS.Text
    whatWUC.SetFocus
    WUC = whatWUC.Text




    If (dateRangeGroup = 1) Then
    EndDate = DMax("[Comp Date Time]", "tblMAFs", "[Type Model Series] LIKE '*" & TMS & "*'")
    DateCalcMonth = DateSerial(Year(EndDate), Month(EndDate), 1)
    StartDate = DateAdd("M", -11, DateCalcMonth)




    Else
    StartDate = DMin("[Comp Date Time]", "tblMAFs", "[Type Model Series] LIKE '*" & TMS & "*'")
    EndDate = DMax("[Comp Date Time]", "tblMAFs", "[Type Model Series] LIKE '*" & TMS & "*'")



    End If




    SearchUpdate = " SELECT tblMAFs.* " _
    & " FROM tblMAFs " _
    & " WHERE tblMAFs.[Type Model Series] LIKE '*" & TMS & "*' " _
    & " AND tblMAFs.[WUC] LIKE '*" & WUC & "*' " _
    & " AND tblMAFs.[Comp Date Time] BETWEEN StartDate and EndDate " _


    & " ORDER BY tblMAFs.[Comp Date Time]; "



    Forms!frmSearch!subfrmMAFs.Form.RecordSource = SearchUpdate
    Forms!frmSearch!subfrmMAFs.Form.Requery


    runQueryBtn_Click_Exit:
    Exit Sub


    runQueryBtn_Click_Err:
    MsgBox ("There is either missing or mismatched search criteria, or an unexpected error occurred. Please check your criteria and try again.")
    Resume runQueryBtn_Click_Exit


    End Sub

  2. #2
    pbaldy's Avatar
    pbaldy is offline Who is John Galt?
    Windows XP Access 2007
    Join Date
    Feb 2010
    Location
    Nevada, USA
    Posts
    22,518
    You have to concatenate the values into the string:

    & " AND tblMAFs.[Comp Date Time] BETWEEN #" & StartDate & "# and #" & EndDate & "# " _
    Paul (wino moderator)
    MS Access MVP 2007-2019
    www.BaldyWeb.com

  3. #3
    gestroup is offline Novice
    Windows 10 Access 2016
    Join Date
    Dec 2020
    Posts
    5
    Quote Originally Posted by pbaldy View Post
    You have to concatenate the values into the string:

    & " AND tblMAFs.[Comp Date Time] BETWEEN #" & StartDate & "# and #" & EndDate & "# " _
    Holy cow! THANK YOU! That did the trick, I think!

  4. #4
    pbaldy's Avatar
    pbaldy is offline Who is John Galt?
    Windows XP Access 2007
    Join Date
    Feb 2010
    Location
    Nevada, USA
    Posts
    22,518
    Happy to help! By the way, you don't need to set focus to controls to get their value, just drop the .Text:

    TMS = whatTMS
    WUC = whatWUC

    I personally would use Me to refer to controls, so you know it's a control rather than a variable:

    TMS = Me.whatTMS
    WUC = Me.whatWUC
    Paul (wino moderator)
    MS Access MVP 2007-2019
    www.BaldyWeb.com

  5. #5
    gestroup is offline Novice
    Windows 10 Access 2016
    Join Date
    Dec 2020
    Posts
    5
    Quote Originally Posted by pbaldy View Post
    Happy to help! By the way, you don't need to set focus to controls to get their value, just drop the .Text:

    TMS = whatTMS
    WUC = whatWUC

    I personally would use Me to refer to controls, so you know it's a control rather than a variable:

    TMS = Me.whatTMS
    WUC = Me.whatWUC
    Thank you again! I was having issues because I needed to extract the text from the Combo box for TMS, instead of the ID, so I finally found that the .Text and the .SetFocus worked for that purpose and just rolled with it. I have another question for you, though, if you don't mind! Since you are already familiar with most of my chunk of code to this point:

    I am trying to export the resulting table to an Excel file. I am having a surprisingly hard time figuring out exactly how to do that. This is what I came up with so far, but I have had various error messages on the exportBtn_click() function:

    Private Sub runQueryBtn_Click()
    'Filters the MAF table based on TMS, WUC, and Date Range selected


    'On Error GoTo runQueryBtn_Click_Err


    Dim SearchUpdate As String
    Dim WUC As String
    Dim TMS As String
    Dim StartDate As Date
    Dim DateCalcMonth As Date
    Dim EndDate As Date




    whatTMS.SetFocus
    TMS = whatTMS.Text
    whatWUC.SetFocus
    WUC = whatWUC.Text




    If (dateRangeGroup = 1) Then
    EndDate = DMax("[Comp Date Time]", "tblMAFs", "[Type Model Series] LIKE '*" & TMS & "*'")
    DateCalcMonth = DateSerial(Year(EndDate), Month(EndDate), 1)
    StartDate = DateAdd("M", -11, DateCalcMonth)




    Else
    StartDate = DMin("[Comp Date Time]", "tblMAFs", "[Type Model Series] LIKE '*" & TMS & "*'")
    EndDate = DMax("[Comp Date Time]", "tblMAFs", "[Type Model Series] LIKE '*" & TMS & "*'")



    End If




    SearchUpdate = " SELECT tblMAFs.* " _
    & " FROM tblMAFs " _
    & " WHERE tblMAFs.[Type Model Series] LIKE '*" & TMS & "*' " _
    & " AND tblMAFs.[WUC] LIKE '*" & WUC & "*' " _
    & " AND tblMAFs.[Comp Date Time] BETWEEN #" & StartDate & "# and #" & EndDate & "# " _
    & " ORDER BY tblMAFs.[Comp Date Time]; "



    Forms!frmSearch!subfrmMAFs.Form.RecordSource = SearchUpdate
    Forms!frmSearch!subfrmMAFs.Form.Requery



    runQueryBtn_Click_Exit:
    Exit Sub


    runQueryBtn_Click_Err:
    MsgBox ("There is either missing or mismatched search criteria, or an unexpected error occurred. Please check your criteria and try again.")
    Resume runQueryBtn_Click_Exit


    End Sub


    Private Sub exportBtn_Click()
    'Exports the MAF table to an Excel file in a single click


    DoCmd.OutputTo acOutputReport, "Forms!frmSearch!subfrmMAFs.Form", acFormatXLSX, , True




    End Sub

  6. #6
    pbaldy's Avatar
    pbaldy is offline Who is John Galt?
    Windows XP Access 2007
    Join Date
    Feb 2010
    Location
    Nevada, USA
    Posts
    22,518
    It would help to know the error, but you need the name of the report:

    DoCmd.OutputTo acOutputReport, "ReportName", acFormatXLSX, , True
    Paul (wino moderator)
    MS Access MVP 2007-2019
    www.BaldyWeb.com

  7. #7
    gestroup is offline Novice
    Windows 10 Access 2016
    Join Date
    Dec 2020
    Posts
    5
    Quote Originally Posted by pbaldy View Post
    It would help to know the error, but you need the name of the report:

    DoCmd.OutputTo acOutputReport, "ReportName", acFormatXLSX, , True

    Okay, here is as far as I have got! I get Access to give me the dialogue box and pick a path correctly (I think), but I cannot figure out how to tell it I want to export the current subform results (highlighted in red). Currently getting run-time error 3011.


    Private Sub exportBtn_Click()
    'Exports the MAF table to an Excel file in a single click


    Dim outputTable As String
    Dim folderPath As FileDialog
    Dim txtFileName As String




    Set folderPath = Application.FileDialog(msoFileDialogFolderPicker)




    With folderPath

    .AllowMultiSelect = False


    ' Set the title of the dialog box
    .Title = "Please select folder for Excel output"


    ' Show the dialog box. If the .Show method returns True, the user picked at least one file. If the .Show method returns False, the user clicked Cancel.
    If .Show = True Then
    txtFileName = .SelectedItems(1)
    Else
    MsgBox "No File Picked!", vbExclamation
    txtFileName = ""
    End If


    End With




    outputTable = Forms!frmSearch!subfrmMAFs.Form.RecordSource




    DoCmd.TransferSpreadsheet acExport, acSpreadsheetTypeExcel12, "outputTable", txtFileName & "" & "RCBOutput.xlsx", True




    End Sub

  8. #8
    pbaldy's Avatar
    pbaldy is offline Who is John Galt?
    Windows XP Access 2007
    Join Date
    Feb 2010
    Location
    Nevada, USA
    Posts
    22,518
    The only way that would work (without the double quotes) is if the record source was the name of a table or query. I don't think it will work with SQL. If it is SQL, I'd probably open a recordset on it and use CopyFromRecordset with Excel automation.
    Paul (wino moderator)
    MS Access MVP 2007-2019
    www.BaldyWeb.com

  9. #9
    gestroup is offline Novice
    Windows 10 Access 2016
    Join Date
    Dec 2020
    Posts
    5
    Quote Originally Posted by pbaldy View Post
    The only way that would work (without the double quotes) is if the record source was the name of a table or query. I don't think it will work with SQL. If it is SQL, I'd probably open a recordset on it and use CopyFromRecordset with Excel automation.
    Thank you! Do you happen to have any sample code excerpts for the CopyFromRecordset Excel automation method you mentioned?

  10. #10
    pbaldy's Avatar
    pbaldy is offline Who is John Galt?
    Windows XP Access 2007
    Join Date
    Feb 2010
    Location
    Nevada, USA
    Posts
    22,518
    This is old but should get you started:

    http://access.mvps.org/access/modules/mdl0035.htm
    Paul (wino moderator)
    MS Access MVP 2007-2019
    www.BaldyWeb.com

  11. #11
    ssanfu is offline Master of Nothing
    Windows 10 Access 2010 32bit
    Join Date
    Sep 2010
    Location
    Anchorage, Alaska, USA
    Posts
    9,664
    Another resource to look at: Exporting to EXCEL Workbook Files

Please reply to this thread with any new information or opinions.

Similar Threads

  1. Replies: 15
    Last Post: 09-12-2018, 07:24 AM
  2. Replies: 5
    Last Post: 04-06-2017, 03:24 PM
  3. Start Date Query Help
    By aamer in forum Access
    Replies: 6
    Last Post: 03-10-2016, 10:41 AM
  4. Replies: 6
    Last Post: 03-02-2016, 12:58 PM
  5. Using date variables from table in query
    By Skybeau in forum Access
    Replies: 1
    Last Post: 12-22-2011, 08:46 PM

Tags for this Thread

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  
Other Forums: Microsoft Office Forums