Learn how to a build a cloud-first strategyRegister Now

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 299
  • Last Modified:

Criteria Form and Like Statement

I have a form that has criteria fields:
Beginning Date
Ending Date
Department
Group
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:
FromEqCriteria
ToEqCriteria
DepartmentCriteria
GroupCriteria

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).  

Scenerio:

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?
Thanks!
Tanya
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 = "%*%"
            End If
        End If
'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)

Open in new window

0
TanyaDH
Asked:
TanyaDH
  • 2
1 Solution
 
Vadim RappCommented:
If you have parameter @MyParm, and the query is using it in WHERE, such as

...WHERE MyColumn=@MyParm
but if @myParm is empty string, then you want to drop this criteria; you can accomplish it by

...WHERE (MyColumn=@MyParm or @MyParm='')

As for the pre-setting the control on the form to the minimum, you have to issue another query, find out that minimum, and set it. But maybe instead of that you can get away with the same trick as above, if the user has left the field empty.
0
 
TanyaDHAuthor Commented:
Thank you for your reply.  I did come up with a solution which is working:

On each of the Lookup Edit Boxes:
Department, Group, FromEQ, ToEQ  I set the NullValue in the properties to % for the Department and the Group.  For the EQ fields I set the FromEQ NullValue to 0000 and the ToEQ NullValue to 9999.
The user cannot leave a blank/null field it will default to % which is the wildcard for the Like statement.  As for the EQ values they will default to 0000 and 9999.

When the View button is clicked it sets the Variables to equal each of the fields accordingly.
The QueryString then uses the variables.  


The code box is the final QueryString.

I thank you for your reply.  I didn't implement it because I had just figured it out and when I signed on to post my solution, I saw you had suggested something.  

Tanya
QueryString = "INSERT INTO FuelConsumptionSpare ( Date, DateEnd, Equipment_Number, Fuel_Type, Unleaded, 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 = " & "'" & "Unleaded" & "'" & " AND FuelTransactions.Transaction_Date >= " & "'" & PassedDate & "'" & " And FuelTransactions.Transaction_Date <= " & "'" & PassedDateEnd & "'" & " AND Originated_From Not Like " & "'" & "%Petro%" & "'" & " 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)

Open in new window

0
 
TanyaDHAuthor Commented:
I accepted my own comment because it works just as I needed it to.  I thought it was more complicated than it really was.  I am satisfied with my solution.
0

Featured Post

Free Tool: SSL Checker

Scans your site and returns information about your SSL implementation and certificate. Helpful for debugging and validating your SSL configuration.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

  • 2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now