Go Back   Access World Forums > Microsoft Access Discussion > Queries

 
Reply
 
Thread Tools Rate Thread Display Modes
Old 07-10-2008, 01:59 AM   #1
praveen_khm
Registered User
 
Join Date: Feb 2006
Posts: 17
Thanks: 0
Thanked 0 Times in 0 Posts
praveen_khm is on a distinguished road
Accept User Input for a Query

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.

Code:
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

praveen_khm is offline   Reply With Quote
Old 07-10-2008, 02:05 AM   #2
maxmangion
AWF VIP
 
maxmangion's Avatar
 
Join Date: Feb 2003
Location: Malta
Posts: 2,805
Thanks: 0
Thanked 1 Time in 1 Post
maxmangion is on a distinguished road
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.
maxmangion is offline   Reply With Quote
Old 07-10-2008, 02:29 AM   #3
praveen_khm
Registered User
 
Join Date: Feb 2006
Posts: 17
Thanks: 0
Thanked 0 Times in 0 Posts
praveen_khm is on a distinguished road
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?

praveen_khm is offline   Reply With Quote
Old 07-10-2008, 02:34 AM   #4
maxmangion
AWF VIP
 
maxmangion's Avatar
 
Join Date: Feb 2003
Location: Malta
Posts: 2,805
Thanks: 0
Thanked 1 Time in 1 Post
maxmangion is on a distinguished road
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.
maxmangion is offline   Reply With Quote
Old 07-10-2008, 02:45 AM   #5
praveen_khm
Registered User
 
Join Date: Feb 2006
Posts: 17
Thanks: 0
Thanked 0 Times in 0 Posts
praveen_khm is on a distinguished road
Thanks a ton. Declaring parameters worked.
praveen_khm is offline   Reply With Quote
Old 07-10-2008, 02:50 AM   #6
maxmangion
AWF VIP
 
maxmangion's Avatar
 
Join Date: Feb 2003
Location: Malta
Posts: 2,805
Thanks: 0
Thanked 1 Time in 1 Post
maxmangion is on a distinguished road
you're welcome

maxmangion is offline   Reply With Quote
Reply

Thread Tools
Display Modes Rate This Thread
Rate This Thread:

Forum Jump

Similar Threads
Thread Thread Starter Forum Replies Last Post
Calculated Query with user "margin" input Gilfdz Queries 2 11-21-2007 04:03 AM
Accepting user input in a text box LuckyPurr General 1 09-10-2006 05:48 AM
update query with criteria from form user input kernelsan Queries 2 04-23-2005 08:49 AM
Change query with user input chancer1967 Macros 1 03-31-2004 05:02 AM
Help with User Input Query blaggers Queries 2 04-21-2002 09:54 PM




All times are GMT -8. The time now is 06:36 PM.


Microsoft Access Help
General
Tables
Queries
Forms
Reports
Macros
Modules & VBA
Theory & Practice
Access FAQs
Code Repository
Sample Databases
Video Tutorials

Featured Forum post


Sponsored Links


Powered by vBulletin®
Copyright ©2000 - 2019, Jelsoft Enterprises Ltd.
(c) copyright 2017 Access World