nz function dlookup

  • Thread starter Thread starter Mariela
  • Start date Start date
M

Mariela

I have tried most combinations in the site but I still get an Error message
in a blank field. I have the following in my field and it works fine, but I
get an #Error when No information is on the TopidID. I have tried several
ways to add Nz but none work. The TopicID field is numerical.

Any help will be appreciated - thanks

=DLookUp("[definition]","tbltopic","[topicid]= " & [topic])
 
Thanks - this is one of the examples I tried before but did not work - I
still get an #Error
--
Regards - MD


Klatuu said:
=Nz(DLookUp("[definition]","tbltopic","[topicid]= " & [topic]), 0)
--
Dave Hargis, Microsoft Access MVP


Mariela said:
I have tried most combinations in the site but I still get an Error message
in a blank field. I have the following in my field and it works fine, but I
get an #Error when No information is on the TopidID. I have tried several
ways to add Nz but none work. The TopicID field is numerical.

Any help will be appreciated - thanks

=DLookUp("[definition]","tbltopic","[topicid]= " & [topic])
Click to expand...
Click to expand...
 
Then I can't tell from here. Do you have any constraints on the table field?

--
Dave Hargis, Microsoft Access MVP


Mariela said:
Thanks - this is one of the examples I tried before but did not work - I
still get an #Error
--
Regards - MD


Klatuu said:
=Nz(DLookUp("[definition]","tbltopic","[topicid]= " & [topic]), 0)
--
Dave Hargis, Microsoft Access MVP


Mariela said:
I have tried most combinations in the site but I still get an Error message
in a blank field. I have the following in my field and it works fine, but I
get an #Error when No information is on the TopidID. I have tried several
ways to add Nz but none work. The TopicID field is numerical.

Any help will be appreciated - thanks

=DLookUp("[definition]","tbltopic","[topicid]= " & [topic])
Click to expand...
Click to expand...
Click to expand...
 
My table - tblTopic - Has TopicID, Topic and Description.
My Table - tblSurvey has a lookup Field Topic that goes to the tblTopic

I have a form - frmSurvey that looks at the information from tblSurvey--
on the form, I have a ComboBox that displays the Topic. I want to have a
txtField that displays the Description of that topic on the form.

I tried creating another ComboBox that would show the description, but it
does not show me the whole infomation - it cuts off at some point.

I don't know if that answers your question, but I appreciate the help


I have a txt

Regards - MD


Klatuu said:
Then I can't tell from here. Do you have any constraints on the table field?

--
Dave Hargis, Microsoft Access MVP


Mariela said:
Thanks - this is one of the examples I tried before but did not work - I
still get an #Error
--
Regards - MD


Klatuu said:
=Nz(DLookUp("[definition]","tbltopic","[topicid]= " & [topic]), 0)
--
Dave Hargis, Microsoft Access MVP


:

I have tried most combinations in the site but I still get an Error message
in a blank field. I have the following in my field and it works fine, but I
get an #Error when No information is on the TopidID. I have tried several
ways to add Nz but none work. The TopicID field is numerical.

Any help will be appreciated - thanks

=DLookUp("[definition]","tbltopic","[topicid]= " & [topic])
Click to expand...
Click to expand...
Click to expand...
 
Yes I am absolutely certain TopicID isa my primary field and is set as an
AutoNumber--
Regards - MD
 
It looks like - correct me if I am wrong - you are testing topicID against
topic.

=Nz(DLookUp("[definition]","tbltopic","[topicid]= " & [topic]), 0)
I thought you said topic was a field in the same table....
Try the above and substitute a real id (in your tbltopic) for topic, like:
=Nz(DLookUp("[definition]","tbltopic","[topicid]= 5"), 0)

If that works, then the problem is the topic field on your form.

Damon
 
On the frmSurvey I select the Topic (The primary field is a number, however
on the Combo Box I show the Topic Name-Not the number). I need to have a
tesxt box that shows me the Description of that Topic. Maybe I am not using
the right way to do this.

For Example

Fields on my tblTopic
TopicID - 1
Topic - ISO 14001
Description - Environmental Management System...........(Note Descriptions
may be too long for a ComboBox - currently cutting off the description after
255 characters.)

