Office Settings & Configuration

How to Use DMax with Multiple Criteria in Access VBA

Kushani Nimanthika Oct 23, 2026 869 views

Question details

The user needs to calculate a sequence number using the DMax function in Access VBA based on both a numeric year and a text platform.

How to Use DMax with Multiple Criteria in Access VBA

Product: Microsoft Access
Device & OS: not provided
Scenario: Generating a new sequential ID before inserting a record into a form by combining numeric and text conditions.
Observed behavior: The user encounters an 'Argument not optional' compile error when failing to pass all required parameters to the sequence generation function.

Before you start

Before modifying your VBA code, review your table field data types to confirm which criteria require text quotation marks and which are purely numeric.

Solution 1Recommended

Combine Numeric and Text Criteria for DMax

Properly format your criteria string by concatenating the numeric variable and quoting the text variable before passing it to the DMax function.

When using domain aggregate functions like DMax in VBA, you must construct a string that perfectly mimics a SQL WHERE clause. Text variables must be wrapped in quotes, while numeric variables do not.

Construct the criteria string

Define your criteria by combining the year (numeric) and platform (text). Use double or single quotes around the text variable: strCriteria = "Year = " & intYear & " AND Platform = '" & strPlatform & "'"

Calculate the sequence with Nz

Wrap your DMax function in the Nz function to handle scenarios where no previous records exist. E.g., Return Nz(DMax("SeqNumber", "YourTable", strCriteria), 0) + 1.

Pass all arguments in the form event

To prevent 'Argument not optional' errors, ensure your BeforeInsert event calls the function with both parameters. E.g., Me.SeqNumber = GetSeqNumber(Me.Year, Me.txtPlatform).

Combine Numeric and Text Criteria for DMax

Syntax Tip: Using single quotes ( ' ) inside your criteria string for text variables makes the VBA code much easier to read than escaping multiple double quotes.

Free Microsoft Office alternative

Looking for a Free Alternative to Microsoft Office?

While Microsoft Access handles complex databases, WPS Office provides a lightweight, highly compatible, and free suite for your daily document, spreadsheet, and presentation tasks. Enjoy a familiar interface without the heavy subscription fees.

1. Download WPS Office: Visit the official WPS website and download the free installation package for your operating system.
2. Install the application: Run the installer and follow the on-screen prompts to complete the setup in minutes.
3. Open your files: Launch WPS Office and instantly open, edit, and save your existing Microsoft Word, Excel, and PowerPoint documents.

Highly compatible with Microsoft Excel (.xlsx), Word (.docx), and PowerPoint (.pptx) file formats.Free and lightweight suite requiring minimal system resources to run smoothly.Familiar tabbed user interface makes migration seamless and easy.Built-in PDF editing tools integrated directly alongside spreadsheet and document management.

microsoft office alternative - wps office

Frequently Asked Questions

Why do I get an 'Argument not optional' error in Access VBA?

This error occurs when you call a function or subroutine without providing all the parameters it requires. For instance, if your sequence function is built to expect both a year and a platform, calling it with just the year will trigger this compile error.

How do I handle null values returned by the DMax function?

You can use the Nz() function to convert null results into a default number. Wrapping your DMax call like Nz(DMax(...), 0) ensures that if the table is empty, the function evaluates to 0, allowing you to safely add 1 to generate the first sequence number.

What is the difference between passing text and numeric criteria in VBA DMax?

Numeric criteria can be directly appended to the criteria string (e.g., "Year = " & intYear). Text criteria, however, must be enclosed in single or double quotes within the string (e.g., "Platform = '" & strPlatform & "'") so the database engine recognizes the value as text rather than a missing parameter.

Office App Problems