JohnDominguez
asked on
How to compare Dates from an ACCESS 2003 database field in an SQL statement Using VB/ASP
I would like to delete all those records in an Access database that are 2 days from today.
So I have this code to get the current date and 2 days in the future.
datCurrent = Date()
datFuture = DateAdd("d",2,datCurrent)
In my Access database I have a field called pick_up_dater, which when creating the database it was defined as a date/time field formatted as a short date (such as 2/11/2009 for example)
later on in a different ASP page I try to do the deletion as follows
Set Con = Server.CreateObject( "ADODB.Connection" )
Con.Open "united2k9"
sqlString = "DELETE FROM rappel " & "WHERE pick_up_dater = " & datFuture
Set recordSet=Con.Execute(sqlS
The code does not generate any error but no deletions take place.
I assume it is because somehow pick_up_dater is not exactly the same to datFuture
The data for the date that is stored in the database is gathered from a form where the user chooses the month, day and time seperatly.
I then string the data together as follows after requesting the values from the previous form that collected them
pudate=PMM & "/" & Pick_Up_Day & "/" & Pick_Up_Year
finally I insert it into the database with the following code
Set Con = Server.CreateObject( "ADODB.Connection" )
Con.Open "united2k9"
sqlString = "INSERT INTO rappel " & _
"(res_req_type, res_req_nmr, namer, emailr, car_categoryr, pick_up_dater, pick_up_timer, pick_up_location, shuttle, drop_off_location, Drop_off_date, drop_off_tme, gpsr, daysr, dailyr, weeklyr, monthlyr ) VALUES (" &_
" '" & rectype & "', " & _
" '" & x2 & "', " & _
" '" & name & "', " & _
" '" & email & "', " & _
" '" & car_category & "', " & _
" '" & pudate & "', " & _
" '" & putime & "', " & _
" '" & pul & "', " & _
" '" & Shuttle_Request & "', " & _
" '" & dol & "', " & _
" '" & rdate & "', " & _
" '" & rtime & "', " & _
" '" & gps & "', " & _
" '" & days1 & "', " & _
" '" & Daily & "', " & _
" '" & Weekly & "', " & _
" '" & Monthly & "' )"
Con.Execute sqlString
Con.Close
Set Con = Nothing
So as far as I can tell all the data fields are holding plain text!
Is this where my error comes in?
Is the databse not storing pick_up_dater as a short date?
If that is the case how do I make it be a date format instead of plain text so that the WHERE clause sees two dates to compare ?
Or is there a time value that I am not seeing that is making the WHERE clause see a date AND a time that of course would not be the same as the datFuture to which it is being compared to?
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
DELETE FROM rappel " & "WHERE pick_up_dater = convert(datetime, '"&datFuture&"', 120)
This should do fro the delete
This should do fro the delete
ASKER
Thanks Capricorn!, It was a matter of seconds and you had it right!
You had helped me before but I messed-up the points for you.
You had helped me before but I messed-up the points for you.
ASKER
WOW you are good Capricorn1 - THANK you so much!
Experts Exchange ROCKS!
Experts Exchange ROCKS!
ASKER
Thank you isaackhazi: I will explore your suggestion also - but Capricorn1's answer worked before I saw your response.
Cheers!
Cheers!
date variables needs to be wrap by #
sqlString = "DELETE * FROM rappel WHERE pick_up_dater = #" & datFuture &"#"
Set recordSet=Con.Execute(sqlS