SQL Statement Problems

Results 1 to 2 of 2

Thread: SQL Statement Problems

  1. #1
    Join Date
    Dec 1969

    Default SQL Statement Problems

    Here is the Error I am receiving....<BR><BR>Microsoft JET Database Engine error &#039 80040e14&#039 <BR>Division by zero <BR>/timeclock/successful_clockinout.asp, line 92 <BR><BR>Line 92 is...<BR>Set objRS = objComm.Execute<BR><BR><BR>So I know it is referring to my SQL Statement. Here is my SQL Statement<BR>strSQL = "SELECT * FROM timeclockdb WHERE empid=" & session("empid") <BR>strSQL = strSQL & " AND datein="<BR>strSQL = strSQL & formatdatetime(NOW,vbshortdate)<BR>objComm.Command Text = strSQL<BR><BR>The debugging I have done.....<BR>1. It works fine if the SQL Statement is....<BR><BR>strSQL = "SELECT * FROM timeclockdb WHERE empid=" & session("empid") <BR>objComm.CommandText = strSQL<BR><BR>2. The Problem is when I add this part of the conditonal part of the statement.....<BR><BR>strSQL = strSQL & " AND datein="<BR>strSQL = strSQL & formatdatetime(NOW,vbshortdate)<BR><BR>Why is it dividing? What am I doing worng.<BR><BR>All my fields in the DB are set correctly. FYI: The fields which are being used in this SQL statement (empid and datein)<BR>empid is a number type in the Access DB I am using, and datein is a Date/Time Type in the Access DB I am using.<BR><BR>HELP<BR><BR>SKOOTER<BR><BR>

  2. #2
    Join Date
    Dec 1969

    Default RE: SQL Statement Problems

    Hi,<BR><BR>Sounds like you&#039re just missing a couple of niggly little hash signs. The last bit of you SQL statment, when build, would read something like - &#039... AND datein=09/31/00&#039 which the Jet Engine is taking to be a numeric division expression (&#039/&#039 is the divide sign). You need to tell it that you actually mean it to be a date, like this...<BR><BR>strSQL = strSQL & " AND datein=#"<BR>strSQL = strSQL & formatdatetime(NOW,vbshortdate) & "#"<BR><BR>Note the hash signs on either side of the date.<BR>Hope that works.<BR><BR>Jerome.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts