Help needed on Up-date Query!

[atiq]

i am trying to update a field within a table. But i dont want to update the whole of the data within the field just one part of it.

The field is the Tutor Group for students in the format 7B, 7D, 7F, 7H, 7M, 7S, 8B, 8D, 8F,... etc where the number represents the year group! i want to use an expression that would add(+) 1 to the year group but leave the letter the same as this doesn change. By doing this the above list would change to 8B, 8D, 8F, 8H, 8M, 8S, 9B, 9D, 9F, ...etc

I attempted to use the following expression: ((*)+1)(*) but it didn't work.

I should have had the year groups separate but i am given an Excel flat file which has details of the students and doesnt separate the the two.

any help would be much appreciated! hope someone can help!

[ADezii]

i am trying to update a field within a table. But i dont want to update the whole of the data within the field just one part of it.

The field is the Tutor Group for students in the format 7B, 7D, 7F, 7H, 7M, 7S, 8B, 8D, 8F,... etc where the number represents the year group! i want to use an expression that would add(+) 1 to the year group but leave the letter the same as this doesn change. By doing this the above list would change to 8B, 8D, 8F, 8H, 8M, 8S, 9B, 9D, 9F, ...etc

I attempted to use the following expression: ((*)+1)(*) but it didn't work.

I should have had the year groups separate but i am given an Excel flat file which has details of the students and doesnt separate the the two.

any help would be much appreciated! hope someone can help!

Here is the basic logic:

Dim strTutorGroup As String, strNewGroup As String
 
strTudorGroup = "7C"
 
strNewGroup = CStr(Val(Left$(strTudorGroup, 1)) + 1) & Right$(strTudorGroup, 1)
 
Debug.Print strNewGroup         'produces 8C

[pks00]

Since the number is at the start, u may find VAL works just as well
so why not just try this, see if this works for you

eg a test module for you

Public Sub TestTutorGroup()
    Dim sTutorGroup As String
 
    sTutorGroup = "9J"
 
    MsgBox "Before: " & sTutorGroup
 
 
    'Increment number by 1
    sTutorGroup = Val(sTutorGroup) + 1 & Right$(sTutorGroup, 1)
 
 
    MsgBox "After: " & sTutorGroup
End Sub
 

[atiq]

Here is the basic logic:

Dim strTutorGroup As String, strNewGroup As String
 
strTudorGroup = "7C"
 
strNewGroup = CStr(Val(Left$(strTudorGroup, 1)) + 1) & Right$(strTudorGroup, 1)
 
Debug.Print strNewGroup         'produces 8C

Thanks for your reply! But how do i use this the Query grid. i tried putting it in the Update to: field in the query grid but doesnt accept this? sorry about this, im new to Access. once again, thanks for ur help so far.

[pks00]

Thanks for your reply! But how do i use this the Query grid. i tried putting it in the Update to: field in the query grid but doesnt accept this? sorry about this, im new to Access. once again, thanks for ur help so far.

Simply set the "Update To" to the example code provided

i.e.
CStr(Val(Left$(strTudorGroup, 1)) + 1) & Right$(strTudorGroup, 1)

or

Val(strTudorGroup)+1 & Right$(strTudorGroup,1)


Note, strTudorGroup must be your fieldname

[ADezii]

Since the number is at the start, u may find VAL works just as well
so why not just try this, see if this works for you

eg a test module for you

Public Sub TestTutorGroup()
    Dim sTutorGroup As String
 
    sTutorGroup = "9J"
 
    MsgBox "Before: " & sTutorGroup
 
 
    'Increment number by 1
    sTutorGroup = Val(sTutorGroup) + 1 & Right$(sTutorGroup, 1)
 
 
    MsgBox "After: " & sTutorGroup
End Sub
 

Better solution, Thanks.