Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Only as the user once.
I have a spreadsheet that gets it data from an external source. I have a
query setup that refreshed the data. However, I need input from the user to say what month and year they want to see. This works fine except for 2 things. Firstly when I run the query with the query window open you get the window and it says whatever you called your variable. I make the name something like "What Month", but when in normal use (the query window is not open) the window doesnt display the variable name. How can I get the input window to tell the user what it wants? My second problem is that there is an OR statement in the query. This means that although the variables input by the user stay the same, the user has to put them in twice, once for each OR line in the query. I have given the two lines the same variable names, but the system still asks twice. How can I stop this from happening? I have a new sheet to produce and it will have 4 OR statements meaning the input of the same information 4 times. |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Only as the user once.
Check out "parameter_query" in the VBA help.
NickHK "Keith" wrote in message ... I have a spreadsheet that gets it data from an external source. I have a query setup that refreshed the data. However, I need input from the user to say what month and year they want to see. This works fine except for 2 things. Firstly when I run the query with the query window open you get the window and it says whatever you called your variable. I make the name something like "What Month", but when in normal use (the query window is not open) the window doesn't display the variable name. How can I get the input window to tell the user what it wants? My second problem is that there is an OR statement in the query. This means that although the variables input by the user stay the same, the user has to put them in twice, once for each OR line in the query. I have given the two lines the same variable names, but the system still asks twice. How can I stop this from happening? I have a new sheet to produce and it will have 4 OR statements meaning the input of the same information 4 times. |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Only as the user once.
Thanks for that Nick. I managed to solve the question about the variable
prompt, but can't seem to see anything refering to using the same variable for all lines on an OR statement. "NickHK" wrote: Check out "parameter_query" in the VBA help. NickHK "Keith" wrote in message ... I have a spreadsheet that gets it data from an external source. I have a query setup that refreshed the data. However, I need input from the user to say what month and year they want to see. This works fine except for 2 things. Firstly when I run the query with the query window open you get the window and it says whatever you called your variable. I make the name something like "What Month", but when in normal use (the query window is not open) the window doesn't display the variable name. How can I get the input window to tell the user what it wants? My second problem is that there is an OR statement in the query. This means that although the variables input by the user stay the same, the user has to put them in twice, once for each OR line in the query. I have given the two lines the same variable names, but the system still asks twice. How can I stop this from happening? I have a new sheet to produce and it will have 4 OR statements meaning the input of the same information 4 times. |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
Only as the user once.
Keith,
If you can replace one instance, can't you replace them all ? Not sure what the problem is. NickHK "Keith" wrote in message ... Thanks for that Nick. I managed to solve the question about the variable prompt, but can't seem to see anything refering to using the same variable for all lines on an OR statement. "NickHK" wrote: Check out "parameter_query" in the VBA help. NickHK "Keith" wrote in message ... I have a spreadsheet that gets it data from an external source. I have a query setup that refreshed the data. However, I need input from the user to say what month and year they want to see. This works fine except for 2 things. Firstly when I run the query with the query window open you get the window and it says whatever you called your variable. I make the name something like "What Month", but when in normal use (the query window is not open) the window doesn't display the variable name. How can I get the input window to tell the user what it wants? My second problem is that there is an OR statement in the query. This means that although the variables input by the user stay the same, the user has to put them in twice, once for each OR line in the query. I have given the two lines the same variable names, but the system still asks twice. How can I stop this from happening? I have a new sheet to produce and it will have 4 OR statements meaning the input of the same information 4 times. |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
Only as the user once.
Nick
It would appear that the SQL statement simply puts in a questionmark for all the instances a variable is used, so if the user is asked for say the month and there are three OR statements in the query is asks the question three times. I might be missing something obvious. I'm an Access programmer not Excel, and there are differences to the code. Any help would be great. Keith "NickHK" wrote: Keith, If you can replace one instance, can't you replace them all ? Not sure what the problem is. NickHK "Keith" wrote in message ... Thanks for that Nick. I managed to solve the question about the variable prompt, but can't seem to see anything refering to using the same variable for all lines on an OR statement. "NickHK" wrote: Check out "parameter_query" in the VBA help. NickHK "Keith" wrote in message ... I have a spreadsheet that gets it data from an external source. I have a query setup that refreshed the data. However, I need input from the user to say what month and year they want to see. This works fine except for 2 things. Firstly when I run the query with the query window open you get the window and it says whatever you called your variable. I make the name something like "What Month", but when in normal use (the query window is not open) the window doesn't display the variable name. How can I get the input window to tell the user what it wants? My second problem is that there is an OR statement in the query. This means that although the variables input by the user stay the same, the user has to put them in twice, once for each OR line in the query. I have given the two lines the same variable names, but the system still asks twice. How can I stop this from happening? I have a new sheet to produce and it will have 4 OR statements meaning the input of the same information 4 times. |
#6
Posted to microsoft.public.excel.programming
|
|||
|
|||
Only as the user once.
Keith,
What is your SQL statement ? NickHK "Keith" wrote in message ... Nick It would appear that the SQL statement simply puts in a questionmark for all the instances a variable is used, so if the user is asked for say the month and there are three OR statements in the query is asks the question three times. I might be missing something obvious. I'm an Access programmer not Excel, and there are differences to the code. Any help would be great. Keith "NickHK" wrote: Keith, If you can replace one instance, can't you replace them all ? Not sure what the problem is. NickHK "Keith" wrote in message ... Thanks for that Nick. I managed to solve the question about the variable prompt, but can't seem to see anything refering to using the same variable for all lines on an OR statement. "NickHK" wrote: Check out "parameter_query" in the VBA help. NickHK "Keith" wrote in message ... I have a spreadsheet that gets it data from an external source. I have a query setup that refreshed the data. However, I need input from the user to say what month and year they want to see. This works fine except for 2 things. Firstly when I run the query with the query window open you get the window and it says whatever you called your variable. I make the name something like "What Month", but when in normal use (the query window is not open) the window doesn't display the variable name. How can I get the input window to tell the user what it wants? My second problem is that there is an OR statement in the query. This means that although the variables input by the user stay the same, the user has to put them in twice, once for each OR line in the query. I have given the two lines the same variable names, but the system still asks twice. How can I stop this from happening? I have a new sheet to produce and it will have 4 OR statements meaning the input of the same information 4 times. |
#7
Posted to microsoft.public.excel.programming
|
|||
|
|||
Only as the user once.
The SQL look like this.
SELECT ndmas.ndm_name, cmprpnym.cmpym_pnym, soitem.soi_product, soitem.soi_desc, soitem.soi_itqty, soitem.soi_price, soitem.soi_invval, soitem.soi_uom, soitem.soi_ittype, sohead.soh_orddate FROM informix.cmprpnym cmprpnym, informix.dlcust dlcust, informix.ndmas ndmas, informix.sohead sohead, informix.soitem soitem WHERE sohead.soh_ordref = soitem.soi_ordref AND sohead.soh_account = dlcust.dlcus_customer AND dlcust.dlcus_group = ndmas.ndm_ndcode AND soitem.soi_product = cmprpnym.cmpym_product AND ((month(soh_orddate)=?) AND (year(soh_orddate)=?) AND (soitem.soi_product Like "MSS-M3%") OR (month(soh_orddate)=?) AND (year(soh_orddate)=?) AND (soitem.soi_product Like "MSS-M7%") OR (month(soh_orddate)=?) AND (year(soh_orddate)=?) AND (soitem.soi_product Like "MSS-F%")) Sorry about the length. As you can see Excel uses ? and not the name I entered into Microsoft Query. "NickHK" wrote: Keith, What is your SQL statement ? NickHK "Keith" wrote in message ... Nick It would appear that the SQL statement simply puts in a questionmark for all the instances a variable is used, so if the user is asked for say the month and there are three OR statements in the query is asks the question three times. I might be missing something obvious. I'm an Access programmer not Excel, and there are differences to the code. Any help would be great. Keith "NickHK" wrote: Keith, If you can replace one instance, can't you replace them all ? Not sure what the problem is. NickHK "Keith" wrote in message ... Thanks for that Nick. I managed to solve the question about the variable prompt, but can't seem to see anything refering to using the same variable for all lines on an OR statement. "NickHK" wrote: Check out "parameter_query" in the VBA help. NickHK "Keith" wrote in message ... I have a spreadsheet that gets it data from an external source. I have a query setup that refreshed the data. However, I need input from the user to say what month and year they want to see. This works fine except for 2 things. Firstly when I run the query with the query window open you get the window and it says whatever you called your variable. I make the name something like "What Month", but when in normal use (the query window is not open) the window doesn't display the variable name. How can I get the input window to tell the user what it wants? My second problem is that there is an OR statement in the query. This means that although the variables input by the user stay the same, the user has to put them in twice, once for each OR line in the query. I have given the two lines the same variable names, but the system still asks twice. How can I stop this from happening? I have a new sheet to produce and it will have 4 OR statements meaning the input of the same information 4 times. |
#8
Posted to microsoft.public.excel.programming
|
|||
|
|||
Only as the user once.
Keith,
But if you fill those parameters from cell values instead (and optionally set it update on change of value), then you bypass this problem. Otherwise, yes, you will be asked for each parameter as MSQuery appears not store the parameters separately, rather just requests a value for each "?". The alternative is create the SQL statement yourself and .Refresh in code. Record a macro of adding a .QueryTable and you will get a good idea of the code involved. NickHK "Keith" wrote in message ... The SQL look like this. SELECT ndmas.ndm_name, cmprpnym.cmpym_pnym, soitem.soi_product, soitem.soi_desc, soitem.soi_itqty, soitem.soi_price, soitem.soi_invval, soitem.soi_uom, soitem.soi_ittype, sohead.soh_orddate FROM informix.cmprpnym cmprpnym, informix.dlcust dlcust, informix.ndmas ndmas, informix.sohead sohead, informix.soitem soitem WHERE sohead.soh_ordref = soitem.soi_ordref AND sohead.soh_account = dlcust.dlcus_customer AND dlcust.dlcus_group = ndmas.ndm_ndcode AND soitem.soi_product = cmprpnym.cmpym_product AND ((month(soh_orddate)=?) AND (year(soh_orddate)=?) AND (soitem.soi_product Like "MSS-M3%") OR (month(soh_orddate)=?) AND (year(soh_orddate)=?) AND (soitem.soi_product Like "MSS-M7%") OR (month(soh_orddate)=?) AND (year(soh_orddate)=?) AND (soitem.soi_product Like "MSS-F%")) Sorry about the length. As you can see Excel uses ? and not the name I entered into Microsoft Query. "NickHK" wrote: Keith, What is your SQL statement ? NickHK "Keith" wrote in message ... Nick It would appear that the SQL statement simply puts in a questionmark for all the instances a variable is used, so if the user is asked for say the month and there are three OR statements in the query is asks the question three times. I might be missing something obvious. I'm an Access programmer not Excel, and there are differences to the code. Any help would be great. Keith "NickHK" wrote: Keith, If you can replace one instance, can't you replace them all ? Not sure what the problem is. NickHK "Keith" wrote in message ... Thanks for that Nick. I managed to solve the question about the variable prompt, but can't seem to see anything refering to using the same variable for all lines on an OR statement. "NickHK" wrote: Check out "parameter_query" in the VBA help. NickHK "Keith" wrote in message ... I have a spreadsheet that gets it data from an external source. I have a query setup that refreshed the data. However, I need input from the user to say what month and year they want to see. This works fine except for 2 things. Firstly when I run the query with the query window open you get the window and it says whatever you called your variable. I make the name something like "What Month", but when in normal use (the query window is not open) the window doesn't display the variable name. How can I get the input window to tell the user what it wants? My second problem is that there is an OR statement in the query. This means that although the variables input by the user stay the same, the user has to put them in twice, once for each OR line in the query. I have given the two lines the same variable names, but the system still asks twice. How can I stop this from happening? I have a new sheet to produce and it will have 4 OR statements meaning the input of the same information 4 times. |
#9
Posted to microsoft.public.excel.programming
|
|||
|
|||
Only as the user once.
Nick
Thanks for all your help. Using cell references works well, but it the users don't like it I'll investigate your option of using code. Thanks again Keith "NickHK" wrote: Keith, But if you fill those parameters from cell values instead (and optionally set it update on change of value), then you bypass this problem. Otherwise, yes, you will be asked for each parameter as MSQuery appears not store the parameters separately, rather just requests a value for each "?". The alternative is create the SQL statement yourself and .Refresh in code. Record a macro of adding a .QueryTable and you will get a good idea of the code involved. NickHK "Keith" wrote in message ... The SQL look like this. SELECT ndmas.ndm_name, cmprpnym.cmpym_pnym, soitem.soi_product, soitem.soi_desc, soitem.soi_itqty, soitem.soi_price, soitem.soi_invval, soitem.soi_uom, soitem.soi_ittype, sohead.soh_orddate FROM informix.cmprpnym cmprpnym, informix.dlcust dlcust, informix.ndmas ndmas, informix.sohead sohead, informix.soitem soitem WHERE sohead.soh_ordref = soitem.soi_ordref AND sohead.soh_account = dlcust.dlcus_customer AND dlcust.dlcus_group = ndmas.ndm_ndcode AND soitem.soi_product = cmprpnym.cmpym_product AND ((month(soh_orddate)=?) AND (year(soh_orddate)=?) AND (soitem.soi_product Like "MSS-M3%") OR (month(soh_orddate)=?) AND (year(soh_orddate)=?) AND (soitem.soi_product Like "MSS-M7%") OR (month(soh_orddate)=?) AND (year(soh_orddate)=?) AND (soitem.soi_product Like "MSS-F%")) Sorry about the length. As you can see Excel uses ? and not the name I entered into Microsoft Query. "NickHK" wrote: Keith, What is your SQL statement ? NickHK "Keith" wrote in message ... Nick It would appear that the SQL statement simply puts in a questionmark for all the instances a variable is used, so if the user is asked for say the month and there are three OR statements in the query is asks the question three times. I might be missing something obvious. I'm an Access programmer not Excel, and there are differences to the code. Any help would be great. Keith "NickHK" wrote: Keith, If you can replace one instance, can't you replace them all ? Not sure what the problem is. NickHK "Keith" wrote in message ... Thanks for that Nick. I managed to solve the question about the variable prompt, but can't seem to see anything refering to using the same variable for all lines on an OR statement. "NickHK" wrote: Check out "parameter_query" in the VBA help. NickHK "Keith" wrote in message ... I have a spreadsheet that gets it data from an external source. I have a query setup that refreshed the data. However, I need input from the user to say what month and year they want to see. This works fine except for 2 things. Firstly when I run the query with the query window open you get the window and it says whatever you called your variable. I make the name something like "What Month", but when in normal use (the query window is not open) the window doesn't display the variable name. How can I get the input window to tell the user what it wants? My second problem is that there is an OR statement in the query. This means that although the variables input by the user stay the same, the user has to put them in twice, once for each OR line in the query. I have given the two lines the same variable names, but the system still asks twice. How can I stop this from happening? I have a new sheet to produce and it will have 4 OR statements meaning the input of the same information 4 times. |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Defining user access (via user id) using macros | Excel Discussion (Misc queries) | |||
How do I report to User A vs User B from shared data table? | Excel Discussion (Misc queries) | |||
How to: User Form to assign a user defined range to a macro variab | Excel Programming | |||
User Defined Functions - Help Text - Make it Easy for the User | Excel Programming | |||
How to: Make user click End User License Agreement acceptance | Excel Programming |