How to use Rich Text in memo fields in Access 2007. Access Archon #157 Introduction Over the years, the functionality of Access forms and reports has been significantly upgraded. In Access 2007, one long-requested feature has finally arrived in a workable form (I recall an early and unreliable implementation that
How to ensure leading zeroes are not discarded. Q: Albin Moroz wrote me to say that he is having problems keeping the leading zeroes in U.S. Social Security Numbers (SSNs). He was using the expression Format(Nz(patientRS(patient.SSN), “”), “###-##-####”) which discards leading zeroes. A: Instead of the # placeholder, which discards
How to export data that’s to be archived to an Excel worksheet, and then remove the exported records from the Access tables. Access Archon #143 The procedure that exports the data to be archived to an Excel worksheet, and then removes the exported records from the Access tables, is listed
A reader offers another method for automatically backing up databases, one which runs every time the database starts. In response to my update on backing up Access databases in AW 7.14, Wim Vermeer contributed his routine, which runs every time the database starts. It will make a backup folder (if
A reader’s method for calculating total hours. Bill Patterson responded to my request in Access Archon #129for alternate (possibly simpler) methods of calculating total hours. I used a series of calculated fields in a query to do the calculations; by contrast, the Nearest_Time function listed below does all the calculations
Here’s a way around an error that can sometimes occur with the IsLoaded property. Q: Chuck Tucker writes that he is getting an error on the IsLoaded function reference in a code sample from my book, Access 2002 Inside-Out. A: IsLoaded is a function that comes with the Access sample
Using the Office Envelope Object in Office 2000 – what works and what doesn’t. Some readers have asked whether the Office Envelope Object (featured in Access Archon #122) can be used in Office 2000. Well, sort of. While writing the article, I tested the two procedures in Office 2000 and
This is the book you need for all those situations when Access security is required. access archon #120 Introduction I have always hated Access security. It is a pain to secure a database, and a nightmare to modify a secured database – and, after all that trouble, the database
A reader suggests using Acrobat Distiller to print forms as they look on screen, and more. Gabriel Linart wrote to suggest using the Acrobat Distiller to print forms as they look on screen, for checking form design or keeping a printed record of data entry.This is an excellent solution if
How to calculate and track elapsed time. We receive lots of questions about tracking elapsed time, which confuses everyone at first. If you’re working within the same 24 hour time period, you can use the simple expression [endtime] – [starttime] to return the number of hours and minutes spent on
Access Archon #162 Getting around Vista Security We have all seen those ads where the Mac guy acts superior to the PC guy who is constantly bothered by the Vista Secret Service agent asking if he wants to cancel or continue every time he tries to do something on his
How to make the current row a different color for continuous forms or datasheet type subform (and for continuous forms-type main forms). Access Archon #173 Introduction When you have a form with a continuous forms or datasheet type subform, sometimes it is a good idea to highlight the current row
Suiting Access 2007’s Navigation Pane display to your liking. Q: Gordon Rennie writes: “Now with Access 2007 the database window has disappeared and been replaced with a stupid list at the side that only shows one item per line. Is there any way to get the database window back? or
How to open a form when making a selection from a listbox on another form. Q: Martin wrote to ask if I could help with code to open a form to a specific record when the user makes a selection from a listbox on another form. A: This is easy
How to save a report to PDF file in Access 2007. Access Archon #156 Introduction For many years now, Access users have wanted the option of saving a report to the PDF format. An Access report saved as a PDF file can be opened by anyone (unlike Access snapshots,
A reader offers some further information about the CreateMonthList function. José Dumoulin wrote in response to the CreateMonthList function in the sample database for Access Archon #146: For us, Europeans, it is necessary, in this line, to replace commas by semicolons: strMonthList = strMonthMinus3 & ” ” & strYearMinus3
How to display an ID field in a closed combo box but hide it in a drop-down list. Q: Rich writes that he needs to display an ID field in a closed combo box, but doesn’t want it to appear in the drop-down list. The field is the first one
A reader suggests another way to convert a numeric option value to text. In AW 7.12 I suggested using the Switch function to convert numeric values from a choice in an option group to text values. Tom Bushaw writes to suggest another possibility, the Choose function. The Choose function yields a
The Office Update page of the Microsoft Web site offers a useful toolkit for analyzing databases prior to converting them to Access 2003 format. It is called the Access 2003 Conversion Toolkit, listed under the Add-ins link under the Access 2003 heading on the page. Donn Edwards offers a utility
How to create a file name that includes the date. Q: Steve Mason is trying to export data from a query to an Excel worksheet, and save each exported file with today’s date in the file name, for example qrytest.01/01/2004.xls. How can this be done? A: Slashes in the date
How to successfully merge data from Access to Word. Q: Bruce Michael writes: “I have been attempting to do a merge in Word 2003 using an Access 2003 query. When I look in the query, the data is correct, but when I actually merge using the data, for one column
How to get group headings to repeat for groups that extend over several pages. Q: Saul writes that he is having problems with getting headers to repeat on each page in a report. A: To get group headings to repeat for groups that extend over several pages, set the Repeat
How to print larger Memo fields without truncation. Q: Tara Santmire wants to know how to print Memo fields with more than 255 characters on an Access report, without truncation. A: All you need to do is set the CanGrow property of the text box bound to the Memo field
