Can Use A Find Expression To Parse Data In A Table

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 Replies


ADVERTISEMENT

Parse Data From One Field Into Many

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

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 4 Replies View Related

Queries :: Parse Data In Query And That Becomes A New Field

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

Modules & VBA :: Parse Email Messages - Feed Data To MDB

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

Need Help With A Calculated Expression/data Table.

Mar 30, 2007

I am very inexperinced with Access, please be kind.
My problem is that from the form view the simple division of 2 #'s are placed in the correct text box I selected, but I am unable to get the value to enter into the data table. Please help.

Thank you

View 4 Replies View Related

Queries :: Find Data In One Table And Put In Field Of Another Table Then Update

Nov 7, 2013

I have an "order details" table that needs to populate a field called "Voucher" with data from another table called "codes". The "codes" table also has a true/false field called "allocated" because once allocated the code cannot be re-used.

I am trying to work out how to automatically allocate the next unallocated code in the "codes" table to each record in the "order details" table when that order details record has a DiscountID of "92".

Order Details Table Fields and conditions/criteria:
ID - primary key
DiscountID - only when the DiscountID = 92
Voucher - only populated when Discount ID = 92

Codes table Fields and conditions/criteria:
ID - primary key
code = text field with a code like "einstein01", "einstein02"
Allocated = False

Is there a way to put the next available code into the order details record then mark that code as allocated in the codes table. Then, move on to the next order details record that has a discountID = 92, input the next unallocated code and mark that code etc. etc.

Ideally, I would like to do this to happen via an event when the Order forms button "Close" is clicked.

View 1 Replies View Related

Convert Expression Calculation Into Data Table?

Jan 1, 2006

Hello all, I'm quite new to Access. I've read many Access tutorials and site but I have yet to found the solution for my problem. So here it is (pardon my english):

Field AmountTotal is
=(Nz([AmountSubTotal])+Nz([AmountSH])-Nz([Discounts]))

I manually input AmountSubTotal, AmountSH and Discounts. I expected an automatic calculation for AmountTotal.

In "Form view" I get the result of AmountTotal that I wanted. But the calculation result doesn't get recorded in my field data table AmountTotal. It just shows blank.

How do I record this automated results into my original table?

Thanks in advance!

View 4 Replies View Related

Creating Expression To Find A Percentage

Jan 20, 2005

I created the following expression on a report, under the details, in a text box:

=nz(pathjbo50,0)/nz(pathjbo50,0)+nz(pathcol50,0)+nz(pathpp50,0)+nz( pathlm50,0)

what I want is to find out what percentage pathjbo50 is of the sum of the other 4 fields.

but I keep getting an "Error" message. How do I get this percentage?

Where is the best place to find literature on writting expressions?

View 2 Replies View Related

The Expression You Entered Has A Function Name That XXX Can't Find

May 23, 2006

I have a database functioning pretty well.
All I did was move the files from one computer to another via USB stick and now I get this alarm when I try to run this particular macro.

It is a macro to set a value to specific field.
The Macro uses a condition [Forms]![tblCustomerCall]![Alarm Number]="000".
If the condition is met then it is supposed to set a value to a field in the same form.
[Forms]![tblCustomerCall]![Alarm Descrption] is the item to set.
Left("TV Parity Alarm",50) is the expression.
So if 000 is entered in then the text TV Parity Alarm is filled into the Alarm description field.

It was working perfect until I moved the files to another computer.
I have Office SP2 installed and have updated office with everything available.

Please help.
Thanks

View 10 Replies View Related

Find Duplicate Data In Different Column In A Table

Sep 12, 2007

Hi

I have a customer database and would like to merge anyone who has the same
phone number or mobile number.

The table is
First name Last name Phone Mobile Email
John Smith 123
Mary Smith 456 123

So I want to find these Mr&Mrs Smith because John phone number is the same
as Mary's mobile

Can you help??

View 2 Replies View Related

Forms :: Find And Replace With New Data In Table

Jan 7, 2014

Is it possible to have a Find & Replace in the table using access forms. ( Like the inbuild option in Excel ), I want the user to search some data & replace with the new data which may present in more than one record.

I want to do it with forms, because i dont want the user to use the tables.

View 3 Replies View Related

General :: Find Function / Expression In Access

Nov 8, 2012

I'm relavtively new to databases but after a steep learning curve (being left with many databases created by my old manager with no instructions on use) I'm getting there.What I am trying to do is pull out the date from records in a field called "Model_ID". The Model_ID field contains this kind of information:

Endscopy20120726JSmith
GISurgery20120521JDoe

I want to bring back "20120726" or "20120521". In excel I can do this with the mid and find functions but find doesn't work in access. I've tried combining the mid function with instr function but it comes back as too complex!

View 10 Replies View Related

Queries :: Find A Way Of Overwriting Records In Table With New Data

Aug 6, 2014

I have a table where csv files get imported to on a daily basis. The key fields I am working with are a supply number and date. The problem I have is that sometimes the csv file will contain information that is correcting/updating information held in the table. This is creating duplicated records.I need to either create a query to find records that have the same supply number and date or find a way of overwriting the records in the table with the new data.

View 2 Replies View Related

Query Expression To Find Records 30 Days Past Due

Mar 22, 2005

I am trying to run a query to print invoices that are 30, 60, and 90 days past due. What expression do I use to search for records that were purchased 30, 60, 90 days prior? I was able to find the expression to add time for the due date but was unable to find the expression to subtract time for my search. Thanks for any help!! Beck

View 1 Replies View Related

Queries :: Find Specific Data In A Table To Produce A Report

May 14, 2013

I have a table that is linked into access 2003. This table is updated by personnel in another location and I have to run a weekly report on engines that are below a certain performance level.

The column heading is MGT Margin and I have to list all of the engines that are below 20 degrees.

Can I run a query that looks at this table and produces a report of all the engines that are below 20 degrees?

I currently have to cut and paste each engine from the updated spread sheet every week onto a separate spread sheet and import that into access. If a query can be used to do what I am after I can use similar principles in other reports I have to run.

View 5 Replies View Related

Parse Text

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

Please Help String Parse

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

Parse A Filed

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

Parse Query

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

Parse Text

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

Modules & VBA :: Parse UDT As Parameter

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

Parse Into Report From Subform

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

Separate/Parse Month From Date

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

Modules & VBA :: Parse HTML Using DOM Without Browser

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

How To Parse And Re-Write A Date Field

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







Copyrights 2005-15 www.BigResource.com, All rights reserved