VBA is quite very loose in what it deems “truthy” and “falsy”. For example, this procedure can produce surprising result:
|
1 2 3 4 5 |
If Me.MyBitField.Value = True Then 'It is true Else 'It is false End If |
At the first glance, it seems expected that if the bit field is set to 1, it would succeed. But in fact, it wouldn’t because True is equivalent to -1, not 1. Unless we…
Drag and Drop In MS Access Doug was on a roll in January of 2004 when he wrote his article on drag and drop in MS Access. He tested it now using Access 2016 and it still works. This article shows you how to add drag-and-drop to your Access application using combinations of multi-value and…
I was working on a design for an Order Management Database, and one of the tasks I dealt with involved dynamically Enabling or Disabling one of the form’s Command Buttons’ using VBA. This gave me the idea for the present Access tip. The command button was located on a Customer Details form which had an Orders…
When you use code to refer to Excel (and other programs as well) you need to be careful not to use any code that does not refer back to the instantiated application object. If you do so then you will find that Access instantiates another Excel (or other program) object which is not visible to…
Hardware matters. Invest in a faster PC and your Microsoft Access databases will run faster. Be alert for sellers packaging fast processors with minimal memory and a slow hard drive to keep prices low. Look at all aspects of the purchase when database speed is a priority. A faster hard drive is worth the investment…
Once you have created an Access Database Application to be used by other people, it may well be important for you to prevent users from gaining access to any of its design features. By this I mean, you may not want users to modify your tables, forms and queries etc in design view (inadvertently or…
Constraints that are created by SSMA or by user via SSMS tend to have ugly names. Worse, auto-generated names are not stable across backups. Meaning if you restore a new database based on a copy of another database, the constraint names will change. That create huge problems during migrations because scripts that references constraint will…
Symptoms Whenever you run a SQL statement you get a “Too Few Parameters, expected X.” Cause Most common cause is misspelling or missing fields in the SQL statement. Resolution Check the SQL statement. If necessary, copy and paste to an Access query and test in the query to get it to highlight which part of…
What is the SQL Server equivalent to Access NZ() function? Access SQL NZ([Event Timestamp]) SQL Server Equivalent COALESCE([Event Timestamp],0) ISNULL([Event Timestamp],0) * do not confuse this with the Access isnull() function.
Before we create the Make Table Query, you might like to create a new blank database file. This is so we have a separate database into which we can paste the new table from the query. In this exercise I have called it HistoricData.accdb. Here is the procedure to create a Make Table Query: Open…