Converting Access Code - FORMAT

Hello,
A colleague passed me this make-table query which works in Access, and we'd
like to automate in SQL. I did some basic debugging but now on parse, SQL
returns that Trim and Format are not recognized function nameS. I think you
can see the what formatting is doing to provide a standard string length.
Could someone advise what I need to do to get this to work in SQL? Any help
would be much appreciated. Thanks, Pancho
SELECT GVMOI2.TranDateSold, GVMOI2.CustomerID, GVMOI2.TaxIDNum,
GVMOI2.TaxIDType,
GVMOI2.ApplicationCode, GVMOI2.AccountNo,
Left("00000000000000000000",20-Len(GVMOI2.TraceNbr)) &
Trim(GVMOI2.TraceNbr) AS TraceNbrEdt, IIf(IsNumeric(GVMOI2.CreditAmtCash),
Left(Format([CreditAmtCash],"0000000000.00"),10) &
Right(Format([CreditAmtCash],"0000000000.00"),2),
"000000000000") AS CashCRFmt, IIf(IsNumeric(GVMOI2!DebitAmtCash),
Left(Format([DebitAmtCash],"0000000000.00"),10) &
Right(Format([DebitAmtCash],"0000000000.00"),2),
"000000000000") AS DebitAmtCashFmt,
IIf(IsNumeric(GVMOI2. CreditAmtChecks),Left(Format([CreditAmtC
hecks],"0000000000.00"),10) &
Right(Format([CreditAmtChecks],"0000000000.00"),2),"000000000000") AS
CreditAmtChecksFmt,
IIf(IsNumeric(GVMOI2. DebitAmtChecks),Left(Format([DebitAmtChe
cks],"0000000000.00"),10) &
Right(Format([DebitAmtChecks],"0000000000.00"),2),"000000000000") AS
DebitAmtChecksFmt,
GVMOI2.TranCode, GVMOI2.TranName, GVMOI2.TellerID, GVMOI2.BranchNo,
GVMOI2.CheckReferenceNbr,
GVMOI2.CheckNbr, GVMOI2.BankNumber, GVMOI2.Remitter1, GVMOI2.Payee1,
GVMOI2.ThirdParty,
"0000000000000000000000000" AS DenominationFmt, GVMOI2.IDType,
GVMOI2.IDNumber,
GVMOI2.IDIssueBy, GVMOI2.IDOthers
INTO MOI_Prep
FROM GVMOI2;Follow the below guidelines to convert the query.
1. & ==> +
2. iif(<condition>,<true>,<false> ) ==> case when <condition> then <true>
else <false> end
3. <table>!<column> ==> <table>.<column>
4. trim(<value> ) ==> rtrim(ltrim(<value> ))
-oj
"Pancho" <Pancho@.discussions.microsoft.com> wrote in message
news:92287E29-C913-43A2-8FA5-0C0B7441CBB6@.microsoft.com...
> Hello,
> A colleague passed me this make-table query which works in Access, and
> we'd
> like to automate in SQL. I did some basic debugging but now on parse, SQL
> returns that Trim and Format are not recognized function nameS. I think
> you
> can see the what formatting is doing to provide a standard string length.
> Could someone advise what I need to do to get this to work in SQL? Any
> help
> would be much appreciated. Thanks, Pancho
> SELECT GVMOI2.TranDateSold, GVMOI2.CustomerID, GVMOI2.TaxIDNum,
> GVMOI2.TaxIDType,
> GVMOI2.ApplicationCode, GVMOI2.AccountNo,
> Left("00000000000000000000",20-Len(GVMOI2.TraceNbr)) &
> Trim(GVMOI2.TraceNbr) AS TraceNbrEdt, IIf(IsNumeric(GVMOI2.CreditAmtCash),
> Left(Format([CreditAmtCash],"0000000000.00"),10) &
> Right(Format([CreditAmtCash],"0000000000.00"),2),
> "000000000000") AS CashCRFmt, IIf(IsNumeric(GVMOI2!DebitAmtCash),
> Left(Format([DebitAmtCash],"0000000000.00"),10) &
> Right(Format([DebitAmtCash],"0000000000.00"),2),
> "000000000000") AS DebitAmtCashFmt,
> IIf(IsNumeric(GVMOI2. CreditAmtChecks),Left(Format([CreditAmtC
hecks],"0000000000.00"),10)
> &
> Right(Format([CreditAmtChecks],"0000000000.00"),2),"000000000000") AS
> CreditAmtChecksFmt,
> IIf(IsNumeric(GVMOI2. DebitAmtChecks),Left(Format([DebitAmtChe
cks],"0000000000.00"),10)
> &
> Right(Format([DebitAmtChecks],"0000000000.00"),2),"000000000000") AS
> DebitAmtChecksFmt,
> GVMOI2.TranCode, GVMOI2.TranName, GVMOI2.TellerID, GVMOI2.BranchNo,
> GVMOI2.CheckReferenceNbr,
> GVMOI2.CheckNbr, GVMOI2.BankNumber, GVMOI2.Remitter1, GVMOI2.Payee1,
> GVMOI2.ThirdParty,
> "0000000000000000000000000" AS DenominationFmt, GVMOI2.IDType,
> GVMOI2.IDNumber,
> GVMOI2.IDIssueBy, GVMOI2.IDOthers
> INTO MOI_Prep
> FROM GVMOI2;
>
>|||OJ,
Thank you for these clear instructions. I will try these tomorrow. Looks
good!
Best Wishes,
P
"oj" wrote:

