Re: dlookup problem

Tech Tip: Click here to run a free scan for Windows Errors and optimize PC performance

Glad you found the problem.

--

Ken Snell
<MS ACCESS MVP>

"Dan D." <DanD@xxxxxxxxxxxxxxxxxxxxxxxxx> wrote in message
news:7BFC336E-EB20-4818-A0EA-592793D1EF77@xxxxxxxxxxxxxxxx
> You did it Ken. I forgot that in Access the table is name differently than
> it
> is in SQL Server. In SQL Server the table is called "EMPLOYEE" but in
> Access
> it is called "SYSADM_EMPLOYEE".
>
> Thanks a lot,
> --
> Dan D.
>
>
> "Ken Snell (MVP)" wrote:
>
>> Try running the DLookup expression that you're using from the VBE
>> Immediate
>> Window... if an error exists, you'll get an error message from ACCESS:
>>
>> ?DLookUp("[EMP_CODE]","EMPLOYEE","[EMP_CODE] = 'ddonah' ")
>>
>> --
>>
>> Ken Snell
>> <MS ACCESS MVP>
>>
>>
>> "Dan D." <DanD@xxxxxxxxxxxxxxxxxxxxxxxxx> wrote in message
>> news:7B64417D-8663-4DFD-8112-33E0FA381F58@xxxxxxxxxxxxxxxx
>> >I tried that but I still get the "#ERROR".
>> > --
>> > Dan D.
>> >
>> >
>> > "Ken Snell (MVP)" wrote:
>> >
>> >> Try removing the [ ] characters from around the table name:
>> >>
>> >> =DLookUp("[EMP_CODE]","EMPLOYEE","[EMP_CODE] = 'ddonah' ")
>> >>
>> >> I have found that ACCESS sometimes thinks the [ ] characters are part
>> >> of
>> >> the
>> >> table's name when there are no "non-letter" and "non-number"
>> >> characters
>> >> in
>> >> the name.
>> >> --
>> >>
>> >> Ken Snell
>> >> <MS ACCESS MVP>
>> >>
>> >>
>> >> "Dan D." <DanD@xxxxxxxxxxxxxxxxxxxxxxxxx> wrote in message
>> >> news:8F634224-BBB5-4B2A-A2F9-E3EB4800C8E0@xxxxxxxxxxxxxxxx
>> >> > Yes, EMP_CODE is a text field. If you'll notice the query in my
>> >> > original
>> >> > post, there are single quotes around "nah". But even using single
>> >> > quotes,
>> >> > it
>> >> > doesn't work. I also tried it without single quotes.
>> >> >
>> >> > Thanks,
>> >> > --
>> >> > Dan D.
>> >> >
>> >> >
>> >> > "Ken Snell (MVP)" wrote:
>> >> >
>> >> >> Is EMP_CODE a text field? If yes, then the DLookup syntax you have
>> >> >> should
>> >> >> work. If it's a numeric field, lose the ' characters that
>> >> >> deliminate
>> >> >> ddonah
>> >> >> value.
>> >> >>
>> >> >> However, you state something about using variables from a form. A
>> >> >> report
>> >> >> cannot read the variables that a form is using unless those
>> >> >> variables
>> >> >> are
>> >> >> declared as Public in the Declarations section of the form's
>> >> >> module,
>> >> >> and
>> >> >> unless the form is open, and unless you refer to the variable as
>> >> >> Forms!FormName.VariableName
>> >> >>
>> >> >> --
>> >> >>
>> >> >> Ken Snell
>> >> >> <MS ACCESS MVP>
>> >> >>
>> >> >> "Dan D." <DanD@xxxxxxxxxxxxxxxxxxxxxxxxx> wrote in message
>> >> >> news:73FBBE58-95E6-4C26-9411-CBA8803B5BE3@xxxxxxxxxxxxxxxx
>> >> >> > I'm trying to use dlookup in the control source of a text field
>> >> >> > in
>> >> >> > the
>> >> >> > header
>> >> >> > section
>> >> >> > of a report. If I used a query to get the result it would be
>> >> >> > this:
>> >> >> > select EMP_CODE from EMPLOYEE WHERE EMP_CODE = 'nah'
>> >> >> >
>> >> >> > The dlookup syntax that I'm using is this:
>> >> >> > =DLookUp("[EMP_CODE]","[EMPLOYEE]","[EMP_CODE] = 'ddonah' ")
>> >> >> >
>> >> >> > I get a "#error" when I run the report. This is just a test to
>> >> >> > see
>> >> >> > if I
>> >> >> > can
>> >> >> > get anything to show up on the report. Eventually
>> >> >> > I want to use a dlookup using variables that will come from a
>> >> >> > form.
>> >> >> > I
>> >> >> > don't
>> >> >> > know if it
>> >> >> > makes any differenct but the table that I'm trying to get the
>> >> >> > data
>> >> >> > from
>> >> >> > is
>> >> >> > not used
>> >> >> > anywhere else in the report.
>> >> >> >
>> >> >> > Thanks,
>> >> >> > --
>> >> >> > Dan D.
>> >> >>
>> >> >>
>> >> >>
>> >>
>> >>
>> >>
>>
>>
>>


.

Relevant Pages

  • Re: dlookup problem
    ... In SQL Server the table is called "EMPLOYEE" but in Access ... you'll get an error message from ACCESS: ... > <MS ACCESS MVP> ... >> Dan D. ...
    (microsoft.public.access.reports)
  • Re: Incorrect syntax near the keyword THEN.
    ... Duane Hookom ... MS Access MVP ... I get this error message: ... I am learning SQL Server. ...
    (microsoft.public.access.tablesdbdesign)
  • Re: what does "ingnore duplicate key" do when creating an index
    ... Controls what happens when an attempt is made to insert a duplicate key ... creates a duplicate key is executed, SQL Server issues a warning and ignores ... error message and rolls back the entire INSERT statement. ... "Dan D." ...
    (microsoft.public.sqlserver.server)
  • Re: Connecting to a sql server database
    ... You are trying to use integrated security in your app (as is shown in the ... line numbered code where the connection string is shown). ... sql server, hence integrated security fails. ... > causing the error message and what the correction for this. ...
    (microsoft.public.dotnet.general)
  • Re: Invalid OLEVERB structure error 80040000 on DTS job
    ... >> Allan Mitchell MCSE,MCDBA, (Microsoft SQL Server MVP) ... >- I copied this DTS package from a 32 bit Windows Server 2003 system. ... >I would only get that error message in the Job History logs though. ...
    (microsoft.public.sqlserver.dts)