My DLookups work on forms but not in queries

Thread view:

Enable EMail Alerts

Start New Thread

Thread rating:

PeteyP - 30 Jun 2006 16:59 GMT

The following to function statements work as calculated form controls but not
in any type of query that I have tried.

DLookUp("[DLA]![One]","DLA","[Dline]=[Caselog]![deadline]")

(DLookUp("[DLB]![Value]","DLB","[PlanD1]=[Dline]"))-(DLookUp("[DLB]![Value]","DLB","[ActD1]=[Dline]"))

Errors returned when attempting to run the queries include "Unknown" and
"Type Conversion Failure." I want the query results so that I can have these
calculations recorded in tables.

Thanks in advance for any help.

Reply to this Message

Duane Hookom - 30 Jun 2006 17:45 GMT

Try something like:
If Dline is numeric:
DLookUp("[One]","DLA","[Dline]=" & [Caselog]![deadline])
If Dline is text:
DLookUp("[One]","DLA","[Dline]=""" & [Caselog]![deadline] & """")

I don't know if it is a good idea for you to "have these calculations
recorded in tables". We normally don't store calculated values.

Signature

Duane Hookom
MS Access MVP

> The following to function statements work as calculated form controls but
> not
[quoted text clipped - 10 lines]
>
> Thanks in advance for any help.

Reply to this Message

PeteyP - 30 Jun 2006 18:57 GMT

Thanks. Actually Dline is a date. I am trying different things involving
the # but no luck yet.

You are right about storing the data in a table. I will keep the data in
the query, not in the table.

> Try something like:
> If Dline is numeric:
[quoted text clipped - 18 lines]
> >
> > Thanks in advance for any help.

Reply to this Message

Douglas J. Steele - 30 Jun 2006 19:17 GMT

DLookUp("[One]","DLA","[Dline]=" & Format([Caselog]![deadline],
"\#mm\/dd\/yyyy\#")

DO NOT change that to dd/mm/yyyy if you're not using mm/dd/yyyy as your
short date format: it will not work!

Signature

Doug Steele, Microsoft Access MVP
http://I.Am/DougSteele
(no private e-mails, please)

> Thanks. Actually Dline is a date. I am trying different things involving
> the # but no luck yet.
[quoted text clipped - 26 lines]
>> >
>> > Thanks in advance for any help.

Reply to this Message