Concatenate Error Help

R

Ray Todd Jr

The following statement creates the following Run-Time Error:

Run-Time Error 3075:

Syntax error (missing operator) in query expression 'PropertyID=2
DefendantType="Owner"

SELECT DISTINCT taDEFENDANTS.PropertyID, Concatentate("SELECT FirstName & '
' & MiddleName & ' ' & LastName & ' ' & Suffix FROM taDEFENDANTS where
PropertyID=" & [PropertyID] & " DefendantType=""Owner""") AS Property_Owner
FROM taDEFENDANTTYPE INNER JOIN taDEFENDANTS ON
taDEFENDANTTYPE.DefendantTypeID = taDEFENDANTS.DefendantTypeID;

Please Help...

Thanks,

Ray.
 
J

Jerry Whittle

What's "Concatentate"? There's no built-in function with that name.

If it's a user defined function, you need to see what it's does. I should be
in a module.

One way to find out is to do a Ctrl + g to bring up the VBA window.

Next do a F2 to bring up the Object Browser.

Put Concatentate in the search window and see what if finds.
 
D

Douglas J. Steele

Not only that, but the SQL being passed to the function doesn't look valid
(it's missing an AND in the Where clause)

SELECT DISTINCT taDEFENDANTS.PropertyID, Concatentate("SELECT FirstName & '
' & MiddleName & ' ' & LastName & ' ' & Suffix FROM taDEFENDANTS where
PropertyID=" & [PropertyID] & " AND DefendantType=""Owner""") AS
Property_Owner
FROM taDEFENDANTTYPE INNER JOIN taDEFENDANTS ON
taDEFENDANTTYPE.DefendantTypeID = taDEFENDANTS.DefendantTypeID;



--
Doug Steele, Microsoft Access MVP

(no private e-mails, please)


Jerry Whittle said:
What's "Concatentate"? There's no built-in function with that name.

If it's a user defined function, you need to see what it's does. I should
be
in a module.

One way to find out is to do a Ctrl + g to bring up the VBA window.

Next do a F2 to bring up the Object Browser.

Put Concatentate in the search window and see what if finds.
--
Jerry Whittle, Microsoft Access MVP
Light. Strong. Cheap. Pick two. Keith Bontrager - Bicycle Builder.


Ray Todd Jr said:
The following statement creates the following Run-Time Error:

Run-Time Error 3075:

Syntax error (missing operator) in query expression 'PropertyID=2
DefendantType="Owner"

SELECT DISTINCT taDEFENDANTS.PropertyID, Concatentate("SELECT FirstName &
'
' & MiddleName & ' ' & LastName & ' ' & Suffix FROM taDEFENDANTS where
PropertyID=" & [PropertyID] & " DefendantType=""Owner""") AS
Property_Owner
FROM taDEFENDANTTYPE INNER JOIN taDEFENDANTS ON
taDEFENDANTTYPE.DefendantTypeID = taDEFENDANTS.DefendantTypeID;

Please Help...

Thanks,

Ray.
Click to expand...
Click to expand...
 
D

Duane Hookom

I would probably use:
SELECT DISTINCT taDEFENDANTS.PropertyID, Concatentate("SELECT FirstName & '
' & MiddleName & ' ' & LastName & ' ' & Suffix FROM taDEFENDANTS where
PropertyID=" & [PropertyID] & " AND DefendantType='Owner'") AS
Property_Owner
FROM taDEFENDANTTYPE INNER JOIN taDEFENDANTS ON
taDEFENDANTTYPE.DefendantTypeID = taDEFENDANTS.DefendantTypeID;

Don't you have a table with unique PropertyID values rather than using
taDEFENDANTS in both the Concatenate() and the main query?


--
Duane Hookom
Microsoft Access MVP


Douglas J. Steele said:
Not only that, but the SQL being passed to the function doesn't look valid
(it's missing an AND in the Where clause)

SELECT DISTINCT taDEFENDANTS.PropertyID, Concatentate("SELECT FirstName & '
' & MiddleName & ' ' & LastName & ' ' & Suffix FROM taDEFENDANTS where
PropertyID=" & [PropertyID] & " AND DefendantType=""Owner""") AS
Property_Owner
FROM taDEFENDANTTYPE INNER JOIN taDEFENDANTS ON
taDEFENDANTTYPE.DefendantTypeID = taDEFENDANTS.DefendantTypeID;



--
Doug Steele, Microsoft Access MVP

(no private e-mails, please)


Jerry Whittle said:
What's "Concatentate"? There's no built-in function with that name.

If it's a user defined function, you need to see what it's does. I should
be
in a module.

One way to find out is to do a Ctrl + g to bring up the VBA window.

Next do a F2 to bring up the Object Browser.

Put Concatentate in the search window and see what if finds.
--
Jerry Whittle, Microsoft Access MVP
Light. Strong. Cheap. Pick two. Keith Bontrager - Bicycle Builder.


Ray Todd Jr said:
The following statement creates the following Run-Time Error:

Run-Time Error 3075:

Syntax error (missing operator) in query expression 'PropertyID=2
DefendantType="Owner"

