Small query function don't work on runtime version

[nd0911]

Hello,

After struggling with this issue I found that the following function returns an error (only on Access runtime version).

I will be happy to hear what I'm doing wrong.



Thank you.

Public Sub SetId_9(tblName As String, IdField As String) 
Dim SQL As String




SQL = "UPDATE " & tblName _
   & " SET [" & IdField & "] = String(9 - Len([" & IdField & "])," & """" & "0" & """)" & " & [" & IdField & "]"




CurrentDb.Execute SQL


End Sub

[CJ_London]

we would be happy to hear what the error is, might provide a clue as to the problem. Also some example data, you appear to be updating a field named idField with a variation of that name. Also not sure what the string function is.

[davegri]

Purpose seems to be to add leading zeros to the existing IdField to make its length equal 9 characters.
Don't know if this will fix the problem, but might make it easier to debug.
Also, the STRING function was introduced in AC2013. If your runtime version predates that it would fail.
In that case, you could try some alternate code to add the leading zeros without the STRING function.

Public Sub SetId_9(tblName As String, IdField As String)
    Dim SQL As String
    Dim arg As String
    if len(IdField) > 9 then
        msgbox "Length > 9"
        exit sub
    end if
    arg = String(9 - Len(IdField), "0") & IdField 

    Debug.Print arg
    SQL = "UPDATE " & tblName & " SET " & IdField = arg
 
    Debug.Print SQL


    Currentdb.Execute SQL
End Sub

[jwhite]

The String function is in Ac2000.

[CJ_London]

Also, the STRING function was introduced in AC2013

learn something new - but think it may be earlier as it works for me in 2010

but if I type

?String(9 - Len([" & "ABC" & "])," & """" & "0" & """)" & " & [" & "ABC" & "]"

in the immediate window I get a compile error - Expected: )

So I don't think the code has ever worked, even in a full version of access

and if idField is numeric, you cannot add preceding zeros which would make it a string.

[andy49]

is the id an autonumber field?

I changed a similar field to a text field, took out the key and it works perfectly

[nd0911]

Hi, first, thank you,

Second, my IdField is a string field but contains only and always numbers, my goal is if the number in the cell is less the 9 characters then add zero's at the beginning.

any suggestions ?

[andy49]

I'm assuming it's not a key field? Is it linked to another field?

[nd0911]

You right it is not a key field and also not link to another field, actually I do not have any relationships of any kind, just tables.

[andy49]

I'm assuming it's not a key field? Is it linked to another field?

[andy49]

Is the table open when you run the function or a form which has it as a data source.

[CJ_London]

perhaps time to review the code you are actually using now - and description of any errors you may be getting. You haven't said whether you have tried davegri's suggestion

[nd0911]

The problem is that I'm new with Access but I'm very good with VBA (on Excel) and my problems is with my PC at work, and now I'm home, here I have full Office version with Access and over there I do not have Access, and I want to simulate the Runtime invairment like there and I dont know if it is possible.

[nd0911]

Ok I take an old Laptop and intall runtime.
Now for sure the String function is what causing the error, I removed the String function and it worked.

Is there some alternative query for my needs ?

Thank you.

[davegri]

If you want to simulate the runtime (without installing it) on a PC that has full Access installed, change the suffix to .accdr and run it.
As for an alternate method:

Public Sub SetId_9(tblName As String, IdField As String)
    Dim SQL As String
    Dim arg As String
    if len(IdField) > 9 then
        msgbox "Length > 9"
        exit sub
    end if
    arg = format(IdField,"000000000") 

    Debug.Print arg
    SQL = "UPDATE " & tblName & " SET IdField = " & arg

    Debug.Print SQL

    Currentdb.Execute SQL
End Sub