User input Criteria for Union Query

[wlfpck]

I wasn't sure if this should go here but I placed the thread here since I am trying to do this in VBA. I just started learning VBA and SQL on my own due to the lack of company resources for getting an actual developer.

Currently right now, I am trying to get my VBA code to dump a query once the user pushes a button. I have the following code to call up the Excel app.

Option Compare Database
Private Type BROWSEINFO
  hOwner As Long
  pidlRoot As Long
  pszDisplayName As String
  lpszTitle As String
  ulFlags As Long
  lpfn As Long
  lParam As Long
  iImage As Long
End Type
Private Declare Function SHGetPathFromIDList Lib "shell32.dll" Alias _
            "SHGetPathFromIDListA" (ByVal pidl As Long, _
            ByVal pszPath As String) As Long
 
Private Declare Function SHBrowseForFolder Lib "shell32.dll" Alias _
            "SHBrowseForFolderA" (lpBrowseInfo As BROWSEINFO) _
            As Long
 
Private Const BIF_RETURNONLYFSDIRS = &H1
Public Function BrowseFolder(szDialogTitle As String) As String
  Dim X As Long, bi As BROWSEINFO, dwIList As Long
  Dim szPath As String, wPos As Integer
 
    With bi
        .hOwner = hWndAccessApp
        .lpszTitle = szDialogTitle
        .ulFlags = BIF_RETURNONLYFSDIRS
    End With
 
    dwIList = SHBrowseForFolder(bi)
    szPath = Space$(512)
    X = SHGetPathFromIDList(ByVal dwIList, ByVal szPath)
 
    If X Then
        wPos = InStr(szPath, Chr(0))
        BrowseFolder = Left$(szPath, wPos - 1)
    Else
        BrowseFolder = vbNullString
    End If
End Function

From here, I have created code that allows me to export (data dump) the query I want into an Excel file.

Dim strPathFile As String, strFile As String, strPath As String
Dim strTable As String, strBrowseMsg As String
Dim blnHasFieldNames As Boolean
strBrowseMsg = "Select the folder where the new EXCEL file will be created:"
strPath = BrowseFolder(strBrowseMsg)
If strPath = "" Then
      MsgBox "No folder was selected.", vbOK, "No Selection"
      Exit Sub
End If
strFile = "PP Severity Error Report.xls"
' Replace tablename with the real name of the table from which
' the data are to be exported
strTable = "qry_PP_Processor_Scores"
strPathFile = strPath & strFile
DoCmd.TransferSpreadsheet acExport, acSpreadsheetTypeExcel12, strTable, strPathFile, True
DoCmd.TransferSpreadsheet acExport, acSpreadsheetTypeExcel12, "qry_PP_Errors_Union", strPathFile, True
DoCmd.TransferSpreadsheet acExport, acSpreadsheetTypeExcel12, "tbl_PP_Error_Severity_Codes", strPathFile, True
End Sub

The qry_PP_Errors_Union is a Union query. In this query there is a date field. I would like to be able to to use that date field as a parameter. So I have written this VBA to prompt the user for a Begin Date and an End Date.

Beep
strBegindatemsg = "Enter the beginning date." & vbCrLf & vbLf
strBegindate = InputBox(Prompt:=strBegindatemsg, Title:="Begin Date")
Beep
strEnddatemsg = "Enter the beginning date." & vbCrLf & vbLf
strEnddate = InputBox(Prompt:=strEnddatemsg, Title:="End Date")

Now the part that I am missing is that I am not sure how to make the "strBegindate" and "strEnddate" the criteria for the union query.

The following is the SQL for my union query.

