Dcount using textbox with like

[naeemahmad]

Hi guys. I think this is pretty siple but cant seem to resovle its syntax. Heres my code and what im trying to achieve;

I want to user to type in values in a textbox..then the value is used as a criteria for a DCount function to determin is records exist.

Source Field Data Type is NUMBER

QUESTION:
1) Can or should I use a string valriable and build the like (wildcard) expression to use as a criterion or can or should I build the LIKE expression within the DCount expression, and if so.. can someone help me with that dcount and LIKE syntax?

THANKS ALL FOR ANY GUIDENCES!

Private Sub Combo89_Change()
Me.Refresh
Dim LNumofRecs As String


Dim qryPRM As String

qryPRM = Forms![frmTIMESHEET_MAINMENU]![Combo89] & "*"

LNumofRecs = DCount("*", "qry_MAINMENU_QRY_LT_RESULTS", "[LT] = " & qryPRM & "")

Me.Requery
If LNumofRecs <= 0 Then
MsgBox ("no rec found!")
Exit Sub
End If

End Sub

[naeemahmad]

OK guys. Just figereued it out. Here my expression syntax below for anyone with the same simple question. just had to look at it one more time and it clicked.

LNumofRecs = DCount("*", "qry_MAINMENU_QRY_LT_RESULTS", "[LT] Like ""*" & Forms![frmTIMESHEET_MAINMENU]![Combo89] & "*""")

[ItsMe]

Instead of storing the Dcount result as a string I would

Dim LNumofRecs As long

Also, you just need to include the single quote with the = operator

"[LT] = '" & qryPRM & "'")

should do it

finally
why use a wild card for your Dcount field? Grab the PK field for that domain.

DCount("PrimaryKeyField", "qry_MAINMENU_QRY_LT_RESULTS",

[naeemahmad]

Very cool. Thanks for the helpfull reminder. Also you are right about storing DCount as string.

Quick Question: Can you please expand/clarify your suggestion; .. sound very interesting.

"Grab the PK field for that domain."

Thanks agin!

[ItsMe]

The first argument in the Dcount() function is requesting a field name. By using the wildcard, I believe you are asking it to count all field names. Perhaps it only considers the first field it comes across, don't know. I try to explicitly define the field it should count and also define a field that I know is "Indexed" and of Number data type.

Replace the * with

DCount("PrimaryKeyField", "qry_MAINMENU_QRY_LT_RESULTS",

[June7]

Use of wildcard in DCount or Count is valid because the function counts records. Except for nulls, content of fields is irrelevant so designating a field is not necessary in the field argument. Using wildcard and not specifying field should include all records in the count as long as each record has value in at least any one field. If the count must be restricted then use the WHERE CONDITION argument. Specifying a field will exclude records from count with null in the field (actually, shows 0 if there is a GROUP BY clause). Maybe specifying a field where it is certain to have a value in every record is more efficient, but I doubt it will be noticeable.

[ItsMe]

Interesting info, thanks

[naeemahmad]

Great info. Thank you. Actually that's exactly why I chose the count all rows as opposed to a particular field. Although there may not be much (if any) potential variance.. I just didn't want to be exclusive to one field. Just my OCD kicking in again! LoL

Anyways, great info and really appreciate the responses!!!