Want to protect your cyber security and still get fast solutions? Ask a secure question today.Go Premium

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 432
  • Last Modified:

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?





0
JohnDominguez
Asked:
JohnDominguez
  • 3
  • 2
1 Solution
 
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
Restore individual SQL databases with ease

Veeam Explorer for Microsoft SQL Server delivers an easy-to-use, wizard-driven interface for restoring your databases from a backup. No expert SQL background required. Web interface provides a complete view of all available SQL databases to simplify the recovery of lost database

 
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

Featured Post

Get expert help—faster!

Need expert help—fast? Use the Help Bell for personalized assistance getting answers to your important questions.

  • 3
  • 2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now