Parse Data Then Search For Delimiter
Sep 10, 2007
Here is what I need to do:
I have 1 table that contains groups responsibilities, there is a field Code-group that the initiator uses to track the groups & their % of impact to the overall project hours. Sadly enough he enters the data as such (EX:
PROJECT1 GROUP1-25%, GROUP2-50%, GROUP3-25%.
The problem is that he has varied instances of groups listed in each record & wants me to be able to parse the data out regardless of how many groups are listed. I know how to parse data when there are a specific number of groups what I can't figure out is how to parse data regardless of how many groups is in the field.
Any help is totally appreciated.
Phoenix
View Replies
ADVERTISEMENT
Dec 7, 2006
Hi All,
Can someone get me started in the right direction?
I have an unbound field on a form "txtScan". This field is populated by a barcode scanner.
The data looks like this ~Field1~Field2~Field3~Field4~Field5~Field6~Field7~ Field8~Field9
I am using ~ as a delimiter. Since I am encrypting the barcode, I can pretty much do anything to the barcode.
I need to parse
~Field1~ into txtFirstName
~Field2~ into txtLastName
~Field3~ into txtCompany etc.
A key point is that one of the fields may be blank so, if field 2 in the string is blank, "Field2" does not get populated.
I've never tackled anything like this before and don't know where to start.
Any help
TIA
View 2 Replies
View Related
May 26, 2013
In a query, I have the following data. If a number has more than six digits, I need to parse past the fifth digit and that becomes a new field. If it has five or fewer digits, no record is entered into Column2. For example:
Column1 Column2
12345
54321
123456 6
12345678 678
123
9876541 41
6
What simple code does it take to perform this in a query?
View 2 Replies
View Related
Jul 9, 2012
I've got a Table with a string of data delimited by an "*". The first part of the string before the "*" can be variable in length, so if I use a LEFT expression to break apart the 2 parts of the string on each side of the "*", my results are incorrect. In MS Excel, I can use the following formula to solve the problem:
=LEFT(B6,(FIND("*",B6)))
You are just substituting the number of positions with a Find command that looks for the "*", and returns anything left of the "*". Unfortunately, the brilliant programmers at Microsoft, once again, don't have an Excel function in Access. how to recreate that functionality in a table in Access?
View 4 Replies
View Related
Jul 28, 2014
I've built an HTML web form that sends an email to me upon submission with all the field inputs. I have previously found success using VBA in Outlook to parse out these emails and feed the substrings into an Excel Sheet (hooray!), but now am looking to feed the substrings into Access, specifically into a table in an existing .mdb file. I figure the way Outlook will "talk to" Access will likely be a bit different from the way it does for Excel.
The way my previous script engaged with Excel was through a rule; every time a new email was received, the following script would run if the message had the subject line generated by my web form. The script would check whether Excel and the target workbook/sheet was open, act accordingly, and then input the substrings (I've excluded that part below), then return Excel and the wb/ws to their initial state. I imagine though, that with Access I might be able to feed the substrings into the .mdb without having to open it proper, the way a front-end talks to a back-end. But perhaps I'm mistaken on that front.
Code:
Option Explicit
Option Compare Text
Public Const xlUp As Integer = -4162 'I genuinely do not know what this is _
about. It came with the initial _
borrowed code.
Public Const wbPath As String = "C:...Workbook.xlsx" 'Workbook path
[Code] .....
View 10 Replies
View Related
Nov 26, 2007
I have the a while loop with gives the string as following
1|2|Joe|Mark
2|4|Mike|Smith
.
I successfully created the .mdb with the tables. But how do I insert the above string with delimiter as pipes?
Any Suggestions
Thanks
View 2 Replies
View Related
Apr 14, 2014
In Access 2007, in a table “Source” -> "UserName" column, I'm trying to truncate and extract value in the string:
a. Value within the parenthesis preceded by "".
b. Value outside the parenthesis preceded by “”
“Source” table contains the column ‘UserName’
“Final” table contains the columns ‘UserName’, “Expected_Output_1” and “Expected_Output_2”.
I tried using the following expressions to extract the output results, but unfortunately there’re not displaying the results in correct format.
For extracting results of “Expected_Output_1” I used: Mid([UserName],InStrRev([UserName],"")+1)
For extracting results of “Expected_Output_2” I used:
Mid(Mid([UserName],InStr([UserName],"(")+1,InStr([UserName],")")-InStr([UserName],"(")-1),InStrRev(Mid([UserName],InStr([UserName],"(")+1,InStr([UserName],")")-InStr([UserName],"(")-1),"")+1)
Attaching the database for your reference.
Expected results should match the snapshot attached or table “Final” in the attached DB
Example:
UserName = C103E000001 then the “Expected_Output_1” = E000001, “Expected_Output_2” = nothing
UserName = NAHoM\_INTRA_MTS (NAHoMDNAGAIntranetAdmin) then the “Expected_Output_1” = _INTRA_MTS, “Expected_Output_2” = DNAGAIntranetAdmin
View 1 Replies
View Related
Jun 28, 2006
Hi,
I need to create a query that will parse a word and separate it into a list of letters.
I have had a go myself but am unable to do it... does anyone else know what to do?
Thanks.
View 1 Replies
View Related
Nov 11, 2005
Probably very simple but I cannot get the syntax right - it has been far too long since I used Access.
I am trying to seperate a field NAME from these formats:
LAST/FIRST M
LAST/FIRST
To two fields: Field LAST and field FIRST
Can someone please help?
Thank you! It is Friday and I want to go home!
View 1 Replies
View Related
Jun 24, 2006
I am tring to seperate a field using a query that has data like this:
02 04 08 18 20 25 31 32 35 39 41 45.... (10 sets of numbers)
into there own fields...
I cant seem to find something along those lines!
View 1 Replies
View Related
Aug 18, 2006
I am looking to parse names that go into a table in the field "FULLNAME", but I only want this qry to run if there is 1 space in the field.
UPDATE tblStandardLayout SET tblStandardLayout.FNAME = Left([FULLNAME],InStr([FULLNAME]," ")), tblStandardLayout.LNAME = Mid([FULLNAME],InStr([FULLNAME]," ")+1);
This would be because I dont want it to try to parse names that are not just "First Last"
View 1 Replies
View Related
Mar 27, 2008
I am a novice here so your patience and help is very much appreciated.
I am trying to parse text from a field.
Here's an example of the source data (all in "field1"):
Example 1
***** Regular *****
Time..08:19 DATE..03/27/2008
Request NO...20081306101
...and this is how I would like it"
New Field 1: Regular
New Field 2: 08:19
New Field 3: 03/27/2008
New Field 4: 20081306101
Because the values change substantially, I would like to be able to select the data in between two known text strings as opposed to using the MID function, an example of which I have successfully used to return "Regular" is:
MID([field1],InStr9[field1],"*****")+6,7)
But because the values change (e.g. "Emergency") I would rather have the end point based on the string "*****" rather than a count of characters (e.g."7")
Any suggestions?
View 3 Replies
View Related
Jul 9, 2015
I have a User Defined Type which stores an array..I have 2 instances of this UDT, one storing matched data, the other storing exception data..I was to parse these arrays to an Excel output routine. However, the parameter variable contains no data when accessing it..
Here is my code:
Code:
Private Type TOutputRow
RACFID As String
FullName As String
Access As String
LastLoggedIn As Date
End Type
[code]...
View 7 Replies
View Related
Aug 31, 2015
I am attempting to create a report and pass on the information from the current form to it. It is supposed to create a student ID and I am unable to get the information into the appropriate fields.I can get all the info from OpenArgs into one box but it wont parse it all out. The Form is within a navigation form. I know the below option is not the best way to do it, but other options I have found it has issues with sending when the form is within a navigation form and I prefer not to redo the entire thing at this point to remove the Navigation form.
Code:
DoCmd.OpenReport "rptStudentID", acViewPreview, , , , OpenArgs = "lblSSID|" & Me.SSID & "lblFirst|" & Me.firstName & "lblLast|" & Me.lastName & "lblDate|" & Me.dateAdded & "lblImg|" & studentList.Column(4)
The Parsing which is mostly just a copy from the above site
[code]....
View 6 Replies
View Related
Mar 29, 2007
I am having trouble parsing the month from a date like 05/12/2007 and I want to return the leading zero with the month. Example of 05/12/2007 and I want to display 05 only.
Please send any suggestions.
Thank You,
MJ
View 2 Replies
View Related
Jan 27, 2014
If I have an XML file, I can load it into an XML document and parse it.
For an HTML file, I have been unable to find similar parsing methods - that is, using DOM and not involving a browser (other than simply treating the file as text). I'd prefer to avoid the browser control because it is different for a number of different Access versions, so the only option remaining that I see is to run Internet Explorer with visibility off.
Can one parse HTML using the DOM without a browser?
View 8 Replies
View Related
Jul 8, 2012
I have a Date Field and I would like to complete another field depending upon the contents of that date field.
In this way: if the Date is, say, 4/27/12 I want the other field to read: April 2012 and so on. Reading the month, putting it into words, reading the year, writing it out. This second field is NOT a date field.
View 2 Replies
View Related
Dec 4, 2006
Solution to parse the strings below and get number of months
1) 10 years 5 months 5 days - Should give 5
2) 6 years 10 months 22 days - Should give 10
View 2 Replies
View Related
Jul 8, 2013
Access 2003
I have a process which imports a txt file into a memo field of my TBLORIGINAL table Each file may have one record or it may have multiple records Every record begins (exactly) with
Quote:
FRE Order Order RAD ShipDate Customer City State Postal Code
For each record in the memo field, I need to create a new record into my TBLPROCESSED table
This can probably be done while it is importing but I need to keep the original file intact
I suppose I could copy and paste but there must a vba way of doing this
View 3 Replies
View Related
Jul 5, 2013
I have a spread sheet which on a per line basis contains columns whose cells contain the path to a file. It looks as if many of the files are tif, pdf, word and excel etc, so multiple file types.
Writing a routine which would take each row and then for each line of file paths turn them into one multi-page document - pdf or something?
View 1 Replies
View Related
Apr 15, 2013
what I want to do is make a button to search range of columns in data table with data type Yes/no and display the results if the value is yes
View 9 Replies
View Related
Aug 23, 2012
I need to identify and parse (substring) a number of digits from a barcode. The barcode digit count will vary, based on the number of details included in the barcode.
For example: the barcode for GS1 DataBar begins with a 4 digit code of 8110 identifying the code as GS1. The 5th digit then identifies the number of digits directly following which constitute the manufacturers code, which can range anywhere from 6 digits to 12 digits. I need to pull the number of digits out of the barcode based on the value of the 5th digit placeholder and pull only that many digits from the string and replace them into another field. There are potentially 70 total digits in the barcode. I have only provided the first section, which I am asking the question on.
Simply put: I need to tell the substring to identify the number to parse from digit 5, (which will have a min of 6 digits and a max of 12)
How do I pull the information out of a string to create a substring in another field by first defining the number of digits to pull out (Digits 6 to 17 min of 6 digits and a max of 12 digits) based on a value within the string (digit 5= value)
So if Digit 5 is 6 the returned value would be: Digits: 6 7 8 9 10 11 (6 6 6 6 6 6 )
If Digit 6 is 9 the returned value would be: Digits: 6 7 8 9 10 11 12 13 14 (9 9 9 9 9 9 9 9 9)
The first portion of the barcode is below: There are other pieces necessary to be pulled the same way, but from other sections, and based on yet another X desinator field (the next with a min of 1 and max of 5) I assume the pull would be similarly completed.
I don't know how to write code, I have used formulas but not this detailed or specific.
Ex: I know = Min (barcode, 5, 6) would pull the 6 digits after the 5 spot, but how do I vary the 6 (count) number in the formula?
Example below: No additional spaces, dashes, letters etc. (A's = 1-4, X = 5, M's = 6-17)
A A A A X M M M M M M M M M M M M
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17
View 2 Replies
View Related
Oct 1, 2013
I have a query and search form with this critera in each field for: Employee, Job Number etc.
Example: Like "*" & [forms]![F_SearchFormWide]![Job Number] & "*"
If I put in may the first 3 characters of the job it pulls it up but when I put the full number nothing comes up.
Also the DB has 40,000 records. The total at bottom of query is only 16,000.
I have tried verifying my join connections making sure all jobs are pulling from job table, I tried all 3 ways. Why its not pulling up all my records.
View 5 Replies
View Related
Nov 21, 2005
I have the database and the Data page, but how do I add a search button to the data page to search the database by a certain field? Any help would be appreciated. I would be happy to send you the files if you give me a contact.
Thanks
View 2 Replies
View Related
May 23, 2006
Hi all
I reread this and it was way to long. What I want to do is use a combo box to list values from a table field, when an option is selected, the rest of the fields update. Not sure what I have to do to achieve this. Any advise would be welcome.
Regards
Craig
View 2 Replies
View Related
Nov 17, 2006
Hello,
Below is the main data entry form of my application. We disrtibute a product called MC Cloth to Shops to display. After a month we visit again and take stock of products sold, replenish and the shop pays for the products sold.
the Database keeps a record of the shop, products displayed, refilled, sold and respective payments.
The dtabase and the form is loaded in Handheld (PDAs) by the sales people who enter data during the visit and then synchronise with a master on return
http://affiliatesexcel.com/MC_main_form.jpg
I need to sum up values in one field for example MC Refill from the first record till the new record and show it in another field, for example MC Refill Total
Another Forum answer to my question about summing up values in one field to be used as default in a second field showed that a search needs to be done based on base field (in this case NAME of customer).
However since I use a COMBO BOX to enter this NAME field values and then select it from a pull down list to create new records, I have this value ONLY in the very first Record. All subsequent records have all other values where as the NAME value remain empty.
NameCust_IDRecordNumAddress
whs01ggggggggg
02qqqqqqq
03mmmmm
04nnnnnnnnn
05ooooooo
06pppppp
Kickstart08xyz
09898989898
012mmp
013qty
This makes the search function impossible based on the NAME value.
As a solution I would like to AUTOMATICALLY copy the NAME value to a second field (for example CUST_ID) during creation of each NEW RECORD so that I can then base my search on this field instead of the Name field (with empty values)
Currently I have the code below which works correctly for entering NEW Data and for recalling by Pull Down .
(I have tried a mehod which entered the values for
all records but this clutter up the Pull down with SAME
Name for repeated records making the PULL DOWN unusable.)
I have tried to copy the Name value to Cust_ID value for each new record but the code gives an error.
+++++++++++
Code:
Private Sub Name_Combo_AfterUpdate()
' This procedure tries to find the matching product's record.
' If the matching record is found, the procedure goes to it.
' If the record isn't found, the focus stays on the current record.
Dim Criteria As String ' This is the argument to the FindFirst method.
Dim MyRS As Recordset ' Recordset used to search.
Dim ComboName As String ' The name of the company to search for.
Const IDYES = 6
Set MyRS = Me.RecordsetClone
' Build the criteria.
ComboName = Chr$(34) & Screen.ActiveControl & Chr$(34)
Criteria = "[Name]=" & ComboName
' Perform the search.
MyRS.FindLast Criteria
If MyRS.NoMatch Then
Response = MsgBox("Could not find the Supplier Name: " & ComboName & " Do you wish to register a New Supplier: " & ComboName & " in this Database?", 4 + 48)
If Response = IDYES Then
MyRS.AddNew ' Create new record.
MyRS("Name") = Screen.ActiveControl
MyRS.Update ' Save changes.
MyRS.Move 0, MyRS.LastModified ' Go to new record
Me.Bookmark = MyRS.Bookmark ' Go to new record
Else
GoTo Endsub
End If
Else
MyRS.AddNew ' Create new record.
MyRS("Name") = Screen.ActiveControl
MyRS("Cust_ID") = MyRS("Name")
MyRS.Update ' Save changes.
MyRS.Move 0, MyRS.LastModified ' Go to new record
Me.Bookmark = MyRS.Bookmark ' Go to new record
'Me.Bookmark = MyRS.Bookmark
Dim recNo As Long
' for this to work there cannot be any RecordNumber with a value of 0
' it finds the highest record number for the name in the combo box
' and returns 0 and exits if no record found.
recNo = Nz(DMax("[RecordNum]", "Miracle_Cloth_Main", "[Cust_ID]='" & Me.Cust_ID & "'"), 0)
Debug.Print "RecordNo: " & recNo & " and Name: '" & Me.Name_Combo & "'"
If recNo = 0 Then
Exit Sub
End If
Me.Text90.SetFocus
DoCmd.FindRecord "'" & recNo & "'", acAnywhere, , acSearchAll, , acCurrent
End If
Endsub:
MyRS.Close
End Sub
+++++++++++++++++++++++
The question is is there an easier way to
achieve the summing function ?
Any help is greatly appreciated as always.
--------------------------------------------------------------------------------
View 14 Replies
View Related
