Commonly asked question.........

[MRL]

Have 2 table fields [Part #:] and [Operation #:] that a user will input from
a form "Input Form" (have 2 text boxes). That all works fine & dandy. Now I
want to put code in "Input Form" so when a report is requested by hitting a
print button and there are no matches for part # & operation # a message box
informs the user. Trying to fit it in under the btn_print sub using such
attempts as:
if [queries]![main table query]![part #:] = "" then
msgbox "Sorry",
Keeps telling me it can't find field ' ' with some symbol in the ''. What
the......any ideas, are : and # things I shouldn't use in field names?
Thanks

 

[Ken Snell [MVP]]

First, it's not good practice to use spaces, #, !, ', @, ?, etc. characters
in table names, field names, and control names. At a minimum, you must
always surround the name with [ ] characters, and these characters can cause
unintended results in queries with regard to which records might be found
and returned.

Second, there is no queries collection, so the code that you are trying to
use will not be successful. Instead, use the DLookup function to "look up" a
value from a query or use the DCount function to see if there is any record
at all with that value:

If DLookup("Part #", "main table query") = "" Then
MsgBox "Sorry"
(etc.)


or (if Part # is a numeric field)

If DCount("*", "main table query", _
"[Part #]=" & Me.NameOfControlOnFormWithThePart#.Value) _
= 0 Then
MsgBox "Sorry"
(etc.)


or (if Part # is a text field)

If DCount("*", "main table query", _
"[Part #]='" & Me.NameOfControlOnFormWithThePart#.Value _
& "'")= 0 Then
MsgBox "Sorry"
(etc.)

 

[MRL]

What does Dlookup return, T/F , Null. Threw in DLookup inside my Print sub
to check if an 8 digit user inputted number (queried) was in the table to
print, if it's not I want to inform the user, I've got this:
Private Sub btnPrint_Click()

If DLookup("[part no]", "main table query") = Null Then
MsgBox "No Process Sheet Written", vbRetryCancel
Makes me think I've got to run my query first, or do I throw in a new
variable, assign the dlookup result and play with that?
MRL again

First, it's not good practice to use spaces, #, !, ', @, ?, etc. characters
in table names, field names, and control names. At a minimum, you must
always surround the name with [ ] characters, and these characters can cause
unintended results in queries with regard to which records might be found
and returned.

Second, there is no queries collection, so the code that you are trying to
use will not be successful. Instead, use the DLookup function to "look up" a
value from a query or use the DCount function to see if there is any record
at all with that value:

If DLookup("Part #", "main table query") = "" Then
MsgBox "Sorry"
(etc.)


or (if Part # is a numeric field)

If DCount("*", "main table query", _
"[Part #]=" & Me.NameOfControlOnFormWithThePart#.Value) _
= 0 Then
MsgBox "Sorry"
(etc.)


or (if Part # is a text field)

If DCount("*", "main table query", _
"[Part #]='" & Me.NameOfControlOnFormWithThePart#.Value _
& "'")= 0 Then
MsgBox "Sorry"
(etc.)

--

Ken Snell
<MS ACCESS MVP>

Have 2 table fields [Part #:] and [Operation #:] that a user will input from
a form "Input Form" (have 2 text boxes). That all works fine & dandy. Now I
want to put code in "Input Form" so when a report is requested by hitting a
print button and there are no matches for part # & operation # a message box
informs the user. Trying to fit it in under the btn_print sub using such
attempts as:
if [queries]![main table query]![part #:] = "" then
msgbox "Sorry",
Keeps telling me it can't find field ' ' with some symbol in the ''. What
the......any ideas, are : and # things I shouldn't use in field names?
Thanks

 

[Douglas J. Steele]

DLookup returns a value if one exists, and Null otherwise. What type of
value it returns depends on the field being queried.

You cannot use = to check whether Null is returned. By definition, Null does
not equal Null.

Try

If IsNull(DLookup("[part no]", "main table query")) Then
MsgBox "No Process Sheet Written", vbRetryCancel

However, if you're trying to determine whether a specific value exists, you
need to provide a third parameter to DLookup (or DCount, for that matter),
specifying the value for which you're looking.

If [part no] is numeric, you'd need:

If IsNull(DLookup("[part no]", "main table query", "[part no] = " &
InputNumber)) Then

If it's text, you'd need:

If IsNull(DLookup("[part no]", "main table query", "[part no] = " &
Chr$(34) & InputNumber & Chr$(34))) Then

--
Doug Steele, Microsoft Access MVP

(No private e-mails, please)

What does Dlookup return, T/F , Null. Threw in DLookup inside my Print sub
to check if an 8 digit user inputted number (queried) was in the table to
print, if it's not I want to inform the user, I've got this:
Private Sub btnPrint_Click()

If DLookup("[part no]", "main table query") = Null Then
MsgBox "No Process Sheet Written", vbRetryCancel
Makes me think I've got to run my query first, or do I throw in a new
variable, assign the dlookup result and play with that?
MRL again

First, it's not good practice to use spaces, #, !, ', @, ?, etc. characters
in table names, field names, and control names. At a minimum, you must
always surround the name with [ ] characters, and these characters can cause
unintended results in queries with regard to which records might be found
and returned.

Second, there is no queries collection, so the code that you are trying to
use will not be successful. Instead, use the DLookup function to "look up" a
value from a query or use the DCount function to see if there is any record
at all with that value:

If DLookup("Part #", "main table query") = "" Then
MsgBox "Sorry"
(etc.)


or (if Part # is a numeric field)

If DCount("*", "main table query", _
"[Part #]=" & Me.NameOfControlOnFormWithThePart#.Value) _
= 0 Then
MsgBox "Sorry"
(etc.)


or (if Part # is a text field)

If DCount("*", "main table query", _
"[Part #]='" & Me.NameOfControlOnFormWithThePart#.Value _
& "'")= 0 Then
MsgBox "Sorry"
(etc.)

--

Ken Snell
<MS ACCESS MVP>

Have 2 table fields [Part #:] and [Operation #:] that a user will

input
from

a form "Input Form" (have 2 text boxes). That all works fine & dandy. Now I
want to put code in "Input Form" so when a report is requested by

hitting
a

print button and there are no matches for part # & operation # a

message
box

informs the user. Trying to fit it in under the btn_print sub using such
attempts as:
if [queries]![main table query]![part #:] = "" then
msgbox "Sorry",
Keeps telling me it can't find field ' ' with some symbol in the ''. What
the......any ideas, are : and # things I shouldn't use in field names?
Thanks

 

[Ken Snell [MVP]]

Ups, yes. DLookup will return Null (not "") if there is no value. So wrap
the DLookup with IsNull function:

If IsNull(DLookup("Part #", "main table query")) = True Then
MsgBox "Sorry"
(etc.)

--

Ken Snell
<MS ACCESS MVP>

What does Dlookup return, T/F , Null. Threw in DLookup inside my Print sub
to check if an 8 digit user inputted number (queried) was in the table to
print, if it's not I want to inform the user, I've got this:
Private Sub btnPrint_Click()

If DLookup("[part no]", "main table query") = Null Then
MsgBox "No Process Sheet Written", vbRetryCancel
Makes me think I've got to run my query first, or do I throw in a new
variable, assign the dlookup result and play with that?
MRL again

First, it's not good practice to use spaces, #, !, ', @, ?, etc. characters
in table names, field names, and control names. At a minimum, you must
always surround the name with [ ] characters, and these characters can cause
unintended results in queries with regard to which records might be found
and returned.

Second, there is no queries collection, so the code that you are trying to
use will not be successful. Instead, use the DLookup function to "look up" a
value from a query or use the DCount function to see if there is any record
at all with that value:

If DLookup("Part #", "main table query") = "" Then
MsgBox "Sorry"
(etc.)


or (if Part # is a numeric field)

If DCount("*", "main table query", _
"[Part #]=" & Me.NameOfControlOnFormWithThePart#.Value) _
= 0 Then
MsgBox "Sorry"
(etc.)


or (if Part # is a text field)

If DCount("*", "main table query", _
"[Part #]='" & Me.NameOfControlOnFormWithThePart#.Value _
& "'")= 0 Then
MsgBox "Sorry"
(etc.)

--

Ken Snell
<MS ACCESS MVP>

Have 2 table fields [Part #:] and [Operation #:] that a user will

input
from

a form "Input Form" (have 2 text boxes). That all works fine & dandy. Now I
want to put code in "Input Form" so when a report is requested by

hitting
a

print button and there are no matches for part # & operation # a

message
box

informs the user. Trying to fit it in under the btn_print sub using such
attempts as:
if [queries]![main table query]![part #:] = "" then
msgbox "Sorry",
Keeps telling me it can't find field ' ' with some symbol in the ''. What
the......any ideas, are : and # things I shouldn't use in field names?
Thanks

 

[Ken Snell [MVP]]

And as Doug has correctly noted,

If IsNull(DLookup("Part #", "main table query", "[Part #]='" & _
Me.NameOfControlOnFormWithThePart#.Value _
& "'")) = True Then
MsgBox "Sorry"
(etc.)

or (above for text, below for numeric)

If IsNull(DLookup("Part #", "main table query", "[Part #]=" & _
Me.NameOfControlOnFormWithThePart#.Value)) _
= True Then
MsgBox "Sorry"
(etc.)

My brain has really left town today..... :-(


--

Ken Snell
<MS ACCESS MVP>

_

"[Part #]='" & Me.NameOfControlOnFormWithThePart#.Value _
& "'")= 0 Then
MsgBox "Sorry"
(etc.)

Ups, yes. DLookup will return Null (not "") if there is no value. So wrap
the DLookup with IsNull function:

If IsNull(DLookup("Part #", "main table query")) = True Then
MsgBox "Sorry"
(etc.)

--

Ken Snell
<MS ACCESS MVP>

What does Dlookup return, T/F , Null. Threw in DLookup inside my Print sub
to check if an 8 digit user inputted number (queried) was in the table to
print, if it's not I want to inform the user, I've got this:
Private Sub btnPrint_Click()

If DLookup("[part no]", "main table query") = Null Then
MsgBox "No Process Sheet Written", vbRetryCancel
Makes me think I've got to run my query first, or do I throw in a new
variable, assign the dlookup result and play with that?
MRL again

First, it's not good practice to use spaces, #, !, ', @, ?, etc. characters
in table names, field names, and control names. At a minimum, you must
always surround the name with [ ] characters, and these characters can cause
unintended results in queries with regard to which records might be found
and returned.

Second, there is no queries collection, so the code that you are

trying

to

use will not be successful. Instead, use the DLookup function to "look up" a
value from a query or use the DCount function to see if there is any record
at all with that value:

If DLookup("Part #", "main table query") = "" Then
MsgBox "Sorry"
(etc.)


or (if Part # is a numeric field)

If DCount("*", "main table query", _
"[Part #]=" & Me.NameOfControlOnFormWithThePart#.Value) _
= 0 Then
MsgBox "Sorry"
(etc.)


or (if Part # is a text field)

If DCount("*", "main table query", _
"[Part #]='" & Me.NameOfControlOnFormWithThePart#.Value _
& "'")= 0 Then
MsgBox "Sorry"
(etc.)

--

Ken Snell
<MS ACCESS MVP>


Have 2 table fields [Part #:] and [Operation #:] that a user will input
from
a form "Input Form" (have 2 text boxes). That all works fine & dandy.
Now I
want to put code in "Input Form" so when a report is requested by hitting
a
print button and there are no matches for part # & operation # a message
box
informs the user. Trying to fit it in under the btn_print sub using such
attempts as:
if [queries]![main table query]![part #:] = "" then
msgbox "Sorry",
Keeps telling me it can't find field ' ' with some symbol in the ''. What
the......any ideas, are : and # things I shouldn't use in field names?
Thanks

 

[MRL]

I Got It!!!!!!!!!! No, we got it! Thanks

DLookup returns a value if one exists, and Null otherwise. What type of
value it returns depends on the field being queried.

You cannot use = to check whether Null is returned. By definition, Null does
not equal Null.

Try

If IsNull(DLookup("[part no]", "main table query")) Then
MsgBox "No Process Sheet Written", vbRetryCancel

However, if you're trying to determine whether a specific value exists, you
need to provide a third parameter to DLookup (or DCount, for that matter),
specifying the value for which you're looking.

If [part no] is numeric, you'd need:

If IsNull(DLookup("[part no]", "main table query", "[part no] = " &
InputNumber)) Then

If it's text, you'd need:

If IsNull(DLookup("[part no]", "main table query", "[part no] = " &
Chr$(34) & InputNumber & Chr$(34))) Then

--
Doug Steele, Microsoft Access MVP

(No private e-mails, please)

What does Dlookup return, T/F , Null. Threw in DLookup inside my Print sub
to check if an 8 digit user inputted number (queried) was in the table to
print, if it's not I want to inform the user, I've got this:
Private Sub btnPrint_Click()

If DLookup("[part no]", "main table query") = Null Then
MsgBox "No Process Sheet Written", vbRetryCancel
Makes me think I've got to run my query first, or do I throw in a new
variable, assign the dlookup result and play with that?
MRL again

First, it's not good practice to use spaces, #, !, ', @, ?, etc. characters
in table names, field names, and control names. At a minimum, you must
always surround the name with [ ] characters, and these characters can cause
unintended results in queries with regard to which records might be found
and returned.

Second, there is no queries collection, so the code that you are trying to
use will not be successful. Instead, use the DLookup function to "look up" a
value from a query or use the DCount function to see if there is any record
at all with that value:

If DLookup("Part #", "main table query") = "" Then
MsgBox "Sorry"
(etc.)


or (if Part # is a numeric field)

If DCount("*", "main table query", _
"[Part #]=" & Me.NameOfControlOnFormWithThePart#.Value) _
= 0 Then
MsgBox "Sorry"
(etc.)


or (if Part # is a text field)

If DCount("*", "main table query", _
"[Part #]='" & Me.NameOfControlOnFormWithThePart#.Value _
& "'")= 0 Then
MsgBox "Sorry"
(etc.)

--

Ken Snell
<MS ACCESS MVP>


Have 2 table fields [Part #:] and [Operation #:] that a user will input
from
a form "Input Form" (have 2 text boxes). That all works fine & dandy.
Now I
want to put code in "Input Form" so when a report is requested by hitting
a
print button and there are no matches for part # & operation # a message
box
informs the user. Trying to fit it in under the btn_print sub using such
attempts as:
if [queries]![main table query]![part #:] = "" then
msgbox "Sorry",
Keeps telling me it can't find field ' ' with some symbol in the ''. What
the......any ideas, are : and # things I shouldn't use in field names?
Thanks

 

[MRL]

We got it my friend, thanks!!!!!!!!!!!!!!

Ups, yes. DLookup will return Null (not "") if there is no value. So wrap
the DLookup with IsNull function:

If IsNull(DLookup("Part #", "main table query")) = True Then
MsgBox "Sorry"
(etc.)

--

Ken Snell
<MS ACCESS MVP>

What does Dlookup return, T/F , Null. Threw in DLookup inside my Print sub
to check if an 8 digit user inputted number (queried) was in the table to
print, if it's not I want to inform the user, I've got this:
Private Sub btnPrint_Click()

If DLookup("[part no]", "main table query") = Null Then
MsgBox "No Process Sheet Written", vbRetryCancel
Makes me think I've got to run my query first, or do I throw in a new
variable, assign the dlookup result and play with that?
MRL again

First, it's not good practice to use spaces, #, !, ', @, ?, etc. characters
in table names, field names, and control names. At a minimum, you must
always surround the name with [ ] characters, and these characters can cause
unintended results in queries with regard to which records might be found
and returned.

Second, there is no queries collection, so the code that you are trying to
use will not be successful. Instead, use the DLookup function to "look up" a
value from a query or use the DCount function to see if there is any record
at all with that value:

If DLookup("Part #", "main table query") = "" Then
MsgBox "Sorry"
(etc.)


or (if Part # is a numeric field)

If DCount("*", "main table query", _
"[Part #]=" & Me.NameOfControlOnFormWithThePart#.Value) _
= 0 Then
MsgBox "Sorry"
(etc.)


or (if Part # is a text field)

If DCount("*", "main table query", _
"[Part #]='" & Me.NameOfControlOnFormWithThePart#.Value _
& "'")= 0 Then
MsgBox "Sorry"
(etc.)

--

Ken Snell
<MS ACCESS MVP>


Have 2 table fields [Part #:] and [Operation #:] that a user will input
from
a form "Input Form" (have 2 text boxes). That all works fine & dandy.
Now I
want to put code in "Input Form" so when a report is requested by hitting
a
print button and there are no matches for part # & operation # a message
box
informs the user. Trying to fit it in under the btn_print sub using such
attempts as:
if [queries]![main table query]![part #:] = "" then
msgbox "Sorry",
Keeps telling me it can't find field ' ' with some symbol in the ''. What
the......any ideas, are : and # things I shouldn't use in field names?
Thanks