Access + Dates = Nightmare

  • Thread starter Thread starter Joe bohen
  • Start date Start date
J

Joe bohen

I call the following function from frmdate, the date
txtDateShort is placed on the form when the user selects a
date from a calendar. When the function gets to the find
criteria it calls the error "arguments are of the wrong
type, are out of acceptable range, or are in conflict with
one another" vardate returns the value #30/04/2003# and
the query field date is formatted as a short date.

Where am I going wrong, I have used other fields in the
query as the find criteria and the function works fine?

Any help would be appreciated.


Dim cnThisConnect As ADODB.Connection
Dim rcdsubbies As New ADODB.Recordset
Dim vardate As Variant


vardate = Format([Forms]![frmdate]!
[txtDateShort], "\#dd\/mm\/yyyy\#")
Set cnThisConnect = CurrentProject.Connection

rcdsubbies.Open "qrydelete", cnThisConnect,
adOpenKeyset, adLockOptimistic, adCmdTable
rcdsubbies.MoveFirst
rcdsubbies.Find "[date] < #" & vardate & "#"
 
Literal dates in SQL clauses must be formatted American.

Try:
Const conJetDate = "\#mm\/dd\/yyyy\#
rcdsubbies.Find "[date] < " & Format(Forms!frmdate.Form!txtDateShort,
conJetDate)

After 10 years working in Access in a country that uses dd/mm/yyyy, our
experience is summarized in this article:
International Dates in Access
at:
http://allenbrowne.com/ser-36.html

For an explanation of the ".Form" bit in the string above, see:
http://allenbrowne.com/casu-04.html
 
Many thanks for your reply. I have incorporated your
suggestion and the function appears to work most of the
time. But unfortunately not all the time. I have processed
some trial data and run the function and all seems to work
and then for no apparent reason the records although
falling before the set date are not deleted. Would you
please have a look at the complete code. Any help again
much appreciated.

Joe

Public Function update()
Dim cnThisConnect As ADODB.Connection
Dim rcdsubbies As New ADODB.Recordset
Const conJetDate = "\#mm\/dd\/yyyy\#"

Set cnThisConnect = CurrentProject.Connection
rcdsubbies.Open "qrydelete", cnThisConnect,
adOpenKeyset, adLockOptimistic, adCmdTable
loop1:
rcdsubbies.MoveFirst
rcdsubbies.Find "[date] < " & Format(Forms!
frmdate.Form!txtDateShort, conJetDate)
If Not rcdsubbies.EOF Then
rcdsubbies.Delete
If rcdsubbies.RecordCount >= 1 Then
GoTo loop1
Else

End If
End If
End Function
 
Why not use a SQL statement to do the deletions in one hit? Perhaps you are
using a join for which it doesn't work but I'd have thought that you
couldn't use a recordset.delete either.

I always use dates in the form dd-mmm-yyyy because of the problems involved
with mm/dd - dd/mm confusion.

declare variables

datDate = Forms!frmdate.Form!txtDateShort
strSQL = "Delete * From Query Where [Date] < " * Format(datDate,
"\#dd-mmm-yyyy\#)
CurrentProject.Connection.Execute strSQL
 
1. If you really do have a field named "date", I would strongly recommond
renaming it. Date is a reserved word in VBA - for the system date, and could
be a source of confusion. Unfortunately, this means tracing the queries,
forms, report and code that refer to the name.

2. If txtDateShort on the subform is unbound (i.e. not bound to a field of
type Date/Time), be sure to set its Format property to "Short Date" or
similar so Access understands the data type correctly.

3. Identify the records that are NOT being correctly selected, and see
what's different about them. For example, check their year with 4 digits in
case they are the wrong century.

4. Instead of a loop, just select the undesirable records and execute a
delete:

CurrentProject.Connection.Execute _
"DELETE FROM qrydelete WHERE [Date] < " & _
Format(Forms!frmdate.Form!txtDateShort, conJetDate) & ";"
 
Thanks for the support. Having changed the date field
name, the function works perfectly.
Regards
Joe
 
Thanks for your imput. Having changed the date field name,
the function works perfectly.
Regards
Joe
 
Back
Top