Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Database Querry
I need to have Excel run a database querry, prompting the user for
information. The SQL that would be used is something a lot like this: SELECT * FROM table18 WHERE (salesdate BETWEEN N'20050315' AND N'20050331') I need to prompt the user for the year and month, preferrably with one prompt, asking for the data in YYYYMM format, and then append the day number to what is entered. How would I accomplish this? Thank you. Joshua |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Database Querry
First, set up cells to contain the dates that will be selected (hide them if
desired). User input can be either directly on the worksheet, some InputBoxes, or a UserForm - just store/copy the results in the cells you set aside. Now, go to data menu, Get External Data, New Database Query. It should prompt you for the data source and I assume/hope your database will be one listed there. Choose the database driver, the file path (if needed) and the userID/password (if needed) to connect to the database. MSQuery will then start and you can set up the query in there, either graphically a la Access or type in the SQL directly - see MSQuery help if you need it. For your date range, use parameters (which are specified by using [] in the criteria grid; e.g. under SALESDATE you would put the following: Between [Date1] and [Date2]). When done return the data to Excel (via File menu or toolbar). It will prompt for Date1 and Date2; for now you can put anything in there. When you get back to Excel the data will appear in a list. Right-click on the list and choose Properties, then Parameters. You will see your Date1 and Date2 parameters - set them to "Get the value from the following cell:" and point them to the cells you designated on your sheet for the date values. Now you just need code to prompt the user for the new dates and refresh the query (which would be something like this: Sheets("Sheet1").Querytables(1).Refresh) This is a very brief outline, but hope it helps. Look in Excel help or MSDN library for more info if needed. "Joshua Campbell" wrote: I need to have Excel run a database querry, prompting the user for information. The SQL that would be used is something a lot like this: SELECT * FROM table18 WHERE (salesdate BETWEEN N'20050315' AND N'20050331') I need to prompt the user for the year and month, preferrably with one prompt, asking for the data in YYYYMM format, and then append the day number to what is entered. How would I accomplish this? Thank you. Joshua |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Database Querry
Thanks for the great reply. But, I guess I should have been a bit more
clear as to where I was running into problems. When I put in the parameters, I always get an error. It is probably how I am doing it. See the example of the criteria for invdate below: Between '20050315' And [DateRange] & '31' I am trying to concatenate the returned value from DateRange and 31. How would I do this? Thanks. Joshua "K Dales" wrote in message ... First, set up cells to contain the dates that will be selected (hide them if desired). User input can be either directly on the worksheet, some InputBoxes, or a UserForm - just store/copy the results in the cells you set aside. Now, go to data menu, Get External Data, New Database Query. It should prompt you for the data source and I assume/hope your database will be one listed there. Choose the database driver, the file path (if needed) and the userID/password (if needed) to connect to the database. MSQuery will then start and you can set up the query in there, either graphically a la Access or type in the SQL directly - see MSQuery help if you need it. For your date range, use parameters (which are specified by using [] in the criteria grid; e.g. under SALESDATE you would put the following: Between [Date1] and [Date2]). When done return the data to Excel (via File menu or toolbar). It will prompt for Date1 and Date2; for now you can put anything in there. When you get back to Excel the data will appear in a list. Right-click on the list and choose Properties, then Parameters. You will see your Date1 and Date2 parameters - set them to "Get the value from the following cell:" and point them to the cells you designated on your sheet for the date values. Now you just need code to prompt the user for the new dates and refresh the query (which would be something like this: Sheets("Sheet1").Querytables(1).Refresh) This is a very brief outline, but hope it helps. Look in Excel help or MSDN library for more info if needed. "Joshua Campbell" wrote: I need to have Excel run a database querry, prompting the user for information. The SQL that would be used is something a lot like this: SELECT * FROM table18 WHERE (salesdate BETWEEN N'20050315' AND N'20050331') I need to prompt the user for the year and month, preferrably with one prompt, asking for the data in YYYYMM format, and then append the day number to what is entered. How would I accomplish this? Thank you. Joshua |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
Database Querry
OK, I see now. First, you will need to format DateRange (which is a single
date???) into yyyymmdd form and then concatenate the 31, if I understand (or, maybe just yyyymm and then the 31 on the end?). So in the cell that your parameter points to, try a formula like this: =TEXT(DateRangeCell,"yyyymmdd")&"31" The parameter will then come into MSQuery as a text value, it is up to you to take it from there and pass it to your SQL query in the proper format. Hope I am now on the right track with you... K Dales "Joshua Campbell" wrote: Thanks for the great reply. But, I guess I should have been a bit more clear as to where I was running into problems. When I put in the parameters, I always get an error. It is probably how I am doing it. See the example of the criteria for invdate below: Between '20050315' And [DateRange] & '31' I am trying to concatenate the returned value from DateRange and 31. How would I do this? Thanks. Joshua "K Dales" wrote in message ... First, set up cells to contain the dates that will be selected (hide them if desired). User input can be either directly on the worksheet, some InputBoxes, or a UserForm - just store/copy the results in the cells you set aside. Now, go to data menu, Get External Data, New Database Query. It should prompt you for the data source and I assume/hope your database will be one listed there. Choose the database driver, the file path (if needed) and the userID/password (if needed) to connect to the database. MSQuery will then start and you can set up the query in there, either graphically a la Access or type in the SQL directly - see MSQuery help if you need it. For your date range, use parameters (which are specified by using [] in the criteria grid; e.g. under SALESDATE you would put the following: Between [Date1] and [Date2]). When done return the data to Excel (via File menu or toolbar). It will prompt for Date1 and Date2; for now you can put anything in there. When you get back to Excel the data will appear in a list. Right-click on the list and choose Properties, then Parameters. You will see your Date1 and Date2 parameters - set them to "Get the value from the following cell:" and point them to the cells you designated on your sheet for the date values. Now you just need code to prompt the user for the new dates and refresh the query (which would be something like this: Sheets("Sheet1").Querytables(1).Refresh) This is a very brief outline, but hope it helps. Look in Excel help or MSDN library for more info if needed. "Joshua Campbell" wrote: I need to have Excel run a database querry, prompting the user for information. The SQL that would be used is something a lot like this: SELECT * FROM table18 WHERE (salesdate BETWEEN N'20050315' AND N'20050331') I need to prompt the user for the year and month, preferrably with one prompt, asking for the data in YYYYMM format, and then append the day number to what is entered. How would I accomplish this? Thank you. Joshua |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
Database Querry
Just read the original post more carefully, "yyyymm" format is what you want.
"Joshua Campbell" wrote: Thanks for the great reply. But, I guess I should have been a bit more clear as to where I was running into problems. When I put in the parameters, I always get an error. It is probably how I am doing it. See the example of the criteria for invdate below: Between '20050315' And [DateRange] & '31' I am trying to concatenate the returned value from DateRange and 31. How would I do this? Thanks. Joshua "K Dales" wrote in message ... First, set up cells to contain the dates that will be selected (hide them if desired). User input can be either directly on the worksheet, some InputBoxes, or a UserForm - just store/copy the results in the cells you set aside. Now, go to data menu, Get External Data, New Database Query. It should prompt you for the data source and I assume/hope your database will be one listed there. Choose the database driver, the file path (if needed) and the userID/password (if needed) to connect to the database. MSQuery will then start and you can set up the query in there, either graphically a la Access or type in the SQL directly - see MSQuery help if you need it. For your date range, use parameters (which are specified by using [] in the criteria grid; e.g. under SALESDATE you would put the following: Between [Date1] and [Date2]). When done return the data to Excel (via File menu or toolbar). It will prompt for Date1 and Date2; for now you can put anything in there. When you get back to Excel the data will appear in a list. Right-click on the list and choose Properties, then Parameters. You will see your Date1 and Date2 parameters - set them to "Get the value from the following cell:" and point them to the cells you designated on your sheet for the date values. Now you just need code to prompt the user for the new dates and refresh the query (which would be something like this: Sheets("Sheet1").Querytables(1).Refresh) This is a very brief outline, but hope it helps. Look in Excel help or MSDN library for more info if needed. "Joshua Campbell" wrote: I need to have Excel run a database querry, prompting the user for information. The SQL that would be used is something a lot like this: SELECT * FROM table18 WHERE (salesdate BETWEEN N'20050315' AND N'20050331') I need to prompt the user for the year and month, preferrably with one prompt, asking for the data in YYYYMM format, and then append the day number to what is entered. How would I accomplish this? Thank you. Joshua |
#6
Posted to microsoft.public.excel.programming
|
|||
|
|||
Database Querry
Okay, I've got one cell where the user enters in the year in yyyy format. I
have another cell where the user enters the month in mm format. In all actuality, the first cell is a cell with "0000" text formatting, and the second is in "00" text formatting. I then have another cell to calculate the start date. The formula for it is =DATEVALUE(B2 & "/15/"&B1) I then have the cell in yyyymmdd text formatting. The formula to calculate the end date is as follows =DATE(B1,(B2)+1,0) I also have this in yyyymmdd text formatting. I created the database query. Using start and end dates that I populated, the query returns data as it should. I then changed the parameters to pull from the above two formulas. However, the result comes back empty. Any ideas? Thanks. Joshua "K Dales" wrote in message ... Just read the original post more carefully, "yyyymm" format is what you want. "Joshua Campbell" wrote: Thanks for the great reply. But, I guess I should have been a bit more clear as to where I was running into problems. When I put in the parameters, I always get an error. It is probably how I am doing it. See the example of the criteria for invdate below: Between '20050315' And [DateRange] & '31' I am trying to concatenate the returned value from DateRange and 31. How would I do this? Thanks. Joshua "K Dales" wrote in message ... First, set up cells to contain the dates that will be selected (hide them if desired). User input can be either directly on the worksheet, some InputBoxes, or a UserForm - just store/copy the results in the cells you set aside. Now, go to data menu, Get External Data, New Database Query. It should prompt you for the data source and I assume/hope your database will be one listed there. Choose the database driver, the file path (if needed) and the userID/password (if needed) to connect to the database. MSQuery will then start and you can set up the query in there, either graphically a la Access or type in the SQL directly - see MSQuery help if you need it. For your date range, use parameters (which are specified by using [] in the criteria grid; e.g. under SALESDATE you would put the following: Between [Date1] and [Date2]). When done return the data to Excel (via File menu or toolbar). It will prompt for Date1 and Date2; for now you can put anything in there. When you get back to Excel the data will appear in a list. Right-click on the list and choose Properties, then Parameters. You will see your Date1 and Date2 parameters - set them to "Get the value from the following cell:" and point them to the cells you designated on your sheet for the date values. Now you just need code to prompt the user for the new dates and refresh the query (which would be something like this: Sheets("Sheet1").Querytables(1).Refresh) This is a very brief outline, but hope it helps. Look in Excel help or MSDN library for more info if needed. "Joshua Campbell" wrote: I need to have Excel run a database querry, prompting the user for information. The SQL that would be used is something a lot like this: SELECT * FROM table18 WHERE (salesdate BETWEEN N'20050315' AND N'20050331') I need to prompt the user for the year and month, preferrably with one prompt, asking for the data in YYYYMM format, and then append the day number to what is entered. How would I accomplish this? Thank you. Joshua |
#7
Posted to microsoft.public.excel.programming
|
|||
|
|||
Database Querry
When you use the DateValue or Date functions, the result is a date variable,
even if you use formatting to change how it appears on the sheet. So my guess is that MSQuery is pulling it in as a date number (e.g. today is 38454) and that is why you are not getting the results you want. You will need to force the values to go to MSQuery as text in the format you want, you can modify your formulas as follows: =TEXT(DATEVALUE(B2 & "/15/"&B1),"yyyymmdd") and =TEXT(DATE(B1,(B2)+1,0),"yyyymmdd") Note that this will create an actual text value for the cell - and the downside is that if you have other formulas that refer to these cells, those formulas will no longer recognize them as dates! If that is the case, you will probably need to keep the formulas the way you have them, but make two new cells the StartDate and EndDate for your query parameters, and in those cells bring in the dates and convert them to text like so: for StartDate (assuming your existing formula is in cell A1) =TEXT(A1,"yyyymmdd") For EndDate (assuming your existing formula for this is in B1) = TEXT(B1,"yyyymmdd") I hope this finally solves your troubles! K Dales "Joshua Campbell" wrote: Okay, I've got one cell where the user enters in the year in yyyy format. I have another cell where the user enters the month in mm format. In all actuality, the first cell is a cell with "0000" text formatting, and the second is in "00" text formatting. I then have another cell to calculate the start date. The formula for it is =DATEVALUE(B2 & "/15/"&B1) I then have the cell in yyyymmdd text formatting. The formula to calculate the end date is as follows =DATE(B1,(B2)+1,0) I also have this in yyyymmdd text formatting. I created the database query. Using start and end dates that I populated, the query returns data as it should. I then changed the parameters to pull from the above two formulas. However, the result comes back empty. Any ideas? Thanks. Joshua "K Dales" wrote in message ... Just read the original post more carefully, "yyyymm" format is what you want. "Joshua Campbell" wrote: Thanks for the great reply. But, I guess I should have been a bit more clear as to where I was running into problems. When I put in the parameters, I always get an error. It is probably how I am doing it. See the example of the criteria for invdate below: Between '20050315' And [DateRange] & '31' I am trying to concatenate the returned value from DateRange and 31. How would I do this? Thanks. Joshua "K Dales" wrote in message ... First, set up cells to contain the dates that will be selected (hide them if desired). User input can be either directly on the worksheet, some InputBoxes, or a UserForm - just store/copy the results in the cells you set aside. Now, go to data menu, Get External Data, New Database Query. It should prompt you for the data source and I assume/hope your database will be one listed there. Choose the database driver, the file path (if needed) and the userID/password (if needed) to connect to the database. MSQuery will then start and you can set up the query in there, either graphically a la Access or type in the SQL directly - see MSQuery help if you need it. For your date range, use parameters (which are specified by using [] in the criteria grid; e.g. under SALESDATE you would put the following: Between [Date1] and [Date2]). When done return the data to Excel (via File menu or toolbar). It will prompt for Date1 and Date2; for now you can put anything in there. When you get back to Excel the data will appear in a list. Right-click on the list and choose Properties, then Parameters. You will see your Date1 and Date2 parameters - set them to "Get the value from the following cell:" and point them to the cells you designated on your sheet for the date values. Now you just need code to prompt the user for the new dates and refresh the query (which would be something like this: Sheets("Sheet1").Querytables(1).Refresh) This is a very brief outline, but hope it helps. Look in Excel help or MSDN library for more info if needed. "Joshua Campbell" wrote: I need to have Excel run a database querry, prompting the user for information. The SQL that would be used is something a lot like this: SELECT * FROM table18 WHERE (salesdate BETWEEN N'20050315' AND N'20050331') I need to prompt the user for the year and month, preferrably with one prompt, asking for the data in YYYYMM format, and then append the day number to what is entered. How would I accomplish this? Thank you. Joshua |
#8
Posted to microsoft.public.excel.programming
|
|||
|
|||
Database Querry
I thought it would be something along those lines. After I converted it to
text, and pointed the parameters to these new fields, everything seems to work like it should. One more question about this spreadsheet that I'm making. After the query is run, I calculate subtotals using Data-Subtotals. Is there a way to add a extra line after the subtotal, before the next group begins? Thanks. Joshua "K Dales" wrote in message ... When you use the DateValue or Date functions, the result is a date variable, even if you use formatting to change how it appears on the sheet. So my guess is that MSQuery is pulling it in as a date number (e.g. today is 38454) and that is why you are not getting the results you want. You will need to force the values to go to MSQuery as text in the format you want, you can modify your formulas as follows: =TEXT(DATEVALUE(B2 & "/15/"&B1),"yyyymmdd") and =TEXT(DATE(B1,(B2)+1,0),"yyyymmdd") Note that this will create an actual text value for the cell - and the downside is that if you have other formulas that refer to these cells, those formulas will no longer recognize them as dates! If that is the case, you will probably need to keep the formulas the way you have them, but make two new cells the StartDate and EndDate for your query parameters, and in those cells bring in the dates and convert them to text like so: for StartDate (assuming your existing formula is in cell A1) =TEXT(A1,"yyyymmdd") For EndDate (assuming your existing formula for this is in B1) = TEXT(B1,"yyyymmdd") I hope this finally solves your troubles! K Dales "Joshua Campbell" wrote: Okay, I've got one cell where the user enters in the year in yyyy format. I have another cell where the user enters the month in mm format. In all actuality, the first cell is a cell with "0000" text formatting, and the second is in "00" text formatting. I then have another cell to calculate the start date. The formula for it is =DATEVALUE(B2 & "/15/"&B1) I then have the cell in yyyymmdd text formatting. The formula to calculate the end date is as follows =DATE(B1,(B2)+1,0) I also have this in yyyymmdd text formatting. I created the database query. Using start and end dates that I populated, the query returns data as it should. I then changed the parameters to pull from the above two formulas. However, the result comes back empty. Any ideas? Thanks. Joshua "K Dales" wrote in message ... Just read the original post more carefully, "yyyymm" format is what you want. "Joshua Campbell" wrote: Thanks for the great reply. But, I guess I should have been a bit more clear as to where I was running into problems. When I put in the parameters, I always get an error. It is probably how I am doing it. See the example of the criteria for invdate below: Between '20050315' And [DateRange] & '31' I am trying to concatenate the returned value from DateRange and 31. How would I do this? Thanks. Joshua "K Dales" wrote in message ... First, set up cells to contain the dates that will be selected (hide them if desired). User input can be either directly on the worksheet, some InputBoxes, or a UserForm - just store/copy the results in the cells you set aside. Now, go to data menu, Get External Data, New Database Query. It should prompt you for the data source and I assume/hope your database will be one listed there. Choose the database driver, the file path (if needed) and the userID/password (if needed) to connect to the database. MSQuery will then start and you can set up the query in there, either graphically a la Access or type in the SQL directly - see MSQuery help if you need it. For your date range, use parameters (which are specified by using [] in the criteria grid; e.g. under SALESDATE you would put the following: Between [Date1] and [Date2]). When done return the data to Excel (via File menu or toolbar). It will prompt for Date1 and Date2; for now you can put anything in there. When you get back to Excel the data will appear in a list. Right-click on the list and choose Properties, then Parameters. You will see your Date1 and Date2 parameters - set them to "Get the value from the following cell:" and point them to the cells you designated on your sheet for the date values. Now you just need code to prompt the user for the new dates and refresh the query (which would be something like this: Sheets("Sheet1").Querytables(1).Refresh) This is a very brief outline, but hope it helps. Look in Excel help or MSDN library for more info if needed. "Joshua Campbell" wrote: I need to have Excel run a database querry, prompting the user for information. The SQL that would be used is something a lot like this: SELECT * FROM table18 WHERE (salesdate BETWEEN N'20050315' AND N'20050331') I need to prompt the user for the year and month, preferrably with one prompt, asking for the data in YYYYMM format, and then append the day number to what is entered. How would I accomplish this? Thank you. Joshua |
#9
Posted to microsoft.public.excel.programming
|
|||
|
|||
Database Querry
If you mean you would like to put blank lines in the list in between the
subtotal groups, I have never tried it but I think it might cause problems if you need to "undo" or revise your spreadsheet. Both the list used to calculate the subtotals and the query results depend on the data being in a continuous range, no blank lines or columns. Inserting blanks might mess it all up. I only can think of a couple of alternatives: 1) Instead of a blank line, maybe just change the row height at the end of the group and format the cell so the text is at the top - it would create a space without actually inserting a blank line. 2) copy the query results (with the subtotals) and paste them into a new range, maybe a new sheet, as values (or linked cells) so you can do whatever you like without changing the original list. The thing I can't help with much is finding which rows the groups end on - I don't use subtotals much and not at all in code, so I don't know how to find the groups and summary lines in VBA - you would need someone else to help you there, I'm afraid. K Dales "Joshua Campbell" wrote: I thought it would be something along those lines. After I converted it to text, and pointed the parameters to these new fields, everything seems to work like it should. One more question about this spreadsheet that I'm making. After the query is run, I calculate subtotals using Data-Subtotals. Is there a way to add a extra line after the subtotal, before the next group begins? Thanks. Joshua "K Dales" wrote in message ... When you use the DateValue or Date functions, the result is a date variable, even if you use formatting to change how it appears on the sheet. So my guess is that MSQuery is pulling it in as a date number (e.g. today is 38454) and that is why you are not getting the results you want. You will need to force the values to go to MSQuery as text in the format you want, you can modify your formulas as follows: =TEXT(DATEVALUE(B2 & "/15/"&B1),"yyyymmdd") and =TEXT(DATE(B1,(B2)+1,0),"yyyymmdd") Note that this will create an actual text value for the cell - and the downside is that if you have other formulas that refer to these cells, those formulas will no longer recognize them as dates! If that is the case, you will probably need to keep the formulas the way you have them, but make two new cells the StartDate and EndDate for your query parameters, and in those cells bring in the dates and convert them to text like so: for StartDate (assuming your existing formula is in cell A1) =TEXT(A1,"yyyymmdd") For EndDate (assuming your existing formula for this is in B1) = TEXT(B1,"yyyymmdd") I hope this finally solves your troubles! K Dales "Joshua Campbell" wrote: Okay, I've got one cell where the user enters in the year in yyyy format. I have another cell where the user enters the month in mm format. In all actuality, the first cell is a cell with "0000" text formatting, and the second is in "00" text formatting. I then have another cell to calculate the start date. The formula for it is =DATEVALUE(B2 & "/15/"&B1) I then have the cell in yyyymmdd text formatting. The formula to calculate the end date is as follows =DATE(B1,(B2)+1,0) I also have this in yyyymmdd text formatting. I created the database query. Using start and end dates that I populated, the query returns data as it should. I then changed the parameters to pull from the above two formulas. However, the result comes back empty. Any ideas? Thanks. Joshua "K Dales" wrote in message ... Just read the original post more carefully, "yyyymm" format is what you want. "Joshua Campbell" wrote: Thanks for the great reply. But, I guess I should have been a bit more clear as to where I was running into problems. When I put in the parameters, I always get an error. It is probably how I am doing it. See the example of the criteria for invdate below: Between '20050315' And [DateRange] & '31' I am trying to concatenate the returned value from DateRange and 31. How would I do this? Thanks. Joshua "K Dales" wrote in message ... First, set up cells to contain the dates that will be selected (hide them if desired). User input can be either directly on the worksheet, some InputBoxes, or a UserForm - just store/copy the results in the cells you set aside. Now, go to data menu, Get External Data, New Database Query. It should prompt you for the data source and I assume/hope your database will be one listed there. Choose the database driver, the file path (if needed) and the userID/password (if needed) to connect to the database. MSQuery will then start and you can set up the query in there, either graphically a la Access or type in the SQL directly - see MSQuery help if you need it. For your date range, use parameters (which are specified by using [] in the criteria grid; e.g. under SALESDATE you would put the following: Between [Date1] and [Date2]). When done return the data to Excel (via File menu or toolbar). It will prompt for Date1 and Date2; for now you can put anything in there. When you get back to Excel the data will appear in a list. Right-click on the list and choose Properties, then Parameters. You will see your Date1 and Date2 parameters - set them to "Get the value from the following cell:" and point them to the cells you designated on your sheet for the date values. Now you just need code to prompt the user for the new dates and refresh the query (which would be something like this: Sheets("Sheet1").Querytables(1).Refresh) This is a very brief outline, but hope it helps. Look in Excel help or MSDN library for more info if needed. "Joshua Campbell" wrote: I need to have Excel run a database querry, prompting the user for information. The SQL that would be used is something a lot like this: SELECT * FROM table18 WHERE (salesdate BETWEEN N'20050315' AND N'20050331') I need to prompt the user for the year and month, preferrably with one prompt, asking for the data in YYYYMM format, and then append the day number to what is entered. How would I accomplish this? Thank you. Joshua |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Microsoft Querry | Excel Worksheet Functions | |||
querry | Excel Discussion (Misc queries) | |||
Use two different value to run a querry | Excel Discussion (Misc queries) | |||
Querry Range and Add | Excel Programming | |||
Web Querry question | Excel Discussion (Misc queries) |