Adding integer increment after string for missing values ms-access

Question

I have a dataset containing road names and road types. There are many missing road names and I would like to populate missing names with a simple string declaring road type followed by a Unique integer identifier as exemplified below.

Path1 Path2 Path...

I am trying to use the declare function, but something is not correct in my statement.

DECLARE @i int
SET @i=0

UPDATE KMS_VEJMIDT_BRUDT
SET VEJNAVN = "Sti"+Str(@i), @i=@i+1
WHERE (((KMS_VEJMIDT_BRUDT.VEJNAVN) Is Null) AND ((KMS_VEJMIDT_BRUDT.VEJMTYPE)="Sti"));

Possible duplicate of [How to declare and set variable in ms access 2007 query](https://stackoverflow.com/questions/5198193/how-to-declare-and-set-variable-in-ms-access-2007-query) — June7, Jun 09 '17 at 16:50
This doesn't like like Access code. Access is VBA, and VBA doesn't use Declare functions. — Johnny Bones, Jun 09 '17 at 17:34
Think OP is trying to write an SQL statement in query designer SQLView which doesn't recognize DECLARE. Review https://stackoverflow.com/questions/44443253/loop-through-records-and-increment-value-in-vba-access/44444821#44444821 and https://stackoverflow.com/questions/44443253/loop-through-records-and-increment-value-in-vba-access/44444821#44444821. — June7, Jun 09 '17 at 18:02

Alex · Accepted Answer · 2017-06-10T04:54:26.673

Edit: I misread the question slightly - I've added "Sti" as a prefix for the generated numbers, and started at 1

You may be able to do this in a 3 step process as described in this answer. I'm assuming that you have an ID primary key field in the KMS_VEJMIDT_BRUDT table.

Step 1:

Make a SELECT query named GENERATE_NEXT_NAVN using the following SQL:

SELECT C.ID, "Sti"&[RowNo] AS NEXT_VEJNAVN
FROM (SELECT A.ID, 
A.VEJMTYPE, 
(select count(*) from  KMS_VEJMIDT_BRUDT as B where A.ID>=B.ID AND B.VEJNAVN Is Null and A.VEJMTYPE = "Sti" ) AS RowNo
FROM KMS_VEJMIDT_BRUDT AS A 
WHERE A.VEJNAVN Is Null 
and A.VEJMTYPE = "Sti"
)  AS C;

Note the use of a Row Number workaround as described in this answer. I use this to generate an incrementing number starting from the highest NAVN present in the source table.

Step 2: Copy the query output from Step 1 into a temporary table:

SELECT GENERATE_NEXT_NAVN.* 
INTO NEXT_NAVN_TEMP
FROM GENERATE_NEXT_NAVN;

Step 3: Update the source table using the values in the temp table:

UPDATE KMS_VEJMIDT_BRUDT 
INNER JOIN NEXT_NAVN_TEMP 
ON KMS_VEJMIDT_BRUDT.ID = NEXT_NAVN_TEMP.ID 
SET KMS_VEJMIDT_BRUDT.VEJNAVN = [NEXT_NAVN_TEMP].[NEXT_VEJNAVN];

Oh great! Glad to help :-) if you were to accept my answer, I'd really appreciate it — Alex, Jun 11 '17 at 10:56

Adding integer increment after string for missing values ms-access

1 Answers1