My DLookups work on forms but not in queries

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

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.
 
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.
 
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.

Duane Hookom said:
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.
--
Duane Hookom
MS Access MVP



PeteyP said:
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.
Click to expand...
Click to expand...
 
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!

--
Doug Steele, Microsoft Access MVP

(no private e-mails, please)


PeteyP said:
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.

Duane Hookom said:
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.
--
Duane Hookom
MS Access MVP



PeteyP said:
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.
Click to expand...
Click to expand...
Click to expand...
 
Back
Top