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))
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?
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
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
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
hi, 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??
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:)
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 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)?
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?
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 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:
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
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
I 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?
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.
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
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.
I don't have any database experience whatsoever so please go easy. I'm guessing this kind of this is extremely simple for all of you.
I'm constructing a database of network resources and devices and I'd like to automatically update the values in one field based on the values of a field in another table.
The first table is called "IP" and the fields are called "Address", "IP Type" and "Device". The second table is called "Devices" and contains the fields "Name", "Description", "Asset Number" and "IP".
Here's an example of the tables: (ignore the "code" tag. i've only used it to align my columns properly)
What I want is for the Device field in the IP table to automatically update it's values based on the values found in the Devices table. In this case, the values that should appear in the Device field in the IP table are "Xserve" and "ProliantX".
I've searched through but haven't found a complete solution, just little pieces which I'm too inexperienced to put together myself.
