What is wrong with this formular

A

Ayo

=IIf(IsNull([Forms]![Project Number]) And IsNull([Forms]![Task Number]),"
",DLookUp("[Project]","[SITE LIST]","[Project Number] = Forms![Project
Number]" And "[Task Number] = Forms![Task Number]"))

The formular is surpose to perform the DLookup only if the are values in the
[Forms]![Project Number] and ([Forms]![Task Number], but that is not what is
happening. Can someone tell me what it is I am doing wrong? Thank you.
 
J

John Mishefske

Ayo said:
=IIf(IsNull([Forms]![Project Number]) And IsNull([Forms]![Task Number]),"
",DLookUp("[Project]","[SITE LIST]","[Project Number] = Forms![Project
Number]" And "[Task Number] = Forms![Task Number]"))

The formular is surpose to perform the DLookup only if the are values in the
[Forms]![Project Number] and ([Forms]![Task Number], but that is not what is
happening. Can someone tell me what it is I am doing wrong? Thank you.
Click to expand...

I saw this:

1) You reference the Forms collection but don't specify a form name
(assuming "Project Number" and "Task Number" are controls on some form).

If you form's name was "frmProject" then a proper reference would look
like this:
IIf(IsNull([Forms]![frmProject]![Project Number]) Or
IsNull([Forms]![frmProject]![Task Number])

2) When you check if the Project Number or Task Number is Null you'll
want to return "" (empty string) if either of them is Null so you want
to use " Or " when checking for IsNull() in both controls.
=IIf(IsNull([Forms]![frmProject]![Project Number]) Or
IsNull([Forms]![frmProject]![Task Number])

3) When doing the DLookup() I'd put the control references outside of
the quotes like this:

DLookUp("[Project]","[SITE LIST]","[Project Number] = " &
Forms![frmProject]![Project Number] & " And [Task Number] = " &
Forms![frmProject]![Task Number]))

Assumptions:

- you form (whatever its name is) is open when this is evaluated.
- "Project Number" and "Task Number" are numbers and not text.

Considering all this try this:

=IIf(IsNull([Forms]![frmProject]![Project Number]) Or
IsNull([Forms]![frmProject]![Task Number])
"",
DLookUp("[Project]","[SITE LIST]","[Project Number] = " &
Forms![frmProject]![Project Number] & " And [Task Number] = " &
Forms![frmProject]![Task Number]))

Replace "frmProject" with your form name.


--
'--------------------------
' John Mishefske
' UtterAccess Editor
' 2007 Microsoft MVP
'--------------------------
 
A

Ayo

I had problems importing the Task Numbers as Text because they had leading
zeros, ie, 0023 or 0006. So I made them text. Are you saying I can't use this
unless Project Number and Task Number are Numbers and not text?

John Mishefske said:
Ayo said:
=IIf(IsNull([Forms]![Project Number]) And IsNull([Forms]![Task Number]),"
",DLookUp("[Project]","[SITE LIST]","[Project Number] = Forms![Project
Number]" And "[Task Number] = Forms![Task Number]"))

The formular is surpose to perform the DLookup only if the are values in the
[Forms]![Project Number] and ([Forms]![Task Number], but that is not what is
happening. Can someone tell me what it is I am doing wrong? Thank you.
Click to expand...

I saw this:

1) You reference the Forms collection but don't specify a form name
(assuming "Project Number" and "Task Number" are controls on some form).

If you form's name was "frmProject" then a proper reference would look
like this:
IIf(IsNull([Forms]![frmProject]![Project Number]) Or
IsNull([Forms]![frmProject]![Task Number])

2) When you check if the Project Number or Task Number is Null you'll
want to return "" (empty string) if either of them is Null so you want
to use " Or " when checking for IsNull() in both controls.
=IIf(IsNull([Forms]![frmProject]![Project Number]) Or
IsNull([Forms]![frmProject]![Task Number])

3) When doing the DLookup() I'd put the control references outside of
the quotes like this:

DLookUp("[Project]","[SITE LIST]","[Project Number] = " &
Forms![frmProject]![Project Number] & " And [Task Number] = " &
Forms![frmProject]![Task Number]))

Assumptions:

- you form (whatever its name is) is open when this is evaluated.
- "Project Number" and "Task Number" are numbers and not text.

Considering all this try this:

=IIf(IsNull([Forms]![frmProject]![Project Number]) Or
IsNull([Forms]![frmProject]![Task Number])
"",
DLookUp("[Project]","[SITE LIST]","[Project Number] = " &
Forms![frmProject]![Project Number] & " And [Task Number] = " &
Forms![frmProject]![Task Number]))

Replace "frmProject" with your form name.


--
'--------------------------
' John Mishefske
' UtterAccess Editor
' 2007 Microsoft MVP
'--------------------------
Click to expand...
 
J

John Mishefske

Ayo said:
I had problems importing the Task Numbers as Text because they had leading
zeros, ie, 0023 or 0006. So I made them text. Are you saying I can't use this
unless Project Number and Task Number are Numbers and not text?
Click to expand...

No. Just that my answer is only valid if they were numbers. With text
you need to delimit the text properly within the expression.

I'll now assume that "Project Number" is a number but "Task Number" is
text. Then my suggested answer looks like this (this will wrap in here,
you need it on one line in your solution):

=IIf(IsNull([Forms]![frmProject]![Project Number]) Or
IsNull([Forms]![frmProject]![Task Number])
"",
DLookUp("[Project]","[SITE LIST]","[Project Number] = " &
Forms![frmProject]![Project Number] & " And [Task Number] = """ &
Forms![frmProject]![Task Number] & """))

I've now wrapped a double quote around the value provided by
Forms![frmProject]![Task Number] with the DLookup().

Text must be delimited, numbers don't.

Still replace "frmProject" with your form name.

--
'--------------------------
' John Mishefske
' UtterAccess Editor
' 2007 Microsoft MVP
'--------------------------
 
A

Ayo

Now I'm getting an error message "The expression you entered has an invalid
string"

John Mishefske said:
Ayo said:
I had problems importing the Task Numbers as Text because they had leading
zeros, ie, 0023 or 0006. So I made them text. Are you saying I can't use this
unless Project Number and Task Number are Numbers and not text?
Click to expand...

No. Just that my answer is only valid if they were numbers. With text
you need to delimit the text properly within the expression.

I'll now assume that "Project Number" is a number but "Task Number" is
text. Then my suggested answer looks like this (this will wrap in here,
you need it on one line in your solution):

=IIf(IsNull([Forms]![frmProject]![Project Number]) Or
IsNull([Forms]![frmProject]![Task Number])
"",
DLookUp("[Project]","[SITE LIST]","[Project Number] = " &
Forms![frmProject]![Project Number] & " And [Task Number] = """ &
Forms![frmProject]![Task Number] & """))

I've now wrapped a double quote around the value provided by
Forms![frmProject]![Task Number] with the DLookup().

Text must be delimited, numbers don't.

Still replace "frmProject" with your form name.

--
'--------------------------
' John Mishefske
' UtterAccess Editor
' 2007 Microsoft MVP
'--------------------------
Click to expand...
 
J

John Mishefske

John said:
=IIf(IsNull([Forms]![frmProject]![Project Number]) Or
IsNull([Forms]![frmProject]![Task Number])
"",
DLookUp("[Project]","[SITE LIST]","[Project Number] = " &
Forms![frmProject]![Project Number] & " And [Task Number] = """ &
Forms![frmProject]![Task Number] & """))
Click to expand...

One small mistake I just saw - the last three double quotes should be four:

=IIf(IsNull([Forms]![frmProject]![Project Number]) Or
IsNull([Forms]![frmProject]![Task Number])
"",
DLookUp("[Project]","[SITE LIST]","[Project Number] = " &
Forms![frmProject]![Project Number] & " And [Task Number] = """ &
Forms![frmProject]![Task Number] & """"))


--
'--------------------------
' John Mishefske
' UtterAccess Editor
' 2007 Microsoft MVP
'--------------------------
 
J

John Mishefske

Ayo said:
Now I'm getting an error message "The expression you entered has an invalid
string"
Click to expand...

Yeah, sorry - see my note posted about the same time as yours. I forgot
a double quote near the end of the expression.

--
'--------------------------
' John Mishefske
' UtterAccess Editor
' 2007 Microsoft MVP
'--------------------------
 
A

Ayo

Still didn't work. Now I have "#Error" in the textbox

John Mishefske said:
John said:
=IIf(IsNull([Forms]![frmProject]![Project Number]) Or
IsNull([Forms]![frmProject]![Task Number])
"",
DLookUp("[Project]","[SITE LIST]","[Project Number] = " &
Forms![frmProject]![Project Number] & " And [Task Number] = """ &
Forms![frmProject]![Task Number] & """))
Click to expand...

One small mistake I just saw - the last three double quotes should be four:

=IIf(IsNull([Forms]![frmProject]![Project Number]) Or
IsNull([Forms]![frmProject]![Task Number])
"",
DLookUp("[Project]","[SITE LIST]","[Project Number] = " &
Forms![frmProject]![Project Number] & " And [Task Number] = """ &
Forms![frmProject]![Task Number] & """"))


--
'--------------------------
' John Mishefske
' UtterAccess Editor
' 2007 Microsoft MVP
'--------------------------
Click to expand...
 
J

John Mishefske

Ayo said:
Still didn't work. Now I have "#Error" in the textbox
Click to expand...

OK. Debug time. Type Ctrl-G to open the immediate window.

Type these lines and hit <enter>. See if any of these result in an error
(replace "frmProject" with your form name):

? Forms![frmProject]![Project Number]


? Forms![frmProject]![Task Number]


? DLookUp("[Project]","[SITE LIST]","[Project Number] = " &
Forms![frmProject]![Project Number] & " And [Task Number] = """ &
Forms![frmProject]![Task Number] & """")


Aaaah... bet you have no matching record. Try this:


=IIf(IsNull([Forms]![frmProject]![Project Number]) Or
IsNull([Forms]![frmProject]![Task Number])
Click to expand...
"",
Nz(DLookUp("[Project]","[SITE LIST]","[Project Number] = " &
Forms![frmProject]![Project Number] & " And [Task Number] = """ &
Forms![frmProject]![Task Number] & """"), "")

Here I wrapped the DLookup() call within Nz() so that if no match was
found you'll get the empty string.

See if that works.

--
'--------------------------
' John Mishefske
' UtterAccess Editor
' 2007 Microsoft MVP
'--------------------------
 
A

Ayo

Thanks John for everything. I was able to figure out another way to do what I
was trying to do and it works great.

John Mishefske said:
Ayo said:
Still didn't work. Now I have "#Error" in the textbox
Click to expand...

OK. Debug time. Type Ctrl-G to open the immediate window.

Type these lines and hit <enter>. See if any of these result in an error
(replace "frmProject" with your form name):

? Forms![frmProject]![Project Number]


? Forms![frmProject]![Task Number]


? DLookUp("[Project]","[SITE LIST]","[Project Number] = " &
Forms![frmProject]![Project Number] & " And [Task Number] = """ &
Forms![frmProject]![Task Number] & """")


Aaaah... bet you have no matching record. Try this:


=IIf(IsNull([Forms]![frmProject]![Project Number]) Or
IsNull([Forms]![frmProject]![Task Number])
Click to expand...
"",
Nz(DLookUp("[Project]","[SITE LIST]","[Project Number] = " &
Forms![frmProject]![Project Number] & " And [Task Number] = """ &
Forms![frmProject]![Task Number] & """"), "")

Here I wrapped the DLookup() call within Nz() so that if no match was
found you'll get the empty string.

See if that works.

--
'--------------------------
' John Mishefske
' UtterAccess Editor
' 2007 Microsoft MVP
'--------------------------
Click to expand...
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Similar Threads

Combining two types of functions. Select Query and DLookup 4
Help with DLookup 5
Filter a column with blank values 13
What is wrong with this Select Statement 8
Access How to check that the value of a field in a new record is not present in 2 fields of the table? 0
dlookup and dao.recordset 4
Before-update reference on a field 5
Create a status designation from 5 date fields using "Not IsNull" 10

Top