[RESOLVED] Very Strange Select Error

[whythetorment]

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

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

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.

[dee-u]

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

[whythetorment]

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.

[whythetorment]

nope same result

[dee-u]

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

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"

[whythetorment]

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:

" 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.

[whythetorment]

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

[whythetorment]

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