Link to home
Start Free TrialLog in
Avatar of JohnDominguez
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(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?





ASKER CERTIFIED SOLUTION
Avatar of Rey Obrero (Capricorn1)
Rey Obrero (Capricorn1)
Flag of United States of America image

Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
or

date variables needs to be wrap by #

sqlString =  "DELETE * FROM rappel  WHERE pick_up_dater = #" & datFuture &"#"
 Set recordSet=Con.Execute(sqlString)
Avatar of isaackhazi
isaackhazi

DELETE FROM rappel " & "WHERE pick_up_dater =  convert(datetime, '"&datFuture&"', 120)

This should do fro the delete
Avatar of JohnDominguez

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