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]![Sea rchField] & '*'")=0
RunCode
Function Name Message()
Else
ApplyFilter
Where = [PLU] Like "*" & [Forms]![Item_Master]![Sea rchField] & "*"
If DCount("[PLU]","[tbl_Item]
RunCode
Function Name Message()
Else
ApplyFilter
Where = [PLU] Like "*" & [Forms]![Item_Master]![Sea
Where field like *" & a & " or field like " & b
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
But why did you indicate multiple critieria, this is a single criteria
ASKER
thanks,
could you put in the field names so I could see the exact syntax?
could you put in the field names so I could see the exact syntax?
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]![Sea rchField] & "*') AND " _
& "([Description] Like '*" & [Forms]![Item_Master]![Sea rchDesc] & "*')"
strCriteria = "([PLU] Like '*" & [Forms]![Item_Master]![Sea
& "([Description] Like '*" & [Forms]![Item_Master]![Sea
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]![Sea rchField] & "*') AND " _
& "([Description] = '" & [Forms]![Item_Master]![Sea rchDesc] & ') AND " _
& "([Size] = " & [Forms]![Item_Master]![Sea rchSize] & ")"
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
If you need exact matches, or numeric values, it would look something like:
strCriteria = "([PLU] Like '*" & [Forms]![Item_Master]![Sea
& "([Description] = '" & [Forms]![Item_Master]![Sea
& "([Size] = " & [Forms]![Item_Master]![Sea
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
ASKER
thanks!!!!
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]![Sea rchField] & "*') AND " _
& "([Description] = '" & [Forms]![Item_Master]![Sea rchDesc] & ') AND " _
& "([Size] = " & [Forms]![Item_Master]![Sea rchSize] & ")"
How would the syntax look with DCOUNT in front of it?
Dcount "([PLU] Like '*" & [Forms]![Item_Master]![Sea
& "([Description] = '" & [Forms]![Item_Master]![Sea
& "([Size] = " & [Forms]![Item_Master]![Sea
ASKER
correction.
I understand the DIM statement that defines the string. Where I am confused is how to invoke it from the DCOUNT function.
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
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.
Once the debugging process is complete, I remove those statements.
ASKER
Sub SearchBox()
Dim strCriteria As String
strCriteria = "([PLU] Like '*" & [Forms]![Item_Master]![Sea rchField] & "*') OR " _
& "([Description] = '" & [Forms]![Item_Master]![Sea rchField] & ")"
Debug.Print strCriteria
If DCount("*", "[tbl_Item]", strCriteria) = 0 Then
Call Message
Else
DoCmd.ApplyFilter(,where ([PLU] Like "*" & [Forms]![Item_Master]![Sea rchField] & "*" Or [Description] Like "*" & [Forms]![Item_Master]![Sea rchField] & "*",)
End If
End Sub
Dim strCriteria As String
strCriteria = "([PLU] Like '*" & [Forms]![Item_Master]![Sea
& "([Description] = '" & [Forms]![Item_Master]![Sea
Debug.Print strCriteria
If DCount("*", "[tbl_Item]", strCriteria) = 0 Then
Call Message
Else
DoCmd.ApplyFilter(,where ([PLU] Like "*" & [Forms]![Item_Master]![Sea
End If
End Sub
ASKER
One more thing! What is wrong with my ELSE statement syntax?
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
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]![Sea rchField] & "*') OR " _
& "([Description] = '" & [Forms]![Item_Master]![Sea rchField] & ")"
If DCount("*", "[tbl_Item]", strCriteria) = 0 Then
Call message
Else
[Forms]![Item_Master].Filt er = strCriteria
[Forms]![Item_Master].Filt erOn = True
End If
End Function
(I am searching for PLU 3013)
Function SearchBox()
Dim strCriteria As String
strCriteria = "([PLU] Like '*" & [Forms]![Item_Master]![Sea
& "([Description] = '" & [Forms]![Item_Master]![Sea
If DCount("*", "[tbl_Item]", strCriteria) = 0 Then
Call message
Else
[Forms]![Item_Master].Filt
[Forms]![Item_Master].Filt
End If
End Function
SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
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:
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