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(sqlString)

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?





JohnDominguezAsked:
Who is Participating?
 
Rey Obrero (Capricorn1)Commented:

date variables needs to be wrap by #

sqlString =  "DELETE FROM rappel  WHERE pick_up_dater = #" & datFuture &"#"
 Set recordSet=Con.Execute(sqlString)
0
 
Rey Obrero (Capricorn1)Commented:
or

date variables needs to be wrap by #

sqlString =  "DELETE * FROM rappel  WHERE pick_up_dater = #" & datFuture &"#"
 Set recordSet=Con.Execute(sqlString)
0
 
isaackhaziCommented:
DELETE FROM rappel " & "WHERE pick_up_dater =  convert(datetime, '"&datFuture&"', 120)

This should do fro the delete
0
Cloud Class® Course: Microsoft Exchange Server

The MCTS: Microsoft Exchange Server 2010 certification validates your skills in supporting the maintenance and administration of the Exchange servers in an enterprise environment. Learn everything you need to know with this course.

 
JohnDominguezAuthor Commented:
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.
0
 
JohnDominguezAuthor Commented:
WOW you are good Capricorn1 - THANK you so much!
Experts Exchange ROCKS!
0
 
JohnDominguezAuthor Commented:
Thank you isaackhazi: I will explore your suggestion also - but Capricorn1's answer worked before I saw your response.
Cheers!
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.