> Follow the below guidelines to convert the query.
> 1. & ==> +
> 2. iif(<condition>,<true>,<false> ) ==> case when <condition> then <true>
> else <false> end
> 3. <table>!<column> ==> <table>.<column>
> 4. trim(<value> ) ==> rtrim(ltrim(<value> ))
> --
> -oj
>
> "Pancho" <Pancho@.discussions.microsoft.com> wrote in message
> news:92287E29-C913-43A2-8FA5-0C0B7441CBB6@.microsoft.com...
>
>|||Hi oj,
I replaced & with +, ! with . on table.column names, and
rtrim(ltrim(<value> ) parses OK now.
Pls take a look at the revised script. I don't quite follow how to change
the iif to case when. I am getting an incorrect syntax error near THEN.
Please let me know what I'm doing wrong on the first CASE line and I'll fix
the rest the same way. Thanks! P
SELECT GVMOI2.TranDateSold, GVMOI2.CustomerID, GVMOI2.TaxIDNum,
GVMOI2.TaxIDType,
GVMOI2.ApplicationCode, GVMOI2.AccountNo,
Left("00000000000000000000",20-Len(GVMOI2.TraceNbr)) +
RTrim(LTrim(GVMOI2.TraceNbr)) AS TraceNbrEdt,
CASE WHEN (IsNumeric(GVMOI2.CreditAmtCash)) THEN
Left(Format([CreditAmtCash],"0000000000.00"),10) +
Right(Format([CreditAmtCash],"0000000000.00"),2), ELSE FALSE
"000000000000") AS CashCRFmt, IIf(IsNumeric(GVMOI2.DebitAmtCash),
Left(Format([DebitAmtCash],"0000000000.00"),10) +
Right(Format([DebitAmtCash],"0000000000.00"),2),
"000000000000") AS DebitAmtCashFmt,
IIf(IsNumeric(GVMOI2. CreditAmtChecks),Left(Format([CreditAmtC
hecks],"0000000000.00"),10) +
Right(Format([CreditAmtChecks],"0000000000.00"),2),"000000000000") AS
CreditAmtChecksFmt,
IIf(IsNumeric(GVMOI2. DebitAmtChecks),Left(Format([DebitAmtChe
cks],"0000000000.00"),10) +
Right(Format([DebitAmtChecks],"0000000000.00"),2),"000000000000") AS
DebitAmtChecksFmt,
GVMOI2.TranCode, GVMOI2.TranName, GVMOI2.TellerID, GVMOI2.BranchNo,
GVMOI2.CheckReferenceNbr,
GVMOI2.CheckNbr, GVMOI2.BankNumber, GVMOI2.Remitter1, GVMOI2.Payee1,
GVMOI2.ThirdParty,
"0000000000000000000000000" AS DenominationFmt, GVMOI2.IDType,
GVMOI2.IDNumber,
GVMOI2.IDIssueBy, GVMOI2.IDOthers
INTO MOI_Prep
FROM GVMOI2;
"oj" wrote:

