Using Custom Functions in Calculated Controls

Custom functions work the same way as MS Access built-in functions such as DateAdd, DatePart and DSum, but are instead created ourselves as database developers.  We do this by creating a public function with the VBA programming language and save it inside a global module within the database. Today I am going to explain how custom functions can be…

Access NZ() function to SQL Server equivalent

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.

Access 2003

Access 2003 Technical Articles

In this section of the MSDN Library, you’ll find technical articles that demonstrate how to build and deploy complex Access 2003 runtime-based solutions, automate Access from another application; use the new XML features in Access 2003, and even how you can animate Access 2003. You can use the TOC to your left to navigate through…

Enable and Disable a Form Control using VBA

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…

Hardware Recommendations For Running Access

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…

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])

IS NULL: Understanding and Using the Null Value in Microsoft Access

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…

Use TryGet pattern for transient values

A number of objects may have extra properties or something where it might not exist for all instances. A good example might be finding Controlsource property on an Access.Control variable:

Not all controls have a ControlSource property and thus can fail to run. A common approach is to use OERN:

This will work…

Access InStr() function to SQL Server equivalent

What is the SQL Server equivalent to Access InStr() function? TargetField: String expression being searched SearchValue: String expression being sought StartPosition: Starting position for each search (optional) Access SQL InStr(TargetField, SearchValue) InStr(StartPosition, TargetField, SearchValue) SQL Server CHARINDEX(SearchValue, TargetField) CHARINDEX(SearchValue, TargetField, StartPosition)