I am trying to construct a query to extract mailing addresses from a table. I have individuals entered into a table (a separate record for each person) but if they are married I want an address such as Mr and Mrs J. Doe so that only one address label is printed off so that only one letter is sent out. If one of them dies then the address should only go to the surviving party eg Mrs J.
View RepliesHi there,
I am a relative newcomer to Access, and am trying to work out if there is a way to isolate email addresses in a line of text, and delete all the text around them.
I have basically got a column which contains blocks of text and I want to extract the email addresses that are embedded within them. Does anyone know if this is possible by running queries?
Thank you in advance for any help! :confused:
Dan
I am trying to loop through a query results to extract email addresses from a query result.
I have the following code
NameCriteria = Forms![MainMenu]![tbl_Course_Details]![Course_Name].Value
DateCriteria = Forms![MainMenu]![tbl_Course_Details]![Course Date].Value
Dim db As DAO.Database
Dim rs As DAO.Recordset
Dim sqlStr As String
[Code] .....
If i run this in access as a query then it returns results but when i run in there then it says no current Record and i cant work out why?
Someone on another post said that sql uses US date format so i tried converting to us date and still no results?
I have to prepare invoice statements to send to my clients. I have managed to generate the invoices as a report.
However, I want to add two types of mailing addresses to this invoice report: "From" mailing address (my company's mailing address) and a "To" mailing address (the recipient's mailing address).
I have created a Clients table which contains the company's mailing address details and a separate ClientContacts table which contains the primary contact name and the secondary contact name (along with their email addresses) for the respective company.
My problem is how to incorporate this information on the invoice report. What I have done is to create an unbounded text box on the invoice report and manually type in this information. Is there a way to add my company mailing address as a "From" mailing address and the recipient's mailing address as a "To" mailing address on the report automatically?
I have been using Access for several years but always in a basic way. I have started work on an existing Members database where the Members Addresses have been pasted in from Notepad or been imported from csv or manual entry.The ones that have been manually entered are on one line. The ones that are imported from csv or pasted from Notepad are on multiple lines like you would naturally type on an envelope..In Notepad they look like this:
'Oakdale' 123 Expression Road
Name of Town
Name of County
Postcode
Sometimes the addresses have 4 lines and sometime they also have the name of a village which makes it 5 or 6 lines. When imported into Access they also view (datasheet view) in the same way with the return at the end of each line. However, I cannot enter a new address in this manner directly into Access.How can I enter the address text into a text filed and be able to format it so that it looks like the above?
I have searched many times for an answer to this but cannot find a way of explaining it that search engines understand. I first came across this 12 years ago when I worked on a massive Members database for a radio station all the addresses where formatted in the same way and had been imported through csv. When I do this the csv file marks up the text as "'Oakdale' 123 Expression Roadâ–¡Name of Townâ–¡Name of Countyâ–¡Postcode"
I have tried exporting to csv ad then importing it into GMAIL and this works fine I have also printed off labels and envelopes and all the addresses print fine too; I just don't know how to input it in that format other than pasting all addresses through a basic text file.
I currently have 8 tables in my Database.
How can i extract all the information in those tables and put all the data into one large table? I want to extract everything apart from one table?
and can I format the large table once the data have been put in i.e. insert new Columns at the start, and populate fields based on the value of other fields values?
Any ideas or help? thanks
Kind Regards
Richard
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?
:( Hi Guys,
I have a very basic DB of customers names and addresses. What I want to do is find all the enquiries within the months of August, September and October, send all these enquiries to a new table so that I can use this new table for a mailshot.
The date of the incoming enquiry is in a field on it own and written as dd/mm/2006.
If there is an easy way to create a mailing list from the original table please let me know.
Best Regards
Keith:o
I have a massive database...100,000 records (well, it seems massive to me right now!).
A part of each record is a hyperlink to a PDF document on the server. I didn't map a drive letter for the path, as everything I've researched has said to not use drive letters but the actual server name and the path.
I did that. Unfortunately, they had an issue with the server a couple of weeks ago...and in mapping it again, they added another layer of folders on the way to the location of these PDFs.
Now I have to go in and edit each individual hyperlink to reflect the new path.
1. Is there a faster way to do this? They want their first report half an hour ago.
2. They have a whole new set of these PDFs to be attached to the records that don't have them yet. Is there a faster way to do it than to have to open each record and put in the path to the document?
I have a table that contains a lot of different information regarding to mailing.
There are columns for Name, Address, Address2, Address 3, City, State, Zip, Country, Base Name, etc.
Some units will only required parts of the above information. I'm using the Name as an identifier in the DB only and Address is the beginning of content that I want to use.
My issue stems out of the fact that the City, State, and Country are not always required but the Base Name, may be used in lieu of the City.
I was looking at making checkboxes next to each of the columns on the form on the field and then using those checkboxes somehow to create a report for Mailing Labels.
I'm just not sure how to set this up.
I am preparing mailing label for a political campaign. On my list of voters, many times two or more people are listed at a single address. By consolidating the labels (and postage) I am able to save a lot of money.
I have written a query to group these names into those with 1,2, or 3 or more/address. For labels I am able to print 2 names/label by using the FirstOfFirstname, FirstOfLastname, LastOfFirstName, LastOfLastName generated in my query. This works fine for 2 names per label.
My question concerns 3 names per label. Does anyone know how to include all 3 names? Suggestions have included using a label with "The {LastName} Family" etc. but many times there is more than one last name per residence. Other suggestions are to print individual labels for each individual and overlap the labels to show all the names but just one address. These does save postage of $.42/mailing, but seems wasteful of labels and looks a little crude besides.
What I think I need is a clever query or queries that will be the data source for the labels. I have room for up to 3 names/label. I have been thinking about printing 2 labels -- one with one name and address and another with the rest of the names but this brings up problems of getting everything on the right envelope.
Hi All,
I have attached a DB that I am trying to create for my small business. Every month we have renewals of the client’s yearly contracts. I have created a form called frmRenewells, on this form you need to select a month and a year (please choose October 2006) this then runs query qryRenewell. The query then opens form frmRenewellSheet with the results. This works fine, but I really need the following to happen:
Open form frmRenewells and enter month & year, on clicking the enter button it launches a word template I have created and automatically pulls all the fields required into the word document. I can then press print and it will print a document for each client in the query.
I am unable to go into word and just open the query as it has * or wildcards in it. Therefore word will not allow me to select the query.
I have attached the Word doc as well
Thanks in advance,
Danian
Ok, I've run into two serious problems in testing, and another question that I'm hoping you guys can
help me solve. Below my questions is the code used for making the search query/email program.
Problem #1: In testing, this search program only is working for me for new data. Any data that I had
in the database prior to implimenting the program will not move past the search stage. In other words, I
will do a search, it will find the emails and bring them up in a box, I hit "OK" and it gets hung up and won't move on
to opening up a new Email with the addresses implemented. However if I enter new data in the database, say with a weird
name so its only bringing up the new entry and no old entry, it works fine and opens up the new email.
Problem #2: Items I would like to search like check boxes. Say I have a check box "Donor", this is listed in the database
as a 1 or 0. I can't have my boss searching 1 or 0. Is there a way to change this to, say, being recorded in the DB
as a Yes or No. That way he would check to search "Donor" and type Yes in the search box and this would search the DB
"Donor" column for "Yes" and bring up the results.
New Question: My boss and I reviewed the form as it is so far yesterday. He was asking if I could add a Print Labels option
in there. I'm wondering instead of adding another seperate search box and all that mess for a labels search print, can I rather
add two check boxes, one labeled "Print Labels" and the other labeled "Email". What this would do is depending on the check box
you selected it would either run the search and email like we have it, or clicking the other check box would run the search and print
labels.
Quote:
'Author: Michael Walts, but use it as you like'Important information! this code requires a reference to the Microsoft DAO object library
Option Compare Database
Option Explicit
Private Sub cmdEmail_Click()
'will hold the dynamic SQL query
Dim strSQL As String
'will hold the WHERE clause portion of our SQL query
Dim strWHERE As String
'will hold all the recipients of this message
Dim strRecipients As String
'the recordset we will use to get the emails of the records that match our criteria
Dim rst As DAO.Recordset
'if there is input in the search criteria, then we will run the query and send the e-mail
If txtSearch <> "" Then
'if you have more buttons, just add mosr cases (the value of the radio button
'= the Case number, so Value of the State radio button is 1, etc.)
Select Case opgSearch.Value
Case 1
strWHERE = "WHERE State = '" & txtSearch & "'"
Case 2
strWHERE = "WHERE City = '" & txtSearch & "'"
End Select
strSQL = "SELECT EMail FROM tblUser " & strWHERE
'run the query and get the results into the recordset
Set rst = CurrentDb.OpenRecordset(strSQL)
'Loop through the recordset and add all the EMails
Do While Not rst.EOF
strRecipients = strRecipients & ";" & rst!EMail
rst.MoveNext
Loop
'remove the first ; from the strRecipients
strRecipients = Right(strRecipients, Len(strRecipients) - 1)
MsgBox strRecipients
DoCmd.SendObject , , , , , strRecipients, "News Letter", txtBody, False
rst.CloseSet rst = Nothing
End If
End Sub
'stops a ' entered in the field from breaking the query
Private Function SQLSafe(safeMe As String) As String
SQLSafe = Replace(safeMe, "'", "''")
End Function
I am trying to create a mailing list of patients. Let's say I am creating a mailing list for February. I need the mailing list to consist of people who have had surgery in February from the beginning of the database, and people who have had surgery three months ago, so anyone who had surgery in November. I have created a form that has a button which is connected to a query, the form has a unbound textbox where I can enter the month in (2 for February). Then the query uses the datepart function to search for this month in their date of surgery. But this only gives me people for surgeries with february, how would I get people who have had surgery three months ago in the same query.
View 2 Replies View RelatedI have a table, "TBL_Email", which simply contains a list of email addresses in a field called "email".
I would like to concatenate all of them together into one string, and add semi-colons to the end e.g.
"123@abc.com;456@def.com;789@hij.com"
I believe a may need a record set, but i'm not entirely sure how they work.
Hello all I am using MS access for a little personal project and I have house number and streets along with a day they are serviced.
example:
unit road type day
1 main st friday
2 main st friday
3 main st friday
4 main st friday
5 main st friday
6 main st friday
7 main st friday
13 turtle st monday
15 turtle st monday
16 turtle st monday
I want to make a report of this data to look like this
unit road type day
1-7 main st friday
13-16 tutle st monday
I figure I have to do some sort of query before I can make the report im jsut not sure how to seperate it or what symbols to use. I know this is faily simple I just can not get the computer to give me what I want....
ok I think i figured it out what I did was take unit and I put it in 2 times one fo min and one for max and then I grouped the road by ascending and it worked out
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!
I'm designing a database for a laboratory. There are many tables that will contain test results. They all have in common a field called ID( primary key) linked by a one to one relationship because one patient has reports in different tables. The ID is unique to each patient. For the Reports, i hit the rocks... How can i structure a report that can extract a patient's records from the different tables where they appear in the database?
View 8 Replies View RelatedI've a table containing ebay sales information, let's simplify it...
Code:
buyer email product quantity price total Receipt Number
A Smith a.smith@acme.com gloves 1 $10.00 $10.00 1
P Brown p.brown@jones.com hat 2 $15.00 $30.00 2
After a lot of faffing about (& learning about sub reports), I've now got access producing a sales receipt from the data....it's in the form of a report (the report generated is one long continuous report, with each page in the report relating to a sales receipt number)
What I want to do now is email each page of the the report (like I say, each page is essentially an individual sales receipt) to the associated email address in bulk.
Therefore there may be 20 sales receipts in a long continuous report, how would I break them out one by one to a pdf file & send it to the correct email address which is contained against the sales receipt number in the same table?!!!
I have inherited a 2003 db with unknown PIDs for the Group & User security.When trying to alter User details, I'm asked for a PID, so I'm trying to find the PID for the existing users.
I've gone to unhide the systems tables to get a dump of the data & hopefully find the PIDs but no luck so far.There is a Parent ID & if I run a query from the Users DB file, it appears as if it something like japanese writing ???
I am looking to transfer a number of spreadsheets that I use to track IP Addresses to an Access Database, I have set up the tables and fields, what I would like to do is search for unused IP Addresses from populated table/fields.
View 2 Replies View RelatedI have this database that do contains duplicate record (not duplicate field).What I'm trying to do here is to extract record that is unique to a new table And those record that were duplicate into a new table.the *Distinct that we used in sql will not work because it is not what I wanted. using distinc will give me unique record of the whole database.say i have this database that contain an ID:
2525
2658
2658
2658
2525
3678
so what i want is to extract unique record to a unique table and those that contain duplicate in another table.so the 3678 will be put in a new table and the 2525 and 2658 in a new table,,That way I will see which file will have duplicate
I have a parameter set on the query at run which filters by Region i.e. the box pops up and they enter a criteria..I would like to have a field entered next to Email that would take all the Email Addresses in my query result and concatenate them so essentially I have an email list I can pop in Outlook.
Or alternatively, a solution to allow access to these email lists via a button on a form. It has to be something very simple for an end user to access. For instance right now, to get there list they hit a button to run the query then enter the parameter then if desired, hit another button to export to excel. Today, they use Excel for everything and are used to having the concatenated email addresses in one cell in excel. For this reason, I am trying to have the email addresses concatenated in the query results to mimic what they are used to today. But I am not sold on it if there is a better way that is easy for them.
I'm trying to create a report for how many "nasty grams" (rejection notices) my company has sent to people who keep sending in paper forms when they are supposed to file electronically.Every letter that goes out has information recorded based on whatever they sent to us - so the only remotely reliable way to count how many each person received is by the address on the envelope (people use different names, different business names, use different telephone numbers on the forms, etc).
I just built several queries that feed into a report that gets sent to my boss on a monthly basis to show the people who've sent in more than one paper form and have received our rejection notices more than once.I'm not the greatest at SQL, but I've been trying to find a way to use DISTINCT Addresses, leave all other fields the same (not DISTINCT), to:
1. Only return people who have received 2 or more letters
2. If at least one of the letters was sent more than 90 days ago
AND If at least one of the letters was within the last 90 days
-If at least one was within the last 90 days, only display the most recent send date of the letter (lots of people get back-to-back letters).
3. Display their names, addresses, telephone numbers, the date of the most recent letter sent, count of the total letters ever sent to that person. (the report will already do this, just need Max date)
My first query counts the number of times each address appears in the main table and simply only has [Address] and [CountofAddress]
My second query has the [Name], the [LetterDated] >=Now()-90, and the qryCountofAddress is linked to the main table by [Address], using [Countof Address] >=2...I have tried Selecting Unique Values in the Properties tab. Yes, I have tried INNER JOIN (but can't get the rest of my fields to display once I make addresses distinct).
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!