Convert doesn't seem to work with access. I did get int to work in this case:
SELECT tblSearchLog.searchid, int(tblsearchlog.minsize) + int(tblsearchlog.maxsize) /2 FROM tblSearchLog WHERE (((tblSearchLog.MinSize)<>"") AND ((tblSearchLog.MaxSize)<>"")) but if I do this: SELECT tblSearchLog.searchid FROM tblSearchLog WHERE (((tblSearchLog.MinSize)<>"") AND ((tblSearchLog.MaxSize)<>"")) and int(tblsearchlog.minsize) + int(tblsearchlog.maxsize) /2 > 500 I get datatype mimatch in criteria expression. This is running it directly in access. I have tried a few variations like and int((int(tblsearchlog.minsize) + int(tblsearchlog.maxsize)) /2) > 500 and by grouping and using having instead of where, get the same problem. Looks like I'll have to change the data type or do some something like run a query without filtering then looping and counting all suitable records. This may actually be quicker in the long run anyway. Thanks for the help guys ----- Original Message ----- From: "Steve Martin" <[EMAIL PROTECTED]> To: <[EMAIL PROTECTED]> Sent: Friday, November 08, 2002 1:11 PM Subject: RE: [ cf-dev ] OT: casting in SQL There's also convert(int, tblsearchlog.maxsize) that has a similar effect IIRC > -----Original Message----- > From: Justin MacCarthy [mailto:macarthy@;iol.ie] > Sent: 08 November 2002 12:44 > To: [EMAIL PROTECTED] > Subject: RE: [ cf-dev ] OT: casting in SQL > > > You can cast in SQL server > > (CAST(tblsearchlog.maxsize AS int) + > CAST(tblsearchlog.minSize AS int)) /2 > > 500 > > I've sure access has something similar > > Justin > > > -----Original Message----- > > From: Giles Roadnight [mailto:giles@;roadnight.name] > > Sent: 08 November 2002 12:36 > > To: [EMAIL PROTECTED] > > Subject: [ cf-dev ] OT: casting in SQL > > > > > > Hi guys > > > > I have this query: > > > > Select count(searchid) as searches > > from tblsearchlog > > where (tblsearchlog.minsize + tblsearchlog.maxsize) /2 > 500 > > and (tblsearchlog.minsize + tblsearchlog.maxsize) /2 < 1000 > > > > which should work fine. The thing is that minsize and > maxsize (for some > > strange reason) are text fields. Really these should be > changed but that > > would be take forever. Can I cast the two fields to numbers > so that this > > will work? I did try messing around with the eval function but if > > I use this > > in CF I get an unknown function error (is there any way of > using access > > fuinctions in cf). > > > > I'll probably have to change the datatype on the field but > I really dont > > want to. > > > > I'm, using CF 4.51 and access 2000. > > > > Thanks > > > > Giles > > > > > > -- > > ** Archive: http://www.mail-archive.com/dev%40lists.cfdeveloper.co.uk/ > > To unsubscribe, e-mail: [EMAIL PROTECTED] > For additional commands, e-mail: [EMAIL PROTECTED] > For human help, e-mail: [EMAIL PROTECTED] > > > > -- ** Archive: http://www.mail-archive.com/dev%40lists.cfdeveloper.co.uk/ To unsubscribe, e-mail: [EMAIL PROTECTED] For additional commands, e-mail: [EMAIL PROTECTED] For human help, e-mail: [EMAIL PROTECTED] -- ** Archive: http://www.mail-archive.com/dev%40lists.cfdeveloper.co.uk/ To unsubscribe, e-mail: [EMAIL PROTECTED] For additional commands, e-mail: [EMAIL PROTECTED] For human help, e-mail: [EMAIL PROTECTED] -- ** Archive: http://www.mail-archive.com/dev%40lists.cfdeveloper.co.uk/ To unsubscribe, e-mail: [EMAIL PROTECTED] For additional commands, e-mail: [EMAIL PROTECTED] For human help, e-mail: [EMAIL PROTECTED]
