We help IT Professionals succeed at work.

We've partnered with Certified Experts, Carl Webster and Richard Faulkner, to bring you two Citrix podcasts. Learn about 2020 trends and get answers to your biggest Citrix questions!Listen Now

x

How to compare  Dates from an ACCESS 2003 database field in an SQL statement Using VB/ASP

Medium Priority
464 Views
Last Modified: 2012-05-06

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?





Comment
Watch Question

CERTIFIED EXPERT
Top Expert 2016
Commented:

date variables needs to be wrap by #

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

Not the solution you were looking for? Getting a personalized solution is easy.

Ask the Experts
CERTIFIED EXPERT
Top Expert 2016

Commented:
or

date variables needs to be wrap by #

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

This should do fro the delete

Author

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.

Author

Commented:
WOW you are good Capricorn1 - THANK you so much!
Experts Exchange ROCKS!

Author

Commented:
Thank you isaackhazi: I will explore your suggestion also - but Capricorn1's answer worked before I saw your response.
Cheers!
Access more of Experts Exchange with a free account
Thanks for using Experts Exchange.

Create a free account to continue.

Limited access with a free account allows you to:

  • View three pieces of content (articles, solutions, posts, and videos)
  • Ask the experts questions (counted toward content limit)
  • Customize your dashboard and profile

*This site is protected by reCAPTCHA and the Google Privacy Policy and Terms of Service apply.

OR

Please enter a first name

Please enter a last name

8+ characters (letters, numbers, and a symbol)

By clicking, you agree to the Terms of Use and Privacy Policy.