Accept User Input for a Query

[praveen_khm]

Hi all,

Here is a small question. Maybe easier, but as am a newbie, not able to find out. I have the below query which pulls the information. There is a "where" condition which works perfectly when I input any data like "WHERE ((([Proposal Table].[EBS Project ID]))) = "2435". However, I do not want it to take a default value. When the user runs the query, it should pop up a box asking for the Project ID. Please let me know how to get an input box here.

TRANSFORM Sum([Proposal Resource].Mandays) AS SumOfMandays
SELECT [Proposal Table].[EBS Project ID], [Proposal Resource].[Onsite / Offshore], [Proposal Resource].[Planned / Actual]
FROM ([Proposal Actual_Planned_Crstb] INNER JOIN [Proposal Table] ON [Proposal Actual_Planned_Crstb].[Proposal Number] = [Proposal Table].[Proposal Number]) INNER JOIN [Proposal Resource] ON [Proposal Table].[Proposal Number] = [Proposal Resource].[Proposal Number]
WHERE ((([Proposal Table].[EBS Project ID])))
GROUP BY [Proposal Table].[EBS Project ID], [Proposal Resource].[Onsite / Offshore], [Proposal Resource].[Planned / Actual]
PIVOT " " & Format([Month],"mmmm");

Thanks,
Praveen

 

[maxmangion]

go to the query design view and in the field which you want the user input put the following in the criteria row:

[Enter your value]

Obviously you can alter the text to your needs.

 

[praveen_khm]

Thanks for your input. However, the error message shows as:
"The Microsoft Jet Database Engine does not recongnize '[Enter your value]' as a valid file name or expression".

Also, I tried the same in SQL code. But in no hope. Can you please suggest?

I am using a cross tab. Should that make a problem?

 

[maxmangion]

Hi,

Parameters do not work like that with crosstab queries, because you have to declare them first. Check out this link and scroll down to the section Handle Parameters and you have the exact procedure how to deal with it.

 

[praveen_khm]

Thanks a ton. Declaring parameters worked.

 

[maxmangion]

you're welcome