These notes are some general tips and tricks learned while working with Access. Some of these are tricks I worked out and others are good tips from folks on the Access Forum on Compuserve. I hope they are helpful to new users. Rick Curtis – Princeton University Outdoor Action Program.
Access supports long file names for internal database objects like tables, queries, reports, etc. These are sorted alphabetically by the database so the naming process can become extremely important. Currently, Access does not update object names. So for example, if you have a table called Departments and you develop queries and reports based on that name, and then change the table name to Department tbl, none of the queries or reports will work until you go in and reestablish a link to the correct name of the object. This can be devastating if you have created complex queries because the old query fields must be completely redone. So pick good, descriptive names the first time around to avoid having to do major cleanup.
Based on articles about Access and my own experience, I suggest using a uniform naming scheme for all Access objects. Here is the naming scheme I use.
Object |
Name | Example |
Tables: |
* tbl | Leaders tbl |
Forms: |
* frm | Trips frm |
Subforms: |
* sbfrm | Leaders on Trips sbfrm |
Queries: |
* qry | Senior Leaders qry |
Report: |
* rpt | Senior Leaders rpt |
Subreport: |
* sbrpt | Leaders on Trips sbrpt |
The reason for providing the abbreviation at the end of the object name is that it identifies the type of object. When you are using the Properties Dialog box in Reports and Forms and you want to change to underlying query or table that the Report of Form is based on, the list will show both queries and tables and if the table is called Leaders and the query is called Leaders you can’t tell them apart (in the dialog box). Using the abbreviation at the end is a big help.
I tend to create a query with a descriptive name like Senior Leaders qry and then create a report with the same name Senior Leaders rpt. this makes it easy for me to identify which query goes with which report.
There are a number of methods for moving data from Q&A to Access.
1. Export the Q&A data to an ASCII file (fixed or delimited). The using the import command in Access to bring the ASCII data across. Access will allow you to customize the ASCII import specifications and save the then for future use. The one draw back with this method is that you will need to have created a table to hold the data, with all the correct field orders and types before you import.
2. Export the Q&A data to an intermediate database type (I would suggest Dbase IV). Then the Dbase IV table can either be attached or converted to an Access table. You do not have to have a table already set up. If attached, the data can still be accessed by Q&A. There are a few important things to remember when using this technique. Q&A can support fields that use the same name (Address, Address) and so can Dbase. However, Access cannot. So if you export the data to Dbase and then import to Access you will get an import error since Access does not know how to place the data with the same field name. This can also happen inadvertently when you export from Q&A to Dbase. Q&A supports field names of any length. When the database is exported to Dbase the field names are truncated to the standard DOS 8 + 3. So if you have fields in Q&A CONTRACT SIGNATURE and CONTRACT SIGNED these will be truncated to CONTRACT.SIG and CONTRACT.SIG and again Access will give you an import error. The way to deal with this is to go into Q&A and change just the field name so that each one is unique within a DOS 8 + 3 structure. So you could change CONTRACT SIGNATURE to CONTRACT 1 and CONTRACT SIGNED to CONTRACT 2. Once you import the data into Access (which supports long field names) you can change the field name back.
People working with Access databases typically keep address data in separate fields such as greeting, firstname, lastname, address, city, state, zip, and country. However, when you dump ASCII data to Printing Services at Princeton University you need to give them a delimited ASCII file where each individual mailing line is concatenated. Here’s a sample table:
| Greeting | Firstname | Lastname | Address | City | State | Zip | Country |
| Mr. | Fred | Flintstone | 1 Bedrock Way | Princeton | NJ | 08540 | |
| Mr. | Barney | Rubble | 2 Bedrock Way | Princeton | NJ | 08540 | |
| Hon. | Dino | Dinosaur | 7 Fleet Street | Hempshire | Essex | LBJ 690 | England |
What Mailing Services needs is data they can merge onto your envelope in this format:
Mr. Fred Flintstone
1 Bedrock Way
Princeton, NJ 08540
There are two options for how to do this depending on whether your mailing is going out first class (must be sorted by country and zip code) or third class (no sorting except country). Let’s deal with third class. Create a query that sets up calculated fields that concatenates the data into individual lines. (This concatenation is the same thing that happens when you create a Label Wizard Report.) Create a query with the following new expressions
Name: [Greeting] & " "& [Firstname] & " "& [Lastname]
Location: [City] & " "&[State] & " "& [zipcode] & " "& [country]
Your query grid will look something like this:
| Name: [Greeting] & " "& [Firstname] & " "& [Lastname] | Address | Location: [City] & " "&[State] & " "& [zipcode] & " "& [country] |
| Mr. Fred Flintstone | 1 Bedrock Way | Princeton, NJ 08540 |
Now all you need to do is
Now what about first class sorting? Well, you need to run your query sorting on both country and zip codes. To do this you also need to have these fields in the query grid. Now when you output the query, you’ll get the extra fields. For some reason in Access 97 even if you have the check boxes turned off for both these fields so you can just use them to sort, when it comes time to export to text, they are exported as well. Having these extra fields can screw up Mailing services. So here’s what I do. I create the query with both zip code and country included as separate fields. I run the query as a Select Query to make sure it’s what I want (always do this). Then I change the query from a Select Query to a Make Table Query and create a new temporary table. All of the data has been moved over and you now have the fields Name, Address, Location, Zip Code and Country where Name and Location are your concatenated fields. I then go into table design and delete the Zip Code and Country field. Everything is still properly sorted from your first query. Save the table and now open it. Now export it to text as described above. When you are finished (and you are sure Mailing Services is happy) delete your temporary table.
It is often helpful to pull data from one table into another to avoid rekeying data. There are lots of ways to do this using the AutoLookup feature, but this only shows you the data from the other table, it doesn't bring it from Table A into Table B. Here's an example, I get an address dump from the Admissions Office of the entire freshmen class. We have over half of the class go on the Outdoor Action Frosh Trip. To avoid having to rekey name, address, phone, UAID, etc. I use macros with Dlookup to pull the data across.
Table A:
Fields: First Name, Last Name, SS#, UAID, Address1, Address2, City, State, Zip, Foreign
Country, Phone
Table B:
Fields: First Name, Last Name, SS#, UAID, Address1, Address2, City, State, Zip, Foreign
Country, Phone
Create a macro called Lookup Form mcr. Each line in the macro serves to update the record in the corresponding control on the form. For example, the following DlookUp expression sets the value of the First Name control on the form to the value of the First Name field from Table A when the Social Security Number entered on the form = the Social Security Number in Table A.
| Macro Name | Action | Item | Expression |
| UpdateFirst | SetValue | [First Name] | DLookUp("[First Name]","[Table A]","[SS#]=Forms![Frosh Application frm]![SS#]") |
| UpdateLast | SetValue | [First Name] | DLookUp("[Last Name]","[Table A]","[SS#]=Forms![Frosh Application frm]![SS#]") |
Create Frosh Application frm with all of the fields in Table B so that the first field is Social Security Number. Now add the rest of the controls. Select the First Name control and select Properties. Set the On Enter Property to the UpdateFirst macro. Now when you enter a new form and type in the social security number and then tab into the First Name control, the value from Table A will be inserted into the First Name field in Table B.
a Microsoft Access 2.0 database for that demonstrates how to generate HTML documents from Access Reports. This new version shows how to format HTML 3.0 tables from Access.
The NOT function in a query does not show null records. In order to show all records which do not match a particular criteria or are Null you need to include the OR statement in the query. For example: to locate the records that are not WL where fields are either WL or Null use
Not "wl" Or Is Null
This is a tip from Inside Microsoft Access by the Cobb Group. You can calculate a person’s age from their birthday by using the following expression.
Age: Year(Now())-Year([Birth date])+(DateSerial(Year()),Month([Birth Date]),Day([Birth Date]))>Now())
You can use the DLOOKUP function to bring up data from another table to a form or report. The following sample statement when used in the [Address1] field on a form will look up the value from a secondary table when the SS# entered on the current form equals the SS# in the remote table. Note: this only looks up the data and shows it on the form. It does not update the field in the primary table.
=DLookUp("[Home Address line 1]","[Test tbl]","[SS#]=Forms![Frosh Applications frm].[SS#]")
You often need to decide when to use expressions in a report or when to use them in a query. I have a database used for calculating van rental. I have a Rental table with COUNTER, GROUP, TYPE OF GROUP, USE DATE, STARTING MILEAGE, ENDING MILEAGE. There is a User Type table with TYPE OF GROUP and RATE (either $0.60 or $0.45) since different groups pay different rental rates. In addition, the rental fee is also based on a calculated field, TOTAL MILEAGE.
The rate structure is as follows
Departments - $0.60 per mile if mileage > 30 else $10
Student Organizations - $0.45 per mile if mileage > 30 else $10
RA's - 50 miles free then $0.45 per mile
I am not sure of the best way to handle this. I am trying to create a report for billing purposes that selects the correct rental calculation based on both the TYPE OF GROUP and the TOTAL MILEAGE. Can an IIF statement in a query handle this, or do I need to create additional fields in the User Type table? Any suggestions are appreciated. Thanks in advance. - Rick
Rental Charge: IIf([Total mileage]>[Miles],[Total Mileage]*[Fee],[Flat Fee])
This expression work well in a query that combined Van Usage and Rental Types. Then when the report was based on the query, group footer and report footer totals were simply =SUM([Rental Charge]). When trying to use the expression in the report IIf([Total mileage]>[Miles],[Total Mileage]*[Fee],[Flat Fee]) worked fine in the detail section to generate the specific rental charge for that date, however, in the group footer if generated an incorrect total in some instances. For example, the mileage for one group that used vans on two different times was
| Group | Mileage | Rental Charge |
| Hawaiian Students Association | 21 | $10 |
| 8 | $10 | |
| Total | $10 |
Notice that the total is still $10 because the formal is calculating the sum of the mileages which is still less than 30 making the flat fee $10 rather than adding the two fees together.
<< Departments pay 0.60 per mile, if the mileage is < 30 they pay a flat fee
of $10. Students pay 0.45 per mile, if the mileage is < 30 they pay a flat
fee of $10. RA's pay 0.45 per mile, and are given 50 free miles. >>
TotalMiles < Miles, Flat Fee, Fee * Total Miles
or
Rental Charge: IIf([Total mileage]<=[Miles],[Flat Fee],[Fee]*[Total mileage])
This is based on the assumption that the Table described is combined is combined with a "Persons" table and the calculation is done per record.
| Type | Fee | Miles | Flat Fee |
| Department | $0.60 | 30 | $10.00 |
| OA | $0.00 | 0 | $0.00 |
| PS | $0.45 | 0 | $0.00 |
| RA/MAA | $0.45 | 50 | $0.00 |
| Student Organization | $0.45 | 30 | $10.00 |
Giving RA/MAA free miles: The best way may be just to break apart the pieces, by testing if the Type="RA" then you can subtract the first 50 miles. I set up a query with three expressions (must be laid out in this order).
1. Total Mileage: ([Ending mileage]-[Starting Mileage])
2. Adjusted Mileage: IIf([Van Usage].[Type]="RA/MAA",[Total Mileage]-50,[Total Mileage])
3. Rental Charge: IIf([Adjusted Mileage]<=[Miles],[Flat Fee],[Adjusted Mileage]*[Fee])
Having two or more columns queried along the same row utilizes an AND with the query parameters. On different rows uses an OR.
To get a range of values you can have the parameter query use the following format BETWEEN X AND Y e.g.
BETWEEN [What start date?] AND [What end date?]. This should be in the Usage Date column.
Senior Parents- Used Stuview to grab parent data into notepad. Add student first and last name and pronouns. Then transfer notepad to Word and set it up as a table. At this point, all were in caps (from Stuview). Use Word Shift-F3 changes all upper case text to first letter upper case. Go in and fix things like Po Box. Need to add an apostrophe in front of zip codes so it transfers into word as text. Then transfer Word Table to Excel. Then import Excel into Access.
With the OA Alumni not in Alumni Records table I had CLASS as a number field but with only two digits. I needed to add the 19 to the class year in order to be able to update the ADVANCE ID from ALUMREC.DBF. So I change the CLASS field to text. Then ran a select query. Changed the select query to an update query with the formula "19"+[Class] in the Update to field. This added the 19 to the class year (but only where there was a class year, which was actually best). Then I went in to those that had no Class year and added the appropriate number.
In order to get the All Friends of OA Donations report to print properly I added the NulltoZero module from NWIND.MDB. This made any null entries appear as zero. Without this i could not sum each column in the Report footer (i.e. Then in the detail section I set up a field [88] for gifts in that time and Field [881] =NulltoZero([88]) which was made invisible. I used the expression =[881]+[891]+[901]+[911]+[921]+[931] in the right side of the Detail section to get the row total for each member. Then I used the expression =NulltoZero(Sum([88])) to create a Report footer sum down the column. This approach eliminates unwanted junk on the report. There are blank spaces if the particular field is null.
This field is in the OA Alumni table. The useful reason to keep this field (even though it duplicates the Class field in Alumni Records info, is that Class is a text field. ClassNo can be set to a number field allowing seraches such as >1983. THe other option is to change Class. However, there are some instances of parents and having this field be text is probably best.
When using numbers to indicate options such as in the Alumni Program Participants Form (where 1=Going, 2=Interested, 3=Future), you can use the Choose function in a report field i.e. Choose([trip status],"Going","Interested","Future") to display the text rather than 1, 2, or 3 as the field values.
Some tables use basic codes such as T, P, TR (for Trip, Presentation, Training). Yet in some reports etc. you need to "spell out" the codes. This works only if there are 3 options. This can be done from the underlying query for a report by setting up an Expression
Activity Type: IIf([Type]="P","Presentation",IIf([Type]="T","Trip","Training"))
Or by setting up an IIF statement in the report. [See page 525 & 719 of Access Bible].
This can also be done through a Lookup table that has the specific details. A lookup table will work for any number of codes. (See Population Description Table in OAPROG.MDB).
From Access Forum: You can nest IIF() statements more deeply than that, but my brain goes berserk looking at it.
Why not create a small table:
A Alumni FR Frosh G General etc.
Index it on the first column, and then create a query that joins it with the main table on that abbreviation field. Have your query include the SECOND column from this new table. Then, you can use it as if it was stored in the main table when you create reports.
This may not be the way others do this, but I almost always do it this way. That way, also, when you need to add more abbreviations, it's REALLY SIMPLE. You don't have to go around digging through expressions to fix it.
If you have a Yes/No field and you need to get counts on it, you can set up a query with an IIF expression that changes the -1 to a 1 allowing you to do a count. For example, on the Frosh trip database, I need to count the number of backpacks requested:
Backpack#:IIf([Backpack]=-1,1,0)
This changes the value from -1, yes to a 1, then you can do a =Count([Backpack#])
In a report you can set up a sum using an IIF expression. This will only count the calues for the field where the value is Yes (-1). Useful for report summaries.
=Sum(IIf([Canoeing Flatwater]=-1,1,0))
You can base a query on a query. For example, the Frosh Trip database uses a Non-wait list query that selects all records before a certain parameter date. Other queries such as Frosh Allergies, Health, etc., can be based on the Non-waitlist query so as to only see the people who are actually going. This means not having to duplicate the parameter query in each other query (e.g Frosh Allergies query).
For example, have to move the seniors from OAPROG.MDB to OALUMNI.MB. Use the Seniors from OA Mail Query; change it from a slect query to a make table query. Give the new table a name. Then export the table into OALUMNI. MDB from OAPROG.MDB.
Moving Frosh from FT to OAMAIL. You can append data from a query in one MDB to a table in another. Make a query calling up all the info in the Frosh table you want to move to the OA Mail table. Change the select query to an append query for OA Mailing List table (if there are any missing SS# you will get a message about no null values in index, fix these). Then append.
With Yes/No fields you can set up an expression (in a query or report) that will only show yes values.
In this case, you need to enter an unbound field to create the expression. If you simply change the original placed field it still has the control name of the original field and Access thinks you have a circular reference. Change the ControlName to something else, like "CanoeFlat".
ControlName: CanoeFlat
ControlSource:=IIf([Canoeing Flatwater]=-1,"Yes")
Also note that you need not supply a value for the False condition.
Created a two new functions in Module 1. Ons is AcadStart for Academic starting year and the other is AcadEnd for academic ending year. Using both will analyze a given starting date and ending date and give you the Academic year.
Function AcadStart (dt As Variant) As String
'Takes date and evaluates if January to June then assigns current year -1 for that date
'Takes dates and evaluates if July to December then assigns current year for that date
'This gives you first part of academic year 'phrase' (19xx - 19xy)
Select Case DatePart("m", dt)
Case 1 To 6
AcadStart = DatePart("yyyy", dt) - 1
Case 7 To 12
AcadStart = DatePart("yyyy", dt)
End Select
End Function
************************************************************************************
Function AcadEnd (dt As Variant) As String
'Takes date and evaluates if January to June then assigns current year for that date
'Takes dates and evaluates if July to December then assigns current year + 1 for that date
'This gives you second part of academic year 'phrase' (19xx - 19xy)
Select Case DatePart("m", dt)
Case 1 To 6
AcadEnd = DatePart("yyyy", dt)
Case 7 To 12
AcadEnd = DatePart("yyyy", dt) + 1
End Select
End Function
******************************************************************************
Then I created a Query with the expression
Academic Year: AcadStart([Start Date]) & " - " & AcadEnd([End Date])
This gives me an academic year of 1994 - 1995 for all events from 7/1/94 - 6/30/95 and switches over to 1995 - 1996 starting 7/1/95.
Set up the following expressions in a query
1. Ziplength: Len([Hzip])
This calculates the length of the Zipcode string.
2. Zip+4: IIf([Ziplength]=9,Left$([HZip],5) & "-" & Right$([HZip],4),[HZip])
This examines the length of the zip code string. If it is 9 then it takes the first 5 characters, adds a hyphen, and adds the next 4 characters creating the proper Zip +4 code. Important - the Ziplength filed must be checked to show on the query or Zip+4 doesn’t work.
Alumni Records separates phone numbers into 3 fields, area code, exchange, number and a fouth, extension. If the person doesn’t give a phone number the exchange is listed as 000 (text string). To concatenate it into one field use the following expression (query of report). This also does not print anyone who doesn’t have a phone
1stPhone: IIf([1st phone-exchange]="000","",[1st Area-Code] & "-" & [1st phone-exchange] & "-" & [1st phone-number])
When using a multi-table query, you can be limited by what fields can be modified if you are doing calculated fields or have a one:many join. (See Access Advisor June/July 1993). In a form such as FOA Member frm which is based on the FOA Member Update qry, three tables are involved OA Alumni, FOA Dues and Alumni Records all connected via Advance ID. To limit the search you want to use a __________ join between OA Alumni tbl (the dynaset of which is being limited by status=L OR FOA=-1) and FOA Dues tbl such that all records in OA Alumni are included and any records in FOA Dues tbl where Advance ID is the same. Since FOA dues is actually a subset of the OA Alumni table, this gives all of the people that we sent the direct mailing to (Leaders and all previous FOA members). However, this results in fields which cannot be edited on the form. This can be changed by going into the form properties and setting the Default Editing to Allow Edits and the Allow updating to Any Tables. One must be careful because this circumvents referential integrity.
You can use the Like command with a parameter query to look for all occurances of something. For example, in the Leader Special Training Table I might want to look gor all leaders with canoeing training (both flatwater or whitewater). By using the LIKE cammand and a parameter I cane enter CANOE as the parameter and get both groups. The punctuation is critical to get this to work.
LIKE "*" & [What type of training] & "*"
From Inside MS Access March, 1994. Trying to create a No-in query to identify those leaders from OA Progrma who have not signed up for FT. Create a Query in the FROSHTRP.MDB that combines the Leaders tbl and the Leaders From OAPROG tbl. Use SS# to create the join between the two tables. Set the join to be an outer join (all records in Leaders from OAPROG tbl and only those records in Leaders tbl where the joined fields are equal). Then pull down the fields you want. Pull down SS# from Leaders tbl and set creiteria to Is Null. Then pull down class from Leaders from OAPROG tbl and set to greater than year desired (eg. for FT 94 use >94 this will elimiate 93 and 94 but no one else.). This is call Leaders not leading from OAPROG #1 qry.
Then create a query based on this query that combines Mailing tbl from OAPROG so you can get phone numbers of people to call.
Sometimes you may need to pull information up from the Report footer to use in other caculations. For example. The Van Rental Usage Summmary by Type rpt in the Vans database looks at the types of van rentors and culucates total mileges, and total rental fees. But I also wanted to get the percentage of mileage and the fees. In order to this, go into the Report Footer section to the total mileage sum and name the control All miles.
Control Name: All Miles
Control Source:=Sum([Total Mileage])
Then I could use the [All miles] in the following caculation in the Type Footer section above
=(Sum([Total Mileage]))/[All miles]
The same thing works for calculating the percentage of rental income.
In the download from MIS the student phone info [phone full] is 6092583552. This can be reduced to a new field [phone] by using an update query
Right([phone],7)
Then to add a hyphen between the numbers, run another update query
Left([phone],3) & "-" & Right([phone],4)
Make sure the field length is long enough to take the extra character.
With a subform in a mainform you can use tab to enter the subform, however, using tab withing the subform merely cycles you through the subform. To move the focus back to the main form you will need to set the OnExit propoerty of the last field in the subform to a Macro which uses the GoToControl command to move to the next field in the main form. An example of this can be seen in the OALUMNI.MDB with the FOA Dues Entry Form and the FOA Dues subform. The Dues Date Field uses the Leave FOA Dues subfrm mcr which is GoToControl with the Control Name being Acknowledgment Required.
In some cases you may want to move from one form to another and "carry" the key field value with you into a new record. For example, with the FOA Dues Entry frm ther is a macro button that moves you to the Gifts Entry frm if a capital gift has been made. You want to have the switch transfer the Advance ID into the Advance ID field on Gifts Entry frm.
You can do complex calculations in queries. For example, the Friends of OA Report for the Recording Secretary needs to display the total amount of the check written to Princeton but also show how much is tax deductable versus how much is paying for goods or services (manual, T-shirt, Crazy Creek). In the query that underlies the report FOA Dues 95 qry I added several calculated fields.
Manual Cost: IIf([Manual] Between 1/1/95 And 12/31/95,40,0)
T-shirt Cost: IIf([T-shirt] Between 1/1/95 And 12/31/95,40,0)
Crazy Creek Cost: IIf([Crazy Creek] Between 1/1/95 And 12/31/95,40,0)
This is because I wanted to keep the Manual. T-shirt and Crazy Creek date fields so we can keep records of who ordered what when and use the date to send out the item (we only want to send out items with a date >1/1/95 for example, not to people who ordered them last year). I also created a Total Payment expression which adds up everything to give the Recording Secretary’s Office the total amount of the check.
Total Payment: [95]+[Manual Cost]+[T-shirt Cost]+[Crazy Creek Cost]
Then I added a Control to the Report
=[Total Payment]-[95] with a text block deducted for OA items ordered.
So the report shows the total amount of the check and calculates the amount to be deducted for items ordered.
The syntax of nested IIF statements can get complicated. Here is a sample expression that looks for the President, if that field is null, it looks for the Vice President, if that field is null, it looks for the Secretary, and if that field is null it defaults to the Treasurer.
AnyOfficer: IIf([STUDORG tbl]![PRESIDENT] Is Not Null,[President],IIf([Vice President] Is Not Null,[Vice President],IIf([Secretary] Is Not Null,[Secretary],[Treasurer])))
This IIF query looks at the First Value and the Second Value as true, then sets the truepart and falsepart
BPFM: IIf([requested role]="L" And [manual] Is Null,"manual"," ")
This IIF eliminates all Temp Assign that are Z*
Assign: IIf([temp assign] Like "z*"," ",[Temp assign])
While printing telethon labels I wanted to be able to have a count for each label based on time zone. In order to do this I grouped the labels by Time Zone and added a group header. This header prints as one label in the report (which makes it easier to see where the time zone breaks occur). In the Header Section I placed
[Time Zone] "Time Zone Count =" [Count(Time Zone])
In the Detail Section of the label I used two fields
[Time Zone] and an unbound expression called Counter with Control Source: =1
Counter increases by one with each record in the Detail Section. When the Time Zone Changes the counter goes back to zero and starts again.
This formula looks at the date of an activity and calculates the Class Year (Senior, Junior...) by subtracting the Class from the Academic Year End. You need the following two expressions in the query in this order.
AcadEndYear: AcadEnd([Infraction Date])
ClassYear: IIf(([AcadEndYear]-[Class])=1900,"Senior",IIf(([AcadEndYear]-[Class])=1899,"Junior",IIf(([AcadEndYear]-[Class])=1898,"Sophomore",IIf(([AcadEndYear]-[Class])=1897,"Freshman","Class Year Unknown"))))
If you wish, you can include the Academic Year as a check.
Academic Year: AcadStart([Infraction Date]) & "-" & AcadEnd([Infraction Date])
From Senior Parents tbl - I get one name from the Registrar’s dump. This query finds the space and makes the first cut there to create the first name (Pfirst). The next expression takes it from the middle and creates the last name (Plast). If there is a Middle Initial, it is parsed into the last name field (Plast) which is good for mail merging with the first name.
Pfirst: Left([Name],InStr([Name]," "))
Plast: Mid([Name],InStr([Name]," "))
an older version was
Pfirst: Left([Name],InStr([Name]," ")+1)
Plast: Mid([Name],InStr([Name]," ")+1)
I am not sure now why the +1 was added.