General help with quieries (1 Viewer)

[Hayley Baxter]

I am currently running many reports based on quieries. I have a db that stores suppliers contracts and for each of these contracts there is a field called Contract type ie software licence, software maintenance, hardware etc. I have been creating lots of quieries to run reports on all the contracts with software licence, maintenance...these contracts could be many different types and I wondered if there was another way to narrow this down.

Instead of me having to create one query for software licence then put criteria = "soft licence" is there a way I can say = contract type and have access look it up because eventually all these quieries are going to mount up taking up memory.

Thanks in advance
Hayley

 

[DougS]

create a parameter query instead, and set criterea for Contract type to make you type something in = [Select Contract Type].
Only 1 query is needed.

 

[adz2013]

Setting up a parameter is a great idea it will narrow down the number of queries in the database.

 

[Hayley Baxter]

That's sounds like I could have saved myself alot of work. If I do this is there ways to set up my own dialoug box and not the one that access automatically will bring up and can I still make it user friendly ie if no matches found for what is typed then inform the user and retry?

Thanks

 

[David R]

Hayley,
use a popup form and tie that to opening your report in the proper way. If you search the archives this has been covered extensively.

HTH,
David R

 

[Hayley Baxter]

Thanks David I am looking into this. I have also noticed that the dialoug prompt box anything you type in here is case sensitive and if you type one letter wrong ie spelling error or miss out a letter the results are not displayed so I need to find a way round this maybe with performing a check on what is typed then an error msg then a cmd button to retry or something.

Thanks again

 

[Rich]

Why not have a combo box that extracts the unique Contract types and use that for the criteria, users will be prevented from entering the wrong data as the criteria.
HTH

 

[Hayley Baxter]

Rich I like your idea but how would I implement this. I have created a form that lists all my reports and they are preview buttons because I am not good with option boxes just yet. The user selects the cmd button to preview the report then up pops the dialoug box Foms!etc etc Even just a nice msg for them stating

"Please enter the value"

Values must be betwwen 1 and 9 etc so that they kno prior to entering. I am still looking up pop up forms so I may be able to work this out myself but haven't had much time to have a look, any tips while I am waiting would be great though.

Thanks