Link to home
Start Free TrialLog in
Avatar of J G
J G

asked on

dcount multiple criteria

I want to add a couple more fields to my macro below.  Currently the Macro tests if there is any matches in a search field to the [PLU] field and filters the found set accordingly.   I want to add 4 more fields [Description] [Size] [Main Link][Cat/Sub] to the functions.  Could you help me with the syntax?


If DCount("[PLU]","[tbl_Item]","[PLU] Like '*' & [Forms]![Item_Master]![SearchField] & '*'")=0

RunCode
Function Name Message()

Else
ApplyFilter
Where =  [PLU] Like "*" & [Forms]![Item_Master]![SearchField] & "*"
Avatar of COACHMAN99
COACHMAN99

Where field like *" &  a &  " or field like " & b
Avatar of Dale Fye
If you are putting this in a function or subroutine, then I like to break it up into two parts, the criteria and the actual domain function.  Doing so gives me the ability to print the criteria before running the domain function and ensure that I have the criteria configured correctly.  I also like to wrap each critieria in it's own set of () to ensure that the logic is handle properly.
Dim strCriteria as string

strCriteria = "([PLU] Like '*" & [Forms]![Item_Master]![SearchField] & "*')"
debug.print strCriteria
If DCount("[PLU]","[tbl_Item]",strCriteria)=0

Open in new window

But why did you indicate multiple critieria, this is a single criteria
Avatar of J G

ASKER

thanks,

could you put in the field names so I could see the exact syntax?
Avatar of J G

ASKER

I currently have single criteria, I want to add a few more fields that my search box will search.
don't know what you mean, put in the field names?  Adding in those field names, without knowing the values they are supposed to relate to is meaningless.  However, to give you an idea, it might look like:

strCriteria = "([PLU] Like '*" & [Forms]![Item_Master]![SearchField] & "*') AND " _
                   & "([Description] Like '*" & [Forms]![Item_Master]![SearchDesc] & "*')"
Avatar of J G

ASKER

thanks,  I just wanted an example to make sure I got the syntax right.
The example above specifically looks for text strings using the LIKE operator.

If you need exact matches, or numeric values, it would look something like:

strCriteria = "([PLU] Like '*" & [Forms]![Item_Master]![SearchField] & "*') AND " _
                   & "([Description] = '" & [Forms]![Item_Master]![SearchDesc] & ') AND "  _
                   & "([Size] = " & [Forms]![Item_Master]![SearchSize] & ")"

note that this criteria uses AND to join the criteria, so it would only return those records which meet all of these critieria.  You might want to include a dropdown in your search area to indicate "AND" or "OR", and then use that value if you want to select records that meet any of those critieria, but not all of them.

Dale
Avatar of J G

ASKER

thanks!!!!
Avatar of J G

ASKER

the quotes at the beginning seem off.

How would the syntax look with DCOUNT in front of it?

Dcount "([PLU] Like '*" & [Forms]![Item_Master]![SearchField] & "*') AND " _
                    & "([Description] = '" & [Forms]![Item_Master]![SearchDesc] & ') AND "  _
                    & "([Size] = " & [Forms]![Item_Master]![SearchSize] & ")"
Avatar of J G

ASKER

correction.

I understand the DIM statement that defines the string.  Where I am confused is how to invoke it from the DCOUNT function.
go back to my original post.  But if all you want to do is count the records in the table which match the criteria, then use the following:
Dim strCriteria as string

strCriteria = "([PLU] Like '*" & [Forms]![Item_Master]![SearchField] & "*') AND " _
            & "([Description] = '" & [Forms]![Item_Master]![SearchDesc] & ') AND "  _
            & "([Size] = " & [Forms]![Item_Master]![SearchSize] & ")" 
debug.print strCriteria
If DCount("*","[tbl_Item]",strCriteria)=0 THEN
    'some other code here
End If

Open in new window

Avatar of J G

ASKER

thanks,  btw why the debug.print?
so that you can actually see what the criteria string looks like, and make sure that it has quotes where needed.  It also gives you the ability to copy and paste it into an actual query, which helps in the debugging process.

Once the debugging process is complete, I remove those statements.
Avatar of J G

ASKER

Sub SearchBox()


Dim strCriteria As String

strCriteria = "([PLU] Like '*" & [Forms]![Item_Master]![SearchField] & "*') OR " _
            & "([Description] = '" & [Forms]![Item_Master]![SearchField] & ")"
           
           
Debug.Print strCriteria
If DCount("*", "[tbl_Item]", strCriteria) = 0 Then

Call Message

Else
 DoCmd.ApplyFilter(,where ([PLU] Like "*" & [Forms]![Item_Master]![SearchField] & "*" Or [Description] Like "*" & [Forms]![Item_Master]![SearchField] & "*",)
   
End If


End Sub
Avatar of J G

ASKER

One more thing!  What is wrong with my ELSE statement syntax?
ASKER CERTIFIED SOLUTION
Avatar of Dale Fye
Dale Fye
Flag of United States of America image

Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
Avatar of J G

ASKER

I am using the code below,  I am getting the following error when I run it:  Run=time error '3075': Syntax error in string in query expression '(PLU] Like '*3013*') OR ([Description] = '3013)'

(I am searching for PLU 3013)

Function SearchBox()


Dim strCriteria As String

strCriteria = "([PLU] Like '*" & [Forms]![Item_Master]![SearchField] & "*') OR " _
            & "([Description] = '" & [Forms]![Item_Master]![SearchField] & ")"
           
           
If DCount("*", "[tbl_Item]", strCriteria) = 0 Then

Call message

Else
    [Forms]![Item_Master].Filter = strCriteria
    [Forms]![Item_Master].FilterOn = True
End If


End Function
SOLUTION
Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
OK, so is the PLU field a text data type, or is it a number?
I assume the Description field is text and would not have that value (3013) as the only characters in the field, so I would use wild cards in that part of the critieria.  And then I would add the debug.print line back in, so that you can see, and copy exactly what strCriteria looks like, to post it here if this still doesn't work.

Lets start with:

strCriteria = "([PLU] Like '*" & [Forms]![Item_Master]![SearchField] & "*') OR " _
            & "([Description] Like '*" & [Forms]![Item_Master]![SearchField] & "*')"
debug.print strCriteria

Open in new window