SQL Issues, I think? (Overflow, runtime 6)

baldeagle

Registered User.
Local time
Today, 15:42
Joined
Nov 9, 2004
Messages
38
Howdy,

I am attempting to create a module that will return true if the two passed values are present in a table.

Code:
tblStaurdays
------------
workorder
WorkDate

Code:
Option Compare Database
Option Explicit
Function fWorkWeekend(Workodr As Integer, Wdate As Date) As String
Dim rst As New ADODB.Recordset
rst.Open "tblSaturdays", CurrentProject.Connection
Dim strSQL As String
strSQL = "Select WorkOrder from tblSaturdays Where [workorder]='" & Workodr & "' And [workdate]= '" & "#" & Wdate & "#" & "'"
rst.Open strSQL, CurrentProject.Connection, adOpenStatic, adLockOptimistic, adCmdText 'static,optimistic,adcmdtext
If rst.EOF Then
  fWorkWeekend = "no"
Else
  fWorkWeekend = "yes"
End If
rst.Close
Set strSQL = Nothing
Set rst = Nothing
End Function

Basically this stop a.sinatra's workday function from removing certain dates. This could run many times per a calculation, and I need to minimize the time it takes to complete (Hence no D*** disfunctions).
 
Last edited:
Take out the first rst.open statement.
Also, why don't you use boolean as the return data type for the function?
Also, you don't need to set the strSQL to nothing, you only need to close the rst and set rst to nothing.
 
Last edited:
Chatbox said:
Take out the first rst.open statement.
Done. Solved that problem.
Chatbox said:
Also, why don't you use boolean as the return data type for the function?
Done
Chatbox said:
Also, you don't need to set the strSQL to nothing, you only need to close the rst and set rst to nothing.
Done.

I'm having issues with data type mismatch, when the rst.open command takes place.
Code:
Option Compare Database
Option Explicit
Function fWorkWeekend(Workodr As Long, Wdate As Date) As Boolean

Dim rst As New ADODB.Recordset
Dim strSQL As String
strSQL = "Select [WorkOrder] from tblSaturdays Where [workorder]='" & Workodr & "' And  [workdate]= '" & Wdate & "'"
rst.Open strSQL, CurrentProject.Connection, adOpenStatic, adLockOptimistic, adCmdText 'static,optimistic,adcmdtext
If rst.EOF Then
  fWorkWeekend = True
Else
  fWorkWeekend = False
End If
rst.Close
Set rst = Nothing
End Function

The table this is connecting to has the workorder set as 'long integer', and the workdate set as 'date'.
Tried it with and without # symbols around the date.
 
You don't want the single quotes around workorder (you do want the # around the date).
 
Since you are only using this to get whether there is a record or not, you don't need a static cursor and optimistic lock. Forwardonly readonly should do, and will probably speed up the operation. Or you could try the following

dim rst as adodb.recordset ' comment below
Dim strSQL As String
strSQL = "Select ..."
set rst=currentproject.connection.execute(strsql,,adcmdtext)
fWorkWeekend = rs.eof
rst.close
set rst=nothing

I prefer not declaring with the New keyword, but use either
set rst=new adodb.recordset, or using the .execute method of either a connection or a command object, as above. This is explicit instantiation, and is also supposed to be faster.

Also note pbaldy comments on the delimiters, remove from the workorder criterion, since numerics don't need delimiters, use hash (#) on the dates (though, if this is SQL server, I think single quotes on dates too).

One additional thingie on the dates, if your date settings differ from US date settings, you'll need to format the date to an unambiguous format, for instance using:

... And [workdate]= #" & format$(Wdate, "mm\/dd\/yyyy") & "#"
 

Users who are viewing this thread

Back
Top Bottom