Parameter box, complex query

[floyd33]

I have a query that is somewhat complex. It is bringing in several tables,
regular queries and crosstab queries. I have also renamed fields in the
query to read like "TM:Region2," as the field names are confusing by
themselves and the renaming helps identify the fields better. The query
itself works fine, but when I go to enter a parameter box on the "TM" field,
it gives me an error message:"The Microsoft Jet database does not recognize
[Enter TM Name] as a valid field name or expression." What am I doing wrong
here? Please help! I have attached a pic of my parameter box below.

<a href="http://photobucket.com" target="_blank"><img src="
http://i219.photobucket.com/albums/cc290/floyd3368/query.jpg" border="0"
alt="Photo Sharing and Video Hosting at Photobucket"></a>

 

[John Spencer]

First thing, with a crosstab query you MUST declare your parameters and if
any
other queries are used in the crosstab their parameters must also be
declared.

Open the query in design mode
Select Query: Parameters from the Menu
Fill in the EXACT name of the parameter in column 1
Select the data type of the parameter in column 2

Basically, you are going to have to declare the parameters since you are
using a crosstab query.
--
John Spencer
Access MVP 2002-2005, 2007
Center for Health Program Development and Management
University of Maryland Baltimore County
..

 

[floyd33 via AccessMonster.com]

It is giving me all the data regardless of using field name as in table or
field name with edited label. It won't filter out only the data I want to
see. What am I doing wrong? Thank you!

First thing, with a crosstab query you MUST declare your parameters and if
any
other queries are used in the crosstab their parameters must also be
declared.

Open the query in design mode
Select Query: Parameters from the Menu
Fill in the EXACT name of the parameter in column 1
Select the data type of the parameter in column 2

Basically, you are going to have to declare the parameters since you are
using a crosstab query.

I have a query that is somewhat complex. It is bringing in several tables,
regular queries and crosstab queries. I have also renamed fields in the

[quoted text clipped - 11 lines]

http://i219.photobucket.com/albums/cc290/floyd3368/query.jpg" border="0"
alt="Photo Sharing and Video Hosting at Photobucket"></a>

 

[John Spencer]

I have absolutely no idea. I can't see your query, I don't know what error
messages (if any) you are getting.

First step would be to post the SQL you are attempting to use. (Menu; View:
SQL). I would venture to say that if it is as complex as you stated that
you will need to post the underlying queries also.

Try trouble shooting it by running each of the queries you have and see if
they work. If they all work, then you will need to look at what you are
doing. Probably your where clause is not doing what you expect it to.

--
John Spencer
Access MVP 2002-2005, 2007
Center for Health Program Development and Management
University of Maryland Baltimore County
..

It is giving me all the data regardless of using field name as in table or
field name with edited label. It won't filter out only the data I want to
see. What am I doing wrong? Thank you!

First thing, with a crosstab query you MUST declare your parameters and if
any
other queries are used in the crosstab their parameters must also be
declared.

Open the query in design mode
Select Query: Parameters from the Menu
Fill in the EXACT name of the parameter in column 1
Select the data type of the parameter in column 2

Basically, you are going to have to declare the parameters since you are
using a crosstab query.

I have a query that is somewhat complex. It is bringing in several
tables,
regular queries and crosstab queries. I have also renamed fields in the

[quoted text clipped - 11 lines]

http://i219.photobucket.com/albums/cc290/floyd3368/query.jpg" border="0"
alt="Photo Sharing and Video Hosting at Photobucket"></a>

 

[floyd33 via AccessMonster.com]

