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?

 

[visualuser]

ETID,

Yes, I have the queries. I'm not sure how to get the dates in there so the macros will run.

Are you still around?

 

[ETID]

If you right click in the data area and choose edit query you can then add a criteria (it's one of the "VIEW" menu items in the MsQuery editor)

drag a the date field from the table to the criteria area of the layout
and in the value: area type ec with the brackets

....(brackets denote prompting from the user)

then when you run the query it will stop and wait for the user to enter a date.

you can also add conditionals to the prompt
I.E. >=[First date] and <[Last date]

Hope this helps...if not there is a VBA way to control this even tighter.

 

[ETID]

oops....typo!

the previous line.....

drag a the date field from the table to the criteria area of the layout
and in the value: area type ec with the brackets

should read:

drag a the date field from the table to the criteria area of the layout
and in the value: area type [Enter date] with the brackets

 

[visualuser]

ETID,

That's good. I like it. I see how I can pretty much tell the box to say whatever I want it to say, but I have one problem.

I'm not sure what way I need to do this then. If in excel I go to get the external data and go throgh odbc, then when I comes up with all the tables, I hit cancel, then msquery opens and I hit close, then hit the sql button and past the query in there. I then get a message that the query can't be represented graphically, but if I hit ok, the data shows up but the Criteria section is greyed out.

I've made my queries in another program, well actually I wrote them and joined them by looking at all the tabels.

Do you know why the criteria isn't showing up.

I was thinking of having only 1 excel file. Wouldn't I have tons of query files if I did it this way?

 

[ETID]

Not sure if I follow....Do you have your tables linked in a single MsQuery session?


or are you linking to a single query in another app that combines multiple queries and tables?


Or am I missing the point all togther????

 

[visualuser]

There are 5 queries that have at least 3 tables in each query.

There are 9 Gl Accounts that I'm running this on.

I'm thinking that ms qeury didn't show all the tables. I one one guy loaded our database into access so he could link more tables.

So do you think the criteria doesn't show up because I have too many tables linked?

 

[ETID]

Hmmm,.... that's a head scratcher . are you using a DSN to link excel to your db?

 

[visualuser]

not sure what you mean. I did a test with the tables and chose one and I was able to make the criteria work, but the way I'm doing it by just pasting the query in there it doesn't show up.

What's dsn? I know what dns is.. I'm using the odbc sqlbase driver.

 

[ETID]

A DSN is A DATA SET NAME .....you build a dsn and it has options like ODBC driver, translation options, read/write,
time out interval and so forth then you set up an odbc connection using this instead of just the odbc driver.

Geting back to the tables though...do you have the relationship of these tables defined in MsQuery ...I.E. Keys fields joined?

 

[visualuser]

Well, when I put the query in there, the table start popping up linking. All three show up at the top then I get the message:

SQL Query can't be represented graphically. Continue anyway?

This is what the help says about the message.

Microsoft Query can't display the Table pane for this query. If the query returns records, you can't edit them or the query's design in the Query window. However, you can edit the query's design by clicking SQL on the View menu and then editing the SQL statement directly.

I have dates in the query like this:
RECEIVABLE_DIST.POSTING_DATE>=@DATE(2001,7,31) AND
RECEIVABLE_DIST.POSTING_DATE<=@DATE(2001,7,31) AND ...

When I hit ok, that data comes up that I need though???

 

[visualuser]

Well, when I put the query in there, the tables start popping up and linking. All three show up at the top then I get the message:

SQL Query can't be represented graphically. Continue anyway?

This is what the help says about the message.

Microsoft Query can't display the Table pane for this query. If the query returns records, you can't edit them or the query's design in the Query window. However, you can edit the query's design by clicking SQL on the View menu and then editing the SQL statement directly.

I have dates in the query like this:

RECEIVABLE_DIST.POSTING_DATE>=@DATE(2001,7,31) AND
RECEIVABLE_DIST.POSTING_DATE<=@DATE(2001,7,31) AND ...

When I hit ok, that data comes up that I need though???

 

[ETID]

Does the >=@date(2001,7,31) convention work for a single table...

 

[visualuser]

ETID,

Ok, interesting....

I grabbed 1 table and enter the date the way I had it and got the error message, then I took the dates out and put your >=[First date] and <[Last date]...I actually looked at the code, then put it in the sql box... and it works fine no error messages!

I think maybe I should go and play around with the formatting and try to somehow get all these qeuries and macros going. There are so many tables that I just didn't really even try to get them into all 1 query, that's at least 15 tables.

Would you mind if I post again at a later date? Something may come up....

Thanks again for your help...Do you want a star or do you even care about that?

 

[ETID]

Glad I was of some help