I need guidance on the best string manipulation functions (Instr, Left, Right) to cleanup my mother's Christmas address list of 300+ names.
I have successfully imported the text file into Excel and exported to Access; fieldnames: FULLNAME, ADDRESS, CITYSTATEZIP
I have found instructions on how to breakout FULLNAME field into FIRSTNAME and LASTNAME.
But within the FULLNAME field are many combinations of titles (Mr., Mr. & Mrs., Dr., HON.) with inconsistent periods applied.
Which one of string manipulation functions:
Instr
Left
Right
would be best for extracting these various titles from this name field?
I understand the concepts behind the above functions, but not enough experience using them to understand the tedious syntax or which string manipulation function would be best for extracting the varying title entries to a separate created field called TITLES.
So far, I have deduced this will be a multi-step process. But asking for guidance:
1.) Which string function is best suited for this?
2.) Example of the function syntax for an update query?
2.) Suggested order to administer update queries?...
to extract misc titles from the FULLNAME field.
I am a novice-casual Access user.
Thanks, Greg
(If someone would copyright these steps into a book called "Cleaning Up Mom's Christmas Address List"... I am sure they could retire from sales on Amazon. :-)
I think I know the answer but want to check. I've been asked to create a query, without querying a query first, but it's the only way I know.
I have two tables
Table1 will have data in a column that is 9 characters long ULCABC123 ULCABC124 ULCABC125 PLTABC123 PLTABC124
Table2 will have data in a column that is 6 characters long ULCABC PLTABC
Question: Can I create a Join from Table2 Field with the Left(Field,6) from Table1
I was thinking something like this. (but then I can't enter design mode) Query1 - Test
Code: SELECT Table2.ORDDETTYPE, Table2.DESCRIPTION FROM Table2 INNER JOIN Table1 ON Table2.ORDDETTYPE = Left(Table1.ORDERDET,6) GROUP BY Table2.ORDDETTYPE, Table2.DESCRIPTION;
I presume the only way to do this is first query Table1 (and call Query2) and return the first 6 characters and the create another query (Query3 in this case) using Query2 field joined with Table2 field.
Query2
Code: SELECT Left([ORDERDET],6) AS NEWORDDET FROM Table1;
Query3
Code: SELECT Table2.ORDDETTYPE, Table2.DESCRIPTION FROM Query2 INNER JOIN Table2 ON Query2.[NEWORDDET] = Table2.ORDDETTYPE GROUP BY Table2.ORDDETTYPE, Table2.DESCRIPTION;
I have several thousands of lines of data which I wish to manipulate programmatically, if at all possible. I think that all of the possible permutations are summed-up by the following examples :
123 A text string 2-8 Another text string A-C Another text string here 3-20 And some more text 3A-126B More text Some text without any numbers or letters at the left
What I need to do is :
Move the alphanumeric data at the left to the right. So the data would end up looking like this :
A text string 123 Another text string 2-8 Another text string here A-C And some more text 3-20 More text 3A-126B Some text without any numbers or letters at the left
I suppose it hinges on identifying where the first space in from the left appears, cutting the string at that point, and
While processing string manipulation on a table (140K records, 200-250 MB) the file has corrupted, and I lost all vba Modules, but the data and other DB objects seemed to be in tact.
I now have a query with a text field, when I make a simple join SELECT, the text comes in fine (and, of course, seems fine when presented in table), but when Group by - I get Gibarish: "CARVEDILOL 6.25MG, TABLETS"
Is presented in Group by as: "砅5"
I have recreated the file, importing queries, including this one, but then recreated it as a totally new query, but get that same results.
Is there an easy way to make a full page address labels (30 to a sheet) without having to create it by dimensions? I can't seem to get all the dimensions correct.
My database contains business names and addresses in separate fields (name, address, city, zip code, state, country)
Now I have to build the address block for labels.
If there were just US addresses it would be easy but a business can be located in any country with the problem being that countries require the block to be build in a specific way (order of city, state, zip code).
In some cases a particular section has to be upper case as well.
Of course I can build it manually in a memo field but the with chance of something easily going wrong I like to automate it.
It does mean the same info will be stored twice which is basically a no no.
I'm thinking of making a separate table with records for all possible layouts and use those as a source for a memo field in the business table. However I can't oversee if that would work alright or not.
Or should there be a way to generate the blocks on the fly?
I am trying to generate standard Avery 2160 address labels. Fonts are small enough to allow for up to 4 print lines none of which quite touch. Players have entered their own names and addresses via a website form, so I we never quite know what is in the fields. If the player has a foreign address, it will all be entered in the address field, whereas US addresses have 1 or 2 lines in the address field, and city, state and zip in their respective fields. Line 1 is set as name, no shrink or grow. Line 2 is set as address, shrink and grow set to yes, and line 3 (text4) is set for city & state & zip, concatenated and trimmed in the query, shrink and grow no. The detail OnPrint event is the following:
Code: Private Sub Detail_Print(Cancel As Integer, PrintCount As Integer) Me.Text4.Visible = False If Len(Text4 & vbNullString) > 1 Then Me.Text4.Visible = True End If End Sub
The problem: Any row containing at least one label with 4 print lines pushes the next entire row of three labels down one line, throwing off the spacing of the labels. If I set line 3's can shrink to yes, then the label following the four-line label never has its text4.visible turned to yes, and the other problem (pushing next row down one line) persists. These labels were originally set up via the labels wizard (Access 2010).
How can I keep it from overflowing from one label to push down the next line? This just shouldn't be this hard!
So I have the following query which works perfectly (and will be use to create a report) but when the query runs to create the report I also want it to mail merge some of the items into word to be used on address labels.
Code: SELECT Dunmow.Date, Dunmow.Undeliverable, Dunmow.Duplicate, Dunmow.[OTM with XXXXXX], Dunmow.[Landlords Salutation], Dunmow.[LL Address 1], Dunmow.[LL Address 2], Dunmow.[LL Address 3], Dunmow.[LL Address 4], Dunmow.[LL Address 5], Dunmow.[LL Postcode], Dunmow.[Letter 1], Dunmow.[Letter 2], Dunmow.[Letter 3], Dunmow.[Letter 4], Dunmow.[Letter 5], Dunmow.[Letter 6], Dunmow.[Letter 7], Dunmow.[Letter 8] FROM Dunmow WHERE (((Dunmow.Date)=DateAdd("d",-7,Date())) AND ((Dunmow.Undeliverable)=False) AND ((Dunmow.Duplicate)=False) AND ((Dunmow.[OTM with XXXXXX])=False) AND ((Dunmow.[Letter 1])=False));
So thats my query, What I want it to do is to take the following
I have to print address labels. 10 x 3.8 cm labels 2 per row 14 per page on an A4 paper. Pretty straightforward one should say. But no. I play around with the margins, column heights and widths, row and column spacing page size wasting time. Is there a hidden and not documented relationship between those elements? For example how is the column height and row spacing linked together? How is this normally done in a professional way?
I would like to be able to pull a list of e-mail addresses from a query and put them in one field separated by a semicolon and space so that it could be sent to word and easily cut and pasted into whatever e-mail program someone has. I know very little VBA--can someone help me?
I have an access table which lists a customers address, however, if the customer has a temporary address it will also list that on a separate line -
CustID Address Type 1234 5 The Street P 1234 12 The Street T 2345 13 The Road P 3456 12 The Avenue P
Where P = Permanent and T = Temporary
I want to set up a query to show the permanent address where there is only one address, but where the customer has a temporary address also, I want to list the temporary one instead.
Ok, I am working on an exsisting database at work that has alot of code/forms/reports and so on. Whoever worked on this before me apparently didn't believe indenting code or anything like that. Now I don't want to to go all this code to clean up the structure of the code. I remember back a few yeras ago I found an app for VB that did this for you. No clue where I came across it at or even what it was called but I was wondering if there was anything available like that for Access?
Hi I have a table that contains telephone numbers. The data is in a variety of formats. Here are some examples: 087 123456 087-123456 (W)087-123456 (H)123456
I would like to strip out hyphens, brackets, spaces, etc.
I hope to do this in a straighforward make table query.
Is there a function available that will strip out non-numerics characters?
In a table, there's a column, "Siebel or Sales Ref". Some of these will have a long sales reference number or an ID from a Siebel sales system (pants), in the format 1-XXXXXXX. The problem is that some have two IDs, separated by a forward slash (/), so like 1-ABC1234/1-DEF5678. He wants to go through the data, and where there's a double ID, split it out, and create a new row with the second ID.
I'm not sure this can be done in Access, I would have done a loop in Excel, but it's his database.
Code: INSERT INTO ActualBaseData SELECT * FROM (SELECT 'ITQ ID', Deadline, Lot, 'Bid Progress', 'Framework Type', 'Tender Type', 'Siebel or Sales Ref' FROM TempBaseData WHERE InStr('Siebel or Sales Ref', '/') = 0
I want to release an Access file which has eveything locked down and cleaned up so that my Form is showing full screen (and nothing else) for an optimal user experience.
I need to create a program that will regularly import a text file of over one million records into an Access table. I've been give a list of about fifty different updates to perform on the data to clean it.
I can't imagine performing all these updates in one query. However, creating fifty individual queries seems horribly inefficient from a processing perspective.
I'm accustomed to stepping sequentially through a table in FoxPro, which seems ideal to me for this type of situation. What is the best way to handle this in Access?
Is there anyway to strip all characters except numeric out of a value? For example: 999/777-5555. I want to strip the / and the hyphen. The characters can vary so is there anyway to recognize just numeric?
I have a table that stores text in a number of different languages. Each entry has a unique ID number. Each language version of the same text shares the ID number. I have a column of tick boxes that show where this text is used. ie 1.0 English_text_record_1 tickcol1=y tickcol2=n tickcol3=y 1.0 French_text_record_1 tickcol1=y tickcol2=n tickcol3=y 1.0 Spanish_text_record_1 tickcol1=y tickcol2=n tickcol3=y 2.0 English_text_record_1 tickcol1=y tickcol2=y tickcol3=y 2.0 French_text_record_1 tickcol1=y tickcol2=y tickcol3=y 2.0 Spanish_text_record_1 tickcol1=y tickcol2=y tickcol3=y
I am trying to set the tick boxes so that, if I tick one in an ID range, all the rest tick on as well. ie if I tick record 1.0 English tick_col_2, then the French and spanish will be ticked as well. Any suggestions will be greatly appreciated.
I have a form bound to a table which stores contact info for a person. I have two controls that are email_address and website_address. I set them both as hyperlinks. So now when the user clicks on the email_address Outlook opens a new message with an email_address in the To field. (I had to use a function found on this forum to replace the "htttp" with "mailto" on AfterUpdate event in order for this to work properly.) However, now trying to add some other features to my DB I ran into problems listed below. I wonder if I should have rather left that control as Text instead of Hyperlink, and use the DoCmd.SendObject on doubleclick event instead of using the above solution. How people usually store email addresses and enable emailing on click?
The problems I ran into: 1) My main form is in popup mode and therefore right click on the hyperlink does not give an option to edit the hyperlink 2) I copied a function from this forum which gathers a list of emails and sends one message to the emaillist. However, my email list instead of generating as: email1@a.com; email2@b.com it generates as: email1@a.com#mailto:email1@a.com#; email2@b.com#email2@b.com#
I guess I could extract the email using vba (not sure how), but I still don't know how to solve issue number 1. So maybe it is better to siwtch the field to plain text and forget the hyperlinks?
Thanks, Mariusz
PS. What procedure on double click would open a default browser, since I also have to deal with web addresses and thought of turning them into text controls?
I currently have a table with contact data set out like so (first few columns shown only, but about 10 in total);
ID Establishment Surname FirstName etc1 etc2 etc3 1 ABC Frost Jim data. data. data. 2 ABC Jennings Paul data.. data.. data.. 3 XYZ Smith Dave data.. data.. data.. etc...
You will see that the first two contacts are from the same establishment, but the third is not.
I would like the table to be formated so that I have a single row for an establishment, but multiple contacts in the same row, i.e. as below.
ID Establishment Surname FirstName etc1 etc2 etc3 Surname_02 FristName_02 etc1_02 etc.. 1 ABC Frost Jim data. data. data. Jennings Paul data.. data.
There are 16,000 records which I need to manipulate (roughly 3 contacts per establishment), hence I wondered if there was a was to do this, or maybe export to excel first, then manipulate and then re-import into an Access table.
I am very new to access and any help would be greatly appreciated.
...which I have to crosstabulate, reorder, recode and place in a new table.
Currently I use two queries in Access to do this, but it takes a long time as there are invariably a million or so records.
I've tried to use VBA to speed things up, but with limited success.
Firstly I tried to do a cross-tab and append for each unique identifier...
Code:Sub PopCrossReorderRecode1()Dim db As DatabaseDim rsAbres As RecordsetDim rsCrosstab As RecordsetSet db = CurrentDb()Set rsAbres = db.OpenRecordset("SELECT [OPIE id] FROM Abres GROUP BY [OPIE id]")For Each Value In rsAbresSet rsCrosstab = db.OpenRecordset("TRANSFORM Max(Abres.[Susceptibility result description]) AS [MaxOfSusceptibility result description] " & _ "SELECT Abres.[OPIE id], Max(Abres.[Susceptibility result description]) AS " & _ "[Total Of Susceptibility result description] " & _ "FROM Abres " & _ "PIVOT Abres.[Antibiotic name];") DoCmd.RunSQL "INSERT INTO CrossReorderRecode ( [OPIE id], Tested, Ak, pA, Ctx, Ctr, Cfx, Cx, Cfd, C, Cp, Co, Fu, G, A, S, T, K, CpL, Nx, Ne, Sp, pS, Su, gT, Tm )" & _ "SELECT rsCrossTab.[OPIE id], rsCrossTab.[Total Of Susceptibility result description], " & _ "rsCrossTab.AMIKACIN, rsCrossTab.AMPICILLIN, rsCrossTab.CEFOTAXIME, rsCrossTab.CEFTRIAXONE, " & _ "rsCrossTab.CEFUROXIME, rsCrossTab.CEPHALEXIN, rsCrossTab.CEPHRADINE, rsCrossTab.CHLORAMPHENICOL," & _ "rsCrossTab.CIPROFLOXACIN, rsCrossTab.COLISTIN, rsCrossTab.FURAZOLIDONE, rsCrossTab.GENTAMICIN, " & _ "rsCrossTab.[HIGH LEVEL AMPICILLIN], rsCrossTab.[HIGH LEVEL STREPTOMYCIN], " & _ "rsCrossTab.[HIGH LEVEL TETRACYCLINE], rsCrossTab.KANAMYCIN, rsCrossTab.[LOW LEVEL CIPROFLOXACIN], " & _ "rsCrossTab.[NALIDIXIC ACID], rsCrossTab.NEOMYCIN, rsCrossTab.SPECTINOMYCIN, rsCrossTab.STREPTOMYCIN, " & _ "rsCrossTab.SULPHONAMIDE, rsCrossTab.TETRACYCLINE, rsCrossTab.TRIMETHOPRIM " & _ "FROM rsCrossTab;"NextEnd Sub
...but just got a run-time error 3251 or an error with the transform statement.
Next I tried to address each antibiotic at time...
Code:Sub PopCrossReorderRecode2()DoCmd.SetWarnings off' Delete the current contents of CrossReorderRecodeDoCmd.RunSQL "DELETE CrossReorderRecode.* FROM CrossReorderRecode;"'Populate OPIE id in CrossReorderRecode from AbresDoCmd.RunSQL "INSERT INTO CrossReorderRecode ( [OPIE id] ) SELECT Abres.[OPIE id] FROM Abres GROUP BY Abres.[OPIE id]", dbOpenTable' Run an update query for each antibiotic, putting the relevant code into the relevant field'AmpicillinDoCmd.RunSQL "UPDATE CrossReorderRecode INNER JOIN Abres ON CrossReorderRecode.[OPIE id] = Abres.[OPIE id] " & _ "SET CrossReorderRecode.pA = IIf([Abres].[Susceptibility result description]=""RESISTANT"",""A"", " & _ "IIf([Abres].[Susceptibility result description]=""INTERMEDIATE"",""pA"", " & _ "(IIf([Abres].[Susceptibility result description]=""SUSCEPTIBLE"","""",""."")))) " & _ "WHERE (((Abres.[Antibiotic name])=""AMPICILLIN"")); "'High level Ampicillin'etc etc
...and whilst this worked it took twice as long as the original query, which I imagine is do to its repetitive nature.
If anyone is able to point me in the right direction I'd be grateful. I'm relatively new to VBA and finding it useful and frustrating in equal measures!
I have two tables: products and orders.I would like to query the products only with orders using the product code and/or model number and join up all product and order information for further manipulation.