Re: puzzling results from dlookup

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

--
Allen Browne - Microsoft MVP. Perth, Western Australia.
Tips for Access users - http://allenbrowne.com/tips.html
Reply to group, rather than allenbrowne at mvps dot org.

"kaosyeti@xxxxxxxxxxx via AccessMonster.com" <u15580@uwe> wrote in message
news:61b0a1bcbf943@xxxxxx
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.


.

Relevant Pages

  • RE: regular expressions. Order matters?
    ... order does matter in a Regex. ... That way, if it can enclose the word in square brackets, it will. ... > I am trying to create a regular expression that will let me know if a string ...
    (microsoft.public.dotnet.languages.csharp)
  • Help DLL calling in Excel 2007 crashes
    ... functions calls a function in a DLL and when that DLL function returns, ... VBA continues executing by starting the same VBA function over. ... ByRef szCompany As String, ByRef szCode As String) As Long ...
    (microsoft.public.excel.programming)
  • Re: how to break apart a large table field into smaller ones in SQL
    ... If your data elements are always separated by a comma you can use a VBA function and the split function to return the parts. ... You could then parse that down to two field with Red and No if you needed to using standard VBA string functions ... ' Procedure: getSection ... i have a memo field in a table ...
    (microsoft.public.access.queries)
  • Re: Possible to do a "wildcard replace" in Word?
    ... > strip off the square brackets and replace them with a preceding ... Your search string should be ... "type a wildcard character" inside that article. ... Now you see why I'm famous for rubbishing 'help'? ...
    (microsoft.public.mac.office.word)
  • Re: Display content from different enteries from the same table
    ... Use a VBA function to concatenate the values form the related table into a string. ... Perth, Western Australia ... Tips for Access users - http://allenbrowne.com/tips.html ...
    (microsoft.public.access.queries)
Loading