Problem with Query/Report

[Guest]

I have done some reading on this and want to get on the right track with the
way I am storing data in my tables. I have been working on a database which
contains a form with 2 combo boxes one dependent on the other. The first is
using a table which contains a "BusinessID" field (AUTO Number )and a
"Business" field (Text). I am storing the "BusinessID" field in another table
used on this form. While setting up a report it is pulling the "BusinessID"
and I want to display the "Business" field so that I can see the text. Is
there a lookup or dlookup I can use in a query so that my report will display
the text value for me? Thanks!

 

[Carl Rapson]

I have done some reading on this and want to get on the right track with
the
way I am storing data in my tables. I have been working on a database
which
contains a form with 2 combo boxes one dependent on the other. The first
is
using a table which contains a "BusinessID" field (AUTO Number )and a
"Business" field (Text). I am storing the "BusinessID" field in another
table
used on this form. While setting up a report it is pulling the
"BusinessID"
and I want to display the "Business" field so that I can see the text. Is
there a lookup or dlookup I can use in a query so that my report will
display
the text value for me? Thanks!

On your report, "hide" the BusinessID control (make its Visible property
False) and add an unbound textbox control (let's call it txtBusiness). Set
the Control Source property of txtBusiness to:

=DLookUp("[Business]","[tblBusinesses]","[BusinessID]=" & Me.BusinessID)

I don't know the name of your table, so I made up the name "tblBusinesses".
You could put this code into the Format event of whichever report section
contains the txtBusiness control instead of in the Control Source of the
control.

Carl Rapson

 

[Guest]

Hi Carl, When I enter the DLookUp formula you suggested it wants to keep
putting the Me.BusinessID in brackets...

=DLookUp("[Business]","[tblBusinesses]","[BusinessID]=" & [Me].[BusinessID])

of course then it is prompting for value of "Me"...

--
If you can read this, thank a Teacher...
If your reading it in english, thank a Veteran!

I have done some reading on this and want to get on the right track with
the
way I am storing data in my tables. I have been working on a database
which
contains a form with 2 combo boxes one dependent on the other. The first
is
using a table which contains a "BusinessID" field (AUTO Number )and a
"Business" field (Text). I am storing the "BusinessID" field in another
table
used on this form. While setting up a report it is pulling the
"BusinessID"
and I want to display the "Business" field so that I can see the text. Is
there a lookup or dlookup I can use in a query so that my report will
display
the text value for me? Thanks!

On your report, "hide" the BusinessID control (make its Visible property
False) and add an unbound textbox control (let's call it txtBusiness). Set
the Control Source property of txtBusiness to:

=DLookUp("[Business]","[tblBusinesses]","[BusinessID]=" & Me.BusinessID)

I don't know the name of your table, so I made up the name "tblBusinesses".
You could put this code into the Format event of whichever report section
contains the txtBusiness control instead of in the Control Source of the
control.

Carl Rapson

 

[Guest]

Hello again Carl, I apologize for my own ignorance on this but haven't used
the DLookUp although I do understand why they want us to store data this way
(ID)... I finally worked this out by creating an expression field in my query
and using that in my report instead of trying to work this in the report
itself. Your response was helpful and I appreciate it!
--
If you can read this, thank a Teacher...
If your reading it in english, thank a Veteran!

Hi Carl, When I enter the DLookUp formula you suggested it wants to keep
putting the Me.BusinessID in brackets...

=DLookUp("[Business]","[tblBusinesses]","[BusinessID]=" & [Me].[BusinessID])

of course then it is prompting for value of "Me"...

--
If you can read this, thank a Teacher...
If your reading it in english, thank a Veteran!

I have done some reading on this and want to get on the right track with
the
way I am storing data in my tables. I have been working on a database
which
contains a form with 2 combo boxes one dependent on the other. The first
is
using a table which contains a "BusinessID" field (AUTO Number )and a
"Business" field (Text). I am storing the "BusinessID" field in another
table
used on this form. While setting up a report it is pulling the
"BusinessID"
and I want to display the "Business" field so that I can see the text. Is
there a lookup or dlookup I can use in a query so that my report will
display
the text value for me? Thanks!

On your report, "hide" the BusinessID control (make its Visible property
False) and add an unbound textbox control (let's call it txtBusiness). Set
the Control Source property of txtBusiness to:

=DLookUp("[Business]","[tblBusinesses]","[BusinessID]=" & Me.BusinessID)

I don't know the name of your table, so I made up the name "tblBusinesses".
You could put this code into the Format event of whichever report section
contains the txtBusiness control instead of in the Control Source of the
control.

Carl Rapson