Results 1 to 7 of 7
  1. #1
    3 Star Lounger
    Join Date
    Jan 2001
    Posts
    294
    Thanks
    0
    Thanked 0 Times in 0 Posts

    SQL ? HANS (Access 2003 )

    I created, with some awesome help from HANS a search form I use on my db. I've attached a sample to this posting. My users love how this search form works. They would however like to search by muliple dates. Something like between date opened and date opened. This would show them the range of dates they want to search on instead of the current single date search. Is this possible to modify? Hans you are familiar with this any thoughts? Thank you very much!!!

  2. #2
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: SQL ? HANS (Access 2003 )

    Try this:
    <pre>If Not IsNull(Me.txtDateOpened) Then
    If Not IsNull(Me.txtDateOpened2) Then
    strWhere = strWhere & " DateOpened Between #" & _
    Format(Me.txtDateOpened, "mm/dd/yyyy") & _
    "# And #" & Format(Me.txtDateOpened2, "mm/dd/yyyy") & "# AND"
    Else
    strWhere = strWhere & " DateOpened >= #" & _
    Format(Me.txtDateOpened, "mm/dd/yyyy") & "# AND"
    End If
    ElseIf Not IsNull(Me.txtDateOpened2) Then
    strWhere = strWhere & " DateOpened <= #" & _
    Format(Me.txtDateOpened2, "mm/dd/yyyy") & "# AND"
    End If
    </pre>

    (You can't copy and paste this directly into your code, paste it into Word, copy the result and paste that into your code)

  3. #3
    3 Star Lounger
    Join Date
    Jan 2001
    Posts
    294
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: SQL ? HANS (Access 2003 )

    hey Hans. I got the code in with no errors but it's not returning any results. Can you take a look and see what I'm doing wrong? Thanks...

  4. #4
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: SQL ? HANS (Access 2003 )

    The code somehow got mangled (did you copy it from e-mail instead of from the Lounge itself?) See attached version.

  5. #5
    3 Star Lounger
    Join Date
    Jan 2001
    Posts
    294
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: SQL ? HANS (Access 2003 )

    I placed it in word but I'm sure I'm the one that mangled it. This will work great. Thanks Hans!!!

  6. #6
    3 Star Lounger
    Join Date
    Jan 2001
    Posts
    294
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: SQL ? HANS (Access 2003 )

    HANS one more thing. This does work very well but would it be possible to have the option of typing in the first date and have it show only that date and if the users wants a range of dates they type a date in the second box as well and it would show the range. Currently if you want a single date to show you have to type it in both boxes. Thanks for all your help..

  7. #7
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: SQL ? HANS (Access 2003 )

    See below.

Posting Permissions

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