I would not do this. The problem is that SQL server does not have a system wide setting to say all nulls are to be "" (some systems like Oracle I do believe have this setting.

This is not really a debate about "" vs null.

The REAL problem is you as a developer want to decide that you not dealing with nulls. However, any left join query which is VERY common type of query will OFTEN be done.

So to get all customers with last invoice date, but some customers don't have invoices yet (so you use a left join). The query will thus return null values for the child table (invoice).

The result?

Well in your "general approach" your "general coding" and "general" application desing you will STILL HAVE to OFTEN deal with nulls in your VBA and your Access application.

So you trying to adopt a design approach in which you don't have nulls, but you WILL HAVE MANY cases in which data sets return nulls despite all tables NEVER saving null values.

If the above issue could be avoided by a "system wide" setting, then I would consider that all empty columns are a "" (zls, or what we call a zero length string).

Because you don't have such a system wide setting, then in much of your general coding approach, you now have to deal with both zls, and null columns in your code etc.

The result is a LOT MORE WORK. And what is MUCH worse is your general coding will over time "assume" "" strings, but then a whole slew of bugs and issues will crop up WHEN you hit the nulls in your over all application. And the reason you hit nulls is become of the SUPER common type of query that will be a LEFT join. So even with all tables never storing null values, you still have to deal with nulls in your code. Now you have to STILL deal with the original problem you attempted to avoid. And since your coding standard was to not deal with nulls, then you get TONS of bugs and issues when you finally hit the null wall.

As a result, you REALLY (but really really really) want to adopt a coding and design standard from day 1 for the OVERALL application design. Since you don't have a system wide choice for "" in place of nulls, then you are far better off to adopt the coding standard of:

For ANY empty column, assume it is null and design your application around this paradigm. After a few years of design, coding, and huge $$$ investments of time, you be able to stick to ONE coding standard and NOT two standards.

I have been migrating access applications to SQL server for 17 years now.

The "big" problem of course is in Access you have a design choice that works rather well (the developer CAN make the choice that all columns are "" in place of null). So if this was the original design assumption, then you may well consider that when using SQL server you also don't allow nulls. (however you will find "many" problems as your post shows).

Now of course if the original application always assumed nulls, then you are in rather great shape after migration of the tables to SQL server.

If you have a large existing application that did assume "" in place of nulls, then I would consider fixing the problem server side with triggers.

Bottom line: You making a "big" design decision here that will fix in stone what you do for the many years in the future. If you CAN make this choice now, then without question assuming that any and all empty columns are null is a FAR better decision.

This decision is not based on some "" vs nulls argument or preference, but simply that the design and developer tool being used (Access as a front end to SQL server) works far better with nulls as the over all design choice due to the limitations of the tool choice here.

You will be forever committing to adding all kinds of extra silly code for controls on a simple form. With 25 or more text boxes on a form, you will now be forced to add code to every single text box. The coding cost of this choice will add up over the years, and increase developer costs - a cost you can 100% avoid by adopting the "simple" design choice that empty columns are always null.

At the risk of repeating myself, it really would be great if one could avoid nulls, and as noted some database systems have a "switch" and thus even those left joins without child records will in fact return "" in place of null when child records don't exist.

Because we don't have this system wide "switch", then as noted, your general overall coding approach in the application will STILL have to deal with null columns in the data with a surprising frequency. Since you can't avoid this issue, then flipping the design choice NOW means that all your coding approaches of assuming null will work for those cases that you can't avoid anyway.

The WHOLE idea of using Access as a front end to SQL server is the RAD ability. You will save 2-3 if not 5x the developer costs compared to using say vb.net, or some other desktop developer tool. So a $5000 project in Access will cost $25,000 in other tools.

You have to do as in Rome what the Romains do so to speak. For example you don't want to use a strong typed variable approach for say a scripting environment because the tools don't support that kind of design choice very well. So you "can" make such a choice but you then will be fighting against the tools you have.

So you have to adopt how the tool works best if you going to use that tool. This means as a developer I have to bite my lip, and do what the language and design tools like and want, not what I like or want as a developer.

I would think long and hard about your initial choice here, since you be stuck with that choice for years to come.

The choice of design tools here (Access + SQL server) is a great setup, but you have to "bend" as to how those tools really work together, or you be fighting your design choices for years.

Hands down: Your design choice should be that any and all columns that are "empty" should be designed with the choice and assumption that all such columns are null when empty. The result is your general coding approach will work for forms, and SQL query and code you start writing over the years. You will hit lots of nulls in your code, so you might as well make that assuming now - you can't avoid this issue. When you start out, you go "hey", golly I don't have to deal with those stupid nulls - but as your project grows, all of a sudden, those null values will crop up with increasing frequency, and you now dealing with a issue that you attempted to avoid. Best to just always make this assuming, and your overall code etc. will work much better as a result.

解决方案是在Change事件中检查Text属性(当前在文本框中输入的内容)，但修改e1d1e属性(将写入底层字段的内容)：

Private Sub MyTextBox_Change()
    If MyTextBox.Text = "" Then
        MyTextBox.Value = ""
    End If
End Sub

这将确保每当清空文本框内容时，都会将空字符串写回数据库，而不是将其写回NULL。

请注意，这仅在修改具有不可为空的字段的行时才有帮助。如果您的Access窗体支持添加行，则还需要添加合适的非空默认值(例如，通过将文本框Default Value属性设置为="")，因为可能不会触发更改事件。

更简单的方法是在可能的情况下修复此服务器端。然后，您甚至可以只使用该表来插入值，而不必在使用该表编写的每个表单和代码中考虑这种特殊情况。

如果您“真的”想要NOT NULL列，只需将其缺省为空字符串。然后，Access在向其中插入或修改值时将不会有任何问题：

CREATE TABLE myTable (myIdentity INT IDENTITY(1,1), myText NVARCHAR(50) NOT NULL DEFAULT (N''));

我看到你改编了文本框的onChange Event。请注意，onChangeEvent只有在您实际更改内容时才会发生。如果您使用任何宏或VBA来更改内容，则不会触发。

如果您确实希望避免传递空值，则可以将Form_BeforeUpdate(Cancel As Integer)与nz(field,nullValue)组合使用

即

Private Sub Form_BeforeUpdate(Cancel As Integer)
    Me.[ColumnName] = Nz(Me.ColumnName, vbNullString) 'Making sure your column doesn't get null value passed
End Sub