Fields on my tblSurvey
SurveyID - 1
Survey - Do you have an EMS system?
Topic - Lookup to tblTopic Topic Field (set to show the Topic Field that
would show ISO 14001)

Form Survey

Survey - Do you have an EMS system?
Topic - ComboBox where I would select ISO 14401

I need to have the description, from the Topic I select on the form,
populate on the text field.

Currently it shows if the Topic is populated, Not all surveys have a topid,
therefore if a Topic is blank, I get an #Error on the Text Field for
Description.

I hope this helps




--
Regards - MD


Damon Heron said:
It looks like - correct me if I am wrong - you are testing topicID against
topic.

=Nz(DLookUp("[definition]","tbltopic","[topicid]= " & [topic]), 0)
I thought you said topic was a field in the same table....
Try the above and substitute a real id (in your tbltopic) for topic, like:
=Nz(DLookUp("[definition]","tbltopic","[topicid]= 5"), 0)

If that works, then the problem is the topic field on your form.

Damon

Mariela said:
Yes I am absolutely certain TopicID isa my primary field and is set as an
AutoNumber--
Regards - MD
Click to expand...
Click to expand...
 
I understand now. Does the combobox topic have a "none" selection? If
not, you could test for a null in the afterupdate of the combobox --
if me.topic.column(0) = vbnullstring then
msgbox "No topic for this survey choice.", vbinformation, "Note"
else
me.yourtxtbox =Nz(DLookUp("[definition]","tbltopic","[topicid]= " &
[topic]), "")
end if

if not, where are you putting the dlookup code? Normally it would be in the
afterupdate event of the combo box....
So if a selection is not made, the dlookup code would not fire.

Damon



Mariela said:
On the frmSurvey I select the Topic (The primary field is a number,
however
on the Combo Box I show the Topic Name-Not the number). I need to have a
tesxt box that shows me the Description of that Topic. Maybe I am not
using
the right way to do this.

For Example

Fields on my tblTopic
TopicID - 1
Topic - ISO 14001
Description - Environmental Management System...........(Note Descriptions
may be too long for a ComboBox - currently cutting off the description
after
255 characters.)

Fields on my tblSurvey
SurveyID - 1
Survey - Do you have an EMS system?
Topic - Lookup to tblTopic Topic Field (set to show the Topic Field that
would show ISO 14001)

Form Survey

Survey - Do you have an EMS system?
Topic - ComboBox where I would select ISO 14401

I need to have the description, from the Topic I select on the form,
populate on the text field.

Currently it shows if the Topic is populated, Not all surveys have a
topid,
therefore if a Topic is blank, I get an #Error on the Text Field for
Description.

I hope this helps




--
Regards - MD


Damon Heron said:
It looks like - correct me if I am wrong - you are testing topicID
against
topic.

=Nz(DLookUp("[definition]","tbltopic","[topicid]= " & [topic]), 0)
I thought you said topic was a field in the same table....
Try the above and substitute a real id (in your tbltopic) for topic,
like:
=Nz(DLookUp("[definition]","tbltopic","[topicid]= 5"), 0)

If that works, then the problem is the topic field on your form.

Damon

Mariela said:
Yes I am absolutely certain TopicID isa my primary field and is set as
an
AutoNumber--
Regards - MD


:

You're absolutely certain that TopicID is defined as a Number in the
table?
Click to expand...
Click to expand...
Click to expand...
 
Thank you Damon - No I do not have a "None" but that is an option I can
explore. I do not necessarily want a msg box to appear if I do not select a
Topic.

The code that I have is on the text box not the Combo Box. My Combo Box is
working fine - I can select the Topic. The problem comes when I do not
select a topic that my Description Text Box gives me the #Error code

=Nz(DLookUp("[definition]","tbltopic","[topicid]= " & [topic]),"")
--
Regards - MD


Damon Heron said:
I understand now. Does the combobox topic have a "none" selection? If
not, you could test for a null in the afterupdate of the combobox --
if me.topic.column(0) = vbnullstring then
msgbox "No topic for this survey choice.", vbinformation, "Note"
else
me.yourtxtbox =Nz(DLookUp("[definition]","tbltopic","[topicid]= " &
[topic]), "")
end if

if not, where are you putting the dlookup code? Normally it would be in the
afterupdate event of the combo box....
So if a selection is not made, the dlookup code would not fire.

