buttons that asks the user questions...

[visualuser]

How do I make a button to ask the user to enter dates for a report to run?

 

[euskadi]

in what application? they're all pretty much the same...
let's assume you're using access. create a button and have it do this:

Sub button1_click()

    dtstart = InputBox("start?")

    dtstop = InputBox("stop?")

    'code for report to run

End Sub

then in another module create these functions. you can add these into your report queries so that the dates the user enters work as parameters in your report. again, assuming you're using access.

global dtstart as date
global dtstop as date

function reportstart()
   reportstart = dtstart
end function

function reportstop()
   reportstop = dtstop
end function

 

[visualuser]

I'm wanting to use it in excel using odbc. I'm trying to pull some figures on some accounts, but need to find a way to tell excel to pull for certain dates.

I want to either have a message pop up to tell the user to enter the starting date, then one that comes up to enter the ending date.

Or maybe have the user enter in a field the dates, then click a button to run the report.

 

[ETID]

IN your MSQuery editor....


use a parameter in the criteria area ....


hard coded criteria:
>="01/01/99"
--------------------------
parameter criteria:
type a "prompt" in brackets [Enter starting date] in place of an actual date

example:
>=[Enter starting date]

 

[euskadi]

When I need to do that. I create the query, then record a macro and make some modification to the query... (helps me get the details right). Then I add a couple input questions, or pull the dates from a spreadsheet, and use VBA to change the SQL as needed. Works pretty slick.


Another way to do it, if you're not pulling terrible amounts of data, is to pull in longer spans of to a data worksheet and limit the data in your report with simple equations.

 

[visualuser]

Does anyone know where I can download an example of this, the button that asks for starting and ending dates.

If not, can someone send me one?

visualuser@yahoo.com

Thanks!

 

[ETID]

Is this an access report based on a query?

 

[visualuser]

Well, I'm doing it in excel using the odbc, trying to at least. Not a lot of people have access installed, so I'm trying to go the excel route. Thanks...

 

[ETID]

OK so have you tried using parameters in msquery?

 

[visualuser]

No, I have not got that far. I'm still trying to pull the right data, but trying to figure out how I"ll get the user to put the dates in.

 

[visualuser]

Actually, I made it auto open and ask for a date and when the user puts the date in, the beginning dates will go into the cells. I assume I can figure out how to use those dates for the report to run on.

 

[ETID]

But, you don't need that a paramter is a prompt...asking for the users input. (although there is no error checking)

 

[visualuser]

Is that not good thing to do? I just need to find the best way for the user to input the dates for when he or she wants to run the report.

I guess I don't understand your reply. I don't have much experience with vba, as you can tell. Thanks again for your help in this.

 

[ETID]

Ok...so far, Have you returned any data all to your .xls via ODBC?

 

[visualuser]

Well,

I'm still in the works of getting the info from all the tables. (yikes.... ) Question though. When I return the query back to excel, a query file is created. How do I make it where there is not a query file. I would like to have one file.

I'm not sure how I will tell the query to use cell b5 and b6 for the starting and ending dates. As you can tell this is my first excel spreadsheet trying to use some macros and what have you...

me->

 

[ETID]

Once you succesfully get the data returned via ODBC, with "hard" criteria, then I can walk you through automating the process using VBA. (I'ts just a tad tedious, at first but well worth it.)

 

[visualuser]

ETID,

Thanks Buddy. I'm almost there. I think I jumped the gun. I didn't think that the queries would be that hard, but come to find out, I needed to make 4 lengthy ones. I'm almost done, then will need to figure out how I'm going to combine them all. ( adding figures in excel )

I'll get back with you later... Thanks again for the assistance. I really appreciate it.

 

[ETID]

Good luck...I set this thread for email notification so I'll know when you complete stage 1

 

[visualuser]

ETID,

I have 4 queries I would like to send to my spreadsheet. I've tested them and they work in ms query off our database.

Question 1.
I need to figure out how to get the queries in the code so I don't have all the ".dqy" file with my excel file.

Question 2.
I'm really not sure how to create the macro to connect through odbc. I'm not sure if it would be good to just make the macro putting in the username and password.

Any suggestions? Thanks.

 

[ETID]

Have you envoked MsQuery from within excel?