Extracting Data From A Table As String

Nov 15, 2005

I need to be able to extract individual fields from a table as strings in order to then send this data to my EPOS printer (which will accept only strings to print) I have created the table with a query so the question could equally be how can i output strings as query results. Im using Access97 HELP!!
I have tried to set up a ADODB.Connection but either Access 97 wont accept it or i've got the coding wrong

View Replies


ADVERTISEMENT

Queries :: Extracting Data From Unusual String

Mar 5, 2015

I have a flat file sent to me today that contains a number of fields with the same issue. As an example, I have a field named Coordinators from a table named Audit and the data within it is formatted as follows:

Mark Hollings;#14664;#Judy Thompson;#10169;#Tammy Wilson;#3608;#Tim Levy;#2785;#David Simpson;#1251

Is there any way to extract only the names while leaving in either the ";" or replacing it with a ","? My desired output would look like this:

Mark Hollings;Judy Thompson;Tammy Wilson;Tim Levy;David Simpson

This field can be populated with one or more names so that's a variable in all of this as well.

View 3 Replies View Related

Extracting Data From One Field In A Table To Another--Help!!!

Jun 27, 2006

Hi Guys,

I have a table called "tblEmployees" with the following fields:
EmployeeNo (Primary key)
Name
Address
DOB etc
Letter1Dated
Letter2Dated

I have another couple of tables:

"tblLetter1" with the following fields:
EmployeeNo (Primary key)
Name
Letter1Dated
Letter1RemainingFields...

"tblLetter2" with the following fields:
EmployeeNo (Primary key)
Name
Letter2Dated
Letter2RemainingFields...

I want to be able to put the data from 'Letter1Dated' field (from the "tblLetter1" table) into the 'Letter1Dated' Field in the "tblEmployees" table.
Likewise, I want to be able to put the data from 'Letter2Dated' field (from the "tblLetter2" table) into the 'Letter2Dated' Field in the "tblEmployees" table.

All three tables have the same primary key (EmployeeNo), which makes it even more difficult to do this!
I understand this may sound abit confusing so i apologise for this.

I would really appreciate it if anyone could point me in the right direction please.

Thanks alot.

View 4 Replies View Related

Extracting Data From A Field In A Table.

Jan 29, 2007

when I have created my table I have created a field called name that stored both last and first name. Right now I need to extract the the last name from the the field name and store it in another field "lastName". Any ways to do that?

View 7 Replies View Related

Extracting Data From Linked Db2 Table Using Access Make Table Query

Aug 29, 2006

Hi,
I am extracting data from linked db2 table using access make table query. First I create a select query and can view the linked db2 data, but when I change to a make table query I get an error message, "invalid argument", when I run the make table query. There is no selection critera specified. Has anyone had this happen? and Do you know a solution?

Thanks for your support
GinnyP

View 1 Replies View Related

Extracting Middle Initial From Text String

Dec 21, 2005

Hello,

Is there an easy way to split a full name into firstname, middle name and lastname? I have a field name with names such as James R Lowes and i would like to split the name in 3 like.
Firstname : James
Middle Initial: R
Lastname: Lowes

I was able to find a module to extract the first name from a string, but don't know much about VB and cannot figure out to do the rest.

Please help!

thanks,

Pablo

View 5 Replies View Related

Extracting Middle Initial From Text String

Dec 21, 2005

Hello,

Is there an easy way to split a full name into firstname, middle name and lastname? I have a field name with names such as James R Lowes and i would like to split the name in 3 like.
Firstname : James
Middle Initial: R
Lastname: Lowes

I was able to find a module to extract the first name from a string, but don't know much about VB and cannot figure out to do the rest.

Please help!

thanks,

Pablo

View 14 Replies View Related

Extracting Data From Multiple Queries To A Table/excel

Apr 24, 2008

