Parameterized Query in non-graphical MS Query

[lynbarr]

Hello, I am using MS Query to extract data from a QuickBooks company database. The query is complex enough that it "can't be presented graphically." Therefore, I cannot add parameters to it, at least not initially. After I get the data into Excel, I can then edit the Command Text within the Connection Properties window to add the parameters (replaced a hard-coded date with a question mark). The next time I refresh, I am prompted to specify the cell for the parameter reference, and I can mark a checkbox to use this value on future refreshes. Wonderful, that is exactly what I want. However, my problem is that my parameter is a date, and Excel stores the date as a serial number, and this doesn't work in my query. I keep getting a message that reads "cannot convert '41671' to a timestamp. I have tried this with both CAST and CONVERT, but neither of those works. Does anyone have any idea how I could get this to work properly?

 

[lynbarr]

I solved this by setting up cells for the user to enter their desired Start/End dates. Then, I created a hidden formula in another cell, which I am using as my parameter. Here were my precise steps: Wrote query, hard-coded a date. Added start/end dates in Excel. Created separate formula to convert date to text =TEXT(B1, "mm-dd-yyyy"). Edit query properties, replacing hard-coded dates with a question mark as a placeholder. Refreshed the query. When prompted, selected my TEXT formula as the basis for the parameters, and marked checkbox to "use this value upon future refresh." Saved the file.

 

[par60056]

Welcome to the forum. Congratulations on solving you own first problem

I tend to use the macro recorder and record doing a query one time. Then I have the connection information any time I want to query that source.

Then I build my own query command using all the VBA string functions to convert dates, times, numbers.

I have dozens of "master" workbooks that have nothing but a parameter page. Fill in a few parameters and run the report macro. It builds the query, fetched the data, copies the data only (no query) to a sheet for reporting and then builds any report. You may want to distribute reports with the raw data but you should never distribute anything that has the information to connect back to the data source.(security is important)

Good luck

 

[lynbarr]

Thanks, Par! Now, where were you two weeks ago?

 

[par60056]

Sorry. I'm on here irregularly.

Writing Excel reports is a part of my job. When I'm working on them I visit and try to help. When working on other things I forget to read the forum.

This week I'm back in Excel.