Newbie Help - Query Intergration

[fatmcgav]

Hi

I'm new to this Access lark, and i have a limited knoweledge of MS Access.

I am currently doing a school project, which involves me designing an Access Database for a garden centre. this garden centre needs to be able to produce reports containing plants that fit certain user defined criteria.

The user will choose the following data:

• Max Height
• Max Width
• Light Needed

which will then be input into the computer.

The garden centre then need to be able to produce a report that contains the above data, plus the plants' common name, flowering period and price, and also their company header.

I am at a loss at what to do, i had a read of the How To Article about controlling Report Data, but i got lost along the way. i have a basic data table containg all the plant info, and a basic stock list report, and also a plant input/edit form.

Thanks in advance for any replies.

[KnooKie]

hi

ok so have you designed and implemented your table structure within Access yet ?

Which version of Access are you using by the way ?

Next step is to design a form that the details can be input into. The form will be bound to a query.

Can you post your table structure ?

[fatmcgav]

Hi

I am running Access XP.

I already have a table containing all my data. the headers are:

• ID (Autonumber & Primary Key)
• Common Name
• Latin Name
• Height
• Width
• Flowering Period
• Light Needed
• Plant Type
• Price

I also have a form that enables me to add/edit those details.

hope this helps

[KnooKie]

sounds like you've almost dome it then. All you need to do is the form for the user defined criteria and then feed this into a query that is behind your report.

What sort of user defined criteria form are you wanting to have ? What is the criteria you are allowing them to choose from ?

[fatmcgav]

They need to be able to select a max height and width, and the amount of light needed.

the amount of light needed is easy, all i need is a drop-down box with the 4choices, but i dont know how to do the other 2, because they can be any number between 0.1M width & height & 3.5M width & height.

Thanks Again

[KnooKie]

for the other 2 can you have text boxes that allow them to enter that.

You can place validation rules on them to stop them putting in too high or to low a figure. You could also input mask them to ensure the correct number of decimal places.

[fatmcgav]

yeh, i have done that, but i'm mot sure how i link those values into the report, so any plant with a max height less than or equal x and a max width less than or equal to x, and which needs x light.

Do i need to use expressions or somet?

Also, is it possible to include a button that takes them to the report, might be increadibly simple, but i cant see how.

Thanks again for all the help.

[KnooKie]

yes you can put expressions into the criteria part of a query using such things as.....

>=
<
>
Between

to specify ranges


You can put things like.....

>=Forms!MyFormName!txtBox1Name AND <=Forms!MyFormName!txtBox2Name

in the criteria part of queries

If you look at OpenReport in the help file you will see it has a WHEREcondition part as one of its arguments i think. Within this you can specify ranges also.


DoCmd.OpenReport reportname[, view][, filtername][, wherecondition]

[fatmcgav]

Right
I have designed the Search Form, and also the Search report, minus the query fields.

What i'm not sure of is how to feed the input criteria into a query which then produces the report.

Thanks Again.