DLookUp Problem

[WWH]

New to Access and have what should be a simple problem.

Have two tables...

1. first table has description of herbs and Chapter+Section number (ie
2.3 - decimal)
2. second table has same Chapter+Section number + column for English
description (text)

Both Chapter+Section are stored in decimal format (eg. 10.2)

Sooo.... trying to generate a report where it prints the Chap+Sect
number
and then follows it with the English text description... ie 2.1 Heat
Clearing Herbs

I use the following:

=DLookUp("[Herb_Category]","[tbl_HerbCategory]","[Section_number] = " &
1.1)

This correctly prints English description for Chap+Sect 1.1.

However... when I replace the fixed constant 1.1 with the control
field
SECTION (or [SECTION]) from the first table (because I want it to
cycle through each Chap.Sect)... I get #ERROR on the report.

What am I doing wrong ? Thanks so much for help.

[Anton]

Hi,

It appears that you are referencing an open form? Try this:

=DLookUp("[Herb_Category]","[tbl_HerbCategory]","[Section_number] =" &
"'" & forms!formName!Control & "'")

Hope this helps

Anton

[W. W. Hutton]

Hi Anton

Thanks for your reply. Sorry I was not
clear.... am trying to display a report
and having it display each Chapter/section.

Thus... in the detail part of the report
it is referencing the control field SECTION
which is to print (for example 2.1), then
follow it with the English text equivalent
which works fine when I use a static value
(eg 1.1), but comes up with #ERROR when I
replace 1.1 with SECTION in the actual
report (no form involved, nor is there a
prompt for the specific Chapter/Section
to print). Although that might be useful
too at a later point !

Soooo... any thoughts on the syntax
when just used within the report ? Thanks !
*** Sent via Developersdex http://www.developersdex.com ***

[Anton]

Morning, just a few questions then the clarify. Is the report you are
using bound to a table/query or are you using dlookup to build the
datasource at runtime? How are the chapter and sections related; can
you give me some information on the structure of the datasource you are
using? I don't quite understand how 2.1 and 1.1 can be used as the
basis for a reference. Let me know and we can take it from there.
Cheers Anton

[W. W. Hutton]

Hi Anton

Sorry - new to this... I will try to be more
clear.

This is being done in a report - not a query
or a form. I simply want a report to print
out the complete list of herb names, section
by section.

Should look like this:

Section 2.1 Clear heat herbs <<< section header

2.1 HerbName1 << generated by detail
2.1 HerbName2 << section in the report

Section 2.2 Clear damp herbs <<< section header

2.2 HerbName3
2.2 HerbName4
2.2 HerbName5

etc

Initially I had problems with syntax and getting
this to work... soooo... I broke it down and
just used a constant value (1.1 or 2.1 - same
difference) in the lookup.

Thus in the report (section header) we find
the following:

Section [Section]
=DLookUp("[Herb_Category]","[tbl_HerbCategory]","[Section_number] = " &
2.1)

This correctly produces:

Section 2.1 Clear heat herbs

But since I want this to print correct section headers
for ALL herbs in the list... I made one
change in the DLookUp:

=DLookUp("[Herb_Category]","[tbl_HerbCategory]","[Section_number] = " &
[Section])

Replacing the 2.1 constant with [Section]

The section# prints/indexes correctly... but
the lookup which should print the English text
instead produces #ERROR.

I don't know why, and I don't know how to
debug further or fix it. I am stuck.

And yes, the report is bound to table: Herb_table.
The English text descriptions are found in
table: tbl_HerbCategory

Thank you so much for your help - I really
appreciate it.

ps. eventually I would like to do a prompt
and just print a specific section of
herbs (likr your example - I think), but
for now.. was just trying to generate
a "simple" complete report !
*** Sent via Developersdex http://www.developersdex.com ***

[Bob Quintal]

W. W. Hutton <ww******@aol.com> wrote in
news:Xs***************@news.uswest.net:

Hi Anton

This is being done in a report - not a query
or a form. I simply want a report to print
out the complete list of herb names, section
by section.

Initially I had problems with syntax and getting
this to work... soooo... I broke it down and
just used a constant value (1.1 or 2.1 - same
difference) in the lookup.

Thus in the report (section header) we find
the following:

Section [Section]
=DLookUp ("[Herb_Category]","[tbl_HerbCategory]","[Section_numbe r] = " & 2.1)

This correctly produces:

Section 2.1 Clear heat herbs

But since I want this to print correct section headers
for ALL herbs in the list... I made one
change in the DLookUp:

=DLookUp ("[Herb_Category]","[tbl_HerbCategory]","[Section_numbe r] = " & [Section])

Replacing the 2.1 constant with [Section]

The section# prints/indexes correctly... but
the lookup which should print the English text
instead produces #ERROR.

A few things to check: 1) although it looks like a number,
{section] may actually be a text type field. You may need to
wrap a cdbl() or val() function to get Access to see it as a
number. 2.) the name of the textbox on your report that
contains [section] may be something else, like text13. If it is,
see if using the name of the textbox. 3) lose the square
brackets. they may be confusing Access.

--
Bob Quintal

PA is y I've altered my email address.

[Anton]

Ok...your best (and at this stage) most simplest way of producing this
report will be to create a query with the herb_table and
herb_Categegory linked by the section numbers. As I am not entirely
sure on the names that you have used for your fields you will need to
think about this a little bit (it is however quite 'logical'). Build a
new query and move to the SQL view from the view menu and paste in the
following and then save and run your query to see if you get the
results that you desire:

SELECT Herb_table.*, tbl_HerbCategory.* FROM tbl_HerbCategory INNER
JOIN Herb_table ON tbl_HerbCategory.Herb_CategoryID =
Herb_table.Herb_ID;

After you have built the query build a new report based on it either
using the report wizard or drop the appropriate fields into the
sections as required. If you do it manually remember to group your data
on the herb_ID eg: 1.1 etc. Once again I hope this helps.

Anton