SELECT DISTINCT taDEFENDANTS.PropertyID, Concatentate("SELECT FirstName &
'
' & MiddleName & ' ' & LastName & ' ' & Suffix FROM taDEFENDANTS where
PropertyID=" & [PropertyID] & " DefendantType=""Owner""") AS
Property_Owner
FROM taDEFENDANTTYPE INNER JOIN taDEFENDANTS ON
taDEFENDANTTYPE.DefendantTypeID = taDEFENDANTS.DefendantTypeID;

Please Help...

Thanks,

Ray.
Click to expand...
Click to expand...
Click to expand...
 
R

Ray Todd Jr

Duane and Douglas:

I updated the sql as indicated below. Duane, I did add the reference to the
PropertyID in the taPROPERTY table in the where statement.

Despite these changes (both with and without the reference to the
taPROPERTY.PropertyID field), I am now getting the following error:

Run-Time Error: 3061
Too Few Parameters. Expected 1.

SELECT DISTINCT taPROPERTY.PropertyID, Concatentate("SELECT FirstName & ' '
& MiddleName & ' ' & LastName & ' ' & Suffix FROM taDEFENDANTS where
PropertyID=" & taPROPERTY.[PropertyID] & " AND DefendantType=""Owner""") AS
Property_Owner
FROM taPROPERTY INNER JOIN (taDEFENDANTTYPE INNER JOIN taDEFENDANTS ON
taDEFENDANTTYPE.DefendantTypeID = taDEFENDANTS.DefendantTypeID) ON
taPROPERTY.PropertyID = taDEFENDANTS.PropertyID;

Thanks,

Ray


