Solved syntax error in DLookup (2 Viewers)

[cheberdy]

I have this code: Unit = DLookup("Unit", "Article", "Article number=" & Article & "OR EAN=" & Standard). I get an error syntax error(missing operator). But I do not know why. Standard and Article are strings.

 

[561414]

"OR EAN="
Is missing a space:
" OR EAN="

Use this before the Dlookup
Debug.Print "Article number=" & Article & "OR EAN=" & Standard

Check if you're getting numbers there, if you're getting strings, you need to add apostrophes. I just read those are strings, then try this:
DLookup("Unit", "Article", "Article number='" & Article & "' OR EAN='" & Standard & "'")

 

[Gasman]

Strings need to be surrounded by single quotes.
Put the criteria into a string variable and debug.print it untill you get it right.
Also need spaces between words.

 

[plog]

All the above plus you need to bracket [Article number]. It's why you only use alphanumeric and underscores in names.

 

[Moosak]

Using My magical tool (Smart Domain Functions Builder V1.0) :

DLookUp("[Unit]","[Article]","[Article number] ="& Article &" Or [EAN] ="& Standard &" ")

The tool can be found here :
Smart Domain Functions Builder V1.0

Here how I used it :

more details in the above link

 

[Pat Hartman]

I didn't download the tool but I have a question. Are you properly delimiting the expressions? Once you allow both AND and OR operators, it is imperative that you use parentheses to control the order in which an expression is evaluated.

For example:
a and b or c or d
Evaluates to:
(a and b) or c or d
Which is NOT the same as:
a and (b or c or d)

 

[Moosak]

I didn't download the tool but I have a question. Are you properly delimiting the expressions? Once you allow both AND and OR operators, it is imperative that you use parentheses to control the order in which an expression is evaluated.

For example:
a and b or c or d
Evaluates to:
(a and b) or c or d
Which is NOT the same as:
a and (b or c or d)

Actually I haven't examen that but most the time it works fine with me.
If I face any such case I will fix the tool then.
Try the tool and feedback me please.

Also I don't know if your example is explaining in real the expressions syntax.

 

[C3P420]

I have this code: Unit = DLookup("Unit", "Article", "Article number=" & Article & "OR EAN=" & Standard). I get an error syntax error(missing operator). But I do not know why. Standard and Article are strings.

Unit = DLookup("[Unit]", "Article", "[Article number] ='" & Article & "' OR [EAN] ='" & Standard & "'")

Since you're using strings you need to use the single quote ' character to surround the strings

If it helps, if Article were "12345" and Standard were "67890", the criteria portion of the Dlookup function would essentially be creating the string below

[Article number] = '12345' OR [EAN] = '67890'

 

[Pat Hartman]

You are offering this tool for the use of others. I am telling you that the tool has a problem if the user doesn't get to specify where the parentheses go. You should at least warn them OR limit the choices to only AND or only OR at any one time.

When building criteria you MUST understand the order of precedence. Logical operators have one just as math operators have one.

a+b*c+d
evaluates to:
a + (b * c) + d
You might have meant:
(a +b) * (c +d)
or
a + (b * (c + d))

You can't just arbitrarily use different relational operators and expect the expression to always work.

 

[Moosak]

a+b*c+d
evaluates to:
a + (b * c) + d
You might have meant:
(a +b) * (c +d)
or
a + (b * (c + d))

Thank you for alerting me to this point, I will try to fix it.
Can you give me the appropriate method when the two conditions ("and" / "or" ) combined

 

[Moosak]

I solve the matter of the parentheses

Now you can try using the tool

 

[Pat Hartman]

Can you give me the appropriate method when the two conditions ("and" / "or" ) combined

Only the person writing the expression knows how it should be evaluated. There is a defined order of evaluation so if you understand that, you will understand how every expression will be evaluated but as I showed with the examples, the defined order may not be what the coder intends.

I see the parentheses but I don't see how you have allowed the user to define where they go. Can you please explain how you decided where to put them.

 

[Moosak]

Can you please explain how you decided where to put them.

