Is CAST function available in Access? Because what I'm trying to accomplish is
to have a string converted to numeric.  Below is the query string:

strBxNo sample data are ENZ-1, ENZ-5, ENZ-50

strSQL = "SELECT strBxNo FROM tblCTranx WHERE " & _
         "(Mid(strBxNo, 1, InStr(1, strBxNo, ""-"")-1))='" & Prefix & "' AND " & _
         "(Mid(strBxNo,InStr(1, strBxNo, ""-"")+1,Len(strBxNo)-InStr(1, strBxNo, ""-""))) " & _
         "BETWEEN " & BxFrom & " AND " & BxTo & " " & _
         "ORDER BY Mid(strBxNo,InStr(1, strBxNo, ""-"")+1,Len(strBxNo)-InStr(1, strBxNo, ""-""))"

This work fine except that it is not ordered properly because after extracting the numeric part
it still treat it as a string and not as a numeric.  Any ideas?

Thanks,

enzo c",) 

If your backend is Access then no (this would have take you about 30 seconds
to test)

Look up CINT() in Access help.

-----Original Message-----
From: Enzo Zaragoza [mailto:enzaux@g...]
Sent: Thursday, 20 March 2003 1:55 PM
To: Access
Subject: [access] CAST Function



	Is CAST function available in Access? Because what I'm trying to
accomplish is
to have a string converted to numeric.  Below is the query string:

strBxNo sample data are ENZ-1, ENZ-5, ENZ-50

strSQL = "SELECT strBxNo FROM tblCTranx WHERE " & _
         "(Mid(strBxNo, 1, InStr(1, strBxNo, ""-"")-1))='" & Prefix & "' AND
" & _
         "(Mid(strBxNo,InStr(1, strBxNo, ""-"")+1,Len(strBxNo)-InStr(1,
strBxNo, ""-""))) " & _
         "BETWEEN " & BxFrom & " AND " & BxTo & " " & _
         "ORDER BY Mid(strBxNo,InStr(1, strBxNo,
""-"")+1,Len(strBxNo)-InStr(1, strBxNo, ""-""))"

This work fine except that it is not ordered properly because after
extracting the numeric part
it still treat it as a string and not as a numeric.  Any ideas?

Thanks,

enzo c",) 




IMPORTANT - PLEASE READ ******************** 
This email and any files transmitted with it are confidential and may 
contain information protected by law from disclosure. 
If you have received this message in error, please notify the sender 
immediately and delete this email from your system. 
No warranty is given that this email or files, if attached to this 
email, are free from computer viruses or other defects. They 
are provided on the basis the user assumes all responsibility for 
loss, damage or consequence resulting directly or indirectly from 
their use, whether caused by the negligence of the sender or not.

Thanks Andrew!

	I have tried the CAST on Access Backend before posting my query. I've resolved by the problem by using
the Round function but may be I'll try CInt()

thanks,

Enzo

-----Original Message-----
From: Haslett, Andrew [mailto:andrew.haslett@i...]
Sent: Thursday, March 20, 2003 11:45 AM
To: Access
Subject: [access] RE: CAST Function


If your backend is Access then no (this would have take you about 30 seconds
to test)

Look up CINT() in Access help.

-----Original Message-----
From: Enzo Zaragoza [mailto:enzaux@g...]
Sent: Thursday, 20 March 2003 1:55 PM
To: Access
Subject: [access] CAST Function



	Is CAST function available in Access? Because what I'm trying to
accomplish is
to have a string converted to numeric.  Below is the query string:

strBxNo sample data are ENZ-1, ENZ-5, ENZ-50

strSQL = "SELECT strBxNo FROM tblCTranx WHERE " & _
         "(Mid(strBxNo, 1, InStr(1, strBxNo, ""-"")-1))='" & Prefix & "' AND
" & _
         "(Mid(strBxNo,InStr(1, strBxNo, ""-"")+1,Len(strBxNo)-InStr(1,
strBxNo, ""-""))) " & _
         "BETWEEN " & BxFrom & " AND " & BxTo & " " & _
         "ORDER BY Mid(strBxNo,InStr(1, strBxNo,
""-"")+1,Len(strBxNo)-InStr(1, strBxNo, ""-""))"

This work fine except that it is not ordered properly because after
extracting the numeric part
it still treat it as a string and not as a numeric.  Any ideas?

Thanks,

enzo c",) 




IMPORTANT - PLEASE READ ******************** 
This email and any files transmitted with it are confidential and may 
contain information protected by law from disclosure. 
If you have received this message in error, please notify the sender 
immediately and delete this email from your system. 
No warranty is given that this email or files, if attached to this 
email, are free from computer viruses or other defects. They 
are provided on the basis the user assumes all responsibility for 
loss, damage or consequence resulting directly or indirectly from 
their use, whether caused by the negligence of the sender or not.