Table setup

[Paul .V.]

I need to setup two tables with default information.

First off I need a company table. Quite simple. Name, Address, phone,
tax number and so on. So I have setup that table with the fiels
corresponding to the above.

Question #1

When I print a report I need to look up one value, the tax number. I
assume I use the dLookup function but I get an #Error. Relating to my
up-coming question (#2) I have setup a table called "Misc" with three
fields MiscID, Description and Detail. In my report I have set the
control source as follows:

= =DLookUp("Detai l","Misc","Desc ription = GSTNumber")

Detail - field
Misc - Table
Description = GSTNumber - pointing to the one record that has the tax
number (GSTNumber being the text in the Description field, the actual
number in the Detail field)

I can't get that number.

Question #2

I have the Misc table setup as above. I want to be able to put
defaults in this table and retrieve them later in a form or report. I
would only need to retrieve one value at a time. These values might
be Logo, File Location, Yes/No switches or whatever. Again, I assume
I need to use the dLookup function but it doesn't seem to be working
for me.

I've searched all through the ms-access forum and I know I've seen it
somewhere before but I just can't seem to find it now.

Thanks in advance.

[PC Datasheet]

Paul,

The problem seems to be in the design of your tables. Try this:
TblCompany
CompanyID
CoName
Address
City
State
Zipcode
Phone
TaxNumber

TblCompanyMisc
CompanyMiscID
CompanyID
Logo
FileLocation
YesNo1
YesNo2
etc

Base your report then on a query that includes both tables.

--
PC Datasheet
Your Resource For Help With Access, Excel And Word Applications
re******@pcdata sheet.com
www.pcdatasheet.com
"Paul .V." <pl******@enabl e.org> wrote in message
news:22******** *************** **@posting.goog le.com...

I need to setup two tables with default information.

First off I need a company table. Quite simple. Name, Address, phone,
tax number and so on. So I have setup that table with the fiels
corresponding to the above.

Question #1

When I print a report I need to look up one value, the tax number. I
assume I use the dLookup function but I get an #Error. Relating to my
up-coming question (#2) I have setup a table called "Misc" with three
fields MiscID, Description and Detail. In my report I have set the
control source as follows:

= =DLookUp("Detai l","Misc","Desc ription = GSTNumber")

Detail - field
Misc - Table
Description = GSTNumber - pointing to the one record that has the tax
number (GSTNumber being the text in the Description field, the actual
number in the Detail field)

I can't get that number.

Question #2

I have the Misc table setup as above. I want to be able to put
defaults in this table and retrieve them later in a form or report. I
would only need to retrieve one value at a time. These values might
be Logo, File Location, Yes/No switches or whatever. Again, I assume
I need to use the dLookup function but it doesn't seem to be working
for me.

I've searched all through the ms-access forum and I know I've seen it
somewhere before but I just can't seem to find it now.

Thanks in advance.

[Paul V]

Thank you for the quick reply.

Although the solution you stated would work, I already have several
fields in my report and I would like to limit the SQL in order to speed
up the database keeping in mind any future growth.

I did end up finding the problem. Sometimes I just have to step away
from the computer and come back to it. It seems the problem stares me
right in the face then. Anywho, I was using quotes wrong. The correct
use of the dLookup should have been the following:

DLookUp("[Detail]","Misc","[Description] = 'GSTNumber'")

Thank you again,

Paul .V.

*** Sent via Developersdex http://www.developersdex.com ***
Don't just participate in USENET...get rewarded for it!

[Larry Linson]

"Paul V" wrote

Although the solution you stated
would work, I already have several
fields in my report and I would like
to limit the SQL in order to speed
up the database keeping in mind
any future growth.

Including extra fields and joins will likely be far, far faster than a
DLookup, which is equivalent to executing a separate additional query for
each and every record. And, in many situations, a query is faster than the
equivalent DLookup, anyway.

Larry Linson
Microsoft Access MVP

[Rick Brandt]

"Larry Linson" <bo*****@localh ost.not> wrote in message
news:BH2xd.380$ 1U6.369@trnddc0 9...

"Paul V" wrote

Although the solution you stated
would work, I already have several
fields in my report and I would like
to limit the SQL in order to speed
up the database keeping in mind
any future growth.

Including extra fields and joins will likely be far, far faster than a
DLookup, which is equivalent to executing a separate additional query for
each and every record. And, in many situations, a query is faster than the
equivalent DLookup, anyway.

I agree with Larry. While the domain functions (like DLookup) are often
disparaged beyond what is appropriate, they are NOT suitable in queries,
reports, continuous forms, or code loops. Anything that will call them lots of
times in a row will usually result in a real resource and performance drain.

--
I don't check the Email account attached
to this message. Send instead to...
RBrandt at Hunter dot com