By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
435,056 Members | 1,320 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 435,056 IT Pros & Developers. It's quick & easy.

Splitting field value

P: 23
I have a access database name:MainDatabase
A table inside this name:All_Table
A field inside this table,name:Login_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.
Apr 3 '12 #1

✓ answered by 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 :
Expand|Select|Wrap|Line Numbers
  1. Split(rs!Login_Account, "\")(1)

Share this Question
Share on Google+
9 Replies


Rabbit
Expert Mod 10K+
P: 12,366
You can use the Split() function and then get the second item in the resulting array.
Apr 3 '12 #2

NeoPa
Expert Mod 15k+
P: 31,494
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 :
Expand|Select|Wrap|Line Numbers
  1. Split(rs!Login_Account, "\")(1)
Apr 4 '12 #3

P: 23
Sir,
Instead of split can i use Mid?
How can i refer to my Table ?
Is it by name"All_Table" ?
Apr 5 '12 #4

NeoPa
Expert Mod 15k+
P: 31,494
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
Apr 5 '12 #5

P: 23
I want to go with Split.
Using ADODB.
I tried this ,
Expand|Select|Wrap|Line Numbers
  1. strTable = "All_Table"
  2. rstTable.Open strTable, cnnDB 'open data sources table with recordset
  3.   'Do Until rstTables.EOF 'until end of tables is reached
  4.   strnew = Split(rstTable!Login_Account, "\")(1)
  5.     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.
Apr 6 '12 #6

100+
P: 759
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. :)

Expand|Select|Wrap|Line Numbers
  1. Dim DB As DAO.Database 'Dimension a variable for a general database
  2.     Set DB = CurrentDb() 'Assign current database to this variable (can be assigned any database, not only the current one)
  3. Dim Rst As DAO.Recordset 'Dimension a variable for a record set from DB database
  4.     Set Rst = DB.OpenRecordset("TableName") 'Assign ta recordset to this variable (can be a table or a query)
  5.     Rst.MoveFirst 'Pointer to the first record
  6.     Do While Not Rst.EOF() 'Do for all records
  7.         With Rst 'Using Rst
  8.             .Edit 'Prepare to Edit
  9.             !fldName = .... 'The field "fldName" from your Rst will be updated to ...
  10.             '............. other code
  11.                 MsgBox(!fldName) 'The field have not (yet) the new value
  12.             .Update 'NOW the "fldName" field is updated in the table
  13.                 MsgBox(!fldName) 'Now, the field have the new value
  14.             .MoveNext 'Move to the next record
  15.         End With 'End to eork with this Rst
  16.     Loop
  17.  
  18. 'Clear the variables in order to free memory
  19.     Rst.Close
  20.         Set Rst = Nothing
  21.     DB.Close
  22.         Set DB = Nothing
Apr 7 '12 #7

NeoPa
Expert Mod 15k+
P: 31,494
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 :
Expand|Select|Wrap|Line Numbers
  1. Dim cdb As DAO.Database
  2. Dim rst As DAO.Recordset
  3.  
  4. Set cdb = CurrentDb()
  5. Set rst = cdb.TableDefs("All_Table").OpenRecordset(dbOpenTable, ...)
You cannot open a recordset of a String variable as your code seems to be attempting.
Apr 8 '12 #8

P: 23
Refering field value with the symbol "!" ,AMAZING.
It was new for me also works magically,successfully.
Really the name of yours"EXPERT" justified.
Apr 10 '12 #9

NeoPa
Expert Mod 15k+
P: 31,494
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 :
Expand|Select|Wrap|Line Numbers
  1. rs!Login_Account
could equally be written as :
Expand|Select|Wrap|Line Numbers
  1. rs.Fields("Login_Account")
Apr 10 '12 #10

Post your reply

Sign in to post your reply or Sign up for a free account.