SELECT LastName, FirstName, Title, TeamName, WorkOrderNumber, DateCompleted, WorkCode, UICError AS Error, "Update and Internal Correspondence" AS Category  FROM qry_PP_UIC_Error
UNION 
SELECT LastName, FirstName, Title, TeamName, WorkOrderNumber, DateCompleted, WorkCode, BIDError, "Bids"  FROM qry_PP_Bid_Error
UNION
SELECT LastName, FirstName, Title, TeamName, WorkOrderNumber, DateCompleted, WorkCode, EmployeeBillError, "Billing"  FROM qry_PP_Billing_Error
UNION
SELECT LastName, FirstName, Title, TeamName, WorkOrderNumber, DateCompleted, WorkCode, BusinessRulesError, "Business Rules"  FROM qry_PP_BR_Error
UNION
SELECT LastName, FirstName, Title, TeamName, WorkOrderNumber, DateCompleted, WorkCode, DamageError, "Damages"  FROM qry_PP_Dam_Error
UNION
SELECT LastName, FirstName, Title, TeamName, WorkOrderNumber, DateCompleted, WorkCode, OEError, "Order Entry"  FROM qry_PP_OE_Error
UNION SELECT LastName, FirstName, Title, TeamName, WorkOrderNumber, DateCompleted, WorkCode, PhotoError, "Photos"  FROM qry_PP_Photo_Error;

Any help would be greatly appreciated. Without the criteria, my code works for dumping everything out into Excel. However, dumping all the data results in a 7 mb Excel file that requires manual deletion of the information that is not pertinent.

 

[pr2-eugin]

Hellowlfpck, Welcome to AWF..

How about appending the WHERE Condition to all the SELECT statements?

SELECT LastName, FirstName, Title, TeamName, WorkOrderNumber, DateCompleted, WorkCode, UICError AS Error, "Update and Internal Correspondence" AS Category  FROM qry_PP_UIC_Error WHERE someField Between #startDate# And #endDate#
UNION 
SELECT LastName, FirstName, Title, TeamName, WorkOrderNumber, DateCompleted, WorkCode, BIDError, "Bids"  FROM qry_PP_Bid_Error WHERE someField Between #startDate# And #endDate#
UNION
SELECT LastName, FirstName, Title, TeamName, WorkOrderNumber, DateCompleted, WorkCode, EmployeeBillError, "Billing"  FROM qry_PP_Billing_Error WHERE someField Between #startDate# And #endDate#
UNION
SELECT LastName, FirstName, Title, TeamName, WorkOrderNumber, DateCompleted, WorkCode, BusinessRulesError, "Business Rules"  FROM qry_PP_BR_Error WHERE someField Between #startDate# And #endDate#
UNION
SELECT LastName, FirstName, Title, TeamName, WorkOrderNumber, DateCompleted, WorkCode, DamageError, "Damages"  FROM qry_PP_Dam_Error WHERE someField Between #startDate# And #endDate#
UNION
SELECT LastName, FirstName, Title, TeamName, WorkOrderNumber, DateCompleted, WorkCode, OEError, "Order Entry"  FROM qry_PP_OE_Error WHERE someField Between #startDate# And #endDate#
UNION SELECT LastName, FirstName, Title, TeamName, WorkOrderNumber, DateCompleted, WorkCode, PhotoError, "Photos"  FROM qry_PP_Photo_Error WHERE someField Between #startDate# And #endDate#;

Theoretical and Not Tested..

 

[CJ_London]

The criteria would be written as:

"... WHERE DateCompleted between #" & format(strBegindate,"mm/dd/yyyy") & "# AND format(strEnddate,"mm/dd/yyyy") & "#"

and needs to be used against each query in the union query

Note: if you already use the american format for dates then you do not need to use the format part of the above.

You probably need to modify your vba that gets the dates to ensure that the user inputs a valid date - assuming you are using a form then you might find it better to have two unbound controls for date entry and a button to start the report running (which would not be enabled until two valid dates are entered.

I'm also assuming you are building your sql in VBA. If you have a query, then you will have to go the form route and in the query use

...format(forms!myform!strbegindate,"mm/dd/yyyy")...etc

Alternatively you can have the query prompt directly by using

...format([Enter the beginning date],"mm/dd/yyyy")...etc

Although this will give you the same issue with invalid dates

 

[wlfpck]

Awesome! Thanks for the quick reply. I'll give this a try as soon as I'm out of all my meetings and such.