Damon



Mariela said:
On the frmSurvey I select the Topic (The primary field is a number,
however
on the Combo Box I show the Topic Name-Not the number). I need to have a
tesxt box that shows me the Description of that Topic. Maybe I am not
using
the right way to do this.

For Example

Fields on my tblTopic
TopicID - 1
Topic - ISO 14001
Description - Environmental Management System...........(Note Descriptions
may be too long for a ComboBox - currently cutting off the description
after
255 characters.)

Fields on my tblSurvey
SurveyID - 1
Survey - Do you have an EMS system?
Topic - Lookup to tblTopic Topic Field (set to show the Topic Field that
would show ISO 14001)

Form Survey

Survey - Do you have an EMS system?
Topic - ComboBox where I would select ISO 14401

I need to have the description, from the Topic I select on the form,
populate on the text field.

Currently it shows if the Topic is populated, Not all surveys have a
topid,
therefore if a Topic is blank, I get an #Error on the Text Field for
Description.

I hope this helps




--
Regards - MD


Damon Heron said:
It looks like - correct me if I am wrong - you are testing topicID
against
topic.

=Nz(DLookUp("[definition]","tbltopic","[topicid]= " & [topic]), 0)
I thought you said topic was a field in the same table....
Try the above and substitute a real id (in your tbltopic) for topic,
like:
=Nz(DLookUp("[definition]","tbltopic","[topicid]= 5"), 0)

If that works, then the problem is the topic field on your form.

Damon

Yes I am absolutely certain TopicID isa my primary field and is set as
an
AutoNumber--
Regards - MD


:

You're absolutely certain that TopicID is defined as a Number in the
table?
Click to expand...
Click to expand...
Click to expand...
 
Do you understand why it is giving you the error? It is because you have no
topic. So take the code out of the textbox and put it in the afterupdate
event of the combobox. That way the textbox will be blank if there is no
topic.
topic.column(0) should be the topicID....


if me.topic.column(0) = vbnullstring then
' comment out if you dont want a msg....msgbox "No topic for this survey
choice.", vbinformation, "Note"
else
me.yourtxtbox =Nz(DLookUp("[definition]","tbltopic","[topicid]= " &
[topic]), "")
end if

Damon

Mariela said:
Thank you Damon - No I do not have a "None" but that is an option I can
explore. I do not necessarily want a msg box to appear if I do not select
a
Topic.

The code that I have is on the text box not the Combo Box. My Combo Box is
working fine - I can select the Topic. The problem comes when I do not
select a topic that my Description Text Box gives me the #Error code

=Nz(DLookUp("[definition]","tbltopic","[topicid]= " & [topic]),"")
--
Regards - MD


Damon Heron said:
I understand now. Does the combobox topic have a "none" selection? If
not, you could test for a null in the afterupdate of the combobox --
if me.topic.column(0) = vbnullstring then
msgbox "No topic for this survey choice.", vbinformation, "Note"
else
me.yourtxtbox =Nz(DLookUp("[definition]","tbltopic","[topicid]= " &
[topic]), "")
end if

if not, where are you putting the dlookup code? Normally it would be in
the
afterupdate event of the combo box....
So if a selection is not made, the dlookup code would not fire.

Damon



Mariela said:
On the frmSurvey I select the Topic (The primary field is a number,
however
on the Combo Box I show the Topic Name-Not the number). I need to have
a
tesxt box that shows me the Description of that Topic. Maybe I am not
using
the right way to do this.

For Example

Fields on my tblTopic
TopicID - 1
Topic - ISO 14001
Description - Environmental Management System...........(Note
Descriptions
may be too long for a ComboBox - currently cutting off the description
after
255 characters.)

Fields on my tblSurvey
SurveyID - 1
Survey - Do you have an EMS system?
Topic - Lookup to tblTopic Topic Field (set to show the Topic Field
that
would show ISO 14001)

Form Survey

Survey - Do you have an EMS system?
Topic - ComboBox where I would select ISO 14401

I need to have the description, from the Topic I select on the form,
populate on the text field.

Currently it shows if the Topic is populated, Not all surveys have a
topid,
therefore if a Topic is blank, I get an #Error on the Text Field for
Description.

