datatype date as text?

[slimjen]

All, using access 2010. One of my coworkers have a problem with a database. He needs to get a count of calls received in a given week. I said I would help because it should be simple. I would just use the field they are using for the date and run a query. Wrong. The field they are using for the date is a text data type!! WTH Apparently when the database was set up years ago and so much data later thought they could use this one field for dates and text. For the most part; the field contains dates. I don’t have any idea how to help without change the datatype of the field to date and risk losing data. Does anyone know of a way to query a date as a text datatype???

[JoeM]

Use the CDATE function, which converts text entries to dates.

[slimjen]

Ok I tried this but getting errors "Invalid us of Null:

CDate([Calls Recd]) Between "#2/17/2010#" And "#10/17/2014#"

[JoeM]

Sounds like you may have some NULL values in your [Calls Recd] field.
Maybe try:

CDate(NZ([Calls Recd],"12/31/2099")) Between #2/17/2010# And #10/17/2014#

Note: You do not want quotes around your date fields after Between...

[slimjen]

Where Am I putting this code in the query. I've tried in the criteria field and as an alias. I get it to convert to text but when I add the criteria Between #01/01/2014# and #08/10/2014# I get datatype mismatch

[JoeM]

If you are using the Query Builder, create a calculated field using this formula:

CDate(NZ([Calls Recd],"12/31/2099"))

I would recommend then checking it to confirm it is working properly (do all the values returned look like valid dates? if not, post some example of entries in your [Calls Recd] field that are not working right).

When everything is working right, then under the Criteria row for that that calculated field add:

Between #01/01/2014# and #08/10/2014#

[slimjen]

Ok. I completed the first part and if work fine.

NewDate: CDate(NZ([Calls Recd],"12/31/2099")) 

Also whats the purpose of "12/31/2099"?

The results seems to be converted to a date because it ever gives me the date picker when I click in the field.
Its when I place the date range in the criteria it gives me datatype mismatched:

between #2/17/2014# and #8/17/2014# 

I went through the table because it had text other than date in the field. I deleted all records without a date and queried on this. Still Datatype mismatch

[JoeM]

Ok. I completed the first part and if work fine.

So, you actually looked at the Datasheet View of your query and inspected all your records?
There aren't any blanks or errors being returned for any fields?
If you sort by this field, those sort of things should sort to the top or bottom of your list.

Also whats the purpose of "12/31/2099"?

Take a look at the NZ function (http://www.techonthenet.com/access/f...dvanced/nz.php). What that is saying is if the value of the field in Null, return "12/31/2099", so when we go to convert the entry to a date, it won't though back an error. This high-end date falls outside of your date range, so those records will not get returned by your query that uses a date range.

Two other things:
1. Are you using an American or European version of Microsoft Office?
2. If you are still getting errors, switch your query to SQL View and copy and paste the SQL code here.

[slimjen]

Ok. Understand about the 12/31/2099. I am using the American Version. Here's my SQL code:

SELECT CDate(Nz([Calls Recd],"12/31/2099")) AS [New Date]
FROM [Master Call Table]
WHERE (((CDate(Nz([Calls Recd],"12/31/2099"))) Between #2/17/2010# And #10/17/2014#));

[JoeM]

So everything sounds fine there. Sounds like a data issue. Did you see this part of my last post?

There aren't any blanks or errors being returned for any fields?
If you sort by this field, those sort of things should sort to the top or bottom of your list.

Did you find any blank, odd, or errors in the calculated field column when you did this?

[slimjen]

Sorry Yes. I double checked the data and deleted any records that didn't contain a date in that field. Thanks

[JoeM]

Without seeing your database, it is going to be very hard to figure out what the issue may be. I have Access 2007 and recreated your scenario the best I could, and it worked for me.

What you may want to try doing is create a small dummy table with only a handful of records, and try applying the same query logic to it, and see if it works. If it does, then you know that the logic/programming is solid, and it is probably some sort of data issue.

[slimjen]

Ok. I'll work on this. Thanks for your help.

[slimjen]

It seems that even though I can convert the dates using the query; when I apply filter; it looks at the original datatype. It works if I change the datatype of the table. Can't change it right now. Need an alternative.

[JoeM]

It seems that even though I can convert the dates using the query; when I apply filter; it looks at the original datatype.

You should be able to do it.
Where/how are you applying this filter?
Are you applying it to the original field or the calculated field?