In Microsoft Access, a null value is a value that is not assigned or unknown. When working with databases, it’s important to understand how to use and identify null values in order to ensure accurate and efficient data management. The IS NULL operator is one of the most useful tools for working with null values…
Minimizing stateful code
First we need to define what a state is. A state means that the object has variable(s) that may cause it to behave differently, depending on the content of the variables. As an example, the Forms collection is stateful: Forms(“SomeForm”).Visible = False This is legal code and will compile but we can’t be assured that…
“Too Few Parameters” error fix
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…
Access DateValue() function to SQL Server equivalent
What is the SQL Server equivalent to Access DateValue() function? Access SQL DateValue([Event Timestamp]) SQL Server Equivalent cast([Event Timestamp] as date) convert(date, [Event Timestamp])
Access CDate() function to SQL Server equivalent
What is the SQL Server equivalent to Access CDate() function? Access SQL CDate([Event Timestamp]) SQL Server Equivalent cast([Event Timestamp] as datetime) convert(datetime, [Event Timestamp])
Module level variables
It is common to create a big wall of declaration in a module such as this: Private strCompanyName As String Private dteOrderDate As Date Private lngOrderID As Long Private objEmail As Object Private objOutlook As Object While the code will compile and work fine, the discoverability and naming of the module level variables does become…
Drag & Drop and Conditional Sorting in Microsoft Access
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…
“Truthy” and “Falsy” Values
VBA is quite very loose in what it deems “truthy” and “falsy”. For example, this procedure can produce surprising result: 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…
Designing a lookup table with logic
It is very common for us to embed some kind of logic based on a lookup table or in some cases, a column of a table. Because we have application logic connected to it, the code are fragile and subject to changes as the requirements develops. We want to avoid this situation where we might…
Creating a Make Table Query
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…