Query Questions

[Stemdriller]

The following is an exert from a query in Access

Poor: DCount("Ratings","tblRatings","Ratings = 1 and Reason =2 and Dept = 3 and Category =1")

I am trying to make it a generic Query so when the query is run it will ask the user What Reason and What Dept and What Category? Pretty much like [ ] in the criteria of a query.


Tried changing it to
Poor: DCount("Ratings","tblRatings","Ratings= 2" And [Reason] And [Dept] And [Category])

But doesn't work

Thanks

 

[Galaxiom]

Using a Domain Function in a query is a surefire way to a very slow query. The Domain function runs for each record and opens a recordset each time to perform a separate query on the data.

Explain more about your data structure so we can help you write an efficient query.

 

[Stemdriller]

Using a Domain Function in a query is a surefire way to a very slow query. The Domain function runs for each record and opens a recordset each time to perform a separate query on the data.

Explain more about your data structure so we can help you write an efficient query.

I am trying to count various entries for various departments, so rather than creating several queries I was just hoping to get the user to input what they specifically want with the one query, which can then go on and create a chart

 

[vbaInet]

This will work:

Poor: DCount("Ratings","tblRatings","Ratings = " & [Enter Rating] & " AND Reason = " & [Enter Reason] & " AND Dept = " & [Enter Department] & " AND Category = " & [Enter Category])

But it will bomb when the box is left blank.

 

[CBrighton]

I don't use domain functions often, but couldn't you embed an Iif statement into it?

e.g.

"Ratings " & Iif(IsNull([Enter Rating]),"like '*' ", "= [Enter Rating] & ")

Never tried it so I could be talking rubbish, but in my mind that should show everything if the input box was left blank?

 

[vbaInet]

This '*' is returning Text so it will still throw an error.

There are other ways of getting it to return the full set of records but it's convoluted. Much better to build the sql in vba.

 

[CBrighton]

Fair enough. Like I said, I don't really use domain functions. I'd much prefer a quick check of a recordset in VBA for most functions performed by domain functions.

 

[vbaInet]

Yep, much quicker. There are some cases where a subquery outperforms a recordset though, especially if the function holding the recordset is called from within the query.

By the way, here's the convoluted method I was referring to:

... "[Ratings] BETWEEN " & IIF(Len(Trim$([Enter Rating] & "")) <> 0, [Enter Rating], DMin("Ratings", "Table")) & " AND " & IIF(Len(Trim$([Enter Rating] & "")) <> 0, [Enter Rating], DMax("Ratings", "Table")) ...

Just for the [Ratings] field - lthough not tested for syntax errors.

 

[Galaxiom]

Fair enough. Like I said, I don't really use domain functions. I'd much prefer a quick check of a recordset in VBA for most functions performed by domain functions.

My impression is that the Domain functions open a recordset built from the arguments. I would be curious if anyone has timed Domain functions versus using a recordset.

There is no doubt they have their appeal particularly their simple syntax. However many new developers see them as a panacea and they are frequently overused because their simplicity masks the underlying process.

My rule of thumb is use the Domain function to return a single value but open a recordset as soon as more values are required. It is obviously going to be far faster to do a FindFirst on an already open recordset than use a DLookUp.

On forms, retrieve a value for one control using the Domain function as a ControlSource but for anything more from the same data, try to structure the form to get the records though controls bound to RecordSource query. It can usually be done (using a subform if necessary) but if not use procedures to retreive values from a recordset.

Populating large numbers of controls with Domain functions makes for a very slow form.

 

[Stemdriller]

vbaInet,

You code worked like a dream and absolute dream. I am forever in your debt.
Your code also enlightened me on how to improve another db too.

Thanks also for everyone else's input.
Best Regards

Gareth aka StemDriller

 

[vbaInet]

vbaInet,

You code worked like a dream and absolute dream. I am forever in your debt.
Your code also enlightened me on how to improve another db too.

Thanks also for everyone else's input.
Best Regards

Gareth aka StemDriller

Which post are you referring to. They both had a caveat.

 

[Stemdriller]

Which post are you referring to. They both had a caveat.

Your initial code. I needed to add a couple of more things to it, but when I saw how to do it and all fell into place.