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
Table 1: Students that are registered this semester. The unique Identifier = full 9 digits of SS#'s
Table 2: Students and regular people current working for the college. Unique identifer = only the last 4 digits of their SS#'s
Objective: I need to know what registerd students or regular people are currently working in the college. In other words, compare table 1 and table 2. The problem is I do not have a common unique identifier in both tables.
Additional Info: Each table also has: * Last_Name * First_Name
I was thinking many do a DUAL comparison...Let's say compare the last four digits numbers and the last and first name from table 2 vs the last four digits and the last and first name from table 1.
When data is input to my form and when i click on preview report, the preview report displays all results (my report source is from my query). However when I click on print preview a portion of data is missing. Basically is the check boxes I have in the report. They all are grayed out. These check boxes are unbounded and not related to the query. Basically I have an event procedure created upon clicking print preview as follows:
Private Sub PreviewMRO_Click() DoCmd.RunCommand acCmdSaveRecord DoCmd.OpenReport "rptMRODataEntry", acViewReport If MROReason = "Repair" Then
[Code] ....
The selections are all drop down boxes in the form and upon selection it will populate the text boxes in my report. It seems to work perfectly upon clicking the preview report button but when I click on print preview the check boxes are grayed out.
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.
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.
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
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.
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?
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.
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;
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.)
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?
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?
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.
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?
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.
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?:(
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.
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)...
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?
I posted this in the table section however it is better suited here I think.
I have a field in a table that I imported in. This field has the first/last/ and Middle initial all in one. So name=Frank H. Urtz.
So I want to set my criteria to say something like give me everything up to the first " " as expr1. Give me everything from first " " to second " " as expr2. Give me everything thing from second " " to the end as expr3.
I am adding another portion to my database and since this is a little different than the other parts I created, I wanted to get some input from the experts.
I am adding Certifications to my database. I am currently tracking skills, OSHA, & Licenses for all employees. My current count for the Certification is around 45. I'm wondering if I should set this up like I did my Skills (look up table for skills that is connected by a one to many relationship to another table) or have all the info on one table?
I am trying to create a Form in which a user is prompted for a road name and that a listing of all values having that road name are displayed. My problem is that Road Name in the database is a combination of the road name and road suffix such as Evergreen Street. I want the operatior to be able to enter just the road name, Evergreen, and get a listing of all Evergreen addresses; Evergreen St., Evergreen Rd., Evergreen Ln., etc. How do I create the prompt so that it ignores the road suffix and searches just on the name? I should add that I know how to use brackets in a form prompting the user to input a parameter. In a query I can use "*" to find all those records having a portion of the desired data. For example in my query I can use "Evergreen*" to find all records where the fisrt portion of the data record is Evergreen. How can I apply the "*" to a user entry prompt.
trying to get something working on a form. When creating a new client, due to privacy, we have to create a Unique ID for each one. The ID consists of their initials, the year, a serial number and the location. I have it mostly working except for the year. Let me show you what I have and the results, then what I would like the results to be.