[RESOLVED] Method 'Value' of Object 'Field' failed

Ive been looking and testing my code for hours and hours now and cant figure out whats wrong...
i found out it's coz of rsx!DateReturned for some reason
ive got this

VB Code:

Dim days as long
 
[COLOR=RoyalBlue]days = DateDiff("d", rsx!DateReturned, rsx!DateDueBack)[/COLOR]

to get rsx i use

VB Code:

sstr = "Select * From Copies, Video, PriceGroup, Client, HireOut " & _
            "Where Copies.CPVideoID = Video.VideoID " & _
            "AND Video.VIDPGID = PriceGroup.PGID " & _
            "AND HireOut.HRTapeID = Copies.TapeID " & _
            "AND HireOut.HRClientID = Client.ClientID " & _
            "AND HireOut.LateCharged = 'No' " & _
            "AND Client.ClientID = " & acrit1

my system date is set to dd/MM/yyyy format and in the database (access) ive set it to dd mmm yyyy hh:nn:ss.....every other query ive used involving dates and the above formats works fine, but for some reason im getting an error on this one....i have no empty fields in DateReturned, and DateReturned is only field in my entire database that is named that?

Any ideas? i get the error on the blue line

try this....

VB Code:

If isDate(rsx!DateReturned) Then
    days = DateDiff("d", Cdate(rsx!DateReturned), Cdate(rsx!DateDueBack))
Else
    Msgbox rsx!DateReturned
End If

ok it says now Application-defined or object-defined error and it highlights Msgbox rsx!DateReturned

ok, post more of your code.. it sounds as if rsx isnt even declared...or set to a recordset.

What if you don't include the field in the msgbox statement. Try just a literal and see if it works.

VB Code:

Dim rsx As New ADODB.Recordset
 
Set rsx = Datacc.GetHireOutCopies2(Text2.Text)

VB Code:

Public Function GetHireOutCopies2(ByVal acrit1 As Long) As ADODB.Recordset
Dim rsx As New ADODB.Recordset
Dim sstr As String
 
rsx.CursorLocation = adUseClient
 
sstr = "Code given at post # 1
 
rsx.Open sstr, DB
 
Set GetHireOutCopies2 = rsx
 
Set rsx = Nothing
 
End Function

for fun, change the rsx in this to just rs.
u are already using rsx, even though I dont think it should interfere...
just curious

VB Code:

Public Function GetHireOutCopies2(ByVal acrit1 As Long) As ADODB.Recordset
Dim rs As New ADODB.Recordset
Dim sstr As String
 
rs.CursorLocation = adUseClient
 
sstr = "Code given at post # 1
 
rs.Open sstr, DB
 
Set GetHireOutCopies2 = rs
 
Set rs = Nothing
 
End Function

i think i fixed it...i decided to copy one of the dates from the DateDueBack field into all the DateReturned fields, and it goes through the code without problem now....maybe one of the values was corrupt or something...

thanx for the help :)