Here is the SQL.
SELECT DISTINCTROW tblRepInformation.Region AS Territory, tblRepInformation.
Region2 AS RSD, tblMeeting.StartDate, tblRepInformation.TerritoryNumber AS
[Primary Rep Territory], [tblRepInformation]![FirstName] & " " &
[tblRepInformation]![LastName] AS TM, tblMeetingStatus.MeetingStatus,
tblMeeting.Venue, tblMeetingType.MeetingType, IIf(IsNull([VenueCity]),"",
[VenueCity] & ", " & [VenueState]) AS Location, tblMeeting.MeetingTitle,
qryAttendeeCount_Crosstab.[Total Of AttendeeCount], IIf([Total Of Faculty
Count]=1,"Yes","Not Registered") AS [Speaker Registered],
qryConfirmedSpeakers.Speaker, IIf(IsNull(qryBudgetHonorarium.SumOfAmount),0,
qryBudgetHonorarium.SumOfAmount) AS Hon, IIf(IsNull(qryBudgetMgmtFee.
SumOfAmount),0,qryBudgetMgmtFee.SumOfAmount) AS Fee, IIf(IsNull
(qryBudgetOOPExpenses.SumOfAmount),0,qryBudgetOOPExpenses.SumOfAmount) AS OOP,
([Hon]+[Fee]+[OOP]) AS Total
FROM (((((((tblMeeting LEFT JOIN tblRepInformation ON tblMeeting.RepID =
tblRepInformation.RepID) LEFT JOIN qryAttendeeCount_Crosstab ON tblMeeting.
MeetingID = qryAttendeeCount_Crosstab.MeetingID) LEFT JOIN tblMeetingStatus
ON tblMeeting.MeetingStatus = tblMeetingStatus.MeetingStatusID) LEFT JOIN
tblMeetingType ON tblMeeting.MeetingType = tblMeetingType.MeetingTypeID) LEFT
JOIN qryConfirmedSpeakers ON tblMeeting.MeetingID = qryConfirmedSpeakers.
MeetingID) LEFT JOIN qryBudgetHonorarium ON tblMeeting.MeetingID =
qryBudgetHonorarium.MeetingID) LEFT JOIN qryBudgetMgmtFee ON tblMeeting.
MeetingID = qryBudgetMgmtFee.MeetingID) LEFT JOIN qryBudgetOOPExpenses ON
tblMeeting.MeetingID = qryBudgetOOPExpenses.MeetingID
ORDER BY tblRepInformation.Region, tblRepInformation.Region2, tblMeeting.
StartDate, tblRepInformation.LastName, tblMeeting.StartDate;

Now, what I need to do is to have a parameter box asking for the user to
enter the RSD (region2) name. It works fine UNTIL I bring in the
qryAttendeeCount_Crosstab. Then it gives me the errors about the MS Jet
Database Engine does not recognize the (criteria) [Enter RSD Full Name] as a
valid field name or expression."

Thanks, John!

I have absolutely no idea. I can't see your query, I don't know what error
messages (if any) you are getting.

First step would be to post the SQL you are attempting to use. (Menu; View:
SQL). I would venture to say that if it is as complex as you stated that
you will need to post the underlying queries also.

Try trouble shooting it by running each of the queries you have and see if
they work. If they all work, then you will need to look at what you are
doing. Probably your where clause is not doing what you expect it to.

It is giving me all the data regardless of using field name as in table or
field name with edited label. It won't filter out only the data I want to

[quoted text clipped - 18 lines]

 

[John Spencer]

First thing, with a crosstab query you MUST declare your parameters and if
any
other queries are used with the crosstab their parameters must also be
declared.

Open the query in design mode
Select Query: Parameters from the Menu
Fill in the EXACT name of the parameter in column 1
Select the data type of the parameter in column 2

So, Probably all you need is to add the first line to the query that looks
like this

PARAMETERS [Enter RSD Full Name] as Text(255);

SELECT DISTINCTROW tblRepInformation.Region AS Territory
, tblRepInformation.Region2 AS RSD, tblMeeting.StartDate
, tblRepInformation.TerritoryNumber AS [Primary Rep Territory]
, [tblRepInformation]![FirstName] & " " &[tblRepInformation]![LastName] AS
TM
, tblMeetingStatus.MeetingStatus
, tblMeeting.Venue, tblMeetingType.MeetingType
, IIf(IsNull([VenueCity]),"",[VenueCity] & ", " & [VenueState]) AS Location
, tblMeeting.MeetingTitle
, qryAttendeeCount_Crosstab.[Total Of AttendeeCount]
, IIf([Total Of Faculty Count]=1,"Yes","Not Registered") AS [Speaker
Registered]
, qryConfirmedSpeakers.Speaker
,
IIf(IsNull(qryBudgetHonorarium.SumOfAmount),0,qryBudgetHonorarium.SumOfAmount)
AS Hon
, IIf(IsNull(qryBudgetMgmtFee.SumOfAmount),0,qryBudgetMgmtFee.SumOfAmount)
AS Fee
,
IIf(IsNull(qryBudgetOOPExpenses.SumOfAmount),0,qryBudgetOOPExpenses.SumOfAmount)
AS OOP
, ([Hon]+[Fee]+[OOP]) AS Total
FROM (((((((tblMeeting LEFT JOIN tblRepInformation ON tblMeeting.RepID =
tblRepInformation.RepID) LEFT JOIN qryAttendeeCount_Crosstab ON tblMeeting.
MeetingID = qryAttendeeCount_Crosstab.MeetingID) LEFT JOIN tblMeetingStatus
ON tblMeeting.MeetingStatus = tblMeetingStatus.MeetingStatusID) LEFT JOIN
tblMeetingType ON tblMeeting.MeetingType = tblMeetingType.MeetingTypeID)
LEFT
JOIN qryConfirmedSpeakers ON tblMeeting.MeetingID = qryConfirmedSpeakers.
MeetingID) LEFT JOIN qryBudgetHonorarium ON tblMeeting.MeetingID =
qryBudgetHonorarium.MeetingID) LEFT JOIN qryBudgetMgmtFee ON tblMeeting.
MeetingID = qryBudgetMgmtFee.MeetingID) LEFT JOIN qryBudgetOOPExpenses ON
tblMeeting.MeetingID = qryBudgetOOPExpenses.MeetingID

