IIf syntax

[melissafvoelker]

Think my syntax is wrong here, but not sure where. I would appreciate any help! Thanks!
SELECT a.[FA Ctr], a.[SAP Co], a.[SAP Ctr], a.[GL Co],



IIf([Len([SAP CO])] ="3",""0"' & [a].[SAP CO]", IIf([Len([SAP CO])]="2",""'00"' & [a].[SAP CO]"), IIf([Len([SAP CO])]="1",""'00"' & [a].[SAP CO]", "[A].[SAP CO]"))) AS Expr3
INTO [Interim Table]
FROM Asset_Map AS a

 

[FlyerMike]

The length (Len()) of a field will be numeric.

Try the following:

SELECT a.[FA Ctr], a.[SAP Co], a.[SAP Ctr], a.[GL Co],
IIf([Len([SAP CO])] =3,'0' & [a].[SAP CO], IIf([Len([SAP CO])]=2,'00' & [a].[SAP CO]), IIf([Len([SAP CO])]=1,'000' & [a].[SAP CO]", "[A].[SAP CO]"))) AS Expr3
INTO [Interim Table]
FROM Asset_Map AS a

I'm curious. If you want leading zeros, have you tried modifying the format of the field in the query/form/report rather than using an IIF function?

Try setting the field format to '0000' (without the apostrophes).

 

[melissafvoelker]

iif syntax

hi
Thanks a lot for your reply. I am trying to change the data in an existing table by using iif and making a table so i can join it to another table which has a text field that has leading zeroes.

am i doing this right?

 

[melissafvoelker]

hi again.
i used the following (what you suggested) but got an error message.

SELECT a.[FA Ctr], a.[SAP Co], a.[SAP Ctr], a.[GL Co],
IIf([Len([SAP CO])] =3,'0' & [a].[SAP CO], IIf([Len([SAP CO])]=2,'00' & [a].[SAP CO]), IIf([Len([SAP CO])]=1,'000' & [a].[SAP CO]", "[A].[SAP CO]"))) AS Expr3
INTO [Interim Table]
FROM Asset_Map AS a

 

[melissafvoelker]

how do i set the field format to '0000' (without the apostrophes).

 

[FlyerMike]

SELECT a.[FA Ctr], a.[SAP Co], a.[SAP Ctr], a.[GL Co],
IIf([Len([SAP CO])] =3,'0' & [a].[SAP CO], IIf([Len([SAP CO])]=2,'00' & [a].[SAP CO]), IIf([Len([SAP CO])]=1,'000' & [a].[SAP CO]", "[A].[SAP CO]"))) AS Expr3
INTO [Interim Table]
FROM Asset_Map AS a

Since you are intending to link to another table with leading zeros, you may want to try a series of update queries.

First look for those records where the length of SAP CO is 1. The Update value for this field is '000' & [SAP CO].

Repeat where length of field is 2, update with '00'
Repeat where length of field is 3, update with '0'

Then, go to the Asset_Map table, and edit the field "SAP CO". There is a format field property. Set it to '0000'.

 

[melissafvoelker]

i am trying to do an update query, but it keeps saying that zero records will be updated. It is also prompting me for parameters and i don't know why.
here is the sql view.


UPDATE Asset_Map SET Asset_Map.[SAP Co] = '0'
WHERE (((Len([a].[sap co]))=3));

 

[Jon K]

Make-Table Query:-

SELECT [FA Ctr], [SAP Co], [SAP Ctr], [GL Co],
Format([SAP Co],"0000") AS NewField INTO [Interim Table]
FROM Asset_Map;


Update Query:-

UPDATE Asset_Map SET Asset_Map.[SAP Co] = Format([SAP Co],"0000");
.

 

[melissafvoelker]

thanks so much to both of you for your help.

jon i tried what you suggested and was able to join the tables.

Thanks so much!

 

[melissafvoelker]

More help requested

help i am having trouble with multiple format statements
(i am not completely helpless, but there is definitely room for improvement)

SELECT [FA Ctr], [SAP Co], [SAP Ctr], [GL Co],
Format([SAP Co],"0000") AS NewField, format([cst ctr], "0000000000") as cost center appended INTO [Interim Table]
FROM Asset_Map;

 

[Jon K]

SELECT [FA Ctr], [SAP Co], [SAP Ctr], [GL Co],
Format([SAP Co],"0000") AS NewField,
format([cst ctr], "0000000000") as [cost center] INTO [Interim Table]
FROM Asset_Map;

 

[melissafvoelker]

Thanks!

thanks jon for your help. I finished the query it turned out great.
Take Care!