I am an access (2003) amateur, willing to generate an excel file or table from the data from many queries. Moreover, some fields in those queries having different names but same data type, should be filled in the same columns in the new excel/table. Should I use Macro/SQL?:(

View 2 Replies View Related

Using A Table For Both Data Entry And Building An SQL String

Oct 8, 2007

I have a table that lists county names. On the data entry form only the county names are displayed. However, I have another form that is used to build a custom query based on various criteria, of which county is one.

To make the table compatible with both both forms, the data source for the dropdown list for data entry is: Select * FROM county WHERE county <> "No Selection"

For the SQL form the code is simply: Select * FROM county The default value of the dropdown list is "No Selection". When dropdown list for county is set to "No Selection" the program interprets it as "select all counties". Other dropdown lists provide other parameters such as the project year, nature of the project etc. Each of these other dropdown lists also have a default value of "No Selection". The SQL form thus gives me the ability to mix and match several criteria. For example, it will display all projects in a particular year for a particular county or all projects in all counties that involve the construction of a residence.

My question, instead of physically having the string "No Selection" in the table itself, is there a way to embed the phrase "No Selection" in the SQL expression itself and still have it as an option on the dropdown list?

View 1 Replies View Related

Extracting Data Into A New Field

Dec 13, 2005

You guys have been so helpful in the past. Perhaps you can help me with this as well. I believe it’s a bit out of my scope. I have a list that reads similar to this:

Field 1

DMA: ALBANY
CAR 1
CAR 2
CAR 3
DMA: BOSTON
CAR 1
CAR 3
CAR 4
CAR 5
CAR 6
DMA: HOUSTON
CAR 1
CAR 3
CAR 4
CAR 5

I need it to read like this:

Field 1 Field 2

DMA: ALBANY CAR 1
DMA: ALBANY CAR 2
DMA: ALBANY CAR 3
DMA: BOSTON CAR 1
DMA: BOSTON CAR 3
DMA: BOSTON CAR 4
DMA: BOSTON CAR 5
DMA: BOSTON CAR 6
DMA: HOUSTON CAR 1
DMA: HOUSTON CAR 3
DMA: HOUSTON CAR 4
DMA: HOUSTON CAR 5

If my list was this short, I would do it all manually, but my record list is in the 10,000s. Basically I want field 1 to read: DMA:ALBANY and Field 2 to read:CAR 1, and so on. I’ve tried linking the table to itself in a query, however, that hasn’t proven to be helpful. I've also searched the FAQ's and newsgroups. Any insight would be appreciated.

Thanks,
Amanda

View 3 Replies View Related

Extracting A Portion Of Data

Oct 3, 2006

I probably have an easy question for one of you out there, but I just cannot get it myself.

I need to extract the data between two characters in a string.

The data will be similar to this:

T4454: Text Text-Text: $296.07: Text Text

I need to get the dollar amount between the dollar sign and the colon on the right of it.

So far, I have this for my query:

Amount1: Mid$([subject],InStr([subject],"$"))

Which gets the amount to the left side, but also has the $ in it.

In the example above, I would like to just end up with 296.07 as the result from the query. I should also add that the dollar amount may vary from 1.00 to #,###.00

Thanks in advance for any help you can provide

View 5 Replies View Related

Extracting Data From Combo Box

Aug 1, 2006

Hi,
I have a combo box, which the first and last name of an employee is combined using trim. Does anybody know of a way to seperate both fields as these would be used an a query so I would need them seen as 2 fields otherwise there won't be any matches.

Cheers,
Ben

View 1 Replies View Related

Extracting Data From A Single Cell

Feb 20, 2005

Hi,

Is there any way that i could extract data from a single cell inside the table to the forms, and vice-versa?

View 1 Replies View Related

Extracting Concatenated Column Data

Oct 23, 2007

Hi, I need a little help. I will give you the background on what i am doing to hopefully make this easier to resolve. P.S I am open to other approaches.

1) Items are requested for order indervidually

Table/Query looks like this

Code:ID#PartNumberStatusQty ReqOrder StatusNeed DateWork Order110-980-21Stock Required2Waiting26/10/2007WO07-12CR212-5-3Stock Required30Waiting26/10/2007WO07-1310-980-21Stock Required1Waiting28/10/2007WO07-24CR212-5-3Stock Required20Waiting28/10/2007WO07-25CR212-5-3Stock Required15Waiting28/10/2007WO07-2

2) The following Module concatenates the columns (my idea being the user can order all items in bulk.

Code:Public Function Conc(Fieldx, Identity, Value, Source) As Variant Dim cnn As ADODB.Connection Dim rs As ADODB.Recordset Dim SQL As String Dim vFld As Variant Set cnn = CurrentProject.Connection Set rs = New ADODB.Recordset vFld = Null SQL = "SELECT [" & Fieldx & "] as Fld" & _ " FROM [" & Source & "]" & _ " WHERE [" & Identity & "]='" & Value & "'" ' open recordset. rs.Open SQL, cnn, adOpenForwardOnly, adLockReadOnly ' concatenate the field. Do While Not rs.EOF If Not IsNull(rs!Fld) Then vFld = vFld & ", " & rs!Fld End If rs.MoveNext Loop ' remove leading comma and space. vFld = Mid(vFld, 3) Set cnn = Nothing Set rs = Nothing ' return concatenated string. Conc = vFldEnd Function

3) A query calls on this

