4

I have an Access 2007 database that requires a query be run every week to gather every record that was worked on the prior week. The current setup is:

  • I have a query where the date range's WHERE criteria is: Between [Forms]![frm_Menu]![txt_fromdate] And [Forms]![frm_Menu]![txt_todate]+"1"
  • "fromdate" and "todate" text boxes in frm_Menu are unbound text boxes. "fromdate" has an AfterUpdate event that fills in "todate" with the date chosen in "fromdate" plus 6 days.
  • frm_Menu has a button for running the query after "fromdate" and "todate" are filled in.

The issue is, when I run the query for a week's worth of records, I get entries outside the selected date range. For example, if I have "3/1/2015" in the "fromdate" text box and "3/7/2015" in the "todate" text box, I'll receive the results from 3/1 to 3/7, but I'm also getting results from 3/10, 3/11, and 3/12.

At first, I thought it might be reading "3/1/2015" as "3/1x/2015," but that doesn't explain why I'm ONLY getting extra results from 3/10 through 3/12 and not 3/13 through 3/19 as well.

Does anyone know what might be causing this? To work around this problem, I've just been running a query that gathers EVERYTHING and then filtering out what's need in Excel before sending it over. Ideally, I'd like for the person who needs this report to be able to open the database themselves, pick the date range they need, and then export the query results from Access.

Phrancis
  • 2,222
  • 2
  • 27
  • 40
Caleb W.
  • 51
  • 6
  • Why are you quoting a numeric? Are your dates date value? – Fionnuala Mar 16 '15 at 14:52
  • Simple answer is, International Date Problems in Access. JET engine considers the Dates in the **MM/DD/YYYY** format. So when you use 3/1/2015 - JET interprets this as **March 1, 2015**, not **January 3, 2015**. Jet clearly identifies there is no month as 13 and upwards, so treats them right. I would suggest format or CDbl() to get the "real" value of the date. – PaulFrancis Mar 16 '15 at 14:56
  • Fionnuala -- Are you talking about the quotation marks around the 1 in the WHERE criteria? That's just how Access automatically formatted it when I entered it. I tested the query with the +"1" removed entirely and still had the same issue where it pulls dates outside the date range. I'm not sure what you mean by date value, but I have the format for both text boxes set to Short Dates. – Caleb W. Mar 16 '15 at 14:56
  • PaulFrancis -- I actually want it to read the dates as MM/DD/YYYY. For the example in the OP, I'm actually wanting results from March 1st through March 7th. I've never heard of the CDbl function, though. Could you expand a little more on how to use it in my case? – Caleb W. Mar 16 '15 at 15:00

1 Answers1

1

It turned out I had the data type for the Dates column as Text instead of Date/Time. Retried the same query after changing the column to Date/Time and it worked perfectly. Just a PEBKAC error.

Caleb W.
  • 51
  • 6