Help with proper syntax on dcount

[MightyRoo]

Hello,
I would appreciate some better understanding of the proper syntax for lookups/dcounts etc
I always struggle, In this particular case I have....

Dim CategoryPath 
CategoryPath = CatBase & "/" & Me.Category1 & "/" & Me.Category2 & "/" & Me.Category3
JunkPath = CategoryPath
DoesCategoryExist = DCount("[CatName]", "TblMagCategory", "[CatName] = [Category3] AND [Path] = [JunkPath] ")

Ideally, I would not use my "JunkPath" and use CategoryPath instead but this cause an error.
The only way I can get this to work is by creating a text box on my form with the name JunkPath with no control source

I have searched and searched but must not be using the right keywords.

Does anyone have a resource with examples? I would appreciate a fix for this instance but want to stop struggling with my syntax.

Thank you!

Scott

 

[MajP]

[CatName] = [Category3] AND [Path] = [JunkPath] "
any variables need to be outside of parentheses. Things inside the parentheses cannot be evaluated. Also string values have to be enclosed by single quotes to be evaluated in SQL, and dates by number signs.

"[CatName] = '" & [Category3] & " AND [Path] = '" & [JunkPath] "'"

a simple example

dim x as integer
x = 7
debug.print "The number = " & X

that would print
The number = 7

Dim X as string
Dim strSql as string
x = "Red"
strSql = "Select * from some table where Color = '" & red & "'"
debug.print strSql

that returns: Select * from some table where color = 'red'

 

[MightyRoo]

MajP,
Thanks for the reply, I am still struggling to understand this and I am getting an error....

When I use.....

                    'Create CategoryPath
                            CategoryPath = CatBase & "/" & Me.Category1 & "/" & Me.Category2  'Variable is declared at top
                            JunkPath = CategoryPath 'this is a field on the form, I am tring to get rid of
                        DoesCategoryExist = DCount("[CatName]", "TblMagCategory", "[CatName] = [Category3] AND [Path] = [JunkPath] ")
                        'Line above Works, 'junkpath is a textbox on form created to make this work
                        'Ideal solution below, uses declared variable, no textbox on form
                       DoesCategoryExist = DCount("[CatName]", "TblMagCategory", "[CatName] = '" & [Category3] & " AND [Path] = '" & [CategoryPath] "'")
                        'The on the line above errors

I receive an error...
Compile Error: Expected: list separator or)

I would appreciate the help, I really need to master this syntax.

Thank you!

Scott

 

[PHV]

DoesCategoryExist = DCount("[CatName]", "TblMagCategory", "[CatName] = '" & [Category3] & "[!]'[/!] AND [Path] = '" & [CategoryPath][!] &[/!] "'")

Hope This Helps, PH.
FAQ219-2884
FAQ181-2886