Access Queries

[njwilli3]

I have a query which creates me a table from another depending on a value fed into it called the 'Reference' field e.g. containing: BDF/739/2005/0 (x number of rows)

What I now want to do is update that reference from BDF/739/2005/0 to BDF/739/2005/1 so that it adds a 1 to the end or a 2 or a 3 depending what the original end number was. Is there an SQL update query which can manipulate strings like this?

Any help on this would be much appreciated

[dannymking]

You could try the following if the reference starts at 0... if not then change the last digit to reflect, removing would set it to 1..

NewRef: Left([Reference],Len([reference])-1) & (DCount("RecordNumb","tblTest","RecordNumb<" & [Recordnumb])+1)-1

[njwilli3]

not quite sure what you mean as Reference may be :

BDF/739/2005/1
BDF/739/2005/2
BDF/739/2005/3 etc.

Sorry for being simple but I dont understand how you used "RecordNumb" in the DCount function? Can you please explain it more?

[dannymking]

Should of Explained.. the Dcount part of the SQL takes the primarykey (needs to be sorted in ascending order) and creates a sequence counter, as explained in your earlier post..

It's just a field name, and will need to be changed to reflect your own primary key in the table..

Actually looking back at your problem.. I'm gonna need to change it slightly.. BRB

[dannymking]

Here you go this is a string manipulation formula you can use..

NewRef: Left([Reference],InStrRev([Reference],"/")) & CLng(Mid([Reference],InStrRev([Reference],"/")+1))+1

[njwilli3]

Thanks I'll try that now