[AccessD] Function Error

Gustav,

You are brilliant and a godsend, thank you so much for all your help,
The last part works like a charm, NO MORE ERRORS.  It always seems to
just take another person looking over it to find a better way.

Thanks again you are wonderful, now I can continue on a happier note for
a Friday....   :-) 

-----Original Message-----
From: Gustav Brock [mailto:Gustav at cactus.dk] 
Sent: Friday, January 27, 2006 11:40 AM
To: accessd at databaseadvisors.com
Subject: Re: [AccessD] Function Error

Hi Mike

I suspect this to be the bad guy:

  Mid([inmates].[sid],(Len([inmates].[sid])-1),1) AS BinRowNum

Try to replace it for a test with:

  '0' AS BinRowNum

and run:

  SELECT SID FROM qryInmatesBinNumberUpdate WHERE BinRowNum = '0'

I guess that could be replaced with:

  Val(Right([inmates].[sid],1)) AS BinRowNum

and then it will be numeric, thus you could try:

  SELECT SID FROM qryInmatesBinNumberUpdate WHERE BinRowNum = 0

/gustav

>>> Mike.W.Gowey at doc.state.or.us 27-01-2006 18:56:21 >>>
This is the SQL of qryInmatesBinNumberUpdate:

SELECT Mid([inmates].[sid],(Len([inmates].[sid])-1),1) AS BinRowNum,
Inmates.SID, Inmates.Namef, Inmates.Namel, Inmates.Namem, Inmates.DOB,
Inmates.Cell, Inmates.Race, Inmates.Custody, Inmates.Age,
Inmates.HasBlues FROM Inmates WHERE (((Inmates.Cell) Not Like "DS*" And
(Inmates.Cell) Not Like "IM*"
And (Inmates.Cell) Not Like "IN*" And (Inmates.Cell) Not Like "M-*" And
(Inmates.Cell) Not Like "S-*" And (Inmates.Cell)<>"") AND ((Exists
(SELECT SID FROM FTLaun WHERE FTLaun.SID = Inmates.SID))=False) AND
((Exists (SELECT SID FROM BinNumSIDAssign WHERE BinNumSIDAssign.SID =
Inmates.SID))=False))
ORDER BY Mid([inmates].[sid],(Len([inmates].[sid])-1),1), Inmates.SID; 

-----Original Message-----
From: Gustav Brock [mailto:Gustav at cactus.dk]
Sent: Friday, January 27, 2006 10:54 AM
To: accessd at databaseadvisors.com
Subject: Re: [AccessD] Function Error

Hi Mike

So what is the SQL of qryInmatesBinNumberUpdate?

/gustav


--
AccessD mailing list
AccessD at databaseadvisors.com
http://databaseadvisors.com/mailman/listinfo/accessd
Website: http://www.databaseadvisors.com