I need to break up part of the value in one of my fields. I can do it in Excel by identifying the position of the dash with eg. =FIND("-",D13) And then using it in a LEFT function. Or using the text to Columns wizard.
How can I do it in a query, there is no find function. If there is an equivelant what is it?
Sample data:
22DF-RED
33AF-0
44TG-Blue
I need only the data to the left of the Dash
Thanks
Ziggy
Hello,
I have data that looks like this:
blahblah-need
blah-aneed
blahblahblah-aaneed
I would like to have everything right of the dash to get:
need
aneed
aaneed
I have used mid and instr to get the left hand stuff. I've tried to modify to get the data to the right of the dash and I am lost.
Your help appreciated.
I have seen variations of this question and have tried to modify for my purpose, but have been unsuccessful. I need to extract the first four digits of the Social Security Number. We no longer view the whole number, but a variation of the first four of social and first four of first name. I have tried many variations of the Left Function to extract the data from the field:
Expr1: SSN(Left([SSN],4))
Expr1: Left([SSN],4)
Etc.
I get various errors, mostly involving the comma. I am at a loss since I usually search for the code and find that it works. In this case, it doesn't work. I am also trying to extract the first four of the first name and will later combine the two although I broke it down since I couldn't get the basic "left" function to work.
Please help if possible. :)
I am trying to extract data from a query. Part of code when form loads, my questions is bold:
'query criteria
vOrgCode = Me.tOrgCode
'sql statement
strSQL = "Select tblMembers.tOrgCode, tblMembers.tGender, tblMembers.nMonthlyIncome, "
strSQL = strSQL & "tblMembers.nFarmSize, tblMembers.tPrimeCrop, tblMembers.nNoHills "
strSQL = strSQL & "From tblMembers Where tOrgCode = '" & vOrgCode & "'"
[code]...
i cannot build a simple query and save it as reference since the number of organizations may be 25 at the minimum. this is why i want to build it on the fly, so to speak.
I have a table with a field named BinNo which has a list of items with a bin no in the format 1.234( this number can be any number up to 6000.9999).
Is there a way for me to have a field on a form where I enter a number (i.e 1) and it lists all the items with a 1 before the decimal point but not 10 or an other number with a one in it. If I enter 10 then it will only give me the items with 10 before the decimal point and not 11 etc.
I have a query that pulls all the data into it but I only want the query to show the items relating to the number I enter into the form field.
I have a Table1 served by Form1..It is a list of: UnqID, process, quantity, totaltime(in seconds).I want to click on a record to bring up a filtered Form2 with the chosen record on it.What I want to be able to do is to now split the quantity (and the time) and put these new records back into Table1 and delete the original record
EG
ID1,10,write a report,2400
I want to delete this and replace it with two (or three/four etc) replacements, but still adding up to 10 quantity and 2400 seconds so that the new data could be:
ID2,5,write a report,1200
ID3,5,write a report,1200
My initial thoughts are to create a holding table to:Append filtered data on Form2 to a holding Table1hld (i don't know how to do this) delete data in Table1.then enter the new quantities into a holding Table2 (that I will input myself) and then append (through a series of queries back into Table1).The first problem is how to append (and subsequently delete) the filtered record from Form2 to Table1hld.
Currently I have a remark field and want to just extract the amount. The data input is not in a structured manner therefore I could not use the mid function.
Example :
Remark
CASH PURCHASE $50K.DEBIT 3007484701
SUB $20,000 FIRST STATE DIVIDEND
AMT:$10,900(FS BRIDGE FD)NO DISC
I have a very simple question that I have not find the result or maybe I do not know what to look for!
I have a query containing a field named year and one named sales, what I need is to have a single result of total sales per year. example
year sales
2005 100
2005 100
2006 80
2006 70
2006 60
The result I would like to put then in a form is:
total 2005 200
total 2006 210
All this by simply running a query without indicating any parameters.
Thanks
M
OK, so i have finally got round to re-building my client's database as suggested several times by RV.
I have three main tables that I need to extract data from - tblMembers, tblModules, tblCompleteModules.
Basically its a database containing the training details of Scout Association leaders. Each Leader needs to complete 26 modules over a period of time.
tblCompleteModules contains the data for the modules that have been completed, MemberID, ModuleID Date Completed and CompletedID.
I know how I can extraxt the modules that members have completed, but how can i extract the data for the modules that have not been completed. For example I need to report on the number of people who haven't completed module number 20 for example.
Any ideas?
I have a word doc from which I want to import data in a Database.
The word doc has a table, something like this :
Projectnumber 10700004
LSname LSTK number one
POnumber 1170-LST-04
Contactname Mr. Karel van Straten
Vendorname Mothercompany name S.A.
Manufacturer Operating Company name
Otherdata Others
Mancity 46100 Hamburg
Mancountry Germany
Manorder 31-0111-009
Mancontact Mrs. Angela Duval
Mantel 0049 2323 209 151
Manmail email@testing.de
At this stage I copy the table data and paste it into a memo field in a Dbase Query and try to manipulate the text in that query to get some of the data segregated , i.e Projectnumber : 10700004 , Mancountry : Germany , etc.
However, this doesn’t work as I want.
e.g. I’m trying to get the Manmail text thru this qry :
Manmail: Mid([Memofield],Len([Memofield])-InStr([Memofield]," ")) , but the output is not consistent.
Would there be a better way to get the original ( doc-table) data in the same way in my database.
Note :
I could use an additional step : copy/paste to Excel and then import to Access, but that’s something I would like to avoid.
Any suggestion will be appreciated.
Cheers, Ron
I am trying to extract data from one field and then put it into another field. I have 2 fields, Code_A and Code_B. I am trying to take the Code * data from Code_A and put it in Code_B. Any help is appreciated.
Code_ACode_B
AlphaCode 3
Code 3
AlphaCode 3
Code 2
DeltaCode 3
Code 3Unknown
CharlieCode 3
Code 3
So the field shows the account number as 00000000234342001.
I need it to display in the query as 234342-001.
In design view I am using Account Number: VAL([MST1_CAV_MBRDETL.ACCTNUM]) to remove the leading zeros.
But I am unsure about the rest.
In addition to this answer, how to manipulate strings and numbers to do this kind of thing? Like pull the first 3 characters and so on.
Hello,
Is it possible please to extract a tables structure and data into SQL in Access. I know how to do this in phpmyadmin (an internet based DBMS) but not so sure in Access.
What I want to do is to create an identical version of the table in SQL Server, so therefore require the structure and data in SQL format if possible.
Any help is much appreciated, thank you.
I have a really simple question :-
I want to write a simple routine to enter a value into a text box, retrieve a record from a table using the value entered as a key (or display a message if it doesn't exist) and then populate a form with the other fields from that record.
So if I entered code 123 it would find the record where code = 123 and display the associated description and price for code 123.
I've created the table and the form but am struggling to do the bit that retrieves the data ! Help!
I have imported several Excel files into Access to create tables in the database.
I teach online and basically I need to know how to extract certain bits of data from each table and put them together.
For example:
Table #1 is my student roster list and contains the fields: Firstname, LastName, SchoolName, and several other fields.
Table #2 is a list of schools throughout the state with fields such as: SchoolName, Registrar, ContactPerson, and so on.
There are several other tables involved but I’m trying to make this question as simple as possible and if I can get this question answered, I think I may be able to figure out the rest.
I would like to print a report out for each individual student that will include the school name from Table #1 and match it with the same school name in Table #2 and then extract the pertinent school information from Table #2 for that school.
I have more than one table with a list of schools. Should I name each field that pertains to the school name with a unique name?
To clarify….
Table#1 can have the same school name listed any number of times because some of the students attend the same school.
Table #2… Each unique school name will be listed only once.
These Excel files come to me regularly as they are updated and I am trying to find an easy way to extract the data that I need.
Can someone please tell me how to write this query?
Thanks so much for your help!
Through searching, I have seen a lot of post regarding 3rd party downloads to extract pdf data to a table, any coding or a sample database that doesnt require 3rd software?
In the end, I want to import the pdf data to the table, then attach the pdf file automatically. URL....I have a pdf form that was developed in Adobe Live Cycle. For sake of example, lets say my form object names are:
TextField1
TextField2
DateField1
CheckBox1
CheckBox1[3]
Full Name, Forename, Surname, Salutation. This data has been extracted from another database where the Surname field was not required but it is now. So i need to find a way to pull data from the salutation field into the surname field where salutation has data but surname is null. The next one when surname and salutation are null but full name has data i need to pull that across.
View 11 Replies View RelatedHow can I stop agents from leaving a database open after they have finished there days work. I maintain and modify a database from home, but some agents forget to close the database at the end of the day. That prevents me from doing any modification on the database. Maybe a simple message that would come up and close on a regular timed bases. Or some other simple way of reminding them to close before leaving for the night.
View 9 Replies View RelatedI've built a macro with wildcards that replaces multiple spaces of varying numbers after perods, commas, and before and after numbers. Now I want to add a search for the same before and after en dashes and hyphens. (Pretty soon I'll have Word streamling a lot of documents I edit!)
View 1 Replies View RelatedHi All experts,
I got two tables while one table contains (sales data) and another one contains (criteria). I would like to extract sales data based on the criteria tables and export to a new table.
Which method is the best to complete this?
Criteria contains many lines like this
CustomerID, ProductID & InvoiceDt
A, Guliter, 2007/10/5-2007/11/7
B, Piano, 2006/7/1-2006/12/31
Thanks in advance!
Regards,
Stanwell
I'm trying to extract invoice and payment transaction data from a very old version of quickbooks. This very old version allows for the export of customer and item lists to .iif format which I can view in Excel. However, there is no method of exporting transactions. All of my searches for how to extract the data have come up empty.
I am pretty sure that I once found a post relating to looping through the file line by line and converting the data using vba. I can't find that post anywhere either.
Any method of extracting data from a quickbook .qbw file. I'm willing to do a fair amount of manual parsing if I have to, but I need to get to the data first.
102,34112,021502503130010010000,QAR,1,24,1,{NULL:0 },{5:Y},6,14.5,31-MAR-2011
Above data is in one field , i want and expression to extract the data between two consecutive comma set by specifying the comma range.
For example if i specify range 2 to 3 then formula should return "021502503130010010000"
Not sure if this is possible but I am trying to extract data from a word document to set up a database.
Basically I am trying to capture data from completed forms, similar to the sample attached, the actual blank form is 20 pages long and once completed can be as many as 30 pages, or even more, although the format doesnt change (i.e. individidual cells will expand to fit the data in the cell).
I want to be able to scan through the completed forms and extract the data i.e. Full Study Title, Short Study Title, Study Type etc. into a database.
The issues:
1. Each document will have a different name but will be stored in the same location.
2. What is on page 8, for example, in one document is not necessarity going to be the same on every document (due to expanding cells)
3. Each sector is in a separate table but tables can spread across several pages.
4. Some data is stored in a checkbox format rather than text.
5. I dont want to extract all of the data, only certain sections (at least at this stage).
I am a new user to access. I would like to extract a table from a URL, it is 4 fields and has around 150 records. One of the fields is made up of png files, relatively small ones. Is it possible to extract all the text and png files straight into access without individually saving each picture and attaching it to a record?
View 2 Replies View RelatedI want to extract specific data from a text file and place it in my table.The following is an example of the data:
Display Author="All Saints" Title="On & On" Genre="Pop" Color="7693971" Tag="2"
I want to extract the following:
Where the word Author= appears to extract the data between the double quotes, so in the above case I want to extract "All Saints" excluding the double quotes.And then where Title= appears extract "On & On", again excluding the double quotes.So I would end up with data in my table looking something like this
strArtist [Author] strSong [Title] strGenre [Genre] strYear [Year]
All Saints On & On Pop
America Venture Highway Rock 1972
Amillionsons Misty Blue Pop 2002
my table is name tblMusicList, the fields are:
strArtist
strSong
strGenre
strYear
I want to use the expression Date() in a query to get today's date and then format this to "ddd" to give me the day of the week.
I then want to link this "ddd" to data in my table that contains the three letter days of the week.
How do I make the day become "static" in order to be able to link.