How to change the sorting order of a report. Q: Steve Rudder writes: “I have an Access report that the user would like to be able to change the sorting order. For example, some of the columns they’d like to sort on in the report are Start Date, End Date
Q: Martin Wichmand writes: I’m trying to make some conditional formatting with VBA, using the following line of code to pop up a message box if the date in the control is today’s date or later: If IsDate(Me![cboOpfoelgning].Value) >= Dato() Then But I just get an error. A: The
Access Archon #144 The following sample VBS program will show how to extract these values: Option Explicit Function FindZip (Str) Dim re, Matches Dim Zips(2) Set re = New RegExp re.Pattern = “((d{5})(-(d{4}))?)” Set Matches = re.Execute (Str) If Matches.Count =
How to back up database objects before making changes to them. Q: Jean H. writes: “I am constantly making changes to forms, reports and other database objects, and sometimes I find that a change doesn’t work, and I need to return to an earlier version. I do back up my
Access Archon #144 Introduction I was recently given an assignment. The client needed to extract information from several log files and put them into a format that would expedite ad-hoc analysis. These log files were in several different folders, and were generated at several times during the day. They wanted
Looks at a method that’ll often fix a problem that’s resistant to all other repair methods. Q: Harold Lewton writes: ” I have 1 database file that works perfectly in Access 2000 and partially in Access 2003. In Access 2003 it works fine while in the Access program itself until
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
The correct code to run when you want to add a record to a table with a value from a form. Q: Sarah Balthazor writes that she is getting an error message when trying to run the following code, intended to add a record to a table with a value
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
How to recover from an AutoNumber disaster. Access Archon #165 Introduction A standard AutoNumber type field (with its NewValue property set to Increment) should start at 1 (though it is possible to start with a higher number), and each new record should be assigned the next higher number, so
The phantom breakpoint: what causes it and how to solve the problem. Q: Don R. writes: When I click a command button to print a Word letter (this button runs VBA code to create the letter from a Word template), the code stops on a certain line, which is highlighted
Access Archon #164 The final (and most complex) procedure is the cmdWordLetters Click event procedure, which creates a new Word document from a hard-coded template for each selected contact; when it is done, the documents are open for inspection. Private Sub cmdWordLetters_Click() On Error GoTo ErrorHandler Dim
How to delete duplicates from another table. Q: John Haynes writes: I am trying to set up a standard query to delete duplicates. The tables consist of customers from 2 different companies company I and company N. I need to delete the customers in company I when there is a
How to sort Reports by date in Access. Access Archon #163 You might need to group records by date using another interval that is not available in the Group On list. If the interval is regular, you can create a query field, such as the Decade field in qryMembers: Decade:
How to show multiple lines of information about listbox items. Access Archon #164 Introduction Listboxes are a convenient way to select one or more items for action, such as creating Word letters or printing a filtered Access report. But sometimes you need to see more than a single line
How to sort reports by date in Access. Access Archon #163 Introduction If you have a report with a date field, you may need to sort the report by date, and Access has a built-in feature that lets you do date sorting very easily, without preparing a query in advance,
How to generate random numbers. Q: Bob wants to know if there is some way to generate random numbers within a certain range (he needs to fill a field in a table with sample values). A: Yes, there is an Rnd function you can use for this purpose. It returns
How to make one cell in a datasheet subform a different color than the others using conditional formatting. Q: Rosie W. writes to ask if there is any way to make one cell in a datasheet subform a different color than the others. This control is locked, and she wants
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 update Word fields in documents created from Access Mergee. Q: Richard Lally writes: I am very interested in trying the Mail Merge method you suggest but I am anxious about one thing. The templates include a number of fields that need updating when a document is created from
How to ensure Access Watch isn’t mistakenly marked as spam in Outlook. Sometimes Office Watch newsletters end up in the Junk Email folder, it might seem like a conspiracy by Microsoft but it’s probably not. You can tweak the Outlook spam filter to ensure Access Watch isn’t mistakenly marked as
How to get Access add-ins to work in Access 2007 running on Windows Vista. Access Archon # 162 Introduction I have add-ins that were originally developed in Access 97 that have worked in all versions up through 2003, and others that had features new to Access 2000 or Access
How to filter a form from a listbox selection. Q: Martin later wrote with a related question: he is getting Runtime error ’13’: Type mismatch when running the following code (which is intended to filter the current form by the double-clicked item in the listbox): Private Sub lstSamtaler_DblClick(Cancel As Integer)
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