[RESOLVED] Very Strange Select Error

guys i have a strange error....ok well i know what the error means but dont why its happening. my code is:

VB Code:

sstr = "select VideoTape.DateAdded, VideoTape.Issues, VideoTape.Barcode, VideoBase.Title from VideoBase, VideoTape " & _
           "Where VideoBase.Code = VideoTape.Base " & _
           "AND DateValue(VideoTape.DateAdded) Between " & "#" & DateValue(aCrit1) & "#" & _
           " AND " & "#" & DateValue(aCrit2) & "#" & _
           " AND VideoTape.Issues <= " & aCrit3 & _
           " AND VideoTape.Deleted = No" & _
           " AND VideoBase.Deleted = No" & _
           " Order By VideoTape.DateAdded"

then to execute it i say

VB Code:

Set rsx = Datacc.GetVideoIssues(DTPicker1.Value, DTPicker2.Value, Text1.Text)

simply enough yes? yes......now dtpicker1 and 2's values are fine. text1 is what i use to specify the issue amounts. now if i enter values 1, 2, 3 etc. to 7 it works perfectly. then i enter value 8 and it gives me a Data Type Mismatch error in criteria expression. then i enter 9 - whatever and it also works perfectly.

anyone know why it bombs out only on 8? i checked in the database and there are issues that went out 8 times. so its very very strange.

Is this Access? If yes then have you tried Compact and Repair already?

yes its access.....no i havent tried it yet, will try it with a copy of database coz other users are using the db at the moment.

nope same result

I'm not sure but how about checking for the value of aCrit3 before the sql?

VB Code:

Msgbox aCrit3
sstr = "select VideoTape.DateAdded, VideoTape.Issues, VideoTape.Barcode, VideoBase.Title from VideoBase, VideoTape " & _
           "Where VideoBase.Code = VideoTape.Base " & _
           "AND DateValue(VideoTape.DateAdded) Between " & "#" & DateValue(aCrit1) & "#" & _
           " AND " & "#" & DateValue(aCrit2) & "#" & _
           " AND VideoTape.Issues <= " & aCrit3 & _
           " AND VideoTape.Deleted = No" & _
           " AND VideoBase.Deleted = No" & _
           " Order By VideoTape.DateAdded"

msgbox returns 8....i checked the db and it is declared a Long Integer, and i declared aCrit3 as long as well. even tried Trim(aCrit3). then i added this to the code:

VB Code:

" AND VideoTape.Issues >= " & aCrit3 & _
" AND VideoTape.Issues <= " & aCrit4 & _

and searched between 9 and 10000000 with no error what so ever. have no idea why its doing dat.

maybe its not issues giving the problem....gonna see if all date's added has a value in it......

hehehe thats just great!......one empty dateadded field and wouldnt you know it....went out 8 times!