Selecting Dates in Access

[captvideo33065]

I'm trying to select transactions between two dates using the Criteria line of an Access Query. The format of the field is mm/dd/yyyy. When the records are selected it produces results by month disregarding year. Example: If I select transactions between 11/01/2001 and 11/31/2001, I get dates like 11/03/1999, 11/15/2000 and 11/30/2001. The field was originally built as a text field but I have since converted it to a date field.

 

[jhall156]

Are you putting #'s on either side of your date criteria? JHall

 

[captvideo33065]

When I put #'s on either side of the dates Access puts quote marks around them and makes the whole line a string. This results in no hits on the database.

 

[jhall156]

Please provide the SQL. We'll get to the bottom of it. JHall

 

[WildHare]

This statement you made in original post:
The field was originally built as a text field but I have since converted it to a date field.
is contradicted by this one in your first reply:
When I put #'s on either side of the dates Access puts quote marks around them and makes the whole line a string.

One or the other can't be correct. I'm betting the conversion from TEXT datatype to DATE datatype didn't take, because the behavior you describe is exactly what happens when you store dates as TEXT fields.

There are two ways to argue with a woman - neither one works.
Another free Access forum:

http://www.forumco.com/jmhare

More Access stuff at

http://www.jmhare.com/wildhare2.htm

 

[captvideo33065]

To Wildhare:
I assumed that that was the problem but I don't know how to verify it. Is there a way to tell whether the conversion actually changed the field from a text field to a date field.

 

[captvideo33065]

To jhall:
Below is the SQL you requested. Thanks for your help.

SELECT [Completed Frame/Private Line].[Billing Circuit], [Completed Frame/Private Line].[Location-Street], [Completed Frame/Private Line].[Location-City], [Completed Frame/Private Line].[Location-State], [Completed Frame/Private Line].[Location-Zip], [Completed Frame/Private Line].[Req Disc Date], [Completed Frame/Private Line].[Actual Disc Date]
FROM [Completed Frame/Private Line]
WHERE ((([Completed Frame/Private Line].[Billing Circuit])<>&quot;blank&quot AND (([Completed Frame/Private Line].[Req Disc Date]) Between [enter from date] And [enter to date]))
ORDER BY [Completed Frame/Private Line].[Billing Circuit];

 

[WildHare]

Captain:
Is there a way to tell whether the conversion actually changed the field from a text field to a date field.

Yes. Open the table in datasheet view. Widen, and look at the column containing your suspect dates. If the data is aligned to the LEFT, it's text. If it's aligned to the right, it's date/time.

Or go into table design and check the actual DATATYPE of the field. There is, to my mind, a BUG in the DDL of ACCESS that allows you to set a display format of a text field to MIMIC a date display format, without actually changing the data. For example, I just entered a TEXT field in to a table, and entered dates like &quot;03/02/1953&quot; and &quot;12/15/1970&quot; . In datasheet view, they aligned LEFT, like TEXT does. Then I went into table design and set a DISPLAY FORMAT of &quot;mm-dd-yy&quot; - but DID NOT change the data type from TEXT to DATE. The data then aligned RIGHT like a date would, but retained the &quot;12/15/1970&quot; values. This could confuse you if you entered a display format that already matched the data that was there, into thinking you've changed the data. If I'd put &quot;mm/dd/yyyy&quot; in the format line, it would look exactly like it always had... see the problem?

There are two ways to argue with a woman - neither one works.
Another free Access forum:

http://www.forumco.com/jmhare

More Access stuff at

http://www.jmhare.com/wildhare2.htm

 

[captvideo33065]

To Wildhare:
Your suggestion seems to be right on the money...The date is aligned to the left. However, the DataType is marked as Date/Time. Is there a way to change the field to a true Date/Time without having to reenter the data. The table contains about 5000 entries.