I have a form that has criteria fields:
From Equipment Number
To Equipment Number
I am using VS 2008 .net using VB.
Each of these boxes set variables that I use in a query:
The issue I am running into is that I need ALL records pulled in if the field is left blank. These are all LookupEdit Boxes. Except for the Date boxes. The Date range boxes will always have an entry. The main criteria that I need to pull is if the user selects "30" for the Department and leaves the Group and the Equipment Number Range boxes blank, then I need the records to pull everything in belonging in Department 30.
The equipment number data is numeric with characters: 0807-VEH, 5110-EQUIP, etc...
In the query, how can I make it pull everything in with the Like Statement if the box is empty (user left blank).
Beginning Date = 01/01/2011
Ending Date = 02/15/2011
Department = ""
Group = "30"
From Equipment Number = ""
To Equipment Number = ""
This needs to pull in:
Records between 01/01/2011 and 02/15/2011
Records belonging to only Group 30
Records from all departments
Records from all equipment
Also, is there a way to set the From Equipment Number to default to the minimum Equipment Number so it wouldn't be blank and likewise the maximum for the To Equipment Number?
Below is a sample of what is in the button click of a View Button. It will send the variables to a querystring that I have in a module, which follows below:
'On btnView click event...have code for each field just showing the one for Department.
If Me.LookUpEditDepartment.Visible = True Then
If Not IsDBNull(Me.LookUpEditDepartment.Text) Or Me.LookUpEditDepartment.Text <> "" Then
DepartmentCriteria = Me.LookUpEditDepartment.Text
ElseIf Me.LookUpEditDepartment.Text = "" Then
DepartmentCriteria = "%*%"
'Code/Query that I'm trying to pull the info with...I only have the Eq range and Group...was testing
QueryString = "INSERT INTO FuelConsumptionSpare ( Date, DateEnd, Equipment_Number, Fuel_Type, Diesel, Mode, Classification, Department_Number, Number_of_Fuels) SELECT " & "'" & PassedDate & "'" & ", " & "'" & PassedDateEnd & "'" & ", FuelTransactions.Equipment_Number, FuelTransactions.Fuel_Type, Sum(FuelTransactions.Gallons_Used) AS SumOfGallons_Used, EquipmentMaster.Mode, EquipmentMaster.Report_Classification, EquipmentMaster.Department_Number, Count(FuelTransactions.Equipment_Number) AS CountOfGallons_Used FROM EquipmentMaster RIGHT JOIN FuelTransactions ON EquipmentMaster.Equipment_Number = FuelTransactions.Equipment_Number WHERE FuelTransactions.Fuel_Type = " & "'" & "Diesel Fuel" & "'" & " and FuelTransactions.Transaction_Date >= " & "'" & PassedDate & "'" & " And FuelTransactions.Transaction_Date <= " & "'" & PassedDateEnd & "'" & " AND Originated_From Not Like " & "'" & "%Petro%" & "'" & " AND FuelTransactions.Equipment_Number >= " & "'" & EqFromCriteria & "'" & " AND FuelTransactions.Equipment_Number <= " & "'" & EqToCriteria & "'" & " AND EquipmentMaster.Report_Classification Like " & "'" & GroupCriteria & "'" & " GROUP BY FuelTransactions.Equipment_Number, FuelTransactions.Fuel_Type, EquipmentMaster.Mode, EquipmentMaster.Report_Classification, EquipmentMaster.Department_Number ORDER BY FuelTransactions.Equipment_Number;"
Call RunSQLQuery(QueryString, EquipmentTrackingConnectionString)