datatype date as text?

[slimjen]

Maybe I need to focus on the big picture. What I am trying to accomplish is to get a number a calls received weekly. It seems I still need to convert the text date to a date first???

[JoeM]

It seems I still need to convert the text date to a date first???

I already showed you how to do that, so that part shouldn't be an issue anymore.

How is the data getting into Access? Is it being imported?
If so, can you change the data type to Date in the table you are importing it into? Then the issue will no longer exist.

[slimjen]

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?

O I was applying the criteria in the field I was converting.

SELECT CDate(Nz([Calls Recd],"12/31/2099")) AS NewDate
FROM [Master Table]
WHERE (((CDate(Nz([Calls Recd],"12/31/2099"))) Between #6/18/2013# And #8/12/2014#));

Should I be doing two different queries?? Convert first?

[slimjen]

Ok. I drop the criteria and converted the text to date and saved the query. Then I used that query to filter the date:

[CODE][SELECT Query3.NewDate
FROM Query3
WHERE (((Query3.NewDate) Between #6/18/2013# And #8/12/2014#));
/CODE]

I still got Datatype mismatch. I am obviously doing something wrong. uhg!

[JoeM]

No, I meant changing the datatype in the underlying Table, if that was possible.
Splitting the query out in two steps will make no difference (as you can see).

I still got Datatype mismatch. I am obviously doing something wrong. uhg!

I don't think so. That usually implies that there is data issue somewhere. Without seeing your data, I really can't research it. Any chance you can upload your database?

Did you try the creating the test I mentioned in post #12 yesterday that you said you were going to try? That should at least prove to you that the logic is solid, and the issue most likely resides in your data.

[slimjen]

Sorry JoeM; yes. I copied some of the data with only the dates into a blank copy of the table without changing the datatype. Used the cdate in a query and filtered. Queried the way I want. So the logic does work. But if I delete the text data in the fields from the offending records I expected the same because there are no longer text in any of the records for that field. The only thing I can think of doing is waiting until the users are out of the database and replace the table into a copy of the table without the offending data because I cannot delete the records all toghether or change the datatype right now. Thanks for all your help.

No, I meant changing the datatype in the underlying Table, if that was possible.
Splitting the query out in two steps will make no difference (as you can see).


I don't think so. That usually implies that there is data issue somewhere. Without seeing your data, I really can't research it. Any chance you can upload your database?

Did you try the creating the test I mentioned in post #12 yesterday that you said you were going to try? That should at least prove to you that the logic is solid, and the issue most likely resides in your data.

[JoeM]

But if I delete the text data in the fields from the offending records I expected the same because there are no longer text in any of the records for that field.

How exactly are you deleting the data in these offending records?
You're not replacing it with a single space, are you?

[Ruegen]

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.

dates can be messy. If you're like me in australia you have to convert the dates to American as that is how access handles it in vba mm/dd/yyyy

also format() formats the date but it is still a date value - it's just presented differently

if all else fails and I have done this a few times - you can convert the date value to a "long" as the date is technically a number and the code looks at the number, then converts it to date

so if you bring it back to a number it does't matter what date format you are working with - if the number is higher, lower or the same as the other number you get more accurate results and it is easier to work with.

[JoeM]

also format() formats the date but it is still a date value - it's just presented differently

Think you meant to say "... but it is still a text value ..."

[Ruegen]

Think you meant to say "... but it is still a text value ..."

actually since it's 4 am here... never mind...

[slimjen]

Hmm. I just put my cursor and hit delete?? Should I be doing this another way??

How exactly are you deleting the data in these offending records?
You're not replacing it with a single space, are you?

[JoeM]

Hmm. I just put my cursor and hit delete?? Should I be doing this another way??

That *should* be fine...

If you can't work it out, a workaround would be to create an exact replica on your data table, except have that one field be a date field (instead of text). Then you could use an Append Query to write the records from your main table to the temp table, and then do all your queries/filtering off the temp table.

The "pain in the neck" thing with that workaround is you have to delete all the data in your temp table and re-run you append query whenever there is a data change in your main table, though you may be able to automate that a bit using Macros or VBA.

[slimjen]

Yeah I agree. I'll spend maybe another 30mins on this thing and if no joy; I'll use your suggestion. Thanks for all your help! Have a great weekend.

[JoeM]

Sorry we couldn't get to the bottom of it. I have a feeling that if I had access to the database, I might be able to figure it out, but I understand that isn't always possible (due to confidentiality/business reasons).

[slimjen]

Actually; since its one table out of the database; Hold on and I'll throw it in a sample database with just that column and see if this will help you help me.