Invalid string in expression builder

[MeSteve]

I am trying to put this:
=DLookUp("ContactName","tbl_Contacts", "ContactID =
Forms![frm_Projects]![subfrm_ProjectManagers].Form![ContactID] )

as a control source on a form. I get an error stating that a string can be
up to 2048 characters..., but this is only 118 characters. What is happening?

 

[BruceM]

There needs to be a closing quote in the criteria:
=DLookUp("ContactName","tbl_Contacts", "ContactID = " &
Forms![frm_Projects]![subfrm_ProjectManagers].Form![ContactID])

This assumes ContactID is a number.

 

[Duane Hookom]

If you count your quotes and parens and square brackets they should all be
even numbers. You are missing a quote. Try:

=DLookUp("ContactName","tbl_Contacts", "ContactID = " &
Forms![frm_Projects]![subfrm_ProjectManagers].Form![ContactID] )

If ContactID is text/string, try:
=DLookUp("ContactName","tbl_Contacts", "ContactID = """ &
Forms![frm_Projects]![subfrm_ProjectManagers].Form![ContactID] & """")

 

[MeSteve]

What would be the proper sytax for this:

=DLookUp("ContactName","tbl_Contacts","ContactID = " &
Forms!frm_Projects!subfrm_ProjectManagers.Form!ContactID & "AND RoleID = 1")

Or can I even do that?

If you count your quotes and parens and square brackets they should all be
even numbers. You are missing a quote. Try:

=DLookUp("ContactName","tbl_Contacts", "ContactID = " &
Forms![frm_Projects]![subfrm_ProjectManagers].Form![ContactID] )

If ContactID is text/string, try:
=DLookUp("ContactName","tbl_Contacts", "ContactID = """ &
Forms![frm_Projects]![subfrm_ProjectManagers].Form![ContactID] & """")


--
Duane Hookom
Microsoft Access MVP

I am trying to put this:
=DLookUp("ContactName","tbl_Contacts", "ContactID =
Forms![frm_Projects]![subfrm_ProjectManagers].Form![ContactID] )

as a control source on a form. I get an error stating that a string can be
up to 2048 characters..., but this is only 118 characters. What is happening?

 

[BruceM]

Have you tested that? It looks as if it would work, as long as RoleID is a
number. You may need a space after the double quote and before the AND ("
AND), but I think it would work as is.

What would be the proper sytax for this:

=DLookUp("ContactName","tbl_Contacts","ContactID = " &
Forms!frm_Projects!subfrm_ProjectManagers.Form!ContactID & "AND RoleID =
1")

Or can I even do that?

If you count your quotes and parens and square brackets they should all
be
even numbers. You are missing a quote. Try:

=DLookUp("ContactName","tbl_Contacts", "ContactID = " &
Forms![frm_Projects]![subfrm_ProjectManagers].Form![ContactID] )

If ContactID is text/string, try:
=DLookUp("ContactName","tbl_Contacts", "ContactID = """ &
Forms![frm_Projects]![subfrm_ProjectManagers].Form![ContactID] & """")


--
Duane Hookom
Microsoft Access MVP

I am trying to put this:
=DLookUp("ContactName","tbl_Contacts", "ContactID =
Forms![frm_Projects]![subfrm_ProjectManagers].Form![ContactID] )

as a control source on a form. I get an error stating that a string
can be
up to 2048 characters..., but this is only 118 characters. What is
happening?

 

[MeSteve]

Yes, and it returns Error#

=DLookUp("ContactName","tbl_Contacts","ContactID = " &
Forms!frm_Projects!subfrm_ProjectManagers.Form!ContactID & " AND RoleID = 1")

Have you tested that? It looks as if it would work, as long as RoleID is a
number. You may need a space after the double quote and before the AND ("
AND), but I think it would work as is.

What would be the proper sytax for this:

=DLookUp("ContactName","tbl_Contacts","ContactID = " &
Forms!frm_Projects!subfrm_ProjectManagers.Form!ContactID & "AND RoleID =
1")

Or can I even do that?

If you count your quotes and parens and square brackets they should all
be
even numbers. You are missing a quote. Try:

=DLookUp("ContactName","tbl_Contacts", "ContactID = " &
Forms![frm_Projects]![subfrm_ProjectManagers].Form![ContactID] )

If ContactID is text/string, try:
=DLookUp("ContactName","tbl_Contacts", "ContactID = """ &
Forms![frm_Projects]![subfrm_ProjectManagers].Form![ContactID] & """")


--
Duane Hookom
Microsoft Access MVP


:

I am trying to put this:
=DLookUp("ContactName","tbl_Contacts", "ContactID =
Forms![frm_Projects]![subfrm_ProjectManagers].Form![ContactID] )

as a control source on a form. I get an error stating that a string
can be
up to 2048 characters..., but this is only 118 characters. What is
happening?

 

[BruceM]

Is RoleID a number field in tbl_Contacts? What happens if you use "RoleID =
1" as the sole criteria?

Yes, and it returns Error#

=DLookUp("ContactName","tbl_Contacts","ContactID = " &
Forms!frm_Projects!subfrm_ProjectManagers.Form!ContactID & " AND RoleID =
1")

Have you tested that? It looks as if it would work, as long as RoleID is
a
number. You may need a space after the double quote and before the AND
("
AND), but I think it would work as is.

What would be the proper sytax for this:

=DLookUp("ContactName","tbl_Contacts","ContactID = " &
Forms!frm_Projects!subfrm_ProjectManagers.Form!ContactID & "AND RoleID
=
1")

Or can I even do that?

:

If you count your quotes and parens and square brackets they should
all
be
even numbers. You are missing a quote. Try:

=DLookUp("ContactName","tbl_Contacts", "ContactID = " &
Forms![frm_Projects]![subfrm_ProjectManagers].Form![ContactID] )

If ContactID is text/string, try:
=DLookUp("ContactName","tbl_Contacts", "ContactID = """ &
Forms![frm_Projects]![subfrm_ProjectManagers].Form![ContactID] &
"""")


--
Duane Hookom
Microsoft Access MVP


:

I am trying to put this:
=DLookUp("ContactName","tbl_Contacts", "ContactID =
Forms![frm_Projects]![subfrm_ProjectManagers].Form![ContactID] )

as a control source on a form. I get an error stating that a string
can be
up to 2048 characters..., but this is only 118 characters. What is
happening?

 

[MeSteve]

RoleID is a number, but not on tbl_Contacts, that is my problem (I think).
RoleID is on tbl_ContactProject and tbl_Roles

Is RoleID a number field in tbl_Contacts? What happens if you use "RoleID =
1" as the sole criteria?

Yes, and it returns Error#

=DLookUp("ContactName","tbl_Contacts","ContactID = " &
Forms!frm_Projects!subfrm_ProjectManagers.Form!ContactID & " AND RoleID =
1")

Have you tested that? It looks as if it would work, as long as RoleID is
a
number. You may need a space after the double quote and before the AND
("
AND), but I think it would work as is.

What would be the proper sytax for this:

=DLookUp("ContactName","tbl_Contacts","ContactID = " &
Forms!frm_Projects!subfrm_ProjectManagers.Form!ContactID & "AND RoleID
=
1")

Or can I even do that?

:

If you count your quotes and parens and square brackets they should
all
be
even numbers. You are missing a quote. Try:

=DLookUp("ContactName","tbl_Contacts", "ContactID = " &
Forms![frm_Projects]![subfrm_ProjectManagers].Form![ContactID] )

If ContactID is text/string, try:
=DLookUp("ContactName","tbl_Contacts", "ContactID = """ &
Forms![frm_Projects]![subfrm_ProjectManagers].Form![ContactID] &
"""")


--
Duane Hookom
Microsoft Access MVP


:

I am trying to put this:
=DLookUp("ContactName","tbl_Contacts", "ContactID =
Forms![frm_Projects]![subfrm_ProjectManagers].Form![ContactID] )

as a control source on a form. I get an error stating that a string
can be
up to 2048 characters..., but this is only 118 characters. What is
happening?

 

[MeSteve]

OK, I found the cause, but I don't know the solution. RoleID is not in
tbl_Contacts, it is in tbl_ContactProject so it is looking in the wrong table.

So what i need is ContactName from tbl_Contacts where RoleID =
subform_ProjectManagers.RoleID, but this needs to be cross-referenced thru
the lookup table ContractProject

Yes, and it returns Error#

=DLookUp("ContactName","tbl_Contacts","ContactID = " &
Forms!frm_Projects!subfrm_ProjectManagers.Form!ContactID & " AND RoleID = 1")

Have you tested that? It looks as if it would work, as long as RoleID is a
number. You may need a space after the double quote and before the AND ("
AND), but I think it would work as is.

What would be the proper sytax for this:

=DLookUp("ContactName","tbl_Contacts","ContactID = " &
Forms!frm_Projects!subfrm_ProjectManagers.Form!ContactID & "AND RoleID =
1")

Or can I even do that?

:

If you count your quotes and parens and square brackets they should all
be
even numbers. You are missing a quote. Try:

=DLookUp("ContactName","tbl_Contacts", "ContactID = " &
Forms![frm_Projects]![subfrm_ProjectManagers].Form![ContactID] )

If ContactID is text/string, try:
=DLookUp("ContactName","tbl_Contacts", "ContactID = """ &
Forms![frm_Projects]![subfrm_ProjectManagers].Form![ContactID] & """")


--
Duane Hookom
Microsoft Access MVP


:

I am trying to put this:
=DLookUp("ContactName","tbl_Contacts", "ContactID =
Forms![frm_Projects]![subfrm_ProjectManagers].Form![ContactID] )

as a control source on a form. I get an error stating that a string
can be
up to 2048 characters..., but this is only 118 characters. What is
happening?

 

[MeSteve]

This is what is semming to work:

=DLookUp("ContactName","tbl_Contacts","ContactID = " &
DLookUp("ContactID","tbl_ContactProject","RoleID = 1 AND ProjectID = " &
[Forms]![frm_Projects]![ProjectID]))

One more thing, how do I prevent getting an error if the value is Null?

RoleID is a number, but not on tbl_Contacts, that is my problem (I think).
RoleID is on tbl_ContactProject and tbl_Roles

Is RoleID a number field in tbl_Contacts? What happens if you use "RoleID =
1" as the sole criteria?

Yes, and it returns Error#

=DLookUp("ContactName","tbl_Contacts","ContactID = " &
Forms!frm_Projects!subfrm_ProjectManagers.Form!ContactID & " AND RoleID =
1")

:

Have you tested that? It looks as if it would work, as long as RoleID is
a
number. You may need a space after the double quote and before the AND
("
AND), but I think it would work as is.

What would be the proper sytax for this:

=DLookUp("ContactName","tbl_Contacts","ContactID = " &
Forms!frm_Projects!subfrm_ProjectManagers.Form!ContactID & "AND RoleID
=
1")

Or can I even do that?

:

If you count your quotes and parens and square brackets they should
all
be
even numbers. You are missing a quote. Try:

=DLookUp("ContactName","tbl_Contacts", "ContactID = " &
Forms![frm_Projects]![subfrm_ProjectManagers].Form![ContactID] )

If ContactID is text/string, try:
=DLookUp("ContactName","tbl_Contacts", "ContactID = """ &
Forms![frm_Projects]![subfrm_ProjectManagers].Form![ContactID] &
"""")


--
Duane Hookom
Microsoft Access MVP


:

I am trying to put this:
=DLookUp("ContactName","tbl_Contacts", "ContactID =
Forms![frm_Projects]![subfrm_ProjectManagers].Form![ContactID] )

as a control source on a form. I get an error stating that a string
can be
up to 2048 characters..., but this is only 118 characters. What is
happening?

 

[BruceM]

I don't know what you mean by "cross-referenced thru the lookup table", but
the domain is tbl_Contacts, so the criteria can evaluate only fields in that
table. If you can fashion a query containing both fields you can use the
qury as the domain.

OK, I found the cause, but I don't know the solution. RoleID is not in
tbl_Contacts, it is in tbl_ContactProject so it is looking in the wrong
table.

So what i need is ContactName from tbl_Contacts where RoleID =
subform_ProjectManagers.RoleID, but this needs to be cross-referenced thru
the lookup table ContractProject

Yes, and it returns Error#

=DLookUp("ContactName","tbl_Contacts","ContactID = " &
Forms!frm_Projects!subfrm_ProjectManagers.Form!ContactID & " AND RoleID =
1")

Have you tested that? It looks as if it would work, as long as RoleID
is a
number. You may need a space after the double quote and before the AND
("
AND), but I think it would work as is.

What would be the proper sytax for this:

=DLookUp("ContactName","tbl_Contacts","ContactID = " &
Forms!frm_Projects!subfrm_ProjectManagers.Form!ContactID & "AND
RoleID =
1")

Or can I even do that?

:

If you count your quotes and parens and square brackets they should
all
be
even numbers. You are missing a quote. Try:

=DLookUp("ContactName","tbl_Contacts", "ContactID = " &
Forms![frm_Projects]![subfrm_ProjectManagers].Form![ContactID] )

If ContactID is text/string, try:
=DLookUp("ContactName","tbl_Contacts", "ContactID = """ &
Forms![frm_Projects]![subfrm_ProjectManagers].Form![ContactID] &
"""")


--
Duane Hookom
Microsoft Access MVP


:

I am trying to put this:
=DLookUp("ContactName","tbl_Contacts", "ContactID =
Forms![frm_Projects]![subfrm_ProjectManagers].Form![ContactID] )

as a control source on a form. I get an error stating that a
string
can be
up to 2048 characters..., but this is only 118 characters. What
is
happening?

 

[BruceM]

I guess you could wrap the second DLookup in the Nz function, with a Value
If Null that is meaningless. The general idea is to construct the second
DLookup so that it returns some sort of value. If there are no records that
match the criteria the expression evaluates to Null, but since you are using
that value in the first criteria expression you have:
=DLookUp("ContactName","tbl_Contacts","ContactID = " & Null
Maybe something like:
Nz(DLookUp("ContactID","tbl_ContactProject","RoleID = 1 AND ProjectID = " &
[Forms]![frm_Projects]![ProjectID]),"999999999")
If the second DLookup returns null then 999999999 is substituted, so the
first DLookup becomes:
=DLookUp("ContactName","tbl_Contacts","ContactID = 999999999")
As long as there is no contact with ID 999999999 the expression will
evaluate to Null, and the text box will be blank. If you need something to
appear in that text box you can wrap the first DLookup in the Nz function.
I think you would do better to use a query that includes both tables, as I
suggested in another post.

This is what is semming to work:

=DLookUp("ContactName","tbl_Contacts","ContactID = " &
DLookUp("ContactID","tbl_ContactProject","RoleID = 1 AND ProjectID = " &
[Forms]![frm_Projects]![ProjectID]))

One more thing, how do I prevent getting an error if the value is Null?

RoleID is a number, but not on tbl_Contacts, that is my problem (I
think).
RoleID is on tbl_ContactProject and tbl_Roles

Is RoleID a number field in tbl_Contacts? What happens if you use
"RoleID =
1" as the sole criteria?

Yes, and it returns Error#

=DLookUp("ContactName","tbl_Contacts","ContactID = " &
Forms!frm_Projects!subfrm_ProjectManagers.Form!ContactID & " AND
RoleID =
1")

:

Have you tested that? It looks as if it would work, as long as
RoleID is
a
number. You may need a space after the double quote and before the
AND
("
AND), but I think it would work as is.

What would be the proper sytax for this:

=DLookUp("ContactName","tbl_Contacts","ContactID = " &
Forms!frm_Projects!subfrm_ProjectManagers.Form!ContactID & "AND
RoleID
=
1")

Or can I even do that?

:

If you count your quotes and parens and square brackets they
should
all
be
even numbers. You are missing a quote. Try:

=DLookUp("ContactName","tbl_Contacts", "ContactID = " &
Forms![frm_Projects]![subfrm_ProjectManagers].Form![ContactID] )

If ContactID is text/string, try:
=DLookUp("ContactName","tbl_Contacts", "ContactID = """ &
Forms![frm_Projects]![subfrm_ProjectManagers].Form![ContactID] &
"""")


--
Duane Hookom
Microsoft Access MVP


:

I am trying to put this:
=DLookUp("ContactName","tbl_Contacts", "ContactID =
Forms![frm_Projects]![subfrm_ProjectManagers].Form![ContactID]
)

as a control source on a form. I get an error stating that a
string
can be
up to 2048 characters..., but this is only 118 characters.
What is
happening?

 

[MeSteve]

OK, I have a query that has the ContactName, ProjectID and RoleID. I changed
the control source to:

=DLookUp("ContactName","qryProjectContacts","ProjectID=forms!frm_Projects!ProjectID AND RoleID = " & 2)

Working like a champ so far. Thanks. Should I still look at moving the
lookup to VBA?

I guess you could wrap the second DLookup in the Nz function, with a Value
If Null that is meaningless. The general idea is to construct the second
DLookup so that it returns some sort of value. If there are no records that
match the criteria the expression evaluates to Null, but since you are using
that value in the first criteria expression you have:
=DLookUp("ContactName","tbl_Contacts","ContactID = " & Null
Maybe something like:
Nz(DLookUp("ContactID","tbl_ContactProject","RoleID = 1 AND ProjectID = " &
[Forms]![frm_Projects]![ProjectID]),"999999999")
If the second DLookup returns null then 999999999 is substituted, so the
first DLookup becomes:
=DLookUp("ContactName","tbl_Contacts","ContactID = 999999999")
As long as there is no contact with ID 999999999 the expression will
evaluate to Null, and the text box will be blank. If you need something to
appear in that text box you can wrap the first DLookup in the Nz function.
I think you would do better to use a query that includes both tables, as I
suggested in another post.

This is what is semming to work:

=DLookUp("ContactName","tbl_Contacts","ContactID = " &
DLookUp("ContactID","tbl_ContactProject","RoleID = 1 AND ProjectID = " &
[Forms]![frm_Projects]![ProjectID]))

One more thing, how do I prevent getting an error if the value is Null?

RoleID is a number, but not on tbl_Contacts, that is my problem (I
think).
RoleID is on tbl_ContactProject and tbl_Roles

:

Is RoleID a number field in tbl_Contacts? What happens if you use
"RoleID =
1" as the sole criteria?

Yes, and it returns Error#

=DLookUp("ContactName","tbl_Contacts","ContactID = " &
Forms!frm_Projects!subfrm_ProjectManagers.Form!ContactID & " AND
RoleID =
1")

:

Have you tested that? It looks as if it would work, as long as
RoleID is
a
number. You may need a space after the double quote and before the
AND
("
AND), but I think it would work as is.

What would be the proper sytax for this:

=DLookUp("ContactName","tbl_Contacts","ContactID = " &
Forms!frm_Projects!subfrm_ProjectManagers.Form!ContactID & "AND
RoleID
=
1")

Or can I even do that?

:

If you count your quotes and parens and square brackets they
should
all
be
even numbers. You are missing a quote. Try:

=DLookUp("ContactName","tbl_Contacts", "ContactID = " &
Forms![frm_Projects]![subfrm_ProjectManagers].Form![ContactID] )

If ContactID is text/string, try:
=DLookUp("ContactName","tbl_Contacts", "ContactID = """ &
Forms![frm_Projects]![subfrm_ProjectManagers].Form![ContactID] &
"""")


--
Duane Hookom
Microsoft Access MVP


:

I am trying to put this:
=DLookUp("ContactName","tbl_Contacts", "ContactID =
Forms![frm_Projects]![subfrm_ProjectManagers].Form![ContactID]
)

as a control source on a form. I get an error stating that a
string
can be
up to 2048 characters..., but this is only 118 characters.
What is
happening?

 

[derek]

I am trying to put this:
=DLookUp("ContactName","tbl_Contacts", "ContactID =
Forms![frm_Projects]![subfrm_ProjectManagers].Form![ContactID] )

as a control source on a form. I get an error stating that a string can be
up to 2048 characters..., but this is only 118 characters. What is happening?

try

assuming contact id is a string not a number

DLookUp("ContactName","tbl_Contacts", "ContactID = '"&
Forms![frm_Projects]![subfrm_ProjectManagers].Form![ContactID] ) &"'")
example = ' B " & blah blah & "B 'B " minus the Bs of course


note the extra quotes and ampersands required the quotes are single
double then at the end is double single double

if it is a number then

DLookUp("ContactName","tbl_Contacts", "ContactID = " &
Forms![frm_Projects]![subfrm_ProjectManagers].Form![ContactID] )

note extra single quotes not needed and only one double quote and one
ampersand



with out the quotes and the ampersand
You are using the literal string
where ContactID = x

x = Forms![frm_Projects]![subfrm_ProjectManagers].Form![ContactID]

even that won't work with out single quotes

 

[BruceM]

I don't see how that can work for the criteria. If ProjectID is a number
you would need something like:
=DLookUp("ContactName","qryProjectContacts", _
"ProjectID = " & forms!frm_Projects!ProjectID & _
" AND RoleID = 2")

Note that the underscores are for readability only. They would not be part
of the expression.

This could be a calculated query field by going to a blank column in query
design view and substituting a made-up field name for the = sign:
FindProject: DLookup("ContactName" etc.)

However, it would probably be better to use criteria in the query expression
if you are going that route. For instance, forget the DLookup function.
Add ContactName, ProjectID, and RoleID to the query. In the ProjectID field
set the criteria to:
forms!frm_Projects!ProjectID
In the Role ID field use the numeral 2

In VBA you could assign the expression to a text box, maybe in the form's
Current event:
Me.SomeTextBox = DLookUp("ContactName","qryProjectContacts", _
"ProjectID = " & forms!frm_Projects!ProjectID & _
" AND RoleID = 2")
but I don't see an advantage to that approach.

OK, I have a query that has the ContactName, ProjectID and RoleID. I
changed
the control source to:

=DLookUp("ContactName","qryProjectContacts","ProjectID=forms!frm_Projects!ProjectID
AND RoleID = " & 2)

Working like a champ so far. Thanks. Should I still look at moving the
lookup to VBA?

I guess you could wrap the second DLookup in the Nz function, with a
Value
If Null that is meaningless. The general idea is to construct the second
DLookup so that it returns some sort of value. If there are no records
that
match the criteria the expression evaluates to Null, but since you are
using
that value in the first criteria expression you have:
=DLookUp("ContactName","tbl_Contacts","ContactID = " & Null
Maybe something like:
Nz(DLookUp("ContactID","tbl_ContactProject","RoleID = 1 AND ProjectID = "
&
[Forms]![frm_Projects]![ProjectID]),"999999999")
If the second DLookup returns null then 999999999 is substituted, so the
first DLookup becomes:
=DLookUp("ContactName","tbl_Contacts","ContactID = 999999999")
As long as there is no contact with ID 999999999 the expression will
evaluate to Null, and the text box will be blank. If you need something
to
appear in that text box you can wrap the first DLookup in the Nz
function.
I think you would do better to use a query that includes both tables, as
I
suggested in another post.

This is what is semming to work:

=DLookUp("ContactName","tbl_Contacts","ContactID = " &
DLookUp("ContactID","tbl_ContactProject","RoleID = 1 AND ProjectID = "
&
[Forms]![frm_Projects]![ProjectID]))

One more thing, how do I prevent getting an error if the value is Null?


:

RoleID is a number, but not on tbl_Contacts, that is my problem (I
think).
RoleID is on tbl_ContactProject and tbl_Roles

:

Is RoleID a number field in tbl_Contacts? What happens if you use
"RoleID =
1" as the sole criteria?

Yes, and it returns Error#

=DLookUp("ContactName","tbl_Contacts","ContactID = " &
Forms!frm_Projects!subfrm_ProjectManagers.Form!ContactID & " AND
RoleID =
1")

:

Have you tested that? It looks as if it would work, as long as
RoleID is
a
number. You may need a space after the double quote and before
the
AND
("
AND), but I think it would work as is.

What would be the proper sytax for this:

=DLookUp("ContactName","tbl_Contacts","ContactID = " &
Forms!frm_Projects!subfrm_ProjectManagers.Form!ContactID & "AND
RoleID
=
1")

Or can I even do that?

:

If you count your quotes and parens and square brackets they
should
all
be
even numbers. You are missing a quote. Try:

=DLookUp("ContactName","tbl_Contacts", "ContactID = " &

Forms![frm_Projects]![subfrm_ProjectManagers].Form![ContactID]
)

If ContactID is text/string, try:
=DLookUp("ContactName","tbl_Contacts", "ContactID = """ &

Forms![frm_Projects]![subfrm_ProjectManagers].Form![ContactID]
&
"""")


--
Duane Hookom
Microsoft Access MVP


:

I am trying to put this:
=DLookUp("ContactName","tbl_Contacts", "ContactID =
Forms![frm_Projects]![subfrm_ProjectManagers].Form![ContactID]
)

as a control source on a form. I get an error stating that
a
string
can be
up to 2048 characters..., but this is only 118 characters.
What is
happening?