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 RepliesIs there a way I can take each entry in a memo field and put it into a text field in a separate table. The database is getting really big and the customer notes field for each record has lots of entries. The memo field looks like this:
8.4.14 Ordered 2 cartons
20.3.14 Ordered 2 cartons
4.3.14 Ordered 2 cartons
18.2.14 ordered 1 carton
30.1.14 ordered 3 cartons SCENTED wipes
[Code]...
I want to take each line and put put the date in a date field and the text in a text field in a separate table linked by CustID. Is there a way to do that?
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
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.
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"
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.
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 field which contains "01501 PRIKLJ. VRHNIKA - LJ / Stevno mesto" .
1.) I would like to extract just text part from that field, how? *number is always on beginning but different 1-10000
2.) Optional, how could I delete in this new only text field part of text after / ...*some rows have some dont /
I have a table with a string field included for an address.
Some rows have postcodes and some dont
How can I extract the PostCode values from the 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.
I have a notes field in the customer table that is a memo field. An example of one customer's notes field data :
<div>20.3.14 Ordered 2 cartons</div>
<div>4.3.14 Ordered 2 cartons</div>
<div>18.2.14 ordered 1 carton</div>
<div>30.1.14 ordered 3 cartons SCENTED wipes</div>
[Code] ....
I want to extract the date to append to a date field in a "Calls" table and the comment into a text field in the "Calls" table. Is there a way I can do this via query or code?
791335.12pack. This is the object that is in one of my access fields. I need to extract the 12 and place that in another column called qty.
View 1 Replies View RelatedI am trying to do some simple table operations. I have a field (Date) containing dates, and an empty field called Day.
I want to extract the day number from the Date field, and write it to the Day field.
I didn't get very far until I ran into trouble when setting my recordset. I get the error "Too few parameters, expected 1". Clicking "Debug", will highlight the code line "Set rs = db.OpenRecordset(sqlString, dbOpenDynaset)".
So far, my code looks as follows:
Code:
Private Sub Command16_Click()
Dim db As DAO.Database
Dim rs As DAO.Recordset
Dim sqlString As String
Dim dataDay As Byte
'Open connection to current Access database
Set db = CurrentDb()
[Code]...
I am not very familiar with the various types of recordset settings. I just want to be able to read data from the Date field, and write data to the Day field.
How Mark disappear field data based on field data last.
Example:I have a field type in the name and on behalf of another field No.
In the case of the Type-B data is deleted Number field, which is before the character.
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
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]
I have read some tutorials online on how to split data into multiple fields.
http://www.databasedev.co.uk/splitting_fields.html
While this is the gist of what I need - it is much more complex.
I am dealing with adresses.
This inherited database has 2 fields - ZIP, and ADDRESS. I need to extract the data within the ADDRESS field to populate other more detailed named fields.
I would like to ultimately have my fileds to be: STREETNUM, STPREDIR, STREETNAME, STREETSUFX, SUD, SNUM.
Example: (Current ADDRESS data)
512 N Manchester Dr Ste 201
(extract ADDRESS and import into)
STREETNUM = 512
STPREDIR = N
STREETNAME = Manchester
STREETSUFX = Dr
SUD = Ste
SNUM = 201
--
Now I have tried several queries to extract the first bit of data 'STREETNUM', using the example on the link I posted above. INSTR, RIGHT, MID, LEFT.
Problem is there are also P.O. Boxes formatted like: PO BOX 1234, so my STREETNUM column includes the PO, when it should include the 1234.
PO Box 1234 should translate to:
STREETNUM = 1234
STREETNAME = PO BOX
All the rest of the fields aren't needed.
As you can see, I'm in OVER my head.
I would greatly appreciate any assistance or direction in accomplishing this task.
I am not a beginner with Access, but I am far from being an expert. A breakdown of some complicated tasks may be necessary... :-)
Thanks in advanced.
I'm trying to set up a query where it returns me only the items that are repeated.
ie...I have a table that has clients that are one time clients and other that are repeated. Each client has a client number. I would like to get only the clients whose client number appears more than once. Is that possible?
I have a (simplified) table with
ID | Date | Time | machine | value
304 1-1-2014 06:00 115 0.54%
305 1-1-2014 06:00 111 0.56%
306 1-1-2014 07:00 111 0.52%
307 1-1-2014 07:00 115 0.53%
308 2-1-2014 07:00 111 0.56%
309 2-1-2014 07:00 115 0.58%
310 2-1-2014 06:00 111 0.54%
311 2-1-2014 08:00 115 0.53%
I try to find the ID corresponding to the maximum date+time grouped by Machine.
In this case I would like to find 311 (maximum date/time for machine 115) and 308 (maximum date/time for machine 111)
The way to extract the maximum date + time is not difficult
SELECT Max([Date]+[time]) AS datevalue, tbl_TexControl.Machine
FROM tbl_TexControl
GROUP BY tbl_TexControl.Machine;
But how do I get the corresponding ID? Not with Max([ID]) because then ID 310 is selected which is not the maximum of the time.
Model Full Name: "Jones, John P." (this was the one not in MS' examples)
Last Name Extract = Jones
lastN = IIf(InStr(1,[Model Full Name],",")=0,"",right([Model Full Name],len([Model Full Name] - (InStr(1,[Model Full Name],",")+2)))First Name Extract = John
firstN = IIf((InStr(1,[Model Full Name],",")+1)=InStrRev([Model Full Name]," "),Right([Model Full Name],Len([Model Full Name])-(InStr(1,[Model Full Name],",")+1)),Mid([Model Full Name],(InStr(1,[Model Full Name],",")+2),(InStrRev([Model Full Name]," ")-1)-(InStr(1,[Model Full Name],","))))