Reports based on form entries

[pwinters]

I'm not sure if I'm posting this in the right forum, but I think this could be done using sql..just not sure how.

Is it possible to create a report (displayed on a website) based on criteria selected from a form? (3 or 4 drop down boxes)

i.e.
User selects Month: April, Team: 1 and 2.
Can I produce a report after the user hits submit with information based on these selections?

I'm currently have an access database, and I'm using sql to query the database and java & jsp to display the sql results.

Thanks!

 

[PWise]

the month part is easy
put this code in the on click event
dim mydb as database
set mydb=currentdb
mydb.querydefs("tablename&quot.sql="select .....,month form tablename where month ='" & me.combomonth & "';"
this is asumming that the month are entered in the table as strings
now the team part is a bit tricky if you want the results from one team put all your teams in the control source of the team combo 1;2;3

but if you might want team 1 or team 2 or team 3 or team 1 and 2 or any orther combination
create a 2 coulmn combox
colunm width 0";3
bound column 1
rowsource
enter these across then down
1;1
2;2
3;3
1 or 2;1 and 2
1 or 3;1 and 3
3 or 2;2 and 3
(if there more teams and combination we might have to explor other methods.)
and change to
mydb.querydefs("tablename&quot.sql="select .....,month,team form tablename where month ='" & me.combomonth & "' and team=" & me.comboteam & ";"

 

[pwinters]

pwise,

Thanks for your reply, but I'm using sql, java, and jsp to display these results to a web page. I'm just using Access to store the data. It looks like the code you gave would work for a module in Access.

Maybe I posted this to the wrong forum...

Thanks anyways.

 

[RiverGuy]

Are you using a Report as in Access Report? It sounds like you're just displaying it on a web page in HTML without like an Access Snapshot Plugin, right?

You might have to do some trick string concatenation to get your query working right.

Like if you have one item selected in your dropdown box, it might look something like: "SELECT Whatever from Whatever WHERE Whatever = '" + yourDropDownValue + "'"

But if you've got two items selected, you would have to put another "Whatever =....." at the end.

Then if you are going to validate more than one column, you would have to be sure to put parentheses around the group of OR's for each different column.

 

[pwinters]

Thanks RiverGuy!

That's exactly right. I'm only using Access to hold the information. I'm not using the forms, reports, buttons, etc. HTML and Java are used to create the web page.

I had to tweek my code a little bit, but your suggestion worked!

Thanks so much