Results 1 to 4 of 4
  1. #1
    Join Date
    Mar 2004
    Posts
    15

    Unanswered: Query not filtering dates correctly - I am stumped

    Setup:
    I have Table1 with a field called DateReceived. DateReceived is a text field with input mask "99/99/0000;0;_". Changing the data type for the field is not an option.
    I also have Table2 which has a field called DateNo of type date/time. I want to append a certain date range of records from Table1 to Table2 including appending DateReceived to DateNo.

    Problem:
    I am having a problem with the parameters in the append query. First I made a function to convert the text in DateReceived to date/time format (See code for MakeDate below). Then I created a calculated field in my query: "mydate: MakeDate([DateReceived])".

    What I Don't Understand:
    If my criteria for the mydate field is "Between #3/1/2004# and #3/30/2004#" then it works correctly. If my criteria is "Between [start] and [end]" and I enter "3/1/2004" and "3/30/2004" then the query returns all records where mydate is in March, regardless of the year.

    I am stumped. Can anyone advise? Thanks.



    Public Function MakeDate(ByVal d As Variant) As Date

    If IsDate(d) Then
    Dim f As Double
    MakeDate = CDate(d)

    'I have also tried these methods with no luck
    'MakeDate = Format(d, "Short Date")
    'MakeDate = "#" & d & "#"
    'f = DateValue(d)
    'MakeDate = CDate(f)
    Else
    MakeDate = #1/1/1900#
    End If

    End Function

  2. #2
    Join Date
    Mar 2004
    Location
    www.scirocco.ca
    Posts
    346

    Re: Query not filtering dates correctly - I am stumped

    Sourround all dates in your query with # so change your function to return a string like this:

    Public Function MakeDate(ByVal d As Variant) As String
    If IsDate(d) Then
    MakeDate = "#" & CDate(d) "#"
    Else
    MakeDate = #1/1/1900#
    End If
    End Function
    http://www.scirocco.ca/images/banner...occobanner.gif

    Download for FREE the ADO/DAO Data Controls that makes life EASIER developing database applications in: VB, FoxPro, Access, VC++, .NET etc... Navigate, Add New, Delete, Update, Search, Undo and Save your changes. Supports Disconnected Recordsets and Transactions!

    Or try our Ask An Expert service to answer any of your questions!

  3. #3
    Join Date
    Mar 2004
    Posts
    15
    If I do that then mydate becomes a text field. Now "Between [start] and [end]" then entering "3/1/2004" and "3/30/2004" returns no records.

    Using "Between #3/1/2004# and #3/30/2004#"* returns the same records as before, including those in March but not in 2004, except mydate reads like "#3/1/2001#" instead of "3/1/2001"

    *Access changes this to "Between '#3/1/2004#' and '#3/30/2004#'" because mydate is now a text field.



    The way I had it before was filtering correctly if I hard coded the date range into the criteria but not if I used parameters which is really the source of my confusion.

    Also, when I used parameters and sort ascending it recognized that 3/26/2001 is before 3/1/2004 so it must be recognizing the year part of the date, so why are March 2001/2002/2003 records not filtered out by "Between [start] and [end]" then entering "3/1/2004" and "3/30/2004"

  4. #4
    Join Date
    Mar 2004
    Location
    Adelaide, Australia
    Posts
    32
    Originally posted by michael515
    If I do that then mydate becomes a text field. Now "Between [start] and [end]" then entering "3/1/2004" and "3/30/2004" returns no records.

    Using "Between #3/1/2004# and #3/30/2004#"* returns the same records as before, including those in March but not in 2004, except mydate reads like "#3/1/2001#" instead of "3/1/2001"

    *Access changes this to "Between '#3/1/2004#' and '#3/30/2004#'" because mydate is now a text field.



    The way I had it before was filtering correctly if I hard coded the date range into the criteria but not if I used parameters which is really the source of my confusion.

    Also, when I used parameters and sort ascending it recognized that 3/26/2001 is before 3/1/2004 so it must be recognizing the year part of the date, so why are March 2001/2002/2003 records not filtered out by "Between [start] and [end]" then entering "3/1/2004" and "3/30/2004"

    Have a look at this -> http://allenbrowne.com/ser-36.html, it should help in determining how MS Access handles dates.

Posting Permissions

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