Solved count No. of record in table (1 Viewer)

[theinviter]

Dear Guys;
I wanna count number in record in table after combo box update,
i tried this but got error .
Me.Text26 = Nz(DCount("*", "[MRN]", "[Location - Clinics Name] Like ""*" & Me.Combo2 & "*""), 0

 

[June7]

What error message?

Missing closing parenthesis for the Nz() function.

 

[plog]

No NZ() needed. If syntatically correct and referencing valid fields and table it will return a value. But...

Yours isn't syntatically correct. Quote marks must have partners. Your criteria argument has 7--somebody doesn't have a partner. The root cause is that you are trying to build a string inside a string and inside the innermost string you actually want quote marks to appear. I suggest you don't use double double quotes in those instances but instead single quotes. That way its less likely to lose count and track of how many you need and where. Like so:

"[Field]='" & Variable & "'"

 

[June7]

Good points.

DCount will return 0 if no records match criteria. Other domain aggregate functions will return Null.

 

[Pat Hartman]

Also:
1. NEVER use like when the data comes from a combo. LIKE is used only when you are searching for a PARTIAL string. Combos return a complete value.
2. ALWAYS give your controls meaningful names. "Text26" is meaningless
3. Do not use embedded spaces and special characters in your column names. This poor practice can cause strange results once you start building controls and writing VBA, not to mention that you have to constantly remember to encase the offending name in square brackets.

 

[Isaac]

NEVER use like when the data comes from a combo. LIKE is used only when you are searching for a PARTIAL string. Combos return a complete value.

Why would you say this? It is entirely possible that someone's specific domain or business environment does indeed provide an atmosphere that may be conducive to a drop-down containing keywords, and a resulting like clause in a SQL statement.

 

[Pat Hartman]

You have a point. A very small one but granted. I'm sure that someone, somewhere puts keywords they want to search on in a combo. I stand corrected. The reason I didn't even consider this a possibility was that it is too inflexible. With a combo, it is all or nothing, you can't really search on partial strings. So, once you've decided to populate your search field table, you need to be sure you have accounted for all possibilities. You can certainly use a combo to help construct a search string but you would need to copy the items into a text box and use the text box in the Where clause rather than the combo. So, technically, you aren't searching on the combo using LIKE,

Remember, when you are typing into a control, the characters are going into the .text buffer. When the entry is finished, it is moved to .Value. But, if you don't type or pick an exact match from the combo, the invalid value does not get transferred to the .Value property.

 

[June7]

Pat, I am not following that. I have a procedure that uses LIKE condition with value from UNBOUND combobox where user can select from list or type anything. And no textbox involved.

 

[Sun_Force]

With a combo, it is all or nothing, you can't really search on partial strings.

@Pat Hartman I can't understand why someone with your experience says something like this.
I have a search form with 3 cascading combo boxes to norrow down a search on drawing numbers.
I also have 3 buttons bellow each combo to allow ambiguous search when the user is not sure what he's looking for.

I've used LIKE to search with only selected combos. Is it a bad approach?
Aren't cascading combos meant to filter the next combo's row source with Like keyword?

 

[June7]

Normally, cascading combo would do filter on exact match to a key. But ambiguous search is feasible.

 

[Pat Hartman]

If you are using a combo, you are searching on a complete string, therefore, you would not be using LIKE with wildcards. LIKE certainly has its place in the universe but it is a very slow operation when you have a large table since the query engine will usually opt to search row by row rather than to use an index. So, if your table contains 100 rows, it almost doesn't matter what you do. But if your table contains a million rows (as many of mine do) it does matter so we are talking about efficiency. Unless you actually have a partial string and so must search row by row, don't use inefficient methods for no purpose.

I take it all back. Looks like the behavior of the combo is different depending on whether limit to list is set to yes or no. When the combo loses focus, whatever you typed does end up in the .Value property as long as you have limit to list set to no. So you can use the combo as a text box as long as the text field is the bound field. When the limit to list property is set to yes, you get an error and can't leave the combo without fixing the selection.

I am very Sorry for the confusion. I always have my combos set to limit to list = yes because the whole point of a combo is to limit the selection to the list. So, you can actually use a combo for a partial search. BUT, the point about using LIKE when you are not actually doing a partial string search still stands.

I just had a flashback to all the applications where I've ended up cleaning up thousands of records because they had garbage in a field i thought was controlled by a combo. This was how the bad data got stored. LimitToList = No, AllowValueListEdits = No = recipe for bad data in tables.

 

[June7]

I have db where combos are used as an assist to provide previously used strings for users to select (and modify if they want). The combos allow free form text input like a textbox - and AutoExpand is very helpful. The RowSource for combo is the destination table, not a lookup table. Yes, this could become a very long list but the db isn't very big and I expect it will be replaced before it would get so unwieldy. It was a replacement for a 20 year old dBaseIV DOS app. Going on 13 years of deployment, give it another 10 and they could be looking at building something completely new. Looking back, I probably could have implemented "filter as you type" on the combobox list to return items that match the input anywhere in the data string, not just starting with.