Problems with my SQL in code.

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

I have an SQL statement that gets some of its values from a form and some
from a variable in the code. the statement is as follows:

SELECT tblPublicTaken.EmpNo, tblPublicTaken.Year, tblPublicTaken.Holiday,
tblPublicTaken.ActualDate, tblPublicTaken.TakenOnDay, tblPublicTaken.Lieu
FROM tblPublicTaken WHERE (((tblPublicTaken.EmpNo) =16) And
((tblPublicTaken.ActualDate) = #25/12/06#))

The number 16 comes from a variable and the date comes from a field on a
form. The actual statement is:

strPublic = "SELECT tblPublicTaken.EmpNo, tblPublicTaken.Year,
tblPublicTaken.Holiday, tblPublicTaken.ActualDate, tblPublicTaken.TakenOnDay,
tblPublicTaken.Lieu FROM tblPublicTaken WHERE (((tblPublicTaken.EmpNo) =" &
intEmpNo & ") And ((tblPublicTaken.ActualDate) = #" &
[Forms]![frmDepartmentUpdate].[Text0] & "#))"

But for some reason this dosn't work. I tried cutting the value and pasting
it directly into the SQL builder of a normal Access query. The first
statement above is how it looks in the SQL view of the query, but if I change
the view to design the date changes from #25/12/06# to #06/12/25#

Why is this happening? The field on the form is not linked to a field.
 
Regardless of what your short date format has been set to in Regional
Settings, Access wants dates in mm/dd/yyyy format. (Okay, this isn't
strictly true: it's okay with unambiguous formats such as yyyy-mm-dd or dd
mmm yyyy. The point is, it's not going to treat dd/mm/yyyy correctly for the
first 12 days of each month)

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