Problem with sql statement in VB

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

Guest

I'm trying to pull some records from a table based on two criteria as follows.

SQLText = "Select EmployeeID, Date, Status " & _
"From TimeTable " & _
"Where EmployeeID = '" & UD.EmployeeID & "' " & _
"And Date = '" & SysDate & "' " & _
"And Status = Yes;"

The Date field in the table is a date format (short date) and the variable
(sysdate) local to the vb application has been dimensioned as a date
variable. When running the statement gives me a data type mismatch error in
criteria statement. I know the error isn't getting caught up on the
employeeID part of the statement because if I change the Date field data type
in the Access table to a Text format this same SQL statement works just fine.
But I need the Date field in the table to be a date format for later SQL
comparisons and data filtering.
 
First, don't use Date (or other reserved words) as the name of a field in a
table. You will confuse ACCESS about whether you mean a field or the VBA
function Date. See KB article number 286335 for more info:
ACC2002: Reserved Words in Microsoft Access
http://support.microsoft.com/default.aspx?scid=kb;en-us;286335

If you must use such names, then those names must be enclosed with [ ]
characters to help ACCESS avoid confusion (though this isn't guaranteed).

Second, how the date field is formatted has no effect on how the data
actually are stored in the field. Date/time fields contain a decimal number.
Dates are stored as double precision numbers, where the integer portion is
the number of days since midnight, December 30, 1899, and the fraction part
is the fraction of a 24-hour day represented by the time.

Thus, if, for example, your field contains the value for January 1, 2004
8:00:00 AM, the actual data value is 37987.3333333333, while if you store
just the date of January 1, 2004 (no time component), the data value is
37987.

Thus, in a query, you must tell ACCESS that you want to compare a date value
(decimal) to the field. You do this by delimiting a date string with #
characters (if the date string is in US format: mm/dd/yyyy).

So, try this:

SQLText = "Select EmployeeID, [Date], Status " & _
"From TimeTable " & _
"Where EmployeeID = '" & UD.EmployeeID & "' " & _
"And [Date] = #" & SysDate & "# " & _
"And Status = Yes;"
 
Ken,
Thanks so much for the reply. The information you provided taught me some
stuff that I have neglected to learn or pick up on until now. Now a bunch of
other problems I've had with dates make more sense. This has solved my
immediate problems as well. Thank you again.

Ken Snell said:
First, don't use Date (or other reserved words) as the name of a field in a
table. You will confuse ACCESS about whether you mean a field or the VBA
function Date. See KB article number 286335 for more info:
ACC2002: Reserved Words in Microsoft Access
http://support.microsoft.com/default.aspx?scid=kb;en-us;286335

If you must use such names, then those names must be enclosed with [ ]
characters to help ACCESS avoid confusion (though this isn't guaranteed).

Second, how the date field is formatted has no effect on how the data
actually are stored in the field. Date/time fields contain a decimal number.
Dates are stored as double precision numbers, where the integer portion is
the number of days since midnight, December 30, 1899, and the fraction part
is the fraction of a 24-hour day represented by the time.

Thus, if, for example, your field contains the value for January 1, 2004
8:00:00 AM, the actual data value is 37987.3333333333, while if you store
just the date of January 1, 2004 (no time component), the data value is
37987.

Thus, in a query, you must tell ACCESS that you want to compare a date value
(decimal) to the field. You do this by delimiting a date string with #
characters (if the date string is in US format: mm/dd/yyyy).

So, try this:

SQLText = "Select EmployeeID, [Date], Status " & _
"From TimeTable " & _
"Where EmployeeID = '" & UD.EmployeeID & "' " & _
"And [Date] = #" & SysDate & "# " & _
"And Status = Yes;"

--

Ken Snell
<MS ACCESS MVP>

Corey said:
I'm trying to pull some records from a table based on two criteria as follows.

SQLText = "Select EmployeeID, Date, Status " & _
"From TimeTable " & _
"Where EmployeeID = '" & UD.EmployeeID & "' " & _
"And Date = '" & SysDate & "' " & _
"And Status = Yes;"

The Date field in the table is a date format (short date) and the variable
(sysdate) local to the vb application has been dimensioned as a date
variable. When running the statement gives me a data type mismatch error in
criteria statement. I know the error isn't getting caught up on the
employeeID part of the statement because if I change the Date field data type
in the Access table to a Text format this same SQL statement works just fine.
But I need the Date field in the table to be a date format for later SQL
comparisons and data filtering.
Click to expand...
Click to expand...
 
Back
Top