Code:SELECT Conc("ID#","PartNumber",[PartNumber],"Qry_70_BOM_Awaiting_Order") AS [BOM ID#], Qry_70_BOM_Awaiting_Order.PartNumber, Sum(Qry_70_BOM_Awaiting_Order.[Qty Req]) AS [Total Qty Required], Conc("Work Order","PartNumber",[PartNumber],"Qry_70_BOM_Awaiting_Order") AS WOrdersFROM Qry_70_BOM_Awaiting_OrderGROUP BY Qry_70_BOM_Awaiting_Order.PartNumber;

Producing this result.

Code:BOM ID# PartNumberTotal Qty RequiredWOrders1, 3 10-900-213 WO07-1, WO07-22, 4, 5 CR2162-5-365 WO07-1, WO07-2, WO07-2

OK, Now after that, (ALL ABOVE WORKS FINE).

I would like my users to purchase againts the new BOM ID# records as seen above. Once a order is placed against these i will, put something against each records to show there order status. (i'll take care of this part.)

Code:BOM ID# PartNumberTotal Qty RequiredWOrders Order Status1, 3 10-900-213 WO07-1, WO07-2 Waiting2, 4, 5 CR2162-5-365 WO07-1, WO07-2, WO07-2 Ordered

Finally, sorry about the lenght of this, I need to be able to use the above BOM ID# to reference back to the the ID# table at the start of the thread. In this example Id's records 2 - 4 and 5 will show ordered.

In other words how do i extract 2, 4, 5 back out of the above and into there original state, but including the order status?

Hope this makes sence

View 1 Replies View Related

Queries :: Extracting Data From A Query

Jun 19, 2015

SELECT tblLeaveBalance_Cashables1.Facility, tblLeaveBalance_Cashables1.Division, tblLeaveBalance_Cashables1.Position, tblLeaveBalance_Cashables1.Name, tblLeaveBalance_Cashables1.[Class Code], tblLeaveBalance_Cashables1.Class_Title AS Classification, tblLeaveBalance_Cashables1.CBID, tblLeaveBalance_Cashables1.[SS Months], tblLeaveBalance_Cashables1.Age,

[code]....

I need to extract data that where vacation time is >640 or annual leave is >640 and where personal Hol hours are >24 and CBID is M06,S06 and R06.

View 4 Replies View Related

General :: Extracting Data In Forms?

Jun 12, 2014

I have managed to get my access DB set up, get the data entry, searching and editing working perfectly (in forms)

The next stage is extracting data from these.

ie I four of the columns I have are

Date Received
Portal (drop down list of 3 choices)
User Initial (list of 4 choices)
Days of the Week (ie Monday, Tuesday etc)

I need to be able to search through the database, ie between X Date and Y Date and mark the totals from each of the sections. so the following

Date: 01/06/2014
to
Date: 13/06/2014

Portals 1 = XXXX
Portal 2 = XXXX
Portal 3 = XXXX

Total = XXXX

Then I'll need to do one for all of the above. and more.

So how can I do this? will a report do it? or a form where it searches down and displays totals?

View 7 Replies View Related

Modules & VBA :: Extracting Data From PDF Form

Aug 7, 2014

I am trying to extract information from a fillable PDF form that my company has created. Rightfully so, they have completely locked down all options, so I am not able to convert the file in Adobe Reader to a text file. I've seen options to extract information using VBA code with Adobe Acrobat; however, I do not have a license for the program. Is there any VBA code that uses Adobe Reader to extract information and put it in an Access database?

View 9 Replies View Related

Tables :: Importing / Extracting Data From PDF Files

Nov 25, 2014

I am building a database for a coworker. For one table, he'd like to extract data from different companies' financial reports, specifically a Schedule RC-B (schedule of securities). The format it comes in is PDF, but there are options to download XBRL, SDF and Taxonomy. My computer doesn't recognize any of these other formats. Plus, I'm guessing each company will have a slightly different format, so I don't know if there'd be an efficient way to set up an automatic import. Any way for importing/extracting data from these files?

View 2 Replies View Related

Modules & VBA :: Extracting XML Data From Multiple Folders

Jul 1, 2015

I have over 500 folders that contain XML files in each folder. I need to go through each folder and append the data from the XML files into my database.

View 1 Replies View Related

Queries :: Extracting Data From List In Separate Field

Sep 19, 2014

695515*97
531924*11
495853*131
377035*181

Is there a way to extract the 97, 11, 131, 181 numbers from the above list in a separate field?

View 1 Replies View Related

Extracting Common Data From Tables To View In A Report

Nov 12, 2012

I have a feedback database with 3 tables, one for complaints, compliments and other. They have the standard common fields such as name, address, date received, nature of enquiry, investigating officer etc etc.A person can have more than one record in the complaints table and that same person could also have 1 one more records in the compliments and others table. What I would like to do is to be able to display the data for that same person that appears in the complaints, compliments and others table in a report using a search facility by either name or address. This will therefore display the number of times that this person has made contact together with dates and the reasons.

View 1 Replies View Related

General :: Extracting Data From Access Without Knowing Number Of Results

Jun 25, 2014

I wish to extract data from my access database...namely all packages we are taking to the post office for dispatch that day. The post office form looks like this...

I need to extract the name the address & postal service used (this data is all contained in our access database. I envisage using a 'flag' to trap those orders that haven't been posted yet (in other words export only those database entries that have not been flagged as posted, then afterwards update the database to change the flag to 'posted')

My dilemma is that I've not really used reports much (I'm assuming that reports is the correct way to go here)...how do you format the output of a report, when you don't know how many lines are going result (some days might see 10 order, other days 20 ...and so on)...

View 3 Replies View Related

Trouble Extracting Field From Table Based On 2 Criteria

Jun 30, 2005

Hi all,
Been pulling my hair out about this for a while, so i hope someone's feeling kind:


http://www.agga.dsl.pipex.com/pics/AWF01.gif

I'm using the BHRefPrefix to build part of a reference number. The Principal and Site exist on a separate form, and i need to use both as criteria in order for the correct BHRefPrefix to be returned.

I sort of get it working using two joins, but it doesn't let me add any new records. I've tried straight queries, queries with DLookup expressions, and i just can't get what i need.

Pete

View 3 Replies View Related

Modules & VBA :: Search Table With Attributes - Extracting HTML Values

Oct 3, 2013

I have a table in msaccess which i would like to use as a search term to search values in another table. What i wanted to do is search the table with attributes and save all found attributes to a new table with its primary id.

Search_Keyword_Table
Id ---- Search_keyword
1 ----- Size - S
2 ----- Size - M
3 ----- Size - L
4 ----- Size - XL

Table to be searched

Id ----- Attributes
1 ----- <select name="attribute" id="attribute"><option value="Size - M">Size - M</option><option value="Size - L">Size - L</option></select>
2 ----- <select name="attribute" id="attribute"><option value="Size - S">Size - S</option><option value="Size - M">Size - M</option><option value="Size - L">Size - L</option></select>

Saved table results
Id ---- Attributes_found
1 ---- Size - M
1 ---- Size - L
2 ---- Size - S
2 ---- Size - M
2 ---- Size - L

View 2 Replies View Related

Modules & VBA :: Test String Test Based On Table Data

Oct 31, 2013

my issue is i have multilble text box in my form & based on change in one of this text box i need the code to compare between data in the form & table & returm Msg if it is not matching. attached screen FYI.

i look in the internet but i could not figuer out the VBA code since i do not know VBA. what comes to my mind to to use select case.

View 14 Replies View Related

Modules & VBA :: Convert JSon Data String To Readable Data

Jun 2, 2014

thos is f.i. a json data sting :

{"vip_kaarten":"0","reg_kaarten":"0","extra_vip_ka arten":"0","bedrag_extra_vip_kaarten":"0.00","extr a_reg_kaarten":"0","bedrag_extra_reg_kaarten":"0.0 0","vrjr_kaarten":"2","extra_vrjr_kaarten":"0","be drag_extra_vrjr_kaarten":"0.00","website_link_spon sorpagina":"1","website_banner":"","social_extra_m ededelingen":""}

How can i convert this to readle data?First string is the field name, second the value.

View 4 Replies View Related







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