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

Altering Access to use either tab/return as a delimiter for split() function

P: 23
Currently I am working on a serial number inventory database. When I was testing functionality, I was using the split function with the space as a delimiter, shown below:

Expand|Select|Wrap|Line Numbers
  1. Dim InputString() As String
  2. Dim i As Integer
  3. InputString = Split(InputName, "    ")
  4. For i = 0 To UBound(InputString)
  5.     CurrentDb.Execute "INSERT INTO InventoryInputT(InputID) VALUES ('" & InputString(i) & "')"
  6. Next i
  7.  
Thus, an entry of
Expand|Select|Wrap|Line Numbers
  1. data1 data2 data3
In the textbox of the form resulted in a table input of

Expand|Select|Wrap|Line Numbers
  1. record1   data1
  2. record2   data2
  3. record3   data3
  4.  
Which is desired. The scanner we purchased uses either a 'tab' delimiter or a 'return'... it can also use 'add jump line' but I don't know what that means and it seems to be similar to return (on the surface)

Is there any way I can alter my code to either:

1) use the tab function
- the issue here is if the data in the scanner is represented as: 'data1 [tab] data2 [tab] data3'

and then I upload that to the textbox, access seems to read the data as:
-input 'data1'
-execute [tab] (which means it navigates out of my textbox to whatever the next button is)
... because it has navigated out of the textbox it can no longer enter 'data2' in the textbox... as such I don't know if this can be solved

2) Can I use the return setup as it allows each data entry to have its own unique line in the textbox
- can I adjust my code so it sets each line of the textbox to a value of i and then runs through my execute code? Example:

data entered (as displayed in textbox):
Expand|Select|Wrap|Line Numbers
  1. data1
  2. data2
  3. data3
  4.  
Get code to read it as:
data1 = line1 = i=0 - execute to table
data2 = line2 = i=1 - execute to table
data3 = line3 = i=2 - execute to table

or something along those lines?

Thank you so much!
May 16 '17 #1

✓ answered by NeoPa

Hi Tyler.

The second parameter of the Split() function is called Delimiter. This can be set to a single space, multiple spaces (as in your example) or even a tab character (vbTab in VBA). It's very flexible.

Share this Question
Share on Google+
3 Replies


NeoPa
Expert Mod 15k+
P: 31,419
Hi Tyler.

The second parameter of the Split() function is called Delimiter. This can be set to a single space, multiple spaces (as in your example) or even a tab character (vbTab in VBA). It's very flexible.
May 17 '17 #2

P: 23
Thanks NeoPa, I was aware of that usage but the tab didn't seem to work with my system. Turned out it was more of the scanner's issue than it was an error with access.

This was fixed by recognizing all serial numbers started with an 's' which allowed me to use the 's' as the delimiter.
May 24 '17 #3

NeoPa
Expert Mod 15k+
P: 31,419
Good to hear you've resolved your issue anyway Tyler :-)
May 25 '17 #4

Post your reply

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