DateAdd giving me a little trouble

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

Other Forums

MS Access

DateAdd giving me a little trouble

Author

Topic

Trudye
Posting Yak Master

169 Posts

Posted - 2008-09-24 : 10:23:52

I need to calc two fields for this pass thru query

The first field is working out just fine: SELECT Max([Date]) AS Last_Run_Date
The second field is the problem. I have to deduct 33 days from the Max date on a table.
What am I doing wrong? The query is running but I am getting an error in that column.

OBTW, the field named Date is text.

SELECT Max([Date]) AS Last_Run_Date, DateAdd("d",-33,Max([Date])) AS Begin_Date
FROM [Last Record Pulled];

Thanx,
Trudye

jsmith8858
Dr. Cross Join

7423 Posts

Posted - 2008-09-24 : 11:22:18

quote:

OBTW, the field named Date is text.

that's what's wrong! Why is this a text field? If it is a date, it should be a DateTime field. This is the #1 most important rule of databases -- always, without exception, use the proper data types in your tables!

Fix your design and it will work as expected.

- Jeff
http://weblogs.sqlteam.com/JeffS

Trudye
Posting Yak Master

169 Posts

Posted - 2008-09-24 : 12:25:43

I inherited this garbage, I never would have made a DATE field text and I never would have named it "DATE"

Do you see anything else that might be useful

jsmith8858
Dr. Cross Join

7423 Posts

Posted - 2008-09-24 : 13:46:22

You need to convert the text field to a date in your expression, you can use CDate() to do this.

Be prepared for the usual assort of errors when it cannot convert entries like "4/34/5" or "Feb-31" to valid dates....

- Jeff
http://weblogs.sqlteam.com/JeffS

Trudye
Posting Yak Master

169 Posts

Posted - 2008-09-25 : 06:14:44

Thanks to all who responded with helpful suggestions. Here is how I got it to work:
First I created a pass-thru query to get the max date (named Last_RUn_Date) from the table, subtract -33 from the max date (named Begin_date). I input the pass-thru into the following query and VOILA!!

DateSerial(Right([Coverage_Effective_Date],4),Left([Coverage_Effective_Date],2),Mid([Coverage_Effective_Date],3,2))) Between [Begin_Date] And [Last_Run_Date])

jsmith8858 maybe you can help me with this. I tried every conceivable way to use CDATE. I didn't get an error msg but the output had ERROR in the column. I also tried converting the date to integer (CINT) which was fine. But still I recieved ERROR in the column when I tried to use the CDATE function. My date format was yyyymmdd so I didn't have the problem you eluded to ("4/34/5" or "Feb-31").

Thanks again,
Trudye

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources