Tek-Tips is the largest IT community on the Internet today!

Members share and learn making Tek-Tips Forums the best source of peer-reviewed technical information on the Internet!

  • Congratulations biv343 on being selected by the Tek-Tips community for having the most helpful posts in the forums last week. Way to Go!

Work Days -"Object vairiable or With Block variable not set"

Status
Not open for further replies.

carlp69

IS-IT--Management
Sep 23, 2003
157
GB
In Access 2002, I have the following code to search a table and return a true/false result if the date 'dtedate' appears in the table.

It keeps returnig an error "Object vairiable or With Block variable not set"


Public Function IsAHoliday(ByVal dteDate As Date) As Boolean
Dim db As DAO.Database
Dim rsHoliday As DAO.Recordset
Set db = CurrentDb()
Set rsHoliday = db.OpenRecordset("SELECT holidayDate From tbl_PublicHolidays WHERE HolidayDate = #'" & dteDate & "'#;")

IsAHoliday = False
MsgBox (dteDate)
With rsHoliday
If .EOF Then
Exit Function
Else
IsAHoliday = True
End If
End With

rsHoliday.Close
Set rsHoliday = Nothing
End Function

Can anybody help!
 
Hi carlp69,

What line are you getting the error on?

The only thing I can see which is obviously wrong is that you have extra single quotes around your date literal. It should be ..

... = #" & dteDate & "#;")

and not ..

... = #'" & dteDate & "'#;")

Enjoy,
Tony
 
I think the problem is in your SQL statement : try taking out the single quotes around the date :
("SELECT holidayDate From tbl_PublicHolidays WHERE HolidayDate = #" & dteDate & "#")
 
dteDate = Date
? dteDate
10/9/03


#'" & dteDate & "'#;") ==> & dteDate & ";"





MichaelRed
m.red@att.net

Searching for employment in all the wrong places
 
I have tried both with and without the single quotes.

The error occurs on the Set rsHoliday = db.OpenRecordset.... Line
 
Hi carlp69,

Sorry, can't see anything else. It works (with the quotes removed) for me in 2000; over to someone with 2002.

Enjoy,
Tony
 
Tried change dtedate to string, still no joy.

Any more ideas?
 
Code:
Public Function IsAHoliday(ByVal dteDate As Date) As Boolean

    Dim db As DAO.Database
    Dim rsHoliday As DAO.Recordset
    Dim strSQL As String

    strSQL = "SELECT HoliDate "
    strSQL = strSQL & "From tblHolidays "
    strSQL = strSQL & "WHERE HoliDate = #" & dteDate & "#;"

    Set db = CurrentDb()
    Set rsHoliday = db.OpenRecordset(strSQL)
    
    IsAHoliday = False
    MsgBox (dteDate)

    With rsHoliday
        If (.EOF) Then
            Exit Function
        Else
            IsAHoliday = True
        End If
    End With
    
    rsHoliday.Close
    Set rsHoliday = Nothing
End Function


MichaelRed
m.red@att.net

Searching for employment in all the wrong places
 
This is strange!

I can get the code to work if I start from scratch, but does not work in my current project.

Is it something to do with the fact I am using an SQL server project rather than a Access .MDB database?

Carl
 
Hi Carl,

I might be completely wrong with this but should you be using DAO definitions with SQL Server?

Enjoy,
Tony
 
How else can i write the code to produce the same result?
 
set up an SQL server ODBC connection in place of the DAO.Recordset.


or -use a LINK to the SQL Server Table.




MichaelRed
m.red@att.net

Searching for employment in all the wrong places
 
PROBLEM SOLVED
I have solved the problem with the following code :-

Public Function IsAHoliday(ByVal dteDate As String) As Boolean
Dim cn As ADODB.Connection
Dim rs As New ADODB.Recordset
Dim sql As String
IsAHoliday = False
Set cn = CurrentProject.Connection
sql = "SELECT HolidayDate FROM tbl_Holidays"
rs.Open sql, cn
rs.MoveFirst
Do While Not (rs.EOF)
If dteDate = rs!HolidayDate Then IsAHoliday = True
rs.MoveNext
Loop
rs.Close
cn.Close
End Function
 
Status
Not open for further replies.

Part and Inventory Search

Sponsor

Back
Top