NEED FUNCTION TO REPLACE CHARACTER IN STRING

[TNN]

The following SQL stmnt gives me the result 419.01 as the value for PymtAmt. I want to remove the decimal point with some kind of a string function with result 41901. I have tried "REPLACE" and "STUFF" and get back "Undefined Function". What function, if any, can I use in the below stmnt?? Where oh where do you find a list of the functions that are applicable to JET SQL??

SELECT ROUND(PaymtAmt,2) AS PymtAmt
FROM(SELECT SUM(IIf(ISNULL([QTRTTL]),0,[QTRTTL])+IIf(ISNULL([QTRTTLSTARTUP]),0,[QTRTTLSTARTUP])-IIf(ISNULL([QTRTTLDEPOSITS]),0,[QTRTTLDEPOSITS])) AS PaymtAmt
FROM qryQTRTTL, qryQTRTTLSTARTUP, qryQTRTTLDEPOSITS)

TNN, Tom

TOM

http://WWW.TNPAYROLL@AOL.COM

 

[hermanlaksko]

Try this:
Num = 419.01
Function Xhange(Nom)

Xhange= CDbl(Left(Num, InStr(1, Num, ".&quot - 1) & Right(Num, 2))
End Function
I guess that we allways know that the number will have 2 decimal places.

 

[Eduu]

Why don't you just multiply the number by 100?

 

[hermanlaksko]

Dear EduU
I am a traditional thinker ;-)
U R absolutely right, how hard does it have to be - I ask myself ;-)

 

[TNN]

hermanlaksko,

Where do I put the Function so that it is available to be used in my SQL statement??? I am using VB6.0. Do I create a Function procedure in VB6.0 ?? Do I create and store the Function in the database, somehow?? I want to be able to use the Function in my SQL statement. Doesn't jet SQL have a function I can use??

I know those are a lot of questions but there is so little documentation on the SQL used in jet SQL. There is all kinds of documentation as regards to server SQL but that syntax doesn,t always apply to jet sql.

Also, I am NOT using Access as part of my application.

Any help or comments are appreciated.

TNN, Tom



TOM

http://WWW.TNPAYROLL@AOL.COM