Type Mismatch in Macro with empty string/null value

[Danishham via AccessMonster.com]

I have attempted to search through the archives but cannot solve this problem.
I am using Access 2007.

I have a text field for Social Security Number with input mask, limited to 11
characters.
I also have a text field called AcceptDeny with the combo box choices of
Accepted or Denied.

I am trying to create a macro that will prompt the user to enter a SSN (if
not already input) when the AcceptDeny field is changed to Accepted.

I reviewed the guidelines on identifying Null and empty string and came up
with a Macro that looks like this:

In the Conditions column:
(IsNull([Forms]![applicantform]![SSN]) Or ([Forms]![applicantform]![SSN]=""))
And [Forms]![applicantform]![AcceptDeny]="Accepted"

The Action column is MsgBox
The Arguments column is: "Please enter a Social Security Number for this
student."

This yields a Type Mismatch error when the macro is run while the form is
loaded.

Please advise.

Danishham

 

[Steve Schapel]

Danishham,

What is the data type of the AcceptDeny field? Is it truly txt with the
literal word "Accepted" entered, or is "Accepted" what you see but the
actual value is something else?

Is it really possible the the SSN field will contain a zero-length string
""? For simplicity, try leaving that out, and use the more standard syntax
for the other part like this:
[Forms]![applicantform]![SSN] Is Null And
[Forms]![applicantform]![AcceptDeny]="Accepted"

 

[Danishham via AccessMonster.com]

The data type is Text and the field is only allowed to hold values:
"Accepted;Denied;DEACTIVATED" and it hold them as their literal text.

I tried the standard syntax and still got the type mismatch....

Danishham

Danishham,

What is the data type of the AcceptDeny field? Is it truly txt with the
literal word "Accepted" entered, or is "Accepted" what you see but the
actual value is something else?

Is it really possible the the SSN field will contain a zero-length string
""? For simplicity, try leaving that out, and use the more standard syntax
for the other part like this:
[Forms]![applicantform]![SSN] Is Null And
[Forms]![applicantform]![AcceptDeny]="Accepted"

I have attempted to search through the archives but cannot solve this
problem.

[quoted text clipped - 27 lines]

Danishham

 

[Steve Schapel]

Danishham,

Sorry, at this stage I don't know what is the problem here.

As an experiment, can you try this please?... Make a query based on the
table that the applicantform form is based on, and in the Criteria for the
SSN field enter Is Null and in the Criteria of the AcceptDeny field enter
"Accepted". Then run the query (view datasheet), and see if it does return
the expected records.

--
Steve Schapel, Microsoft Access MVP

The data type is Text and the field is only allowed to hold values:
"Accepted;Denied;DEACTIVATED" and it hold them as their literal text.

I tried the standard syntax and still got the type mismatch....

Danishham

Danishham,

What is the data type of the AcceptDeny field? Is it truly txt with the
literal word "Accepted" entered, or is "Accepted" what you see but the
actual value is something else?

Is it really possible the the SSN field will contain a zero-length string
""? For simplicity, try leaving that out, and use the more standard
syntax
for the other part like this:
[Forms]![applicantform]![SSN] Is Null And
[Forms]![applicantform]![AcceptDeny]="Accepted"

I have attempted to search through the archives but cannot solve this
problem.

[quoted text clipped - 27 lines]

Danishham

 

[Ken Snell [MVP]]

Steve -

Might be an issue related to the presence of an input mask for the SSN
field/control on the form?

--

Ken Snell
<MS ACCESS MVP>
http://www.accessmvp.com/KDSnell/

Danishham,

Sorry, at this stage I don't know what is the problem here.

As an experiment, can you try this please?... Make a query based on the
table that the applicantform form is based on, and in the Criteria for the
SSN field enter Is Null and in the Criteria of the AcceptDeny field enter
"Accepted". Then run the query (view datasheet), and see if it does
return the expected records.

--
Steve Schapel, Microsoft Access MVP

The data type is Text and the field is only allowed to hold values:
"Accepted;Denied;DEACTIVATED" and it hold them as their literal text.

I tried the standard syntax and still got the type mismatch....

Danishham

Danishham,

What is the data type of the AcceptDeny field? Is it truly txt with the
literal word "Accepted" entered, or is "Accepted" what you see but the
actual value is something else?

Is it really possible the the SSN field will contain a zero-length string
""? For simplicity, try leaving that out, and use the more standard
syntax
for the other part like this:
[Forms]![applicantform]![SSN] Is Null And
[Forms]![applicantform]![AcceptDeny]="Accepted"

I have attempted to search through the archives but cannot solve this
problem.
[quoted text clipped - 27 lines]

Danishham

 

[Danishham via AccessMonster.com]

Hi Steve,
I ran the proposed query and it returned 66 records. So, something strange
is definitely going on here...
I wondered too about the input mask issue that Ken mentioned....

Client simply needs a little message box to pop up to remind user to input
SSN when student is accepted - what should I do to accomplish this since the
conventional approaches thus far are failing?

Thanks!
Danishham

Danishham,

Sorry, at this stage I don't know what is the problem here.

As an experiment, can you try this please?... Make a query based on the
table that the applicantform form is based on, and in the Criteria for the
SSN field enter Is Null and in the Criteria of the AcceptDeny field enter
"Accepted". Then run the query (view datasheet), and see if it does return
the expected records.

The data type is Text and the field is only allowed to hold values:
"Accepted;Denied;DEACTIVATED" and it hold them as their literal text.

[quoted text clipped - 21 lines]

 

[Danishham via AccessMonster.com]

Update: I changed the macro to check a different field which is also a combo
box. For some reason, that works to prompt the user to enter the SSN.
Unfortunately, I still haven't figured out how to get the MsgBox to trigger
when the orginal combo box states "Accepted". For now, hopefully this will
be a acceptable work-around.

If anyone has any input on this thread, please do post it - Thanks to
everyone for the troubleshooting.

[quoted text clipped]

 

[Steve Schapel]

Danishham,

I have run out of ideas. But if you would care to zip up a database file
that contains this problem form and any other relavent objects, and email it
to me, I would be willing to have a look at it.
steves (at) mvps (dot) org