Urgent question - How to change column format 4

[Rmcta]

I have a database with 6 columns set up as text but they contain dates. Example: 1/25/03
I have to create a query that returns all records which are today()-45

Do I need to convert the columns into date format before I can create such a query?

I would like to do that but I don't really know how to do it as my database has been split.

Thank you for guiding me!

Rama

 

[mpastore]

As long as the text columns have valid dates in them you could format the text field as date like this:

select * from mytable where cdate([mytextdate])=(date()-45)

Mike Pastore

Hats off to (Roy) Harper

 

[Rmcta]

Thank you for your reply.

My table is tblCalls
CallMade1/ Text / Format: 99/99/00;0;_
CallMade2/ Text / Format: 99/99/00;0;_
CallMade3/ Text / Format: 99/99/00;0;_
CallReturned1/ Text / Format: 99/99/00;0;_
CallReturned2/ Text / Format: 99/99/00;0;_
CallReturned3/ Text / Format: 99/99/00;0;_

My query is:
SELECT *
FROM tblCalls
WHERE (tblCalls.CallReturned1 is null AND tblCalls.CallMade1 < Now()-45) OR (tblCalls.CallReturned2 is null AND tblCalls.CallMade2 < Now()-45) or (tblCalls.CallReturned3 is null AND tblCalls.CallMade3 < Now()-45);

BUT ..IT DOES NOT WORK!

I tried what you suggested but it does not work

 

[mpastore]

Please send SQL with changes I had suggested so I can look at it.

when you say it does not work, are you getting an error message or are the wrong results coming back in your query result?

Mike Pastore

Hats off to (Roy) Harper

 

[Rmcta]

SELECT *
FROM tblCalls
WHERE ([tblCalls].[CallReturned1] Is Null And cdate([tblCalls].[CallMade1])<Now()-45) Or ([tblCalls].[CallReturned2] Is Null And cdate( [tblCalls].[CallMade2]) <Now()-45) Or ([tblCalls].[CallReturned3] Is Null And cdate( [tblCalls].[CallMade3])<Now()-45);

The error is: Invalid use of Null

Sample data is:
ID CallMade1 CallMade2 CallMade3 CallReturned1 CallReturned2 CallReturned3
1 5 /12/03 5 /18/03
2 3/15/03 3/19/03 3 /15/03
3 04/11/03 04/14/03 06/04/03 04/12/03 04/16/03
4 6 /01/03

 

[mpastore]

Need to check for null condition. Try

iif(isnull([tblCalls].[CallMade1],Now(),cdate( [tblCalls].[CallMade1])) <Now()-45)

Need to apply this to all callmade fields

Mike Pastore

Hats off to (Roy) Harper

 

[Rmcta]

I'm sorry..I don't think I understand how to write the new statement.

What I am trying to do is a query to show me all calls that have been made more than 45 days ago but not yet returned.

Thank you for your help!

 

[mpastore]

Try this:

SELECT *
FROM tblCalls
WHERE (tblCalls.CallReturned1 Is Null And IIf(isnull(tblCalls.CallMade1),Now(),cdate(tblCalls.CallMade1))<Now()-45) Or (tblCalls.CallReturned2 Is Null And IIf(isnull(tblCalls.CallMade2),Now(),cdate(tblCalls.CallMade2))<Now()-45) Or (tblCalls.CallReturned3 Is Null And IIf(isnull(tblCalls.CallMade3),Now(),cdate(tblCalls.CallMade3))<Now()-45);


Mike Pastore

Hats off to (Roy) Harper

 

[Rmcta]

IT WORKD!
THANK YOU very much for your great help!
Rama

 

[Rmcta]

May I trouble you to help me understand the following part of the statement?

IIf(isnull(CallMade1),Now(),cdate(CallMade1))<Now()-45

 

[mpastore]

It says that if there is no value in the callmade1 field, give me the record where todays's date is less than today's date-45 days. This will of course never be true, it's a kludgy way of negating the statement and is generally a bad coding practice because it's confusing.

If there is a value in callmade1, then your callmade1 field is cast as a datatype date and is checked to see if it's 45 days or more ago. Now() gives today's date, now()-45 is 45 days ago.

Here's what you can do better next time

- Use date fields instead of text fields to store dates.
- Use boolean fields to store things such as callreturned. A boolean field (yes/no) is more concise.
- Try to never use fields like callmade1,callmade2, etc. It violates good database design practice. What you should have is a call master record in one table that contains a call master ID. Then in another table you could have a record for each returned call that relates to the master record.

Good luck with the system.

Panic and preparation are the cornerstones of innovation.


Mike Pastore

Hats off to (Roy) Harper

 

[Rmcta]

Thank you!