Access 2000 NULL Argument Issue

Posted on 2005-05-06
Last Modified: 2012-05-05
Greetings, experts.

We have a situation with the following statement:

 DoCmd.OpenForm rst![Argument], , , , , , rst![OpenArgs]

If the last argument is NULL, Access 2000 gives an error, because it doesn't like all of the commas with a NULL at the end. However, if we change it to concatenate and empty string as follows:

 DoCmd.OpenForm rst![Argument], , , , , , rst![OpenArgs] & ""

It works fine. I suspect the following would also work, but I haven't tested it:

If IsNull(rst![OpenArgs]) then
     DoCmd.OpenForm rst![Argument]
     DoCmd.OpenForm rst![Argument], , , , , , rst![OpenArgs]
end if

So we can fix this issue on a case by case basis.

However, we are converting this database from Access 97 to 2000, and there are tons of places this kind of code is used throughout the application. Is there a global fix for this issue so we don't have to waste lots of time going through and modifying each one?

Question by:jdrits
    LVL 65

    Accepted Solution

    A simple Find...Replace in code should work.  Type your code block above in Notepad, then copy-paste it into the Replace textbox.

    For a more 'elegant' solution, download Rick Fisher's 'Find & Replace Utility at ($39 buy, free 30-day download), which is an Access add-in that lets you find and replace strings in your entire Access database.

    For a few more bells and whistles, Speed Ferret at also does this, but with a $199 price tag.  However, using this allows you to do the same single find.. replace withing Access, VB, AND SQL Server in one shot.

    Hope this helps.
    LVL 2

    Author Comment


    Thanks, but the problem isn't a simple search and replace. The code I put above is only an example of the issue. There are other instances that use other code or variables, arguments, etc.

    The one thing I could do is do a find for multiple commas in a row. I suppose if there isn't a global way to address this issue, that's what I'll have to do.

    Thanks for your suggestion, though.


    Featured Post

    How to run any project with ease

    Manage projects of all sizes how you want. Great for personal to-do lists, project milestones, team priorities and launch plans.
    - Combine task lists, docs, spreadsheets, and chat in one
    - View and edit from mobile/offline
    - Cut down on emails

    Join & Write a Comment

    This article is a continuation or rather an extension from Cascading Combos ( and builds on examples developed in detail there. It should be understandable alone, but I recommend reading the previous artic…
    Introduction The Visual Basic for Applications (VBA) language is at the heart of every application that you write. It is your key to taking Access beyond the world of wizards into a world where anything is possible. This article introduces you to…
    Show developers how to use a criteria form to limit the data that appears on an Access report. It is a common requirement that users can specify the criteria for a report at runtime. The easiest way to accomplish this is using a criteria form that a…
    Basics of query design. Shows you how to construct a simple query by adding tables, perform joins, defining output columns, perform sorting, and apply criteria.

    729 members asked questions and received personalized solutions in the past 7 days.

    Join the community of 500,000 technology professionals and ask your questions.

    Join & Ask a Question

    Need Help in Real-Time?

    Connect with top rated Experts

    19 Experts available now in Live!

    Get 1:1 Help Now