I have a field (Name) that is formatted: lastname, firstname
I need to parse the name field so I can update two fields for firstname and lastname....I have done this before, using left and instr functions, but cannot remember how...can you help?
thank you!
Tricia:)
Hi
I have text file which contains different types of Record (identified by RecordType).Its a simple FLAT file having specific record structure for each type of record.
My requirement is
1.Parse the .txt file,identify record type
2.Based on corresponding record structure apply validation rules
3.Generate error report where data violates validation rules.
I am new to access . If any of u have sample code on how to parse any file and show error report accordingly, that will help immensely
Thanks
I am trying to do the following
If any one of you have experienced Abacus ticketing system, each reservation is comprised of a group of names as follows
1.1 Gul/Mohammad Atif Mr 2.1 Gul/Mohammad Adil Mr
3.1 Bond/James Mr
I have to copy the above data from the abacus tcketing system into a text box.
My question is how can i devide the above three names into three seperate text boxes.
I need some assistance parsing some information from a web address. Here is an example of what the web address is.-http://www.websitename.com/ct20/pages2/client_bcphone_service_order.aspx?client_id=160399 4&contract_document_id=924182&cipid=206123444-What I am trying to do is get the client ID and the contract id. The client id being 1603994 and the contract id being 924182. Any ideas would be appreciated.Gregg
View 5 Replies View Relatedhi, im new to both access and sql and was hoping i could get some help...
i have a string of numbers seperated by commas stored in my database. i need to count how many numbers there are in each string and then display all the results with less than 36 numbers contained.
am i able to do this with an sql statement, or am i likely to need vba??
(example string: 9.89007472991943, 12.3332061767578, 14.4694166183472, 16.287145614624, 17.5347270965576, 17.6327610015869, 16.1364498138428, ...)
thx in advance
I am using the following statement in an update query (which by the way I found in this forum) to pull out the first name from the FirstName field. The field could have a first name or a first name and a middle initial. If the field has a first name and niddle intital: John P then I get John. If the field only has a first name, I get #Error in the update field. I have thousands of records to move to a new Oracle table which has a first name field and a middle intial field.
FNAME: IIf([FirstName] Is Null," ",Left([FirstName],InStr(1,[FirstName]," ")-1))
Any help would be greatly appreciated.
Thank You:confused:
Does anyone know of an expression I could use in a query to parse a full name field into last name, first name and middle initial?
View 4 Replies View RelatedPlease Help -
I have a field called MSG_DESC. In this field resides description of various errors i am logging.
I want to exctract only a piece of data from each field. in each field there is a value called SAP#**********
As noted above, it is 10 digits long. Problem is that there is no set place in the description where it is, so right, mid, lft trim does not work.
Can you please assist in how to extract this piece of data from the field.
I'm trying to parse text strings by certain spaces and characters within the string. For Example, if my text string is RAST 2006-A1 B1 mtge, how can I 1) create a new field with just the RAST 2006-A1, 2) Create a field with just the RAST 2006-A1 B1?
I appreciate any help I can get. Thanks!
Hello!
I would like to convert existing MS Access forms to an internal Delphi application.
Any information about the fomat of representing forms in the Access database and the method of reading/parsing the forms would be appreciated.
Also, I wonder if it's possible to read the forms without using Window's technologies (ADO, etc)?
Thanks in advance!
I have the following string: NE1220904
I need to remove the left two characters and the right four characters of a string in a query. I have found how to remove one side or the other, but not from both sides. Thanks in advance for the help!
Hi,
Is it possible to parse, and format a textfield in a report on a line by line basis?
What I am trying to do is get a document to be processed into a printable format using reports. The text, and formatting information of each line, and any specific word details are stored in separate columns in the table. If, for example an entire line needs to be made Bold, I can get that done. What I would like to know is that if a word in a particular line needs to be formatted differently to the rest of the line, eg underlined, is there a way to do so?
For example, if I had the line as follows:
The quick brown fox jumps over the lazy dog.
I'd have the default, unformatted text as one field in the table, and the word, in this case quick, and it's formatting in another field of the table.
So rather than stating my question again, can it be done?
Thanks in advance,
Shaitan.
I am having a problem with parsing the array value to Forms!<arraryvaluehere>.dirty.
Please see the code below:
Public strListOfForms(0 To 2) As String
strListOfForms(0) = "frmForm1"
strListOfForms(1) = "frmForm2"
strListOfForms(2) = "frmForm3"
For i = 0 To 2
Debug.Print CurrentProject.AllForms(strListOfForms(i)).IsLoaded
Debug.Print Forms!strListOfForms(i).Dirty
Next i
Note: CurrentProject.AllForms(strListOfForms(i)).IsLoade d works and Forms!strListOfForms(i).Dirty does not.
Is this because it is taking "strListOfForms(i)" literally? Is there a way that I can get around this?
We publish a PDF file every week full of taskings that our subordinate organizations need to accomplish for the following week. I would like to have parse the PDF and store the data in a table. I think the only way to start this operation is to first save the PDF to Text (Plain) as this create some way to delimit and parse the data. So, once it's saved as a txt file, it basically looks something like this:
Code:
//
Column 1 Data/StuffIDontCareAbout/
StuffIDontCareAbout/StuffIDontCareAbout//Column 2 Data
StuffIDontCareAbout/StuffIDontCareAbout/
Column 3 Data
[Code] ....
...and so on and so forth anywhere between 50-100 more times
The entire text file will always start and end with "//" at the top and bottom. You also see that each tasking paragraph (starting with "Column 1 Data" and ending with "Column 3 Data") is preceded by a "//" on its own line, and also followed by a "//" on its own line. The text file will always look exactly like this with each tasking paragraph having the same number of forward slashes.
Column 1 Data always starts on a new line under "//" and runs until the first "/" (single forward slash).
Column 2 Data always starts after the "//" on the 2nd paragraph line and runs until the end of the paragraph line
Column 3 Data always starts on the 4th paragraph line and runs until the end of the paragraph line
Here's a quick example of what I'm talking about:
Code:
//
37 NOS001/DCO/
TaskPer/TBD//310001ZAUG2014-292359ZSEP2014
GenText/Remarks/
(U/FOUO) This will contain the actual task description and details. You can see that "(U/FOUO)" contains a forward slash.
[Code] ....
So after it's parsed, my table would have the following new rows:
Code:
Column 1 | Column 2 | Column 3
37 NOS001 |310001ZAUG2014-292|(U/FOUO) This will contain
582 NOS012 |280001ZAUG2014-022|(U/FOUO) This another task
I have tables that are ODBC linked to a SQL database. The source tables have record date and time stamps written into one field. This means that I can't use the Access date filter functions on these fields. Is there an easy way to parse this field into two fields, one each for the date and time?
View 3 Replies View Related:confused:
I have a Microsoft Access table with the following columns: A,B,C,D,E,F.
In first row of Column A, I have the following string value: "Al,Peggy,Kelly,Bud,Buck"
What I would like to do is parse this string as such:
Column B:"Al"
Column C:"Peggy"
Column D:"Kelly"
Column E:"Bud"
Column F:"Buck"
Is there a simple VB funtion to accomplish this?
We have imported an Excel spreadsheet with dates in one column that were imported as numbers such as '39287', which can also be displayed in Excel as July 24, 2007. We concatenated this import to a table where all our dates are stored as 20070724. Now we have both formats in one column, with over 70,000 records in the 20070724 format and a few thousand in the Excel format. How can we convert the fields in the Excel format to the appropriate YYYYMMDD format we use?
Many thanks for your help, we can find a good way to clean this up...
Genevatexan
Hello! I have an Excel file that I need to get into Access. Problem?: I need to "parse" portions of it. What I have is as follows:
Roach, Richard 555-55-5555 01-6500-3000
Date In Out In Out
Data Data Data Data Data
Data Data Data Data Data
.
.
.
I need to pull only the Name (Roach, Richard) from the first line and then all of the data in the "table" below it.
Is there a way to do this? PLEASE don't tell me to give up. I know I'm a novice, but I'm desperate. Thank you so much!
KellyJo
I have a form in which the user has the privilege to download some data from the table.the user will paste some of the numbers to the textbox from the excel in order to download the specific data.i need to write a query to parse the textbox using the in condition
View 12 Replies View RelatedI have a Db with a table imported from sql server. One of the fields contains data wrapped between xml tags. The xml tag identifiers when looked at as columns are not consistently the same tag identifiers but effectively I am parsing the string to provide columns of data from between the tag identifiers.
example:
<T6><A29>Compton Group</A29><A31>CO009</A31><A32>CO161/71</A32><A30>Invu Proposal</A30><A28>05 September 2007</A28><A41>Sales</A41></T6>
I am wondering if the method I am using to deconcatenate the string is the bext way. In my query I reference the left tag value .
TagVal1: ParseTagOpenVal([Attributes],3)
And then I parse the result based on the right hand tag value
Val1: ParseTagCloseVal([TagVal1],1)
I have a couple of modules for parsing the string and I am able to deconcatenate using this method but I am wondering if its the only way or the most efficient?
I have a field in our database with five distinct strings, separated by a delimited (a slash or "/").
I've been able to successfully write the formula to parse the first field:
ARL: Left$([pipeline]![Short Description],InStr(1,[pipeline]![Short Description],"/")-1)
The second field:
BRANCHMGR: Mid(Left([pipeline]![Short Description],InStr(InStr(1,[pipeline]![Short Description],"/")+1,[pipeline]![Short Description],"/")-1),InStr(1,[pipeline]![Short Description],"/")+1)
and the last field:
DATE: Mid$([pipeline]![Short Description],InStrRev([pipeline]![Short Description],"/")+1)
how to get the third and fourth strings parsed. I know its going to include some nested InStr or InStrRev commands -- just having trouble figuring out the structure. Could potential need a LEN command as well.
Typical field might look like this:
"Name1/Name2/Description/Update/Date"
I have about 150k rows that look like this
SHORT_DESCRIPTION
unable to view
unable to send out some stuff
what is going on
Is it possible for access to query through each field, and tell me the total number of occurence for each word? In this small example, the output would be:
Unable 2
to 2
view 1
send 1
out 1
some 1
stuff 1
what 1
is 1
going 1
on 1
Is this possible?
I am using this function
Expr2: Mid$([ItemInventoryAssemblyRefFullName],(InStrRev([ItemInventoryAssemblyRefFullName],"-")+1),(InStr([ItemInventoryAssemblyRefFullName],":"))-(InStrRev([ItemInventoryAssemblyRefFullName],"-"))-1)
to parse this string
4150-ATH:GRP_54X8/BL
into this
ATH
It works great until I get to a string that looks like this
4110-SPECIAL:OLY:LONG-TRACK:REPAIRS
Which I need to return the word SPECIAL but it gives me #Error in the Query.
It would seem that when there are multiple : it is giving the error but when there is only one it works fine. Is there anyther way to atchieve what I am doing?
I am doing this in an update query to get data from Quickbooks into a table in Access.
I'm trying to parse the following into an Array by splitting the csv file using a "," comma separator. There should be 63 different data pieces in this File. When I do a count of them from the (ubound array) i only get 54. The last data piece on each row gets concatenated to the first data piece of the next line. Is there a way to stop this from happening? This is causing problems with working with the data.
Date,Open,High,Low,Close,Volume,Adj Close
2013-06-07,1625.27,1644.40,1625.27,1643.38,3371990000,1643 .38
2013-06-06,1609.29,1622.56,1598.23,1622.56,3547380000,1622 .56
2013-06-05,1629.05,1629.31,1607.09,1608.90,3632350000,1608 .90
2013-06-04,1640.73,1646.53,1623.62,1631.38,3653840000,1631 .38
2013-06-03,1631.71,1640.42,1622.72,1640.42,3952070000,1640 .42
2013-05-31,1652.13,1658.99,1630.74,1630.74,4099600000,1630 .74
2013-05-30,1649.14,1661.91,1648.61,1654.41,3498620000,1654 .41
2013-05-29,1656.57,1656.57,1640.05,1648.36,3587140000,1648 .36
I have a record in a table (Table1) with the following:
Item Start End
A 2012 2030
I have another table (Table2) with the following:
Item Start
A 2018
A 2025
What I would like to do is update/append Table1 so that it looks like the following:
Item Start End
A 2012 2017
A 2018 2024
A 2025 2030
How can I get the value from a field in one table (in the sub form) to copy/insert into a field in another table (in the main form) when adding a new record?The main form and sub form are linked using parent/child linking, and the sub form is in a tab.I have table A (Visit Dates) in the main form which is used to record the date of a visit to a church. Table B (Quarters and Peals) is used to record an event that took place at that church during that visit. Note that not all visits in table A require a record to be created in table B - but half or more do.
In tables A and B I have a field called "QuarterOrPealID" and these are both primary keys, though the field in table B is set to 'no duplicates' and in table A it's set to 'duplicates allowed', as table A has its own auto number/pk. They are both linked in the relationships.
So, when I add a new record to table A using the main form, I might then need to click on the tab in the sub form to create a new record in table B, which has to be linked to the same record in table A. When the "QuarterOrPealID" auto number/pk is generated in the sub form (table B), I need that value to update to the "QuarterOrPealID" field of the main form (table A), so that when I'm viewing these records the form pulls all the information nicely together.