Solved count No. of record in table

[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.

 

[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.

 

[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.

 

[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.