Recordset Date Problem

[JFK]

Hi,

This is a bit of a strange one. We have a recordset that takes a name
and a date referenced from another form as parameters. Last week this
worked fine and then this morning for reasons that we can't fathom it
errors with no current record.

The code extract is:

x = Forms![choose dates]![Text1].Value
y = Forms![choose dates]![Text3].Value

sql = "SELECT activities.date, activities.staff_name,
activities.[activity start], activities.[activity end],
activities.description FROM activities WHERE (((activities.date) = #" &
x & "#) And ((activities.staff_name) = '" & y & "')) ORDER BY
activities.[activity start], activities.[activity end];"

I think it's something to do with the date parameter as we have a
DCOUNT function elsewhere that has also started playing up this
morning.

Any suggestions?!?!?

 

[Douglas J Steele]

First of all, you shouldn't use Date as a field name: that's a reserved
word. If you cannot change the field, at least put square brackets around
it, to help Access figure out that it's a field:

SELECT activities.[date] ... WHERE (((activities.[date]) = #" & ...

I just noticed your e-mail address (you REALLY shouldn't post real addresses
in newsgroups: you're going to be inundated with Spam now). Doubtlessly your
Short Date format is set to dd/mm/yyyy. Access doesn't work with that
format. Last week, when you had dates like 28/03/2006, Access was smart
enough to realize that there is no 28th month, so it correctly treated that
as 28 March, 2006. Today, Access is going to treat 03/04/2006 as 04 March,
2006, regardless of your Regional Settings. Fortunately, it's
straight-forward to handle this: simply format your dates as mm/dd/yyyy:

....WHERE (((activities.[date]) = " & Format(x, "\#mm\/dd\/yyyy\#") & ")...

Also, how are you populating the field? If it contains time as well as date
(such as it will if you're using the Now() function to populate it), you
won't be able to check for equality to a date. That's because Dates are
stored as 8 byte floating point numbers, where the integer portion
represents the date as the number of days relative to 30 Dec, 1899, and the
decimal portion represents the time as a fraction of a day. Today (03 Apr,
2006) is 38810. 6:00 AM today would be 38810.25, Noon today would be 38810.5
and so on. If activities.[date] is noon today, then checking for equality to
Date() (which is 38810) won't yield anything.

You can use either

....WHERE (((activities.[date]) BETWEEN " & Format(x, "\#mm\/dd\/yyyy\#") &
" AND " & Format(DateAdd("d", 1, x), "\#mm\/dd\/yyyy\#") & ") ...

or

....WHERE (((DateValue(activities.[date])) = #" & x & "#) ...

(The former is more efficient, especially with large tables, since Access is
smart enough to know that it only has to execute the DateAdd function once,
as opposed to having to apply the DateValue to each row in the table)

You might find it worthwhile to read Allen Browne's "International Dates in
Access" at http://allenbrowne.com/ser-36.html or what I had in my September
2003 Access Answers column for Pinnacle Publication's "Smart Access"
newsletter. (The column and accompanying database can be downloaded for free
at http://www.accessmvp.com/djsteele/SmartAccess.html)

--
Doug Steele, Microsoft Access MVP

(no e-mails, please!)

Hi,

This is a bit of a strange one. We have a recordset that takes a name
and a date referenced from another form as parameters. Last week this
worked fine and then this morning for reasons that we can't fathom it
errors with no current record.

The code extract is:

x = Forms![choose dates]![Text1].Value
y = Forms![choose dates]![Text3].Value

sql = "SELECT activities.date, activities.staff_name,
activities.[activity start], activities.[activity end],
activities.description FROM activities WHERE (((activities.date) = #" &
x & "#) And ((activities.staff_name) = '" & y & "')) ORDER BY
activities.[activity start], activities.[activity end];"

I think it's something to do with the date parameter as we have a
DCOUNT function elsewhere that has also started playing up this
morning.

Any suggestions?!?!?

 

[JFK]

Thanks for the help.

Incidentally, how the hell do you remove your email address!!!

Only joined as I had a problem in work, don't remember seeing an option
to hide email address??

 

[Douglas J Steele]

I see you're posting through Google. I'm afraid I have no idea how to munge
your address through that interface.