Duane Hookom said:
I would probably use:
SELECT DISTINCT taDEFENDANTS.PropertyID, Concatentate("SELECT FirstName & '
' & MiddleName & ' ' & LastName & ' ' & Suffix FROM taDEFENDANTS where
PropertyID=" & [PropertyID] & " AND DefendantType='Owner'") AS
Property_Owner
FROM taDEFENDANTTYPE INNER JOIN taDEFENDANTS ON
taDEFENDANTTYPE.DefendantTypeID = taDEFENDANTS.DefendantTypeID;

Don't you have a table with unique PropertyID values rather than using
taDEFENDANTS in both the Concatenate() and the main query?


--
Duane Hookom
Microsoft Access MVP


Douglas J. Steele said:
Not only that, but the SQL being passed to the function doesn't look valid
(it's missing an AND in the Where clause)

SELECT DISTINCT taDEFENDANTS.PropertyID, Concatentate("SELECT FirstName & '
' & MiddleName & ' ' & LastName & ' ' & Suffix FROM taDEFENDANTS where
PropertyID=" & [PropertyID] & " AND DefendantType=""Owner""") AS
Property_Owner
FROM taDEFENDANTTYPE INNER JOIN taDEFENDANTS ON
taDEFENDANTTYPE.DefendantTypeID = taDEFENDANTS.DefendantTypeID;



--
Doug Steele, Microsoft Access MVP

(no private e-mails, please)


Jerry Whittle said:
What's "Concatentate"? There's no built-in function with that name.

If it's a user defined function, you need to see what it's does. I should
be
in a module.

One way to find out is to do a Ctrl + g to bring up the VBA window.

Next do a F2 to bring up the Object Browser.

Put Concatentate in the search window and see what if finds.
--
Jerry Whittle, Microsoft Access MVP
Light. Strong. Cheap. Pick two. Keith Bontrager - Bicycle Builder.


:

The following statement creates the following Run-Time Error:

Run-Time Error 3075:

Syntax error (missing operator) in query expression 'PropertyID=2
DefendantType="Owner"

SELECT DISTINCT taDEFENDANTS.PropertyID, Concatentate("SELECT FirstName &
'
' & MiddleName & ' ' & LastName & ' ' & Suffix FROM taDEFENDANTS where
PropertyID=" & [PropertyID] & " DefendantType=""Owner""") AS
Property_Owner
FROM taDEFENDANTTYPE INNER JOIN taDEFENDANTS ON
taDEFENDANTTYPE.DefendantTypeID = taDEFENDANTS.DefendantTypeID;

Please Help...

Thanks,

Ray.
Click to expand...
Click to expand...
Click to expand...
 
J

John Spencer

Spelling error?
Concatenate instead of ConcatenTate
Note the extra "T".

John Spencer
Access MVP 2002-2005, 2007-2008
Center for Health Program Development and Management
University of Maryland Baltimore County
Duane and Douglas:

I updated the sql as indicated below. Duane, I did add the reference to the
PropertyID in the taPROPERTY table in the where statement.

Despite these changes (both with and without the reference to the
taPROPERTY.PropertyID field), I am now getting the following error:

Run-Time Error: 3061
Too Few Parameters. Expected 1.

SELECT DISTINCT taPROPERTY.PropertyID, Concatentate("SELECT FirstName & ' '
& MiddleName & ' ' & LastName & ' ' & Suffix FROM taDEFENDANTS where
PropertyID=" & taPROPERTY.[PropertyID] & " AND DefendantType=""Owner""") AS
Property_Owner
FROM taPROPERTY INNER JOIN (taDEFENDANTTYPE INNER JOIN taDEFENDANTS ON
taDEFENDANTTYPE.DefendantTypeID = taDEFENDANTS.DefendantTypeID) ON
taPROPERTY.PropertyID = taDEFENDANTS.PropertyID;

Thanks,

Ray


Duane Hookom said:
I would probably use:
SELECT DISTINCT taDEFENDANTS.PropertyID, Concatentate("SELECT FirstName & '
' & MiddleName & ' ' & LastName & ' ' & Suffix FROM taDEFENDANTS where
PropertyID=" & [PropertyID] & " AND DefendantType='Owner'") AS
Property_Owner
FROM taDEFENDANTTYPE INNER JOIN taDEFENDANTS ON
taDEFENDANTTYPE.DefendantTypeID = taDEFENDANTS.DefendantTypeID;

Don't you have a table with unique PropertyID values rather than using
taDEFENDANTS in both the Concatenate() and the main query?


--
Duane Hookom
Microsoft Access MVP


Douglas J. Steele said:
Not only that, but the SQL being passed to the function doesn't look valid
(it's missing an AND in the Where clause)

SELECT DISTINCT taDEFENDANTS.PropertyID, Concatentate("SELECT FirstName & '
' & MiddleName & ' ' & LastName & ' ' & Suffix FROM taDEFENDANTS where
PropertyID=" & [PropertyID] & " AND DefendantType=""Owner""") AS
Property_Owner
FROM taDEFENDANTTYPE INNER JOIN taDEFENDANTS ON
taDEFENDANTTYPE.DefendantTypeID = taDEFENDANTS.DefendantTypeID;



--
Doug Steele, Microsoft Access MVP

(no private e-mails, please)


What's "Concatentate"? There's no built-in function with that name.

If it's a user defined function, you need to see what it's does. I should
be
in a module.

One way to find out is to do a Ctrl + g to bring up the VBA window.

Next do a F2 to bring up the Object Browser.

Put Concatentate in the search window and see what if finds.
--
Jerry Whittle, Microsoft Access MVP
Light. Strong. Cheap. Pick two. Keith Bontrager - Bicycle Builder.


:

The following statement creates the following Run-Time Error:

Run-Time Error 3075:

Syntax error (missing operator) in query expression 'PropertyID=2
DefendantType="Owner"

SELECT DISTINCT taDEFENDANTS.PropertyID, Concatentate("SELECT FirstName &
'
' & MiddleName & ' ' & LastName & ' ' & Suffix FROM taDEFENDANTS where
PropertyID=" & [PropertyID] & " DefendantType=""Owner""") AS
Property_Owner
FROM taDEFENDANTTYPE INNER JOIN taDEFENDANTS ON
taDEFENDANTTYPE.DefendantTypeID = taDEFENDANTS.DefendantTypeID;

Please Help...

Thanks,

Ray.
Click to expand...
Click to expand...
Click to expand...
 
R

Ray Todd Jr

Hello John:

Thanks. I have corrected the spelling error. Unforunately, I am still
receiving the following error:

Run-Time Error 3061:

Too Few parameters. Expected 1.

Thanks,

Ray.

SELECT DISTINCT taPROPERTY.PropertyID, taDEFENDANTS.PropertyID,
Concatenate("SELECT FirstName & ' ' & MiddleName & ' ' & LastName & ' ' &
Suffix FROM taDEFENDANTS where PropertyID=" & taPROPERTY.[PropertyID] & "
AND DefendantType=""Owner""") AS Property_Owner
FROM taPROPERTY INNER JOIN (taDEFENDANTTYPE INNER JOIN taDEFENDANTS ON
taDEFENDANTTYPE.DefendantTypeID = taDEFENDANTS.DefendantTypeID) ON
taPROPERTY.PropertyID = taDEFENDANTS.PropertyID;


John Spencer said:
Spelling error?
Concatenate instead of ConcatenTate
Note the extra "T".

John Spencer
Access MVP 2002-2005, 2007-2008
Center for Health Program Development and Management
University of Maryland Baltimore County
Duane and Douglas:

I updated the sql as indicated below. Duane, I did add the reference to the
PropertyID in the taPROPERTY table in the where statement.

Despite these changes (both with and without the reference to the
taPROPERTY.PropertyID field), I am now getting the following error:

Run-Time Error: 3061
Too Few Parameters. Expected 1.

SELECT DISTINCT taPROPERTY.PropertyID, Concatentate("SELECT FirstName & ' '
& MiddleName & ' ' & LastName & ' ' & Suffix FROM taDEFENDANTS where
PropertyID=" & taPROPERTY.[PropertyID] & " AND DefendantType=""Owner""") AS
Property_Owner
FROM taPROPERTY INNER JOIN (taDEFENDANTTYPE INNER JOIN taDEFENDANTS ON
taDEFENDANTTYPE.DefendantTypeID = taDEFENDANTS.DefendantTypeID) ON
taPROPERTY.PropertyID = taDEFENDANTS.PropertyID;

Thanks,

Ray


Duane Hookom said:
I would probably use:
SELECT DISTINCT taDEFENDANTS.PropertyID, Concatentate("SELECT FirstName & '
' & MiddleName & ' ' & LastName & ' ' & Suffix FROM taDEFENDANTS where
PropertyID=" & [PropertyID] & " AND DefendantType='Owner'") AS
Property_Owner
FROM taDEFENDANTTYPE INNER JOIN taDEFENDANTS ON
taDEFENDANTTYPE.DefendantTypeID = taDEFENDANTS.DefendantTypeID;

Don't you have a table with unique PropertyID values rather than using
taDEFENDANTS in both the Concatenate() and the main query?


--
Duane Hookom
Microsoft Access MVP


:

Not only that, but the SQL being passed to the function doesn't look valid
(it's missing an AND in the Where clause)

SELECT DISTINCT taDEFENDANTS.PropertyID, Concatentate("SELECT FirstName & '
' & MiddleName & ' ' & LastName & ' ' & Suffix FROM taDEFENDANTS where
PropertyID=" & [PropertyID] & " AND DefendantType=""Owner""") AS
Property_Owner
FROM taDEFENDANTTYPE INNER JOIN taDEFENDANTS ON
taDEFENDANTTYPE.DefendantTypeID = taDEFENDANTS.DefendantTypeID;



--
Doug Steele, Microsoft Access MVP

(no private e-mails, please)


What's "Concatentate"? There's no built-in function with that name.

If it's a user defined function, you need to see what it's does. I should
be
in a module.

One way to find out is to do a Ctrl + g to bring up the VBA window.

Next do a F2 to bring up the Object Browser.

Put Concatentate in the search window and see what if finds.
--
Jerry Whittle, Microsoft Access MVP
Light. Strong. Cheap. Pick two. Keith Bontrager - Bicycle Builder.


:

The following statement creates the following Run-Time Error:

Run-Time Error 3075:

Syntax error (missing operator) in query expression 'PropertyID=2
DefendantType="Owner"

SELECT DISTINCT taDEFENDANTS.PropertyID, Concatentate("SELECT FirstName &
'
' & MiddleName & ' ' & LastName & ' ' & Suffix FROM taDEFENDANTS where
PropertyID=" & [PropertyID] & " DefendantType=""Owner""") AS
Property_Owner
FROM taDEFENDANTTYPE INNER JOIN taDEFENDANTS ON
taDEFENDANTTYPE.DefendantTypeID = taDEFENDANTS.DefendantTypeID;

Please Help...

Thanks,

Ray.
Click to expand...
Click to expand...
Click to expand...
 
R

Ray Todd Jr

The problem seems to be revolving around the literal value for the
DefendantType which is OWNER. This (OWNER) is a constant not a variable or a
field in a table, just a literal value that can be found in the DefendantType
field.


Ray Todd Jr said:
Hello John:

Thanks. I have corrected the spelling error. Unforunately, I am still
receiving the following error:

Run-Time Error 3061:

Too Few parameters. Expected 1.

Thanks,

Ray.

SELECT DISTINCT taPROPERTY.PropertyID, taDEFENDANTS.PropertyID,
Concatenate("SELECT FirstName & ' ' & MiddleName & ' ' & LastName & ' ' &
Suffix FROM taDEFENDANTS where PropertyID=" & taPROPERTY.[PropertyID] & "
AND DefendantType=""Owner""") AS Property_Owner
FROM taPROPERTY INNER JOIN (taDEFENDANTTYPE INNER JOIN taDEFENDANTS ON
taDEFENDANTTYPE.DefendantTypeID = taDEFENDANTS.DefendantTypeID) ON
taPROPERTY.PropertyID = taDEFENDANTS.PropertyID;


John Spencer said:
Spelling error?
Concatenate instead of ConcatenTate
Note the extra "T".

John Spencer
Access MVP 2002-2005, 2007-2008
Center for Health Program Development and Management
University of Maryland Baltimore County
Duane and Douglas:

I updated the sql as indicated below. Duane, I did add the reference to the
PropertyID in the taPROPERTY table in the where statement.

Despite these changes (both with and without the reference to the
taPROPERTY.PropertyID field), I am now getting the following error:

Run-Time Error: 3061
Too Few Parameters. Expected 1.

SELECT DISTINCT taPROPERTY.PropertyID, Concatentate("SELECT FirstName & ' '
& MiddleName & ' ' & LastName & ' ' & Suffix FROM taDEFENDANTS where
PropertyID=" & taPROPERTY.[PropertyID] & " AND DefendantType=""Owner""") AS
Property_Owner
FROM taPROPERTY INNER JOIN (taDEFENDANTTYPE INNER JOIN taDEFENDANTS ON
taDEFENDANTTYPE.DefendantTypeID = taDEFENDANTS.DefendantTypeID) ON
taPROPERTY.PropertyID = taDEFENDANTS.PropertyID;

Thanks,

Ray


:

I would probably use:
SELECT DISTINCT taDEFENDANTS.PropertyID, Concatentate("SELECT FirstName & '
' & MiddleName & ' ' & LastName & ' ' & Suffix FROM taDEFENDANTS where
PropertyID=" & [PropertyID] & " AND DefendantType='Owner'") AS
Property_Owner
FROM taDEFENDANTTYPE INNER JOIN taDEFENDANTS ON
taDEFENDANTTYPE.DefendantTypeID = taDEFENDANTS.DefendantTypeID;

Don't you have a table with unique PropertyID values rather than using
taDEFENDANTS in both the Concatenate() and the main query?


--
Duane Hookom
Microsoft Access MVP


:

Not only that, but the SQL being passed to the function doesn't look valid
(it's missing an AND in the Where clause)

SELECT DISTINCT taDEFENDANTS.PropertyID, Concatentate("SELECT FirstName & '
' & MiddleName & ' ' & LastName & ' ' & Suffix FROM taDEFENDANTS where
PropertyID=" & [PropertyID] & " AND DefendantType=""Owner""") AS
Property_Owner
FROM taDEFENDANTTYPE INNER JOIN taDEFENDANTS ON
taDEFENDANTTYPE.DefendantTypeID = taDEFENDANTS.DefendantTypeID;



--
Doug Steele, Microsoft Access MVP

(no private e-mails, please)


What's "Concatentate"? There's no built-in function with that name.

If it's a user defined function, you need to see what it's does. I should
be
in a module.

One way to find out is to do a Ctrl + g to bring up the VBA window.

Next do a F2 to bring up the Object Browser.

Put Concatentate in the search window and see what if finds.
--
Jerry Whittle, Microsoft Access MVP
Light. Strong. Cheap. Pick two. Keith Bontrager - Bicycle Builder.


:

The following statement creates the following Run-Time Error:

Run-Time Error 3075:

Syntax error (missing operator) in query expression 'PropertyID=2
DefendantType="Owner"

SELECT DISTINCT taDEFENDANTS.PropertyID, Concatentate("SELECT FirstName &
'
' & MiddleName & ' ' & LastName & ' ' & Suffix FROM taDEFENDANTS where
PropertyID=" & [PropertyID] & " DefendantType=""Owner""") AS
Property_Owner
FROM taDEFENDANTTYPE INNER JOIN taDEFENDANTS ON
taDEFENDANTTYPE.DefendantTypeID = taDEFENDANTS.DefendantTypeID;

Please Help...

Thanks,

Ray.
Click to expand...
Click to expand...
Click to expand...
 
R

Ray Todd Jr

One things that I don't think I have mentioned previously is that
DefendantType is in another table taDEFENDANTTYPE

so, I have three tables I am getting info from:

taPROPERTY
PropertyID -Auto

taDEFENDANTS
DefendantID -Auto
PropertyID -Long
FirstName -Text
DefendantTypeID -Long

taDefendantType
DefendantTypeID -Auto
DefendantType -Text

Ray Todd Jr said:
The problem seems to be revolving around the literal value for the
DefendantType which is OWNER. This (OWNER) is a constant not a variable or a
field in a table, just a literal value that can be found in the DefendantType
field.


Ray Todd Jr said:
Hello John:

Thanks. I have corrected the spelling error. Unforunately, I am still
receiving the following error:

Run-Time Error 3061:

Too Few parameters. Expected 1.

Thanks,

Ray.

SELECT DISTINCT taPROPERTY.PropertyID, taDEFENDANTS.PropertyID,
Concatenate("SELECT FirstName & ' ' & MiddleName & ' ' & LastName & ' ' &
Suffix FROM taDEFENDANTS where PropertyID=" & taPROPERTY.[PropertyID] & "
AND DefendantType=""Owner""") AS Property_Owner
FROM taPROPERTY INNER JOIN (taDEFENDANTTYPE INNER JOIN taDEFENDANTS ON
taDEFENDANTTYPE.DefendantTypeID = taDEFENDANTS.DefendantTypeID) ON
taPROPERTY.PropertyID = taDEFENDANTS.PropertyID;


John Spencer said:
Spelling error?
Concatenate instead of ConcatenTate
Note the extra "T".

John Spencer
Access MVP 2002-2005, 2007-2008
Center for Health Program Development and Management
University of Maryland Baltimore County

Ray Todd Jr wrote:
Duane and Douglas:

I updated the sql as indicated below. Duane, I did add the reference to the
PropertyID in the taPROPERTY table in the where statement.

Despite these changes (both with and without the reference to the
taPROPERTY.PropertyID field), I am now getting the following error:

Run-Time Error: 3061
Too Few Parameters. Expected 1.

SELECT DISTINCT taPROPERTY.PropertyID, Concatentate("SELECT FirstName & ' '
& MiddleName & ' ' & LastName & ' ' & Suffix FROM taDEFENDANTS where
PropertyID=" & taPROPERTY.[PropertyID] & " AND DefendantType=""Owner""") AS
Property_Owner
FROM taPROPERTY INNER JOIN (taDEFENDANTTYPE INNER JOIN taDEFENDANTS ON
taDEFENDANTTYPE.DefendantTypeID = taDEFENDANTS.DefendantTypeID) ON
taPROPERTY.PropertyID = taDEFENDANTS.PropertyID;

Thanks,

Ray


:

I would probably use:
SELECT DISTINCT taDEFENDANTS.PropertyID, Concatentate("SELECT FirstName & '
' & MiddleName & ' ' & LastName & ' ' & Suffix FROM taDEFENDANTS where
PropertyID=" & [PropertyID] & " AND DefendantType='Owner'") AS
Property_Owner
FROM taDEFENDANTTYPE INNER JOIN taDEFENDANTS ON
taDEFENDANTTYPE.DefendantTypeID = taDEFENDANTS.DefendantTypeID;

Don't you have a table with unique PropertyID values rather than using
taDEFENDANTS in both the Concatenate() and the main query?


--
Duane Hookom
Microsoft Access MVP


:

Not only that, but the SQL being passed to the function doesn't look valid
(it's missing an AND in the Where clause)

SELECT DISTINCT taDEFENDANTS.PropertyID, Concatentate("SELECT FirstName & '
' & MiddleName & ' ' & LastName & ' ' & Suffix FROM taDEFENDANTS where
PropertyID=" & [PropertyID] & " AND DefendantType=""Owner""") AS
Property_Owner
FROM taDEFENDANTTYPE INNER JOIN taDEFENDANTS ON
taDEFENDANTTYPE.DefendantTypeID = taDEFENDANTS.DefendantTypeID;



--
Doug Steele, Microsoft Access MVP

(no private e-mails, please)


What's "Concatentate"? There's no built-in function with that name.

If it's a user defined function, you need to see what it's does. I should
be
in a module.

One way to find out is to do a Ctrl + g to bring up the VBA window.

Next do a F2 to bring up the Object Browser.

Put Concatentate in the search window and see what if finds.
--
Jerry Whittle, Microsoft Access MVP
Light. Strong. Cheap. Pick two. Keith Bontrager - Bicycle Builder.


:

The following statement creates the following Run-Time Error:

Run-Time Error 3075:

Syntax error (missing operator) in query expression 'PropertyID=2
DefendantType="Owner"

SELECT DISTINCT taDEFENDANTS.PropertyID, Concatentate("SELECT FirstName &
'
' & MiddleName & ' ' & LastName & ' ' & Suffix FROM taDEFENDANTS where
PropertyID=" & [PropertyID] & " DefendantType=""Owner""") AS
Property_Owner
FROM taDEFENDANTTYPE INNER JOIN taDEFENDANTS ON
taDEFENDANTTYPE.DefendantTypeID = taDEFENDANTS.DefendantTypeID;

Please Help...

Thanks,

Ray.
Click to expand...
Click to expand...
Click to expand...
 
J

Jerry Whittle

Hi John,

Do you know of a Concatenate or ConcatenTate function. I searched for both
in the VBA window and came up with zip. Is it in some reference that I don't
have checked?
--
Jerry Whittle, Microsoft Access MVP
Light. Strong. Cheap. Pick two. Keith Bontrager - Bicycle Builder.

John Spencer said:
Spelling error?
Concatenate instead of ConcatenTate
Note the extra "T".

John Spencer
Access MVP 2002-2005, 2007-2008
Center for Health Program Development and Management
University of Maryland Baltimore County
Duane and Douglas:

I updated the sql as indicated below. Duane, I did add the reference to the
PropertyID in the taPROPERTY table in the where statement.

Despite these changes (both with and without the reference to the
taPROPERTY.PropertyID field), I am now getting the following error:

Run-Time Error: 3061
Too Few Parameters. Expected 1.

SELECT DISTINCT taPROPERTY.PropertyID, Concatentate("SELECT FirstName & ' '
& MiddleName & ' ' & LastName & ' ' & Suffix FROM taDEFENDANTS where
PropertyID=" & taPROPERTY.[PropertyID] & " AND DefendantType=""Owner""") AS
Property_Owner
FROM taPROPERTY INNER JOIN (taDEFENDANTTYPE INNER JOIN taDEFENDANTS ON
taDEFENDANTTYPE.DefendantTypeID = taDEFENDANTS.DefendantTypeID) ON
taPROPERTY.PropertyID = taDEFENDANTS.PropertyID;

Thanks,

Ray


Duane Hookom said:
I would probably use:
SELECT DISTINCT taDEFENDANTS.PropertyID, Concatentate("SELECT FirstName & '
' & MiddleName & ' ' & LastName & ' ' & Suffix FROM taDEFENDANTS where
PropertyID=" & [PropertyID] & " AND DefendantType='Owner'") AS
Property_Owner
FROM taDEFENDANTTYPE INNER JOIN taDEFENDANTS ON
taDEFENDANTTYPE.DefendantTypeID = taDEFENDANTS.DefendantTypeID;

Don't you have a table with unique PropertyID values rather than using
taDEFENDANTS in both the Concatenate() and the main query?


--
Duane Hookom
Microsoft Access MVP


:

Not only that, but the SQL being passed to the function doesn't look valid
(it's missing an AND in the Where clause)

SELECT DISTINCT taDEFENDANTS.PropertyID, Concatentate("SELECT FirstName & '
' & MiddleName & ' ' & LastName & ' ' & Suffix FROM taDEFENDANTS where
PropertyID=" & [PropertyID] & " AND DefendantType=""Owner""") AS
Property_Owner
FROM taDEFENDANTTYPE INNER JOIN taDEFENDANTS ON
taDEFENDANTTYPE.DefendantTypeID = taDEFENDANTS.DefendantTypeID;



--
Doug Steele, Microsoft Access MVP

(no private e-mails, please)


What's "Concatentate"? There's no built-in function with that name.

If it's a user defined function, you need to see what it's does. I should
be
in a module.

One way to find out is to do a Ctrl + g to bring up the VBA window.

Next do a F2 to bring up the Object Browser.

Put Concatentate in the search window and see what if finds.
--
Jerry Whittle, Microsoft Access MVP
Light. Strong. Cheap. Pick two. Keith Bontrager - Bicycle Builder.


:

The following statement creates the following Run-Time Error:

Run-Time Error 3075:

Syntax error (missing operator) in query expression 'PropertyID=2
DefendantType="Owner"

SELECT DISTINCT taDEFENDANTS.PropertyID, Concatentate("SELECT FirstName &
'
' & MiddleName & ' ' & LastName & ' ' & Suffix FROM taDEFENDANTS where
PropertyID=" & [PropertyID] & " DefendantType=""Owner""") AS
Property_Owner
FROM taDEFENDANTTYPE INNER JOIN taDEFENDANTS ON
taDEFENDANTTYPE.DefendantTypeID = taDEFENDANTS.DefendantTypeID;

Please Help...

Thanks,

Ray.
Click to expand...
Click to expand...
Click to expand...
 
J

John Spencer

SO you need to get that into the concatenated query or if you know the value
of DefendentTypeID that equates to DefendentType = "Owner" then you can use
that DefendentTypeID in the Concatenate SQL. For instance if Owner equates to
1 then the expression would be

Concatenate("SELECT FirstName & ' ' & MiddleName & ' ' & LastName & ' ' &
Suffix FROM taDEFENDANTS where PropertyID=" & taPROPERTY.[PropertyID] & " AND
DefendantTypeID=1") AS Property_Owner

If "Owner" equates to several different DefendantTypeIDs then you would need
to include the taDefendantType table in the query

Or you can use DefendantTYpeID In (1,3,5,9,22) in place of DefendantTypeID = 1
in the expression above.

Concatenate("SELECT FirstName & ' ' & MiddleName & ' ' & LastName & ' ' &
Suffix FROM taDEFENDANTS INNER JOIN taDefendantType ON
taDefendants.DefendantTypeID = taDefendantType.DefendantTypeID WHERE
PropertyID=" & taPROPERTY.[PropertyID] & " AND DefendantTypeID=1") AS
Property_Owner

John Spencer
Access MVP 2002-2005, 2007-2008
Center for Health Program Development and Management
University of Maryland Baltimore County
One things that I don't think I have mentioned previously is that
DefendantType is in another table taDEFENDANTTYPE

so, I have three tables I am getting info from:

taPROPERTY
PropertyID -Auto

taDEFENDANTS
DefendantID -Auto
PropertyID -Long
FirstName -Text
DefendantTypeID -Long

taDefendantType
DefendantTypeID -Auto
DefendantType -Text

Ray Todd Jr said:
The problem seems to be revolving around the literal value for the
DefendantType which is OWNER. This (OWNER) is a constant not a variable or a
field in a table, just a literal value that can be found in the DefendantType
field.


Ray Todd Jr said:
Hello John:

Thanks. I have corrected the spelling error. Unforunately, I am still
receiving the following error:

Run-Time Error 3061:

Too Few parameters. Expected 1.

Thanks,

Ray.

SELECT DISTINCT taPROPERTY.PropertyID, taDEFENDANTS.PropertyID,
Concatenate("SELECT FirstName & ' ' & MiddleName & ' ' & LastName & ' ' &
Suffix FROM taDEFENDANTS where PropertyID=" & taPROPERTY.[PropertyID] & "
AND DefendantType=""Owner""") AS Property_Owner
FROM taPROPERTY INNER JOIN (taDEFENDANTTYPE INNER JOIN taDEFENDANTS ON
taDEFENDANTTYPE.DefendantTypeID = taDEFENDANTS.DefendantTypeID) ON
taPROPERTY.PropertyID = taDEFENDANTS.PropertyID;


:

Spelling error?
Concatenate instead of ConcatenTate
Note the extra "T".

John Spencer
Access MVP 2002-2005, 2007-2008
Center for Health Program Development and Management
University of Maryland Baltimore County

Ray Todd Jr wrote:
Duane and Douglas:

I updated the sql as indicated below. Duane, I did add the reference to the
PropertyID in the taPROPERTY table in the where statement.

Despite these changes (both with and without the reference to the
taPROPERTY.PropertyID field), I am now getting the following error:

Run-Time Error: 3061
Too Few Parameters. Expected 1.

SELECT DISTINCT taPROPERTY.PropertyID, Concatentate("SELECT FirstName & ' '
& MiddleName & ' ' & LastName & ' ' & Suffix FROM taDEFENDANTS where
PropertyID=" & taPROPERTY.[PropertyID] & " AND DefendantType=""Owner""") AS
Property_Owner
FROM taPROPERTY INNER JOIN (taDEFENDANTTYPE INNER JOIN taDEFENDANTS ON
taDEFENDANTTYPE.DefendantTypeID = taDEFENDANTS.DefendantTypeID) ON
taPROPERTY.PropertyID = taDEFENDANTS.PropertyID;

Thanks,

Ray


:

I would probably use:
SELECT DISTINCT taDEFENDANTS.PropertyID, Concatentate("SELECT FirstName & '
' & MiddleName & ' ' & LastName & ' ' & Suffix FROM taDEFENDANTS where
PropertyID=" & [PropertyID] & " AND DefendantType='Owner'") AS
Property_Owner
FROM taDEFENDANTTYPE INNER JOIN taDEFENDANTS ON
taDEFENDANTTYPE.DefendantTypeID = taDEFENDANTS.DefendantTypeID;

Don't you have a table with unique PropertyID values rather than using
taDEFENDANTS in both the Concatenate() and the main query?


--
Duane Hookom
Microsoft Access MVP


:

Not only that, but the SQL being passed to the function doesn't look valid
(it's missing an AND in the Where clause)

SELECT DISTINCT taDEFENDANTS.PropertyID, Concatentate("SELECT FirstName & '
' & MiddleName & ' ' & LastName & ' ' & Suffix FROM taDEFENDANTS where
PropertyID=" & [PropertyID] & " AND DefendantType=""Owner""") AS
Property_Owner
FROM taDEFENDANTTYPE INNER JOIN taDEFENDANTS ON
taDEFENDANTTYPE.DefendantTypeID = taDEFENDANTS.DefendantTypeID;



--
Doug Steele, Microsoft Access MVP

(no private e-mails, please)


What's "Concatentate"? There's no built-in function with that name.

If it's a user defined function, you need to see what it's does. I should
be
in a module.

One way to find out is to do a Ctrl + g to bring up the VBA window.

Next do a F2 to bring up the Object Browser.

Put Concatentate in the search window and see what if finds.
--
Jerry Whittle, Microsoft Access MVP
Light. Strong. Cheap. Pick two. Keith Bontrager - Bicycle Builder.


:

The following statement creates the following Run-Time Error:

Run-Time Error 3075:

Syntax error (missing operator) in query expression 'PropertyID=2
DefendantType="Owner"

SELECT DISTINCT taDEFENDANTS.PropertyID, Concatentate("SELECT FirstName &
'
' & MiddleName & ' ' & LastName & ' ' & Suffix FROM taDEFENDANTS where
PropertyID=" & [PropertyID] & " DefendantType=""Owner""") AS
Property_Owner
FROM taDEFENDANTTYPE INNER JOIN taDEFENDANTS ON
taDEFENDANTTYPE.DefendantTypeID = taDEFENDANTS.DefendantTypeID;

Please Help...

Thanks,

Ray.
Click to expand...
Click to expand...
Click to expand...
 
R

Ray Todd Jr

Jerry:

Duane Hookom has a generic Concatenate function located here:

http://www.rogersaccesslibrary.com/OtherLibraries.asp#Hookom,Duane.


Ray.

Jerry Whittle said:
Hi John,

Do you know of a Concatenate or ConcatenTate function. I searched for both
in the VBA window and came up with zip. Is it in some reference that I don't
have checked?
--
Jerry Whittle, Microsoft Access MVP
Light. Strong. Cheap. Pick two. Keith Bontrager - Bicycle Builder.

John Spencer said:
Spelling error?
Concatenate instead of ConcatenTate
Note the extra "T".

John Spencer
Access MVP 2002-2005, 2007-2008
Center for Health Program Development and Management
University of Maryland Baltimore County
Duane and Douglas:

I updated the sql as indicated below. Duane, I did add the reference to the
PropertyID in the taPROPERTY table in the where statement.

Despite these changes (both with and without the reference to the
taPROPERTY.PropertyID field), I am now getting the following error:

Run-Time Error: 3061
Too Few Parameters. Expected 1.

SELECT DISTINCT taPROPERTY.PropertyID, Concatentate("SELECT FirstName & ' '
& MiddleName & ' ' & LastName & ' ' & Suffix FROM taDEFENDANTS where
PropertyID=" & taPROPERTY.[PropertyID] & " AND DefendantType=""Owner""") AS
Property_Owner
FROM taPROPERTY INNER JOIN (taDEFENDANTTYPE INNER JOIN taDEFENDANTS ON
taDEFENDANTTYPE.DefendantTypeID = taDEFENDANTS.DefendantTypeID) ON
taPROPERTY.PropertyID = taDEFENDANTS.PropertyID;

Thanks,

Ray


:

I would probably use:
SELECT DISTINCT taDEFENDANTS.PropertyID, Concatentate("SELECT FirstName & '
' & MiddleName & ' ' & LastName & ' ' & Suffix FROM taDEFENDANTS where
PropertyID=" & [PropertyID] & " AND DefendantType='Owner'") AS
Property_Owner
FROM taDEFENDANTTYPE INNER JOIN taDEFENDANTS ON
taDEFENDANTTYPE.DefendantTypeID = taDEFENDANTS.DefendantTypeID;

Don't you have a table with unique PropertyID values rather than using
taDEFENDANTS in both the Concatenate() and the main query?


--
Duane Hookom
Microsoft Access MVP


:

Not only that, but the SQL being passed to the function doesn't look valid
(it's missing an AND in the Where clause)

SELECT DISTINCT taDEFENDANTS.PropertyID, Concatentate("SELECT FirstName & '
' & MiddleName & ' ' & LastName & ' ' & Suffix FROM taDEFENDANTS where
PropertyID=" & [PropertyID] & " AND DefendantType=""Owner""") AS
Property_Owner
FROM taDEFENDANTTYPE INNER JOIN taDEFENDANTS ON
taDEFENDANTTYPE.DefendantTypeID = taDEFENDANTS.DefendantTypeID;



--
Doug Steele, Microsoft Access MVP

(no private e-mails, please)


What's "Concatentate"? There's no built-in function with that name.

If it's a user defined function, you need to see what it's does. I should
be
in a module.

One way to find out is to do a Ctrl + g to bring up the VBA window.

Next do a F2 to bring up the Object Browser.

Put Concatentate in the search window and see what if finds.
--
Jerry Whittle, Microsoft Access MVP
Light. Strong. Cheap. Pick two. Keith Bontrager - Bicycle Builder.


:

The following statement creates the following Run-Time Error:

Run-Time Error 3075:

Syntax error (missing operator) in query expression 'PropertyID=2
DefendantType="Owner"

SELECT DISTINCT taDEFENDANTS.PropertyID, Concatentate("SELECT FirstName &
'
' & MiddleName & ' ' & LastName & ' ' & Suffix FROM taDEFENDANTS where
PropertyID=" & [PropertyID] & " DefendantType=""Owner""") AS
Property_Owner
FROM taDEFENDANTTYPE INNER JOIN taDEFENDANTS ON
taDEFENDANTTYPE.DefendantTypeID = taDEFENDANTS.DefendantTypeID;

Please Help...

Thanks,

Ray.
Click to expand...
Click to expand...
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

SQL Help 8
Concatenate WHERE Criteria Question 1
Inner Join SQL Help 0
Getting Multiple Owners Names for Single Pieces of Property 8
Report Query Difficulties 11
SQL Error Help 7
Opening form to a specific record 8
Concatenate Question 1

Top