I hope this helps




--
Regards - MD


:

It looks like - correct me if I am wrong - you are testing topicID
against
topic.

=Nz(DLookUp("[definition]","tbltopic","[topicid]= " & [topic]), 0)
I thought you said topic was a field in the same table....
Try the above and substitute a real id (in your tbltopic) for topic,
like:
=Nz(DLookUp("[definition]","tbltopic","[topicid]= 5"), 0)

If that works, then the problem is the topic field on your form.

Damon

Yes I am absolutely certain TopicID isa my primary field and is set
as
an
AutoNumber--
Regards - MD


:

You're absolutely certain that TopicID is defined as a Number in
the
table?
Click to expand...
Click to expand...
Click to expand...
 
Yes I did add the code on the afterupdate of the combobox and it did not
help. But I did find the solution which was to chage the code on the text
box to read as follows

=IIf([topic]>0,DLookUp("[definition]","tbltopic","[topicid]= " & [topic]),"")
--
Thank you for your help - it was much appreciated

Regards - MD


Damon Heron said:
Do you understand why it is giving you the error? It is because you have no
topic. So take the code out of the textbox and put it in the afterupdate
event of the combobox. That way the textbox will be blank if there is no
topic.
topic.column(0) should be the topicID....


if me.topic.column(0) = vbnullstring then
' comment out if you dont want a msg....msgbox "No topic for this survey
choice.", vbinformation, "Note"
else
me.yourtxtbox =Nz(DLookUp("[definition]","tbltopic","[topicid]= " &
[topic]), "")
end if

Damon

Mariela said:
Thank you Damon - No I do not have a "None" but that is an option I can
explore. I do not necessarily want a msg box to appear if I do not select
a
Topic.

The code that I have is on the text box not the Combo Box. My Combo Box is
working fine - I can select the Topic. The problem comes when I do not
select a topic that my Description Text Box gives me the #Error code

=Nz(DLookUp("[definition]","tbltopic","[topicid]= " & [topic]),"")
--
Regards - MD


Damon Heron said:
I understand now. Does the combobox topic have a "none" selection? If
not, you could test for a null in the afterupdate of the combobox --
if me.topic.column(0) = vbnullstring then
msgbox "No topic for this survey choice.", vbinformation, "Note"
else
me.yourtxtbox =Nz(DLookUp("[definition]","tbltopic","[topicid]= " &
[topic]), "")
end if

if not, where are you putting the dlookup code? Normally it would be in
the
afterupdate event of the combo box....
So if a selection is not made, the dlookup code would not fire.

Damon



On the frmSurvey I select the Topic (The primary field is a number,
however
on the Combo Box I show the Topic Name-Not the number). I need to have
a
tesxt box that shows me the Description of that Topic. Maybe I am not
using
the right way to do this.

For Example

Fields on my tblTopic
TopicID - 1
Topic - ISO 14001
Description - Environmental Management System...........(Note
Descriptions
may be too long for a ComboBox - currently cutting off the description
after
255 characters.)

Fields on my tblSurvey
SurveyID - 1
Survey - Do you have an EMS system?
Topic - Lookup to tblTopic Topic Field (set to show the Topic Field
that
would show ISO 14001)

Form Survey

Survey - Do you have an EMS system?
Topic - ComboBox where I would select ISO 14401

I need to have the description, from the Topic I select on the form,
populate on the text field.

Currently it shows if the Topic is populated, Not all surveys have a
topid,
therefore if a Topic is blank, I get an #Error on the Text Field for
Description.

I hope this helps




--
Regards - MD


:

It looks like - correct me if I am wrong - you are testing topicID
against
topic.

=Nz(DLookUp("[definition]","tbltopic","[topicid]= " & [topic]), 0)
I thought you said topic was a field in the same table....
Try the above and substitute a real id (in your tbltopic) for topic,
like:
=Nz(DLookUp("[definition]","tbltopic","[topicid]= 5"), 0)

If that works, then the problem is the topic field on your form.

Damon

Yes I am absolutely certain TopicID isa my primary field and is set
as
an
AutoNumber--
Regards - MD


:

You're absolutely certain that TopicID is defined as a Number in
the
table?
Click to expand...
Click to expand...
Click to expand...
 
Back
Top