Splitting field value

[usha2]

I have a access database name:MainDataba se
A table inside this name:All_Table
A field inside this table,name:Logi n_Account
Structure of data in that field is:DOM\xyz
My question is:
How can i split this fiels value to get only'xyz'in a recordset.
Hope for some vba code and Thanks in advance.

[Rabbit]

You can use the Split() function and then get the second item in the resulting array.

[NeoPa]

Usha2:
Hope for some vba code

Then you should include what you already have in your question.

Assuming a recordset variable of rs, then you could access this part of the field using :

Split(rs!Login_Account, "\")(1)

[usha2]

Sir,
Instead of split can i use Mid?
How can i refer to my Table ?
Is it by name"All_Table" ?

[NeoPa]

Usha2:
Instead of split can i use Mid?

No. They are entirely separate functions.

Can you achieve similar results with code that uses Mid?
Yes, but not as easily. You're welcome to try of course.

Usha2:
How can i refer to my Table ?
Is it by name"All_Table" ?

I have no idea what you're talking about o.O

[usha2]

I want to go with Split.
Using ADODB.
I tried this ,

strTable = "All_Table"
rstTable.Open strTable, cnnDB 'open data sources table with recordset
  'Do Until rstTables.EOF 'until end of tables is reached
  strnew = Split(rstTable!Login_Account, "\")(1)
    Debug.Print strnew

This shows the error msg:
Run Time error'91':
Object variable or With block variable not set
May be Error in openingg recordset.
Please rectify my code and Thank U so much.

[Mihail]

Here you have a sketch for how to read/write data from/into a table or a query.
Note that the query must be updatable in order to write in.

I wish to thanks to SmileyCoder for this routine !
I make some minor (cosmetic) changes so, if something do not work, is my fault. :)

Dim DB As DAO.Database 'Dimension a variable for a general database
    Set DB = CurrentDb() 'Assign current database to this variable (can be assigned any database, not only the current one)
Dim Rst As DAO.Recordset 'Dimension a variable for a record set from DB database
    Set Rst = DB.OpenRecordset("TableName") 'Assign ta recordset to this variable (can be a table or a query)
    Rst.MoveFirst 'Pointer to the first record
    Do While Not Rst.EOF() 'Do for all records
        With Rst 'Using Rst
            .Edit 'Prepare to Edit
            !fldName = .... 'The field "fldName" from your Rst will be updated to ...
            '............. other code
                MsgBox(!fldName) 'The field have not (yet) the new value
            .Update 'NOW the "fldName" field is updated in the table
                MsgBox(!fldName) 'Now, the field have the new value
            .MoveNext 'Move to the next record
        End With 'End to eork with this Rst
    Loop

'Clear the variables in order to free memory
    Rst.Close
        Set Rst = Nothing
    DB.Close
        Set DB = Nothing

[NeoPa]

If All_Table is a table in your current database then you probably want to use DAO rather than ADODB (as I guess you're using). The reference to the table object would then be :

Dim cdb As DAO.Database
Dim rst As DAO.Recordset

Set cdb = CurrentDb()
Set rst = cdb.TableDefs("All_Table").OpenRecordset(dbOpenTable, ...)

You cannot open a recordset of a String variable as your code seems to be attempting.

[usha2]

Refering field value with the symbol "!" ,AMAZING.
It was new for me also works magically,succe ssfully.
Really the name of yours"EXPERT" justified.

[NeoPa]

Thank you.

In truth, the bang (!) character is fairly commonly used in VBA and generally works as a shortcut to a default collection of some kind. In this case the .Fields collection of a DAO.Recordset object. So :

rs!Login_Account

could equally be written as :

rs.Fields("Login_Account")