[Okta Webinar] Learn how to a build a cloud-first strategyRegister Now


Access 2000 NULL Argument Issue

Posted on 2005-05-06
Medium Priority
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 66

Accepted Solution

Jim Horn earned 2000 total points
ID: 13945233
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 http://www.rickworld.com ($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 http://www.moshannon.com 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.

Author Comment

ID: 13946041

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

New feature and membership benefit!

New feature! Upgrade and increase expert visibility of your issues with Priority Questions.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Access custom database properties are useful for storing miscellaneous bits of information in a format that persists through database closing and reopening.  This article shows how to create and use them.
In Part II of this series, I will discuss how to identify all open instances of Excel and enumerate the workbooks, spreadsheets, and named ranges within each of those instances.
Access reports are powerful and flexible. Learn how to create a query and then a grouped report using the wizard. Modify the report design after the wizard is done to make it look better. There will be another video to explain how to put the final p…
With just a little bit of  SQL and VBA, many doors open to cool things like synchronize a list box to display data relevant to other information on a form.  If you have never written code or looked at an SQL statement before, no problem! ...  give i…
Suggested Courses

873 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