By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
426,234 Members | 1,832 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 426,234 IT Pros & Developers. It's quick & easy.

How to check for empty field?

P: n/a
Hello,

I'm a new user designing a simple database to retrieve pre-prepared
docunents for printing.

I have five tables, a form to design the documents, a form to customise and
retrieve the documents, a query to pull the data together from the tables
and a report which formats the document with the custom data ready to print.

It works very well unless:

In the form used to retrieve the documents is a field that allows entry of
the document name. This works unless it's left empty. In which case the
query suddenly stops working. The datasheet view just has the column
headings and none of the data is retrieved, whether it is valid or not. I
have set the default value for the field to 'None' and if 'None' appears in
that field then a blank document is produced. This is correct.

The question is how to check a field is not null or zero length etc and
force the word 'None' to appear there if the operator forgets to make a
valid entry.

Ideally The check would be applied to the form as data is entered and also
to the destination table to double check.

Does someone have a simple example of this working? I would love to see it!
I'm using Access 97 but have more recent versions at work to test with.

Many thanks in advance for your help,

Paul

Nov 12 '05 #1
Share this Question
Share on Google+
2 Replies


P: n/a
You can use either the Nz() function or everyone's favorite iif() to
produce the desired result:

dim DocName as string
DocName = iif(isnull(Me.txtDocName), "None", Me.txtDocName)
' or
DocName = Nz(Me.txtDocName, "None")

assuming that txtDocName is the name of the text box on the form where
you enter the name for the document.

Pavel

Paul Telco wrote:

Hello,

I'm a new user designing a simple database to retrieve pre-prepared
docunents for printing.

I have five tables, a form to design the documents, a form to customise and
retrieve the documents, a query to pull the data together from the tables
and a report which formats the document with the custom data ready to print.

It works very well unless:

In the form used to retrieve the documents is a field that allows entry of
the document name. This works unless it's left empty. In which case the
query suddenly stops working. The datasheet view just has the column
headings and none of the data is retrieved, whether it is valid or not. I
have set the default value for the field to 'None' and if 'None' appears in
that field then a blank document is produced. This is correct.

The question is how to check a field is not null or zero length etc and
force the word 'None' to appear there if the operator forgets to make a
valid entry.

Ideally The check would be applied to the form as data is entered and also
to the destination table to double check.

Does someone have a simple example of this working? I would love to see it!
I'm using Access 97 but have more recent versions at work to test with.

Many thanks in advance for your help,

Paul

Nov 12 '05 #2

P: n/a
If the field is required, set it at form level. Or check it in the
BeforeInsert event of the form. If it's acted upon/used by a button's
code, check it for null in the code before doing anything else.

If IsNull(Me.MyField) Then
Msgbox "MyField has to be filled in!",vbokonly
Me.MyField.Setfocus
end if
Nov 12 '05 #3

This discussion thread is closed

Replies have been disabled for this discussion.