Results 1 to 15 of 15
  1. #1
    New Lounger
    Join Date
    Jun 2015
    Posts
    22
    Thanks
    6
    Thanked 0 Times in 0 Posts

    Question Creating search parameters

    Hello,

    I have a database and I have created a query for certain fields in my database. I can get the query to run correctly if i do a simple search by Job, worker, or a specific date. However I want to be able to search by a date range. Every time i change from the "Like [Forms]![Search Database]![qdate1] &"*" " to a between function for the date range, my other two search fields stop working. Can someone help me to be able to search by job, worker, and a date range?

    thank you!!!

  2. #2
    New Lounger
    Join Date
    Apr 2015
    Posts
    18
    Thanks
    0
    Thanked 0 Times in 0 Posts
    I use the next query for this task:
    SELECT column_name(s)
    FROM table_name
    WHERE column_name BETWEEN value1 AND value2;

    SELECT * FROM Customers
    WHERE Country='Germany'
    AND (City='Berlin' OR City='München');
    Last edited by kaufen; 2015-10-05 at 08:22.

  3. #3
    New Lounger
    Join Date
    Jun 2015
    Posts
    22
    Thanks
    6
    Thanked 0 Times in 0 Posts
    thanks I will try this and see if it works

  4. #4
    Super Moderator RetiredGeek's Avatar
    Join Date
    Mar 2004
    Location
    Manning, South Carolina
    Posts
    9,434
    Thanks
    372
    Thanked 1,457 Times in 1,326 Posts
    Enginerd,

    If you are using the query grid include the date field twice and check for >= in one and <= in the other.
    DateRngQuery.JPG
    Results:
    DateRngResults.JPG

    HTH
    May the Forces of good computing be with you!

    RG

    PowerShell & VBA Rule!

    My Systems: Desktop Specs
    Laptop Specs

  5. #5
    New Lounger
    Join Date
    Jun 2015
    Posts
    22
    Thanks
    6
    Thanked 0 Times in 0 Posts
    Capture.PNG

    This is my search query. I want to be able to search by any of those three fields but i want the date to be a date range. The above options do not seem to work. Any suggestions?

    thank you!

  6. #6
    Super Moderator RetiredGeek's Avatar
    Join Date
    Mar 2004
    Location
    Manning, South Carolina
    Posts
    9,434
    Thanks
    372
    Thanked 1,457 Times in 1,326 Posts
    Enginerd,

    How is the Date Molded field defined in your table? If it is a date the quotes in the criteria are messing you up. Dates should be enclosed in hashes (#date#) as in my example above. HTH
    May the Forces of good computing be with you!

    RG

    PowerShell & VBA Rule!

    My Systems: Desktop Specs
    Laptop Specs

  7. #7
    New Lounger
    Join Date
    Jun 2015
    Posts
    22
    Thanks
    6
    Thanked 0 Times in 0 Posts
    it is defined as a date. i attempted to change the quotes to hashes but it gives an error message saying the operation is invalid. i do not want to put a specific date as my range because i want the date to be searchable for any date range.

  8. #8
    Super Moderator RetiredGeek's Avatar
    Join Date
    Mar 2004
    Location
    Manning, South Carolina
    Posts
    9,434
    Thanks
    372
    Thanked 1,457 Times in 1,326 Posts
    Enginerd,

    Try building the criteria value as in: "#" & qdate1 & "#"

    BTW: where are qdate1 & 2 coming from? If from a form you may need to fully qualify them. HTH
    May the Forces of good computing be with you!

    RG

    PowerShell & VBA Rule!

    My Systems: Desktop Specs
    Laptop Specs

  9. #9
    3 Star Lounger
    Join Date
    Apr 2001
    Location
    Levin, Manawatu-Wanganui, New Zealand
    Posts
    324
    Thanks
    9
    Thanked 28 Times in 26 Posts
    Quote Originally Posted by enginerd View Post
    Capture.PNG

    This is my search query. I want to be able to search by any of those three fields but i want the date to be a date range. The above options do not seem to work. Any suggestions?

    thank you!
    Hi
    It is not clear to me where qDate1 is coming from. If it is on a userform then perhaps that expression needs qualifying.
    Try something like this ... Between [forms]![frmName]![qDate1] And [forms]![frmName]![qDate2] as in the attached screenshot.

    [frmName] is the name of your form that contains the controls into which you enter the query dates.

    qrySample.png

    Cheers
    G

  10. The Following User Says Thank You to geofrichardson For This Useful Post:

    enginerd (2015-07-21)

  11. #10
    New Lounger
    Join Date
    Jun 2015
    Posts
    22
    Thanks
    6
    Thanked 0 Times in 0 Posts
    Geofrichardson,

    This code works specifically for the dates. However, when I use this to search between a date range, i can ONLY search for a date range. I need to be able to search by a date range AND the other two fields.

  12. #11
    Super Moderator RetiredGeek's Avatar
    Join Date
    Mar 2004
    Location
    Manning, South Carolina
    Posts
    9,434
    Thanks
    372
    Thanked 1,457 Times in 1,326 Posts
    Enginerd,

    With the Access Query Form for a record to be selected all the criteria tests on a single line must be true!

    If you want to have the query sometimes include the date and sometimes not then you will have to create code to dynamically build the SQL statement from the information provided on the form. Is this what you are after? HTH
    May the Forces of good computing be with you!

    RG

    PowerShell & VBA Rule!

    My Systems: Desktop Specs
    Laptop Specs

  13. #12
    New Lounger
    Join Date
    Jun 2015
    Posts
    22
    Thanks
    6
    Thanked 0 Times in 0 Posts
    Maybe. I'm very new to coding. I want to take my database and have the query search by Operator, OR Job ID, OR a date range. Currently it is only letting me search by the date range.

  14. #13
    Super Moderator RetiredGeek's Avatar
    Join Date
    Mar 2004
    Location
    Manning, South Carolina
    Posts
    9,434
    Thanks
    372
    Thanked 1,457 Times in 1,326 Posts
    Enginerd,

    Ok you want something like this:

    Code:
       strSQL = "SELECT Field1,Field2,Field3,...
       strSQL = strSQL & " From TableName " 
       If FormName!FieldName.Value <> "" Then
         strSQL = strSQL & " Where [Operator] = FormName!FieldName.Value
       Else
         If FormName!FieldName2.Value <> "" Then
           strSQL = StrSQL & " Where [Job Id] = FormName!FieldName2.Value
         Else
            If FormName!DateField1 <> "" and FormName!DateField2 <> "" Then
              strSQL = strSQL & "Where ( ([TableDateField] >= FormName!DateField1 ) and " & _
                                                      "([TableDateField] <= FormName!DateField2 ) )"
            Else  
              Exit Sub  'No Query Parameters Provided by User
            End if
         End If
       End If
    
      '*** Run the SQL ***
       DoCMD.RunSQL strSQL
    Note: The above is untested air code. The logic is what you want but the syntax may need some tweaking.

    HTH
    Last edited by RetiredGeek; 2015-07-21 at 17:22.
    May the Forces of good computing be with you!

    RG

    PowerShell & VBA Rule!

    My Systems: Desktop Specs
    Laptop Specs

  15. #14
    3 Star Lounger
    Join Date
    Apr 2001
    Location
    Levin, Manawatu-Wanganui, New Zealand
    Posts
    324
    Thanks
    9
    Thanked 28 Times in 26 Posts

    Using multiple rows in query grid.

    Hi
    To construct logical OR use multiple rows in your query grid.

    Continuing with my horse analogy.
    The accompanying screenshot, though simple, demonstrates this effect.
    qryColourName.png
    This is the statement generated by the above query grid.
    Code:
    SELECT tblHorses.HorseColour, tblHorses.HorseName
    FROM tblHorses
    WHERE (((tblHorses.HorseColour)="Bay") AND ((tblHorses.HorseName)="Ruby")) OR (((tblHorses.HorseColour)="Black"));
    This will generate the Bay horses named Ruby as well as (OR) all the horses coloured black.

    You will need to be careful about mixing your 'AND' and "OR.

    G

  16. #15
    New Lounger
    Join Date
    Jun 2015
    Posts
    22
    Thanks
    6
    Thanked 0 Times in 0 Posts
    thank you. I will try this

Posting Permissions

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