> Follow the below guidelines to convert the query.
> 1. & ==> +
> 2. iif(<condition>,<true>,<false> ) ==> case when <condition> then <true>
> else <false> end
> 3. <table>!<column> ==> <table>.<column>
> 4. trim(<value> ) ==> rtrim(ltrim(<value> ))
> --
> -oj
>
> "Pancho" <Pancho@.discussions.microsoft.com> wrote in message
> news:92287E29-C913-43A2-8FA5-0C0B7441CBB6@.microsoft.com...
>
>|||There is no format() in sqlserver. You'd want to use convert() instead.
IIf(IsNumeric(GVMOI2.DebitAmtCash),
Left(Format([DebitAmtCash],"0000000000.00"),10) +
Right(Format([DebitAmtCash],"0000000000.00"),2),
"000000000000") AS DebitAmtCashFmt,
==>
case when IsNumeric(GVMOI2.DebitAmtCash)=1 then
right(convert(varchar,convert(money,1000
0000000+GVMOI2.DebitAmtCash)),13)
else replicate('0',10) AS DebitAmtCashFmt,
-oj
"Pancho" <Pancho@.discussions.microsoft.com> wrote in message
news:E2C8FF99-A473-4D32-9BB2-59AEFF996FC9@.microsoft.com...
> Hi oj,
> I replaced & with +, ! with . on table.column names, and
> rtrim(ltrim(<value> ) parses OK now.
> Pls take a look at the revised script. I don't quite follow how to change
> the iif to case when. I am getting an incorrect syntax error near THEN.
> Please let me know what I'm doing wrong on the first CASE line and I'll
> fix
> the rest the same way. Thanks! P
> SELECT GVMOI2.TranDateSold, GVMOI2.CustomerID, GVMOI2.TaxIDNum,
> GVMOI2.TaxIDType,
> GVMOI2.ApplicationCode, GVMOI2.AccountNo,
> Left("00000000000000000000",20-Len(GVMOI2.TraceNbr)) +
> RTrim(LTrim(GVMOI2.TraceNbr)) AS TraceNbrEdt,
> CASE WHEN (IsNumeric(GVMOI2.CreditAmtCash)) THEN
> Left(Format([CreditAmtCash],"0000000000.00"),10) +
> Right(Format([CreditAmtCash],"0000000000.00"),2), ELSE FALSE
> "000000000000") AS CashCRFmt, IIf(IsNumeric(GVMOI2.DebitAmtCash),
> Left(Format([DebitAmtCash],"0000000000.00"),10) +
> Right(Format([DebitAmtCash],"0000000000.00"),2),
> "000000000000") AS DebitAmtCashFmt,
> IIf(IsNumeric(GVMOI2. CreditAmtChecks),Left(Format([CreditAmtC
hecks],"0000000000.00"),10)
> +
> Right(Format([CreditAmtChecks],"0000000000.00"),2),"000000000000") AS
> CreditAmtChecksFmt,
> IIf(IsNumeric(GVMOI2. DebitAmtChecks),Left(Format([DebitAmtChe
cks],"0000000000.00"),10)
> +
> Right(Format([DebitAmtChecks],"0000000000.00"),2),"000000000000") AS
> DebitAmtChecksFmt,
> GVMOI2.TranCode, GVMOI2.TranName, GVMOI2.TellerID, GVMOI2.BranchNo,
> GVMOI2.CheckReferenceNbr,
> GVMOI2.CheckNbr, GVMOI2.BankNumber, GVMOI2.Remitter1, GVMOI2.Payee1,
> GVMOI2.ThirdParty,
> "0000000000000000000000000" AS DenominationFmt, GVMOI2.IDType,
> GVMOI2.IDNumber,
> GVMOI2.IDIssueBy, GVMOI2.IDOthers
> INTO MOI_Prep
> FROM GVMOI2;
>
> "oj" wrote:
>|||Thanks again oj. I have marked both of your posts as helpful and will try
this code.
Best Regards,
p
"oj" wrote:

> There is no format() in sqlserver. You'd want to use convert() instead.
> IIf(IsNumeric(GVMOI2.DebitAmtCash),
> Left(Format([DebitAmtCash],"0000000000.00"),10) +
> Right(Format([DebitAmtCash],"0000000000.00"),2),
> "000000000000") AS DebitAmtCashFmt,
> ==>
> case when IsNumeric(GVMOI2.DebitAmtCash)=1 then
> right(convert(varchar,convert(money,1000
0000000+GVMOI2.DebitAmtCash)),13)
> else replicate('0',10) AS DebitAmtCashFmt,
>
> --
> -oj
>
> "Pancho" <Pancho@.discussions.microsoft.com> wrote in message
> news:E2C8FF99-A473-4D32-9BB2-59AEFF996FC9@.microsoft.com...
>
>|||oj,
This is getting closer but now I get an incorrect syntax near the word AS on
the last line of the section below:
SELECT GVMOI2.TranDateSold, GVMOI2.CustomerID, GVMOI2.TaxIDNum,
GVMOI2.TaxIDType,
GVMOI2.ApplicationCode, GVMOI2.AccountNo,
Left("00000000000000000000",20-Len(GVMOI2.TraceNbr)) +
RTrim(LTrim(GVMOI2.TraceNbr)) AS TraceNbrEdt,
CASE WHEN IsNumeric(GVMOI2.CreditAmtCash)=1 THEN
Right(Convert(varchar,convert(money,1000
000000000+GVMOI2.CreditAmtCash)),13)
ELSE
Replicate('0',13) AS CashCRFmt,
It looks like they want to name the column CreditAmtCash when there is a
numeric value and name it CashCRFmt when the value is non-numeric. Let me
know if I am using Replicate correctly.
Thanks,
p
"oj" wrote:

> There is no format() in sqlserver. You'd want to use convert() instead.
> IIf(IsNumeric(GVMOI2.DebitAmtCash),
> Left(Format([DebitAmtCash],"0000000000.00"),10) +
> Right(Format([DebitAmtCash],"0000000000.00"),2),
> "000000000000") AS DebitAmtCashFmt,
> ==>
> case when IsNumeric(GVMOI2.DebitAmtCash)=1 then
> right(convert(varchar,convert(money,1000
0000000+GVMOI2.DebitAmtCash)),13)
> else replicate('0',10) AS DebitAmtCashFmt,
>
> --
> -oj
>
> "Pancho" <Pancho@.discussions.microsoft.com> wrote in message
> news:E2C8FF99-A473-4D32-9BB2-59AEFF996FC9@.microsoft.com...
>
>