iif, len, replace queries - need assistance

[Allison_H]

Hi,

I am somewhat of a beginner in regards to my Access skillset.

I am trying to create two separate queries and am in need of assistance. I
have been trying to use iif, len and replace but am struggling.

1. Text Field (numeric and alphanumeric): If the length of the string is not
equal to 10 characters I need to set that value to "9999999999".

2. Text Field (numeric and alphanumeric): I need to remove all "-" and all
spaces. From this result I need to display the first 10 characters. If there
are less then 10 characters then no value is to be displayed.

Thank you,

Allison

 

[John W. Vinson]

Hi,

I am somewhat of a beginner in regards to my Access skillset.

I am trying to create two separate queries and am in need of assistance. I
have been trying to use iif, len and replace but am struggling.

1. Text Field (numeric and alphanumeric): If the length of the string is not
equal to 10 characters I need to set that value to "9999999999".

UPDATE tablename
SET fieldname = "9999999999"
WHERE Len([fieldname]) <> 10;

No IIF needed or appropriate; you use an update Query, with a criterion
selecting only those records you want to update.

Back up your database first of course just in case it goes wrong!

2. Text Field (numeric and alphanumeric): I need to remove all "-" and all
spaces. From this result I need to display the first 10 characters. If there
are less then 10 characters then no value is to be displayed.

As a calculated field - or, for a permanent irrevokable update, an expression
in the Update To line of an Update query -

Left(Replace(Replace([fieldname], "-", ""), " ", ""), 10)


John W. Vinson [MVP]