puzzling results from dlookup

[kaosyeti]

hey... i don't code a whole lot but i'm getting a funny reaction from my
access 2003 from a dlookup. here's my line of code:

DLookup("1v2", "tblpicks", "[week] = 1 and [player] = " & Chr(34) & Me.
txtboxplayer & Chr(34) & " and [year] = 2006")

this is a table where 1v2 is a yes/no field, currently valued at 'no' or '0'.
txtboxplayer is a text field with a first and last name (with a space between,
if that matters) and week and year are just integers.

the puzzling part is that it keeps telling me that i'm missing an operator
and if i put that exact line of code into my immediate window, it tells me
that i'm missing an equal sign. i don't see where, but i am REALLY tired
right now. am i crazy? thanks for looking.

 

[Allen Browne]

There are several possible issues here.

1. Since the field name begins with a number, use square brackets around the
name.

2. Year is a VBA function, and it will confuse Access if you use it as a
field name. In this context, you can probably get away with it by specifying
that you want the year field from the tblpicks table (and therefore not the
Year function from the VBA library.)

3. Is there are field named player in the table? If not the DLookup() won't
be able to match this.

4. Try breaking the criteria (3rd argument) into a separate string, so you
can print the results to the immediate window. Then when it fails you can
press Ctrl+G to see the result and understand what's wrong, i.e. what Access
thinks is a missing operator.

5. The brackets are optional, but can help you and Access to ensure the
Criteria string is correctly formed.

6. Be sure to return the result to a Variant to handle the situation where
there is no match. Alternatively, use Nz().

Dim bResult As Boolean
Dim strWhere As String
strWhere = "([week] = 1) and ([player] = " & Chr(34) & _
Me.txtboxplayer & Chr(34) & ") and (tblpicks.[year] = 2006)"
Debug.Print strWhere
bResult = Nz(DLookup("[1v2]", "tblpicks", strWhere), False)

The field named Year will still give you problems elsewhere in your
database. I suggest you rename it to something like PlayerYear.

There is a new free utility to help you identify where you have used
reserved words as field names, and other issues with your data structure.
See:
Database Issue Checker
at:
http://allenbrowne.com/AppIssueChecker.html