Help with Update Query

[Middlemarch]

@CJ I couldn't get that line to compile. I best guessed and put &" ahead of WHERE but then got an error on Track External name not defined.
Track is definitely a number data type.


This is too much of a problem so I've added a new field and not changing Track now. But it'd still be interesting to see the correct sql construction.

[Micron]

I suggest you post a zipped db with just enough to replicate the issue.

[CJ_London]

sorry should be
sql = "UPDATE Tracks SET Track = " & 1+([Track]/10) & " WHERE tID=" & c - 1

[Middlemarch]

I still can't get it to work so will do as @Micron suggests and upload it. Neither of the sql strings work, (External name not defined.) but at one time something did work (but not correctly).
That's now deleted.
I'm intrigued what the "/10" should do and what would put the "." in. And why the error.

[June7]

You aren't concatenating [Track] from a form, you want to just use the field from the table:

sql = "UPDATE Tracks SET Track =1 & '.' & [Track] WHERE tID=" & c - 1

Note that 1.10 shows as 1.1 in number field therefore is not distinguished from the 1.1 items (same for 1.20 and 1.2). Options are to

1) save to a text field
or
2) save the 1 through 9 values as 1.01, etc. and set for 2 decimal place display
sql = "UPDATE Tracks SET Track = 1 + ([Track] / 100) WHERE tID=" & c - 1


Why c - 1 and not just 1001 ?

[ArviLaanemets]

The field Track is a number (a double one).
In your module, you are trying to run an update query

sql = "UPDATE Tracks SET Track = " & 1 + ([Track] / 10) & " WHERE tID=" & c - 1

This query returns an error when run, because it is trying to add to string "UPDATE Tracks SET Track = " the value of expression (1 + ([Track] / 10)) - but there is no variable [Track] defined for this sub, not can the procedure to read it from somewhere. To be working, this code string must be

sql = "UPDATE Tracks SET Track = " & "1 + ([Track] / 10)" & " WHERE tID=" & c - 1

Now about what you get when you run this sub:
When Track = 1, you get 1 + 1/10 = 1.1;
When Track = 10, you get 1 + 10/10 = 2;
When Track = 11, you get 1 + 11/10 = 2.1;
When Track = 20, you get 1 + 20/10 = 3

Is this what you really want? Or do you really want values 1.01, 1.1, 1.11, 1.2, etc. instead. This you can get e.g. with query like

SELECT tID, CDbl("1." & RIGHT("00" & CStr(Track), 2)) AS ConvertedTrack

It assumes the original value of Track never gets >= 100. Otherwise you have to increase the number trailing zeros in ConvertedTrack formula of query.
And be aware, it depends on you regional settings, i.e. on your decimal delimiter, are you using "1." or "1," in same formula.

[Middlemarch]

Thanks @June7 , this has been quite interesting, I was looking at a Form but had not requeryed it. So may not have seen some previous changes/attempts.
I see what you mean about not distinguished, I'm now not using this format.
Would you know what caused the (External name not defined. error? It doesn't happen now.
Originally c was going to be passed to the procedure, I had just left it for testing one record.

[June7]

The posted db did not have a form. Code was in a general module and the concatenated reference to [Track] was meaningless. There was no [Track] object (such as field or textbox on form) to reference.