Help with Update Query

[Middlemarch]

I know this is wrong but can't quite see how to correct it

sql = "Update Tracks set Track = " & "1." & [Track] & " Where tID= " = c - 1

Track is a Numeric field currently holding 1, 2, 3 etc and I want to change to 1.1, 1.2 etc.
I tried VAL but didn't work. Access also gives error Microsoft Access can't find the field '|1' referred to in your expression.
Bit puzzled why it's showing the pipe character.

[June7]

Why would you need to update the field when this value can be constructed in query or textbox when needed?

Why do you want to save values like 1.1 and 1.2? Will there be 1.1.1?

The last = sign should probably be &.

[Middlemarch]

Why would you need to update the field when this value can be constructed in query or textbox when needed?

I don't have/want a query or textbox, I want it in the table.

Why do you want to save values like 1.1 and 1.2? Will there be 1.1.1?

No. 1 means 1 and .1 means part 1 of 1. Never more.

The last = sign should probably be &.

Quite right, but still tells me Access can't find the field '|1'
Wonder why it thinks that's the field name when it's the field value.
I'll try Edit with a select query if Update won't work.

[June7]

Are you running this behind a form that has [Track] as a field in RecordSource?

I tested this SQL and it works for me.

If you want to provide db for analysis, follow instructions at bottom of my post.

[Welshgasman]

I know this is wrong but can't quite see how to correct it

sql = "Update Tracks set Track = " & "1." & [Track] & " Where tID= " = c - 1

Track is a Numeric field currently holding 1, 2, 3 etc and I want to change to 1.1, 1.2 etc.
I tried VAL but didn't work. Access also gives error Microsoft Access can't find the field '|1' referred to in your expression.
Bit puzzled why it's showing the pipe character.

Why not debug.print it and see what you actually have?

[CJ_London]

Update Tracks

& [Track] &

Track is a Numeric field

so which is it? Track or Tracks?

[June7]

CJ, Tracks is table, Track is field.

[Micron]

Suggest you get a real query working then copy the sql and modify that for using in code if that's what this is for. I would have thought more like
sql = "Update Tracks set [Track] = 1." & [Track] & " Where tID= " & c - 1 That assumes c is a variable in your code and cannot be 0 or null.

Then debug.print the sql as WGM suggested, copy it and see if it will run in a new query.

[Middlemarch]

I was debug.printing it and trying different things in the query designer. Eventually OK. Problem was the quotes and so many "&" chars.

sql = "UPDATE Tracks SET Track = " & Chr$(34) & "1." & Chr$(34) & " & [Track] WHERE (((tID)=" & c - 1 & "));"

Thanks for the feedback.

[June7]

So Track is not a numeric field?

[Middlemarch]

Yes Track is Numeric. So why are quotes needed? No there way "." is there?

[June7]

If Track is numeric field type, the apostrophe or quote delimiter should not be needed. They are used when field is text type.

[Middlemarch]

I couldn't make it work without the quotes. It would change the . into # and instead of 1.1, 1.2 it became 11 12.

[Micron]

I couldn't make it work without the quotes.

Then the values can be considered numeric but the field data type must be text. Look at the table field in design view and see what it is.

[CJ_London]

if track is numeric, why not just add them together

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