Tek-Tips is the largest IT community on the Internet today!

Members share and learn making Tek-Tips Forums the best source of peer-reviewed technical information on the Internet!

  • Congratulations Rhinorhino on being selected by the Tek-Tips community for having the most helpful posts in the forums last week. Way to Go!

How to subtract days from a date 1

Status
Not open for further replies.

mkohl

Programmer
Feb 22, 2005
82
US
Ok, I am dumbfounded by this. I had it working before but my toddler crashed my harddrive and all my programmers were not retrievable(oops no backups).

Problem: Im updating columns in a microsoft access database. After every 90 days I want to reprocess the reocords in a table. And for some reason my query statement is not pulling out/updating the correct records.

I had it working before my computer crashed but now I am trying something alittle different since I dont remember exactly what my query statement was before.

'declare varaibles
Dim myquery As String
Dim NintyDayDate As Date

'initialize variables
NintyDayDate = Date.Now.Today.AddDays(-90)

'change code to 'N' if the last email was sent
'over 90 days ago, for preprocessing

myquery = "update companyinfo set Code = 'N' where [Date] <= '" & NintyDayDate & "'"

I've tested this several times changing dates to the year 2004. but it doesnt seem to work. When I previously programmed this I did all my calculations in the query statement instead of creating a variable NinetyDayDate, but I just cant seem to make it happen.

 
That will not work because inside my database the field [Date] has the format of '6/2/2005' so if I use Date.Now.Add(-90) it will give me Time and Date.

I tried it and it will not work.
 
I think this is right
NintyDayDate = Date.Now.AddDays(-90)



Try Changing this
myquery = "update companyinfo set Code = 'N' where [Date] <= '" & NintyDayDate & "'"

To

myquery = "update companyinfo set Code = 'N' where [Date] <= #" & NintyDayDate.ToShortDateString & "#"
 
To remove even more lines of code...

myquery = "update companyinfo set Code = 'N' where [Date] <= #" & Date.Now.AddDays(-90)
.ToShortDateString & "#"

 
or , once again (i'm starting to sound boring), USE PARAMETERS.

Christiaan Baes
Belgium

If you want to get an answer read this FAQ faq796-2540
There's no such thing as a winnable war - Sting
 
Status
Not open for further replies.

Part and Inventory Search

Sponsor

Back
Top