Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
Parameter question on Query in Excel
I need to set up a parameter field for criteria that is user defined. I need
it to be 'Like' (begin with) and I know that I would need to [Enter the information here] but when I put these together it does not work. Please can someone help as I am fairly new to this and totally self taught - well with a bit of help from this site! I am running the query in Excel using the Wizard. After selecting the data source and columns required I am editing it via the wizard. I set criteria on Visit Date field - is not null so that I dont get any blanks. I set criteria on Cell address to contains C as there could be B cells also in this field. And finally I want to set criteria on Site Name - so that the user can determine which site he wants to see the visit history of. The sheet/source contains only 4 columns, 3 as mentioned above and a column which is for date changed. The sheet is to record changes to a specific column in another sheet so records the data changed, when it was changed and what cell plus the name of the site for that record. I am not sure what other information you need so if I have not given something please just let me know. I am really new to queries and do not know where to go from here. A point that just occurred to me is that the sheet is currently empty. Not sure if that will have an impact or not but thought it best to mention. Thanks |
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
Parameter question on Query in Excel
Sorry - did not think yestersdays post sucessfully loaded.
-- Leanne M (Aussie) (Changed Display name from Leanne) United Kingdom (Don''t ask me what an Aussie is doing living in the UK!) "Leanne" wrote: I need to set up a parameter field for criteria that is user defined. I need it to be 'Like' (begin with) and I know that I would need to [Enter the information here] but when I put these together it does not work. Please can someone help as I am fairly new to this and totally self taught - well with a bit of help from this site! I am running the query in Excel using the Wizard. After selecting the data source and columns required I am editing it via the wizard. I set criteria on Visit Date field - is not null so that I dont get any blanks. I set criteria on Cell address to contains C as there could be B cells also in this field. And finally I want to set criteria on Site Name - so that the user can determine which site he wants to see the visit history of. The sheet/source contains only 4 columns, 3 as mentioned above and a column which is for date changed. The sheet is to record changes to a specific column in another sheet so records the data changed, when it was changed and what cell plus the name of the site for that record. I am not sure what other information you need so if I have not given something please just let me know. I am really new to queries and do not know where to go from here. A point that just occurred to me is that the sheet is currently empty. Not sure if that will have an impact or not but thought it best to mention. Thanks |
#3
Posted to microsoft.public.excel.misc
|
|||
|
|||
Parameter question on Query in Excel
just in case,
http://www.dailydoseofexcel.com/arch...or-in-msquery/ regards FSt1 "Leanne M (Aussie)" wrote: Sorry - did not think yestersdays post sucessfully loaded. -- Leanne M (Aussie) (Changed Display name from Leanne) United Kingdom (Don''t ask me what an Aussie is doing living in the UK!) "Leanne" wrote: I need to set up a parameter field for criteria that is user defined. I need it to be 'Like' (begin with) and I know that I would need to [Enter the information here] but when I put these together it does not work. Please can someone help as I am fairly new to this and totally self taught - well with a bit of help from this site! I am running the query in Excel using the Wizard. After selecting the data source and columns required I am editing it via the wizard. I set criteria on Visit Date field - is not null so that I dont get any blanks. I set criteria on Cell address to contains C as there could be B cells also in this field. And finally I want to set criteria on Site Name - so that the user can determine which site he wants to see the visit history of. The sheet/source contains only 4 columns, 3 as mentioned above and a column which is for date changed. The sheet is to record changes to a specific column in another sheet so records the data changed, when it was changed and what cell plus the name of the site for that record. I am not sure what other information you need so if I have not given something please just let me know. I am really new to queries and do not know where to go from here. A point that just occurred to me is that the sheet is currently empty. Not sure if that will have an impact or not but thought it best to mention. Thanks |
#4
Posted to microsoft.public.excel.misc
|
|||
|
|||
Parameter question on Query in Excel
Thanks, I did see your reply to yesterdays post. I got an error message
when I put that post on so thought it did not load hence the reposting today - should have checked first hey. I have entered what you suggested and now I do not get an error message - but I do not get the results I was expecting. I will have a look at this site but could you just check for me that I have entered the rest correctly? I have used the Wizzard (what would I do with out them!) and set filter on Visit Date is not null, Cell Address contains C (and it is displayed as Like '%C%') - this is because it is actually a cell address ie $c$2 and could contain cells from column B also but this report is for changes to c only. Then i went into wizzard to edit and entered the Site Name criteria and returned data to excel. It works ok but I do not get any results and I know there are as I have checked the sheet. Thanks -- Leanne M (Aussie) (Changed Display name from Leanne) United Kingdom (Don''t ask me what an Aussie is doing living in the UK!) "FSt1" wrote: just in case, http://www.dailydoseofexcel.com/arch...or-in-msquery/ regards FSt1 "Leanne M (Aussie)" wrote: Sorry - did not think yestersdays post sucessfully loaded. -- Leanne M (Aussie) (Changed Display name from Leanne) United Kingdom (Don''t ask me what an Aussie is doing living in the UK!) "Leanne" wrote: I need to set up a parameter field for criteria that is user defined. I need it to be 'Like' (begin with) and I know that I would need to [Enter the information here] but when I put these together it does not work. Please can someone help as I am fairly new to this and totally self taught - well with a bit of help from this site! I am running the query in Excel using the Wizard. After selecting the data source and columns required I am editing it via the wizard. I set criteria on Visit Date field - is not null so that I dont get any blanks. I set criteria on Cell address to contains C as there could be B cells also in this field. And finally I want to set criteria on Site Name - so that the user can determine which site he wants to see the visit history of. The sheet/source contains only 4 columns, 3 as mentioned above and a column which is for date changed. The sheet is to record changes to a specific column in another sheet so records the data changed, when it was changed and what cell plus the name of the site for that record. I am not sure what other information you need so if I have not given something please just let me know. I am really new to queries and do not know where to go from here. A point that just occurred to me is that the sheet is currently empty. Not sure if that will have an impact or not but thought it best to mention. Thanks |
#5
Posted to microsoft.public.excel.misc
|
|||
|
|||
Parameter question on Query in Excel
hi
can you give examples of your data and perhaps you expected results that you didn't get? Regards FSt1 "Leanne M (Aussie)" wrote: Thanks, I did see your reply to yesterdays post. I got an error message when I put that post on so thought it did not load hence the reposting today - should have checked first hey. I have entered what you suggested and now I do not get an error message - but I do not get the results I was expecting. I will have a look at this site but could you just check for me that I have entered the rest correctly? I have used the Wizzard (what would I do with out them!) and set filter on Visit Date is not null, Cell Address contains C (and it is displayed as Like '%C%') - this is because it is actually a cell address ie $c$2 and could contain cells from column B also but this report is for changes to c only. Then i went into wizzard to edit and entered the Site Name criteria and returned data to excel. It works ok but I do not get any results and I know there are as I have checked the sheet. Thanks -- Leanne M (Aussie) (Changed Display name from Leanne) United Kingdom (Don''t ask me what an Aussie is doing living in the UK!) "FSt1" wrote: just in case, http://www.dailydoseofexcel.com/arch...or-in-msquery/ regards FSt1 "Leanne M (Aussie)" wrote: Sorry - did not think yestersdays post sucessfully loaded. -- Leanne M (Aussie) (Changed Display name from Leanne) United Kingdom (Don''t ask me what an Aussie is doing living in the UK!) "Leanne" wrote: I need to set up a parameter field for criteria that is user defined. I need it to be 'Like' (begin with) and I know that I would need to [Enter the information here] but when I put these together it does not work. Please can someone help as I am fairly new to this and totally self taught - well with a bit of help from this site! I am running the query in Excel using the Wizard. After selecting the data source and columns required I am editing it via the wizard. I set criteria on Visit Date field - is not null so that I dont get any blanks. I set criteria on Cell address to contains C as there could be B cells also in this field. And finally I want to set criteria on Site Name - so that the user can determine which site he wants to see the visit history of. The sheet/source contains only 4 columns, 3 as mentioned above and a column which is for date changed. The sheet is to record changes to a specific column in another sheet so records the data changed, when it was changed and what cell plus the name of the site for that record. I am not sure what other information you need so if I have not given something please just let me know. I am really new to queries and do not know where to go from here. A point that just occurred to me is that the sheet is currently empty. Not sure if that will have an impact or not but thought it best to mention. Thanks |
#6
Posted to microsoft.public.excel.misc
|
|||
|
|||
Parameter question on Query in Excel
Data in the sheet the report is pulling from is as follows
A B C D 1 01/05/08 22/04/08 $C$2 Marchwood ERF 2 26/04/08 24/04/08 $B$3 Porstmouth ERF 3 01/05/08 24/04/08 $C$3 Portsmouth ERF Column a = Visit Date (data that was changed in another sheet) Column B = Date that the change was made Column c = Cell address of what was changed Column D = Site name for that row - Row 2 is the record for Marchwood, Row 3 is the record for Portsmouth and so on. The cell address of what was changed not only tells me the record that was changed but if it is column c that was changed it is a visit date and if it was column b that was changed it is an invoice date that was changed. I ran Visit Date a as is not null, Cell Address as contains C and Site Name as the user defined entry and entered Portsmouth. Hence I expected to see 01/05/08 24/04/08 $C$3 Portsmouth ERF - though in truth in the report I just need columns A and D to be visible to the user. This report is to show a history of visits for whatever site the user requests. I will then make the same report for invoice history. Hope this explains enough but please let me know if you have more questions. Anything I can do to help you help me! Thanks -- Leanne M (Aussie) (Changed Display name from Leanne) United Kingdom (Don''t ask me what an Aussie is doing living in the UK!) "FSt1" wrote: hi can you give examples of your data and perhaps you expected results that you didn't get? Regards FSt1 "Leanne M (Aussie)" wrote: Thanks, I did see your reply to yesterdays post. I got an error message when I put that post on so thought it did not load hence the reposting today - should have checked first hey. I have entered what you suggested and now I do not get an error message - but I do not get the results I was expecting. I will have a look at this site but could you just check for me that I have entered the rest correctly? I have used the Wizzard (what would I do with out them!) and set filter on Visit Date is not null, Cell Address contains C (and it is displayed as Like '%C%') - this is because it is actually a cell address ie $c$2 and could contain cells from column B also but this report is for changes to c only. Then i went into wizzard to edit and entered the Site Name criteria and returned data to excel. It works ok but I do not get any results and I know there are as I have checked the sheet. Thanks -- Leanne M (Aussie) (Changed Display name from Leanne) United Kingdom (Don''t ask me what an Aussie is doing living in the UK!) "FSt1" wrote: just in case, http://www.dailydoseofexcel.com/arch...or-in-msquery/ regards FSt1 "Leanne M (Aussie)" wrote: Sorry - did not think yestersdays post sucessfully loaded. -- Leanne M (Aussie) (Changed Display name from Leanne) United Kingdom (Don''t ask me what an Aussie is doing living in the UK!) "Leanne" wrote: I need to set up a parameter field for criteria that is user defined. I need it to be 'Like' (begin with) and I know that I would need to [Enter the information here] but when I put these together it does not work. Please can someone help as I am fairly new to this and totally self taught - well with a bit of help from this site! I am running the query in Excel using the Wizard. After selecting the data source and columns required I am editing it via the wizard. I set criteria on Visit Date field - is not null so that I dont get any blanks. I set criteria on Cell address to contains C as there could be B cells also in this field. And finally I want to set criteria on Site Name - so that the user can determine which site he wants to see the visit history of. The sheet/source contains only 4 columns, 3 as mentioned above and a column which is for date changed. The sheet is to record changes to a specific column in another sheet so records the data changed, when it was changed and what cell plus the name of the site for that record. I am not sure what other information you need so if I have not given something please just let me know. I am really new to queries and do not know where to go from here. A point that just occurred to me is that the sheet is currently empty. Not sure if that will have an impact or not but thought it best to mention. Thanks |
#7
Posted to microsoft.public.excel.misc
|
|||
|
|||
Parameter question on Query in Excel
hi leanne,
i just did a test on your data. i copied your data and pasted it seveal time to create a dummy table. i created an MSQ and for critera (in the wizard) i use.....Like %C%.... the MSQ returned all C location. in the MSQ criteria pane , it displayed as .....like '%C%' i also use the Contains keyword. .....contains C... and i got all C's again. you might try that. so i am now unsure as to what your are doing wrong unless it just a syntax problem. regards FSt1 "Leanne M (Aussie)" wrote: Data in the sheet the report is pulling from is as follows A B C D 1 01/05/08 22/04/08 $C$2 Marchwood ERF 2 26/04/08 24/04/08 $B$3 Porstmouth ERF 3 01/05/08 24/04/08 $C$3 Portsmouth ERF Column a = Visit Date (data that was changed in another sheet) Column B = Date that the change was made Column c = Cell address of what was changed Column D = Site name for that row - Row 2 is the record for Marchwood, Row 3 is the record for Portsmouth and so on. The cell address of what was changed not only tells me the record that was changed but if it is column c that was changed it is a visit date and if it was column b that was changed it is an invoice date that was changed. I ran Visit Date a as is not null, Cell Address as contains C and Site Name as the user defined entry and entered Portsmouth. Hence I expected to see 01/05/08 24/04/08 $C$3 Portsmouth ERF - though in truth in the report I just need columns A and D to be visible to the user. This report is to show a history of visits for whatever site the user requests. I will then make the same report for invoice history. Hope this explains enough but please let me know if you have more questions. Anything I can do to help you help me! Thanks -- Leanne M (Aussie) (Changed Display name from Leanne) United Kingdom (Don''t ask me what an Aussie is doing living in the UK!) "FSt1" wrote: hi can you give examples of your data and perhaps you expected results that you didn't get? Regards FSt1 "Leanne M (Aussie)" wrote: Thanks, I did see your reply to yesterdays post. I got an error message when I put that post on so thought it did not load hence the reposting today - should have checked first hey. I have entered what you suggested and now I do not get an error message - but I do not get the results I was expecting. I will have a look at this site but could you just check for me that I have entered the rest correctly? I have used the Wizzard (what would I do with out them!) and set filter on Visit Date is not null, Cell Address contains C (and it is displayed as Like '%C%') - this is because it is actually a cell address ie $c$2 and could contain cells from column B also but this report is for changes to c only. Then i went into wizzard to edit and entered the Site Name criteria and returned data to excel. It works ok but I do not get any results and I know there are as I have checked the sheet. Thanks -- Leanne M (Aussie) (Changed Display name from Leanne) United Kingdom (Don''t ask me what an Aussie is doing living in the UK!) "FSt1" wrote: just in case, http://www.dailydoseofexcel.com/arch...or-in-msquery/ regards FSt1 "Leanne M (Aussie)" wrote: Sorry - did not think yestersdays post sucessfully loaded. -- Leanne M (Aussie) (Changed Display name from Leanne) United Kingdom (Don''t ask me what an Aussie is doing living in the UK!) "Leanne" wrote: I need to set up a parameter field for criteria that is user defined. I need it to be 'Like' (begin with) and I know that I would need to [Enter the information here] but when I put these together it does not work. Please can someone help as I am fairly new to this and totally self taught - well with a bit of help from this site! I am running the query in Excel using the Wizard. After selecting the data source and columns required I am editing it via the wizard. I set criteria on Visit Date field - is not null so that I dont get any blanks. I set criteria on Cell address to contains C as there could be B cells also in this field. And finally I want to set criteria on Site Name - so that the user can determine which site he wants to see the visit history of. The sheet/source contains only 4 columns, 3 as mentioned above and a column which is for date changed. The sheet is to record changes to a specific column in another sheet so records the data changed, when it was changed and what cell plus the name of the site for that record. I am not sure what other information you need so if I have not given something please just let me know. I am really new to queries and do not know where to go from here. A point that just occurred to me is that the sheet is currently empty. Not sure if that will have an impact or not but thought it best to mention. Thanks |
#8
Posted to microsoft.public.excel.misc
|
|||
|
|||
Parameter question on Query in Excel
Hi,
Sorry for the delay - I guess the site has been playing up for me as I could not reply or post until now! I have tried this again as I noticed that you did not mention anything about Visit Date = is not null. This is what is causing me not to see the results I want. I have changed it to Visit date = Like '%' and I get what I want. Thank you for your help with it. Do you know of any good sources for notes on creating queries in Excel? I must be doing something wrong if I can not get these to run on other computers even after declaring the data source. -- Leanne M (Aussie) (Changed Display name from Leanne) United Kingdom (Don''t ask me what an Aussie is doing living in the UK!) "FSt1" wrote: hi leanne, i just did a test on your data. i copied your data and pasted it seveal time to create a dummy table. i created an MSQ and for critera (in the wizard) i use.....Like %C%.... the MSQ returned all C location. in the MSQ criteria pane , it displayed as .....like '%C%' i also use the Contains keyword. .....contains C... and i got all C's again. you might try that. so i am now unsure as to what your are doing wrong unless it just a syntax problem. regards FSt1 "Leanne M (Aussie)" wrote: Data in the sheet the report is pulling from is as follows A B C D 1 01/05/08 22/04/08 $C$2 Marchwood ERF 2 26/04/08 24/04/08 $B$3 Porstmouth ERF 3 01/05/08 24/04/08 $C$3 Portsmouth ERF Column a = Visit Date (data that was changed in another sheet) Column B = Date that the change was made Column c = Cell address of what was changed Column D = Site name for that row - Row 2 is the record for Marchwood, Row 3 is the record for Portsmouth and so on. The cell address of what was changed not only tells me the record that was changed but if it is column c that was changed it is a visit date and if it was column b that was changed it is an invoice date that was changed. I ran Visit Date a as is not null, Cell Address as contains C and Site Name as the user defined entry and entered Portsmouth. Hence I expected to see 01/05/08 24/04/08 $C$3 Portsmouth ERF - though in truth in the report I just need columns A and D to be visible to the user. This report is to show a history of visits for whatever site the user requests. I will then make the same report for invoice history. Hope this explains enough but please let me know if you have more questions. Anything I can do to help you help me! Thanks -- Leanne M (Aussie) (Changed Display name from Leanne) United Kingdom (Don''t ask me what an Aussie is doing living in the UK!) "FSt1" wrote: hi can you give examples of your data and perhaps you expected results that you didn't get? Regards FSt1 "Leanne M (Aussie)" wrote: Thanks, I did see your reply to yesterdays post. I got an error message when I put that post on so thought it did not load hence the reposting today - should have checked first hey. I have entered what you suggested and now I do not get an error message - but I do not get the results I was expecting. I will have a look at this site but could you just check for me that I have entered the rest correctly? I have used the Wizzard (what would I do with out them!) and set filter on Visit Date is not null, Cell Address contains C (and it is displayed as Like '%C%') - this is because it is actually a cell address ie $c$2 and could contain cells from column B also but this report is for changes to c only. Then i went into wizzard to edit and entered the Site Name criteria and returned data to excel. It works ok but I do not get any results and I know there are as I have checked the sheet. Thanks -- Leanne M (Aussie) (Changed Display name from Leanne) United Kingdom (Don''t ask me what an Aussie is doing living in the UK!) "FSt1" wrote: just in case, http://www.dailydoseofexcel.com/arch...or-in-msquery/ regards FSt1 "Leanne M (Aussie)" wrote: Sorry - did not think yestersdays post sucessfully loaded. -- Leanne M (Aussie) (Changed Display name from Leanne) United Kingdom (Don''t ask me what an Aussie is doing living in the UK!) "Leanne" wrote: I need to set up a parameter field for criteria that is user defined. I need it to be 'Like' (begin with) and I know that I would need to [Enter the information here] but when I put these together it does not work. Please can someone help as I am fairly new to this and totally self taught - well with a bit of help from this site! I am running the query in Excel using the Wizard. After selecting the data source and columns required I am editing it via the wizard. I set criteria on Visit Date field - is not null so that I dont get any blanks. I set criteria on Cell address to contains C as there could be B cells also in this field. And finally I want to set criteria on Site Name - so that the user can determine which site he wants to see the visit history of. The sheet/source contains only 4 columns, 3 as mentioned above and a column which is for date changed. The sheet is to record changes to a specific column in another sheet so records the data changed, when it was changed and what cell plus the name of the site for that record. I am not sure what other information you need so if I have not given something please just let me know. I am really new to queries and do not know where to go from here. A point that just occurred to me is that the sheet is currently empty. Not sure if that will have an impact or not but thought it best to mention. Thanks |
#9
Posted to microsoft.public.excel.misc
|
|||
|
|||
Parameter question on Query in Excel
AAAAARRRRRRGGGGGGGG
Now I can not get it to work for Invoice History (Cell address with B in) I have done exactly the same thing but can only get responses for one site and not others. I even tried editing the Visit report and changing the c to a b and then saving it as Invoice - just to make sure I was not doing anything wrong. It still does not work. I really need my head examining for agreeing to do this - I should have admited it was beyond my capacity. -- Leanne M (Aussie) (Changed Display name from Leanne) United Kingdom (Don''t ask me what an Aussie is doing living in the UK!) "Leanne" wrote: I need to set up a parameter field for criteria that is user defined. I need it to be 'Like' (begin with) and I know that I would need to [Enter the information here] but when I put these together it does not work. Please can someone help as I am fairly new to this and totally self taught - well with a bit of help from this site! I am running the query in Excel using the Wizard. After selecting the data source and columns required I am editing it via the wizard. I set criteria on Visit Date field - is not null so that I dont get any blanks. I set criteria on Cell address to contains C as there could be B cells also in this field. And finally I want to set criteria on Site Name - so that the user can determine which site he wants to see the visit history of. The sheet/source contains only 4 columns, 3 as mentioned above and a column which is for date changed. The sheet is to record changes to a specific column in another sheet so records the data changed, when it was changed and what cell plus the name of the site for that record. I am not sure what other information you need so if I have not given something please just let me know. I am really new to queries and do not know where to go from here. A point that just occurred to me is that the sheet is currently empty. Not sure if that will have an impact or not but thought it best to mention. Thanks |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Excel Query Parameter | Excel Discussion (Misc queries) | |||
Parameter query question | Excel Discussion (Misc queries) | |||
How to choose if I use a parameter or not in a parameter query | Excel Discussion (Misc queries) | |||
How to put a parameter into an Excel Query | Excel Discussion (Misc queries) | |||
How to use a Access Query that as a parameter into Excel database query | Excel Discussion (Misc queries) |