WHERE tblRepInformation.Region2 =[Enter RSD Full Name]

ORDER BY tblRepInformation.Region, tblRepInformation.Region2
, tblMeeting.StartDate, tblRepInformation.LastName
, tblMeeting.StartDate;

--
John Spencer
Access MVP 2002-2005, 2007
Center for Health Program Development and Management
University of Maryland Baltimore County
..

First step would be to post the SQL you are attempting to use. (Menu;
View:
SQL). I would venture to say that if it is as complex as you stated that
you will need to post the underlying queries also.

Try trouble shooting it by running each of the queries you have and see if
they work. If they all work, then you will need to look at what you are
doing. Probably your where clause is not doing what you expect it to.

It is giving me all the data regardless of using field name as in table
or
field name with edited label. It won't filter out only the data I want
to

[quoted text clipped - 18 lines]

http://i219.photobucket.com/albums/cc290/floyd3368/query.jpg"
border="0"
alt="Photo Sharing and Video Hosting at Photobucket"></a>

 

[floyd33 via AccessMonster.com]

Thank you so much, John. I was making it harder than it needed to be. It
works!

First thing, with a crosstab query you MUST declare your parameters and if
any
other queries are used with the crosstab their parameters must also be
declared.

Open the query in design mode
Select Query: Parameters from the Menu
Fill in the EXACT name of the parameter in column 1
Select the data type of the parameter in column 2

So, Probably all you need is to add the first line to the query that looks
like this

PARAMETERS [Enter RSD Full Name] as Text(255);

SELECT DISTINCTROW tblRepInformation.Region AS Territory
, tblRepInformation.Region2 AS RSD, tblMeeting.StartDate
, tblRepInformation.TerritoryNumber AS [Primary Rep Territory]
, [tblRepInformation]![FirstName] & " " &[tblRepInformation]![LastName] AS
TM
, tblMeetingStatus.MeetingStatus
, tblMeeting.Venue, tblMeetingType.MeetingType
, IIf(IsNull([VenueCity]),"",[VenueCity] & ", " & [VenueState]) AS Location
, tblMeeting.MeetingTitle
, qryAttendeeCount_Crosstab.[Total Of AttendeeCount]
, IIf([Total Of Faculty Count]=1,"Yes","Not Registered") AS [Speaker
Registered]
, qryConfirmedSpeakers.Speaker
,
IIf(IsNull(qryBudgetHonorarium.SumOfAmount),0,qryBudgetHonorarium.SumOfAmount)
AS Hon
, IIf(IsNull(qryBudgetMgmtFee.SumOfAmount),0,qryBudgetMgmtFee.SumOfAmount)
AS Fee
,
IIf(IsNull(qryBudgetOOPExpenses.SumOfAmount),0,qryBudgetOOPExpenses.SumOfAmount)
AS OOP
, ([Hon]+[Fee]+[OOP]) AS Total
FROM (((((((tblMeeting LEFT JOIN tblRepInformation ON tblMeeting.RepID =
tblRepInformation.RepID) LEFT JOIN qryAttendeeCount_Crosstab ON tblMeeting.
MeetingID = qryAttendeeCount_Crosstab.MeetingID) LEFT JOIN tblMeetingStatus
ON tblMeeting.MeetingStatus = tblMeetingStatus.MeetingStatusID) LEFT JOIN
tblMeetingType ON tblMeeting.MeetingType = tblMeetingType.MeetingTypeID)
LEFT
JOIN qryConfirmedSpeakers ON tblMeeting.MeetingID = qryConfirmedSpeakers.
MeetingID) LEFT JOIN qryBudgetHonorarium ON tblMeeting.MeetingID =
qryBudgetHonorarium.MeetingID) LEFT JOIN qryBudgetMgmtFee ON tblMeeting.
MeetingID = qryBudgetMgmtFee.MeetingID) LEFT JOIN qryBudgetOOPExpenses ON
tblMeeting.MeetingID = qryBudgetOOPExpenses.MeetingID

WHERE tblRepInformation.Region2 =[Enter RSD Full Name]

ORDER BY tblRepInformation.Region, tblRepInformation.Region2
, tblMeeting.StartDate, tblRepInformation.LastName
, tblMeeting.StartDate;

First step would be to post the SQL you are attempting to use. (Menu;
View: [quoted text clipped - 13 lines]
border="0"
alt="Photo Sharing and Video Hosting at Photobucket"></a>