Results 1 to 5 of 5

Thread: Date problem

  1. #1
    Join Date
    Aug 2004
    Posts
    4

    Angry Unanswered: Date problem

    I am trying to do a 6 month report with data stored in a query. My boss wants to be able to enter a date and come up with data on that month and 6 months before (for forecasting reasons). The only way I see to do this is to have the users enter two dates in a form to open up the query with the data (to filter the info) using a between statement to show the 6 month span of data. All the dates are entered in the format “99/99/9999” so I had to separated and concatenated the month and year (ex. Jan, 2004) using the format function (Format([Due Date],"mmm"", ""yyyy"). The reason I left out the day in the date was because I only wanted to show a monthly summary of the data (using a summary query to sum up the data from another query on a monthly basses) only showing the month/year once (using Group By) and not a bunch of dates in that month. After I got the query to work I tried to do a between statement to search between two month/year values, here is my code;

    Dim strFormName As String
    Dim strFilterName As String

    strFormName = "Performance (Summary)"
    strFilterName = "[Month/Year] BETWEEN '" & Me!txtDate1 & "' AND '" & Me!txtDate2 & "'"
    DoCmd.OpenForm strFormName, , , strFilterName

    End Sub

    Now for some reason when I enter the two month/year dates in the text boxes, I don’t get the right records to appear. It either includes data from other months that should not be there or records are missing that should be there. Also the “filter by form” button in access actually starts a between statement for you when a date field is highlighted. It also gives me the same results as my filter form does. Is there some kind of bug in Access 2003 or did I miss something? Is there any suggestion on a better way to go about doing this?

    I am using Access 2003, any help is appreciated.
    Last edited by Lord Willix; 08-24-04 at 14:09.

  2. #2
    Join Date
    Sep 2003
    Location
    MI
    Posts
    3,713
    ... BETWEEN #" & DateAdd("m",-6,SomeTextBox.Value) & "# AND #" & SomeTextBox.Value & "# ...

    And the problem is?
    Back to Access ... ADO is not the way to go for speed ...

  3. #3
    Join Date
    Aug 2004
    Posts
    4
    I cannot use the method you have listed because the dates are not listed in full format. I had to split them into a month and year interval using the format function so I could group by month and year only as stated in my original post. So instead of a full date I have it listed as "Jan, 2004" which is a string if I am not mistaken. In theory, the simple between statement that I posted in my original post should have worked. Access's built in between statement when you use a "filter by form" option on a date text box gives me the same wrong results as my custom filter. So when I want to choose records for information between lets say, (Jan, 2004) AND (Jun, 2004) it will return records based off months in which the dates are not even between those months. Maybe I should filter the dates in a different way to obtain my results, but I can’t think of any.

  4. #4
    Join Date
    Sep 2003
    Location
    MI
    Posts
    3,713
    Quote Originally Posted by Lord Willix
    I cannot use the method you have listed because the dates are not listed in full format. I had to split them into a month and year interval using the format function so I could group by month and year only as stated in my original post. So instead of a full date I have it listed as "Jan, 2004" which is a string if I am not mistaken. In theory, the simple between statement that I posted in my original post should have worked. Access's built in between statement when you use a "filter by form" option on a date text box gives me the same wrong results as my custom filter. So when I want to choose records for information between lets say, (Jan, 2004) AND (Jun, 2004) it will return records based off months in which the dates are not even between those months. Maybe I should filter the dates in a different way to obtain my results, but I can’t think of any.
    What type is this column (the month/year column)?
    Back to Access ... ADO is not the way to go for speed ...

  5. #5
    Join Date
    Aug 2004
    Posts
    4
    Well, the table that I used to get the date values from is a date/time data type so when I used the format function to get the month and year values I assumed the new field (month year) in the query would still be a date/time data type like the original date field was.

Posting Permissions

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