Access 2002: need some general advice 2

[baddy]

Hi all, I have an Access database I use at work and to be honest I am very naive at using this program. All I really use it for is to run queries, since I already know SQL syntax (I don't much care for the query design tool) i just type them by hand. What I would ideally like to do is make a report that would allow the users to choose a date from a dropdown, and then it would basicly show the results from a bunch of queiries for that date in the report with graphs and what not. I'm sure this is a pretty simple thing to do, but I just have no idea where to start.

There are a few fundamental hurdles that I run into. For example, since I want the user to input something (the date), do I need to make a form? From what I understand, forms are just for entering data into the db but I am not sure about that. I went out and bought the Access 2002 Bible, but its a bit heavy (~2000 pages) so I haven't spent too much time in it. Any shoves in the right direction would be really appreciated.

 

[RamziSaab]

You will need to make a form, but depending on how complicating the queries need to be u might need to use some vba...if they are not too complicated you can use the wizard and predefined macros to make a simple form that will make a report!

 

[xlbo]

Actually, you don't need a form - you can do it in SQL:
Excerpt from one of my queries:

HAVING (((dbo_Calendar.Punch_Fin_Yr)=[Enter Year]))

Where the text prompt is within the [ ]

or, in VBA

mYear = inputbox("Enter Year&quot
then
"HAVING (((dbo_Calendar.Punch_Fin_Yr)='" & mYear & "'))"

would be how to do it BUT, if you use a form, you can use validation much more easily to make sure that they have entered a VALID entry so the query doesn't fall over

Rgds, Geoff
It's Super Happy Campo Funtime!
Want the best answers to your questions ? - then read me baby one more time - faq222-2244

 

[baddy]

Thanks xlbo, that is some good info. However, I am having a bit of trouble with HAVING in a query statement. Here is what I tried:


HAVING (((user_yr)=[Enter Year]))
SELECT * FROM mytable WHERE mytable.year LIKE user_yr;

And I get the error " Invalid SQL statement; expected 'DELETE', 'INSERT', 'PROCEDURE', 'SELECT', or 'UPDATE' " -- could you maybe give me a example of a simple select using HAVING? Thanks a lot, I appreciate your help.

 

[RamziSaab]

Not sure but i think this is syntax

SELECT Table1.Field5
FROM Table1
WHERE (((Table1.Field5)=[date]));

table1 is ur table name
field5 name of ur field
[date] ur prompt

 

[xlbo]

Sorry - it was just an excerpt from my SQL as I mentioned - the HAVING bit is not important - it is the [ ] - these make Access pop up an input box with whatever is inside the [ ] as a prompt. It then takes whatever is entered into the input box and pops it into the SQL

This should work:
SELECT * FROM mytable WHERE mytable.year = [Enter Year];

Rgds, Geoff
It's Super Happy Campo Funtime!
Want the best answers to your questions ? - then read me baby one more time - faq222-2244

 

[baddy]

You got it, thanks a lot.