I told the code to put parentheses around the two criterias if it is ("or") between them, and if there are three or four criterias and ("or") between them, it puts the parentheses in front of the first criteria and at the end of the last criteria.

Is that correct ?

 

[Moosak]

Examples :
Cr = Criteria
Cr1 Cr2 Cr3 Cr4

( Cr1 or Cr2 ) and Cr3 and Cr4
Cr1 and ( Cr2 or Cr3 ) and Cr4
Cr1 and Cr2 and ( Cr3 or Cr4 )
( Cr1 or Cr2 or Cr3 ) and Cr4
( Cr1 or Cr2 or Cr3 or Cr4 )
( Cr1 or Cr2 ) and ( Cr3 or Cr4 )

That's how I've done it.

 

[Moosak]

I don't see how you have allowed the user to define where they go.

The tool also allows the user to modify the syntax and test it, through direct modification in the yellow box.
Try it yourself.

 

[Pat Hartman]

The examples you posted may not be what the user intends. As I said, you can NOT automatically generate the parentheses because there is no way for you to know the intention of the user. It is better to give the user a warning whenever both AND and OR are used that he needs to include parentheses in order to control the order of precedence. The best you could do if there are NO parentheses specified is to add them based on the rules for the order of precedence - which is NOT what you did above. Applying the order of precedence programmatically REQUIRES that you understand all the nuances and is not a trivial programming task since it involves recursion.

An alternate interpretation for sample 1 is:
Cr1 or ( Cr2 and Cr3 and Cr4 )
An alternate interpretation for sample 2 is:
( Cr1 and Cr2 ) or ( Cr3 and Cr4 )

It is important if you program to know this stuff. Operator precedence determines how operators are parsed concerning each other. Operators with higher precedence become the operands of operators with lower precedence.
()
NOT
AND
OR

So the DEFAULT interpretation of: Cr1 or Cr2 and Cr3 and Cr4 ==== Cr1 or ( Cr2 and ( Cr3 and Cr4)) ---- The innermost pair is Cr3 and Cr4, then that expression is AND'd with Cr2, and the whole expression is OR'd with Cr1. This should give you a clue why the Access QBE goes hog-wild with parentheses. It is using boolean logic to construct the expressions and doesn't try to simplify them to get rid of the extraneous pairs when it is done.

The default for Cr1 and Cr2 or Cr3 and Cr4 Is ( Cr1 and Cr2 ) or ( Cr3 and Cr4 ) ------ Each pair of AND's is created and the two expressions are OR'd

Consider how NOT works with and without parentheses.
Cr1 or Cr2 and not Cr3 and Cr4 ==== ( Cr1 or Cr2 ) and ( not( Cr3 ) and Cr4 )
It is quite possible, the user actually wanted ---- ( Cr1 or Cr2 ) and not( Cr3 and Cr4 )

If the starting expression includes (), the expressions inside each set of parentheses is evaluated and may result in additional parentheses being added.

Here's the best example I came up with explaining how evaluation actually works. It is for math but the principle is the same for boolean operations. Watch carefully to see what ends up as the operands for the division.

 

[Moosak]

I can see that the tool is not giving the user Every thing in the Final shape but It did most the job for him, Also it give him a chance to modify the given result as he wish. Okay I will make some little modification to the tool to give the user the choice of adding the parentheses where ever he want .

Thank you

 

[Pat Hartman]

The point I'm making and you keep missing, is not that your tool has to do everything but that you should NOT be fooling the user into thinking it does cover all bases. YOU didn't know there was any such thing as precedence with Boolean operators or math operators. But now that you know that there is a DEFAULT to the order in which an expression is evaluated and that default may not be what the user intends brings you much closer to a useful tool for the user. Order of operation is critical to the result an expression returns and you can help the user avoid invalid (or unexpected) results. Simply making the user aware of this by warning them of the problem and then giving them the ability to add parentheses is more than sufficient. Simply explaining the (), Not, And, Or hierarchy goes a long way toward avoiding problems. There are more logical operators but these four are the most commonly used. NAND, NOR, XOR are much less frequently used.