Problem deleting using in Access using VB/SQL

[james]

greetings. when i run this code and theTime falls in 2007 (ie. user
input is 1, 2, or 3) the program will delete the given number of days
only (not months).
Using msgbox, I've found that theTime is correct after the DateAdd
function, but deletion using SQL is wrong, very wrong. (I'm in
Australia so our date format is dd/mm/yyyy).

Dim userMonths As Integer
Dim nowTime As String
Dim theTime As String
Dim delTime As Date
'get user input on how many months in the past they wish to collect
records for
userMonths = InputBox("How many months do you wish to collect records
for? (1-48)", "SCIA Record Collector")
userMonths = userMonths * -1
'set the current time
nowTime = Now
theTime = DateAdd("m", userMonths, nowTime)
'delete records before the current time
DoCmd.RunSQL "DELETE * FROM POSITION WHERE ([PROCDATE] < " & "#" &
delTime & "#" & ")"

example of PROCDATE: 4/04/2007 9:41:26 AM

Cheers in advance!
James.

 

[james]

after doing a few experiments with SQL window in query, i've
discovered the program seems to be swapping months with days in some
instances... any fixes?

 

[james]

more searching reveals this about Australian date/s format/s...
http://groups.google.com.au/group/m...+days+confusion&rnum=1&hl=en#df6a6e9d72bedae9

 

[james]

none of the other threads really answered my question too well.
i've learnt:

1) if you have a database with existing dd-mm-yyy format, don't
despair!
a) ensure the date column of your database has "Field Type" of
"Date" in design view
b) set variables similar to x=Day(Now), y=Month(Now), z=Year(Now)
c) then you can create a date as newDate = z & "/" & y & "/" & x
--> gives you the format yyyy/mm/dd
d) now do your query and it will work! example --> DoCmd.RunSQL
"DELETE * FROM POSITION WHERE ([Date_Field] < " & "#" & newDate & "#"
& ")"

2) if you're building a new database use a format such as yyyy-mm-dd

 

[RoyVidar]

none of the other threads really answered my question too well.
i've learnt:

1) if you have a database with existing dd-mm-yyy format, don't
despair!
a) ensure the date column of your database has "Field Type" of
"Date" in design view
b) set variables similar to x=Day(Now), y=Month(Now),
z=Year(Now) c) then you can create a date as newDate = z & "/" &
y & "/" & x --> gives you the format yyyy/mm/dd
d) now do your query and it will work! example --> DoCmd.RunSQL
"DELETE * FROM POSITION WHERE ([Date_Field] < " & "#" & newDate & "#"
& ")"

2) if you're building a new database use a format such as yyyy-mm-dd

The format of the date in the table, doesn't really matter, as long as
it is a Date/Time field. What matters, is that when you feed a dynamic
SQL string to the Jet engine, any date litterals needs to be in a
format the Jet engine understands. Among the safe formats for Access,
are the ISO 8601 date format "yyyy-mm-dd" and a US format
#mm\/dd\/yyyy".

For more information, have a look at http://allenbrowne.com/ser-36.html

So, from your first post (where delTime is a valid date), you could use
for instance

....WHERE [PROCDATE] < #" & format$(delTime, "yyyy-mm-dd") & "#"

or

....WHERE [PROCDATE] < #" & format$(delTime, "mm\/dd\/yyyy") & "#"

Allen Brownes article contains more explanations

 

[Tony Toews [MVP]]

DoCmd.RunSQL "DELETE * FROM POSITION WHERE ([PROCDATE] < " & "#" &
delTime & "#" & ")"

SQL statements require that the dates be either completely unambiguous
or in mm/dd/yy, or mm/dd/yyyy format. Otherwise Access/Jet will do
it's best to interpret the date with unknown results depending on the
specific date it is working with. You can't assume that the system
you are working on is using those date formats. Thus you should use
the logic at the following web page.
Return Dates in US #mm/dd/yyyy# format
http://www.mvps.org/access/datetime/date0005.htm

Also currentdb.execute will return error messages which docmd.runsql
won't. With a bit of jiggery pokery you can figure out how many
records the query actually did the action on. Finally performance
can be significantly different between the two methods. One posting
stated currentdb.execute took two seconds while docmd.runsql took
eight seconds. As always YMMV.

Tony
--
Tony Toews, Microsoft Access MVP
Please respond only in the newsgroups so that others can
read the entire thread of messages.
Microsoft Access Links, Hints, Tips & Accounting Systems at
http://www.granite.ab.ca/accsmstr.htm