Address List

F

FJ Questioner

My mailing list often has two last names for a given address. Each is a
separate row in my database. I want to be able to make an address label that
says "The Smith & Jones Residence". How do I write a query that will pick up
both last names for a given address (the address is a single field).

Thanks,

FJquestioner
 
J

John Spencer

If it is No more than 2 names you could use a Totals query.

SELECT IIF(Min(LastName)= Max(LastName)
, Min(LastName)
,Max(LastName) & " and " & Min(LastName)) as OneName
, Address
FROM SomeTable
GROUP BY Address


Otherwise, you could take a look at Duane Hookom's concatenate function
(google search) and modify it to return the needed string.

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

cttechsupport

Well - very similar actually to the text of your example.....

In your query you can define field (column) names yourself - you're
not stuck with what you can drag down from the table.
So...............
For a combined name field enter in the box where you'd normally drag
down a table field..........
(assuming you have two Last Name fields called Lname1 and Lname2)

NameField:[Lname1] & " & " & [Lname2] & "Residence"

That simple !

Good luck

Charlie
 
F

FJ Questioner

Thanks but I'm not sure I follow.

ID Last Name St# Street
1 Smith 11 Apple St.
2 Jone 11 Apple St.


Ultimately I want to be able to produce an address label like this:

The Smith & Jones Residence
11 Apple St.


As you suggested I went to Duane Hookom's concatenate function and I tried
the following:

LastNames: Concatenate("SELECT [LastName] FROM
  • WHERE [Address] =" &
    [Address])

    However, I've no idea if this will work because I keep getting error message
    "Undefined function"Concatenate" in expression"

    Am I way off track?

    Thanks again.
 
F

FJ Questioner

Thanks but I don't think I explained properly. My database looks like this:

ID Last Name St# Street
1 Smith 11 Apple St.
2 Jone 11 Apple St.


Ultimately I want to be able to produce an address label like this:

The Smith & Jones Residence
11 Apple St.


I tried the following:

LastNames: Concatenate("SELECT [LastName] FROM
  • WHERE [Address] =" &
    [Address])

    However, I've no idea if this will work because I keep getting error message
    "Undefined function"Concatenate" in expression"

    Am I way off track?

    Thanks again.

    FJ

    [email protected] said:
    Well - very similar actually to the text of your example.....

    In your query you can define field (column) names yourself - you're
    not stuck with what you can drag down from the table.
    So...............
    For a combined name field enter in the box where you'd normally drag
    down a table field..........
    (assuming you have two Last Name fields called Lname1 and Lname2)

    NameField:[Lname1] & " & " & [Lname2] & "Residence"

    That simple !

    Good luck

    Charlie

    My mailing list often has two last names for a given address. Each is a
    separate row in my database. I want to be able to make an address label that
    says "The Smith & Jones Residence". How do I write a query that will pick up
    both last names for a given address (the address is a single field).

    Thanks,

    FJquestioner
    Click to expand...
    Click to expand...
 
J

John Spencer

Did you copy the concatenate function and paste it into a VBA module in your
database and save the module with a name other than concatenate?


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

FJ Questioner said:
Thanks but I'm not sure I follow.

ID Last Name St# Street
1 Smith 11 Apple St.
2 Jone 11 Apple St.


Ultimately I want to be able to produce an address label like this:

The Smith & Jones Residence
11 Apple St.


As you suggested I went to Duane Hookom's concatenate function and I tried
the following:

LastNames: Concatenate("SELECT [LastName] FROM
  • WHERE [Address] =" &
    [Address])

    However, I've no idea if this will work because I keep getting error
    message
    "Undefined function"Concatenate" in expression"

    Am I way off track?

    Thanks again.



    John Spencer said:
    If it is No more than 2 names you could use a Totals query.

    SELECT IIF(Min(LastName)= Max(LastName)
    , Min(LastName)
    ,Max(LastName) & " and " & Min(LastName)) as OneName
    , Address
    FROM SomeTable
    GROUP BY Address


    Otherwise, you could take a look at Duane Hookom's concatenate function
    (google search) and modify it to return the needed string.

    '====================================================
    John Spencer
    Access MVP 2002-2005, 2007-2008
    Center for Health Program Development and Management
    University of Maryland Baltimore County
    '====================================================
    Click to expand...
Click to expand...
 
F

FJ Questioner

No I just wrote it in a regular query (as per Hookum's example). I'm not
very familiar with VBA...

John Spencer said:
Did you copy the concatenate function and paste it into a VBA module in your
database and save the module with a name other than concatenate?


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

FJ Questioner said:
Thanks but I'm not sure I follow.

ID Last Name St# Street
1 Smith 11 Apple St.
2 Jone 11 Apple St.


Ultimately I want to be able to produce an address label like this:

The Smith & Jones Residence
11 Apple St.


As you suggested I went to Duane Hookom's concatenate function and I tried
the following:

LastNames: Concatenate("SELECT [LastName] FROM
  • WHERE [Address] =" &
    [Address])

    However, I've no idea if this will work because I keep getting error
    message
    "Undefined function"Concatenate" in expression"

    Am I way off track?

    Thanks again.



    John Spencer said:
    If it is No more than 2 names you could use a Totals query.

    SELECT IIF(Min(LastName)= Max(LastName)
    , Min(LastName)
    ,Max(LastName) & " and " & Min(LastName)) as OneName
    , Address
    FROM SomeTable
    GROUP BY Address


    Otherwise, you could take a look at Duane Hookom's concatenate function
    (google search) and modify it to return the needed string.

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


    FJ Questioner wrote:
    My mailing list often has two last names for a given address. Each is
    a
    separate row in my database. I want to be able to make an address label
    that
    says "The Smith & Jones Residence". How do I write a query that will
    pick up
    both last names for a given address (the address is a single field).

    Thanks,

    FJquestioner
    Click to expand...
Click to expand...
Click to expand...
 
J

John Spencer

Well, you need to do so or the concatenate function will not work.

Do you know how to do this or are you going to require step by step
directions?

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

FJ Questioner

Sorry, I just gave it a whirl but to ne effect. I'm going to need the step by
step......
 
J

John Spencer

Ok

Open Your database
Select Modules
Click on NEW
Paste in the Concatenate code from the Sample
--- Check the code
SELECT Debug: Compile from the menu
If you get errors then you will need to fix the cause. Most of the time the
error will be due to lines wrapping.

For Duane's code, you will probably have to make sure you have the DAO
library
Menu: Tools: References
If you don't have Microsoft DAO x.x Object Library, scroll down the list,
find it, and check it.
Close the dialog window

Duane's code has two places where you have to comment out a bit of code and
comment in a different bit of code. He has comments on where to do that in
the code. Comment out requires you to place an apostrophe at the start of
the line(s) to be commented out. Comment in - remove the apostrophe.

Select Debug: Compile again and see if the code compiles.

NOW SAVE the module as MOD_Concatenate

Try your query again.


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

FJ Questioner

I copied the following into my new module (although I have no clue what it
means). When I ran Compile, I got the error message: "User defined Type not
defined" and
the "rs As New ADODB.Recordset" in the 2nd line of the commented in section
was highlighted (not in yellow but in blue as if I was about to copy that
section).

Incidentally I checked the appropriate Microsoft DAO x.x Object Library as
you suggested.

Any idea as to what's wrong?

Thanks again.



Function Concatenate(pstrSQL As String, _
Optional pstrDelim As String = ", ") _
As String
'Created by Duane Hookom, 2003
'this code may be included in any application/mdb providing
' this statement is left intact
'example
'tblFamily with FamID as numeric primary key
'tblFamMem with FamID, FirstName, DOB,...
'return a comma separated list of FirstNames
'for a FamID
' John, Mary, Susan
'in a Query
'SELECT FamID,
'Concatenate("SELECT FirstName FROM tblFamMem
' WHERE FamID =" & [FamID]) as FirstNames
'FROM tblFamily
'

'======For DAO uncomment next 4 lines=======
'====== comment out ADO below =======
'Dim db As DAO.Database
'Dim rs As DAO.Recordset
'Set db = CurrentDb
'Set rs = db.OpenRecordset(pstrSQL)

'======For ADO uncomment next two lines=====
'====== comment out DAO above ======
Dim rs As New ADODB.Recordset
rs.Open pstrSQL, CurrentProject.Connection, _
adOpenKeyset, adLockOptimistic
Dim strConcat As String 'build return string
With rs
If Not .EOF Then
.MoveFirst
Do While Not .EOF
strConcat = strConcat & _
.Fields(0) & pstrDelim
.MoveNext
Loop
End If
.Close
End With
Set rs = Nothing
'====== uncomment next line for DAO ========
'Set db = Nothing
If Len(strConcat) > 0 Then
strConcat = Left(strConcat, _
Len(strConcat) - Len(pstrDelim))
End If
Concatenate = strConcat
End Function
 
J

John Spencer

That was one of the lines that needed to be commented out. You are using
DAO, so the lines that Duane has for ADO need to be commented out and the
lines for DAO need to be uncommented. Near the top of the code, it should
look like:

'======For DAO uncomment next 4 lines========
'====== comment out ADO below ========
Dim db As DAO.Database
Dim rs As DAO.Recordset
Set db = CurrentDb()
Set rs = db.OpenRecordset(pstrSQL)

'======For ADO uncomment next two lines========
'====== comment out DAO above ========
'Dim rs As New ADODB.Recordset
'rs.Open pstrSQL, CurrentProject.Connection, adOpenKeyset,
adLockOptimistic

And near the bottom of the function
'====== comment out next line for ADO ===========
Set db = Nothing

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

FJ Questioner said:
I copied the following into my new module (although I have no clue what it
means). When I ran Compile, I got the error message: "User defined Type
not
defined" and
the "rs As New ADODB.Recordset" in the 2nd line of the commented in
section
was highlighted (not in yellow but in blue as if I was about to copy that
section).

Incidentally I checked the appropriate Microsoft DAO x.x Object Library as
you suggested.

Any idea as to what's wrong?

Thanks again.



Function Concatenate(pstrSQL As String, _
Optional pstrDelim As String = ", ") _
As String
'Created by Duane Hookom, 2003
'this code may be included in any application/mdb providing
' this statement is left intact
'example
'tblFamily with FamID as numeric primary key
'tblFamMem with FamID, FirstName, DOB,...
'return a comma separated list of FirstNames
'for a FamID
' John, Mary, Susan
'in a Query
'SELECT FamID,
'Concatenate("SELECT FirstName FROM tblFamMem
' WHERE FamID =" & [FamID]) as FirstNames
'FROM tblFamily
'

'======For DAO uncomment next 4 lines=======
'====== comment out ADO below =======
'Dim db As DAO.Database
'Dim rs As DAO.Recordset
'Set db = CurrentDb
'Set rs = db.OpenRecordset(pstrSQL)

'======For ADO uncomment next two lines=====
'====== comment out DAO above ======
Dim rs As New ADODB.Recordset
rs.Open pstrSQL, CurrentProject.Connection, _
adOpenKeyset, adLockOptimistic
Dim strConcat As String 'build return string
With rs
If Not .EOF Then
.MoveFirst
Do While Not .EOF
strConcat = strConcat & _
.Fields(0) & pstrDelim
.MoveNext
Loop
End If
.Close
End With
Set rs = Nothing
'====== uncomment next line for DAO ========
'Set db = Nothing
If Len(strConcat) > 0 Then
strConcat = Left(strConcat, _
Len(strConcat) - Len(pstrDelim))
End If
Concatenate = strConcat
End Function


John Spencer said:
Ok

Open Your database
Select Modules
Click on NEW
Paste in the Concatenate code from the Sample
--- Check the code
SELECT Debug: Compile from the menu
If you get errors then you will need to fix the cause. Most of the time
the
error will be due to lines wrapping.

For Duane's code, you will probably have to make sure you have the DAO
library
Menu: Tools: References
If you don't have Microsoft DAO x.x Object Library, scroll down the
list,
find it, and check it.
Close the dialog window

Duane's code has two places where you have to comment out a bit of code
and
comment in a different bit of code. He has comments on where to do that
in
the code. Comment out requires you to place an apostrophe at the start
of
the line(s) to be commented out. Comment in - remove the apostrophe.

Select Debug: Compile again and see if the code compiles.

NOW SAVE the module as MOD_Concatenate

Try your query again.


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

FJ Questioner

OK got it. It seemed to compile ok but when I ran the query I got an error
message:

Runtime error 3075. Syntax error (missing operator) in query expression.

It then highlighted this Module line in Yellow:
Set rs = db.OpenRecordset(pstrSQL)

BTW, the actual query I wrote was as follows:
LastNames: Concatenate("SELECT [LastName] FROM
  • WHERE [X] =" & [X])

    List=table with names and address
    X=a column within the same query in which I concatenated the street name and
    number into a single string.

    I'm zeroing in on it !

    Thanks once again.

    FJ



    John Spencer said:
    That was one of the lines that needed to be commented out. You are using
    DAO, so the lines that Duane has for ADO need to be commented out and the
    lines for DAO need to be uncommented. Near the top of the code, it should
    look like:

    '======For DAO uncomment next 4 lines========
    '====== comment out ADO below ========
    Dim db As DAO.Database
    Dim rs As DAO.Recordset
    Set db = CurrentDb()
    Set rs = db.OpenRecordset(pstrSQL)

    '======For ADO uncomment next two lines========
    '====== comment out DAO above ========
    'Dim rs As New ADODB.Recordset
    'rs.Open pstrSQL, CurrentProject.Connection, adOpenKeyset,
    adLockOptimistic

    And near the bottom of the function
    '====== comment out next line for ADO ===========
    Set db = Nothing

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

    FJ Questioner said:
    I copied the following into my new module (although I have no clue what it
    means). When I ran Compile, I got the error message: "User defined Type
    not
    defined" and
    the "rs As New ADODB.Recordset" in the 2nd line of the commented in
    section
    was highlighted (not in yellow but in blue as if I was about to copy that
    section).

    Incidentally I checked the appropriate Microsoft DAO x.x Object Library as
    you suggested.

    Any idea as to what's wrong?

    Thanks again.



    Function Concatenate(pstrSQL As String, _
    Optional pstrDelim As String = ", ") _
    As String
    'Created by Duane Hookom, 2003
    'this code may be included in any application/mdb providing
    ' this statement is left intact
    'example
    'tblFamily with FamID as numeric primary key
    'tblFamMem with FamID, FirstName, DOB,...
    'return a comma separated list of FirstNames
    'for a FamID
    ' John, Mary, Susan
    'in a Query
    'SELECT FamID,
    'Concatenate("SELECT FirstName FROM tblFamMem
    ' WHERE FamID =" & [FamID]) as FirstNames
    'FROM tblFamily
    '

    '======For DAO uncomment next 4 lines=======
    '====== comment out ADO below =======
    'Dim db As DAO.Database
    'Dim rs As DAO.Recordset
    'Set db = CurrentDb
    'Set rs = db.OpenRecordset(pstrSQL)

    '======For ADO uncomment next two lines=====
    '====== comment out DAO above ======
    Dim rs As New ADODB.Recordset
    rs.Open pstrSQL, CurrentProject.Connection, _
    adOpenKeyset, adLockOptimistic
    Dim strConcat As String 'build return string
    With rs
    If Not .EOF Then
    .MoveFirst
    Do While Not .EOF
    strConcat = strConcat & _
    .Fields(0) & pstrDelim
    .MoveNext
    Loop
    End If
    .Close
    End With
    Set rs = Nothing
    '====== uncomment next line for DAO ========
    'Set db = Nothing
    If Len(strConcat) > 0 Then
    strConcat = Left(strConcat, _
    Len(strConcat) - Len(pstrDelim))
    End If
    Concatenate = strConcat
    End Function


    John Spencer said:
    Ok

    Open Your database
    Select Modules
    Click on NEW
    Paste in the Concatenate code from the Sample
    --- Check the code
    SELECT Debug: Compile from the menu
    If you get errors then you will need to fix the cause. Most of the time
    the
    error will be due to lines wrapping.

    For Duane's code, you will probably have to make sure you have the DAO
    library
    Menu: Tools: References
    If you don't have Microsoft DAO x.x Object Library, scroll down the
    list,
    find it, and check it.
    Close the dialog window

    Duane's code has two places where you have to comment out a bit of code
    and
    comment in a different bit of code. He has comments on where to do that
    in
    the code. Comment out requires you to place an apostrophe at the start
    of
    the line(s) to be commented out. Comment in - remove the apostrophe.

    Select Debug: Compile again and see if the code compiles.

    NOW SAVE the module as MOD_Concatenate

    Try your query again.


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

    Sorry, I just gave it a whirl but to ne effect. I'm going to need the
    step
    by
    step......



    :

    Well, you need to do so or the concatenate function will not work.

    Do you know how to do this or are you going to require step by step
    directions?

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

    message
    No I just wrote it in a regular query (as per Hookum's example).
    I'm
    not
    very familiar with VBA...

    :

    Did you copy the concatenate function and paste it into a VBA
    module
    in
    your
    database and save the module with a name other than concatenate?


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

John Spencer

Your query string needs some work.

First: X is a string and as such the query must identify it as a string by
using quote marks around X.
Second: X is unknown in the table List so you must rebuild X in the where
clause.

I assumed that you concatenated in a space between Street Name and Street
Number and I assumed that you built X in the same order and manner.

Something like the following is what I would expect to see.

LastNames: Concatenate("SELECT [LastName] FROM
  • WHERE [Street name ] &
    "" "" & [Street Number] =""" & [X] & """")

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

    FJ Questioner said:
    OK got it. It seemed to compile ok but when I ran the query I got an
    error
    message:

    Runtime error 3075. Syntax error (missing operator) in query expression.

    It then highlighted this Module line in Yellow:
    Set rs = db.OpenRecordset(pstrSQL)

    BTW, the actual query I wrote was as follows:
    LastNames: Concatenate("SELECT [LastName] FROM
    • WHERE [X] =" & [X])

      List=table with names and address
      X=a column within the same query in which I concatenated the street name
      and
      number into a single string.

      I'm zeroing in on it !

      Thanks once again.

      FJ



      John Spencer said:
      That was one of the lines that needed to be commented out. You are using
      DAO, so the lines that Duane has for ADO need to be commented out and the
      lines for DAO need to be uncommented. Near the top of the code, it
      should
      look like:

      '======For DAO uncomment next 4 lines========
      '====== comment out ADO below ========
      Dim db As DAO.Database
      Dim rs As DAO.Recordset
      Set db = CurrentDb()
      Set rs = db.OpenRecordset(pstrSQL)

      '======For ADO uncomment next two lines========
      '====== comment out DAO above ========
      'Dim rs As New ADODB.Recordset
      'rs.Open pstrSQL, CurrentProject.Connection, adOpenKeyset,
      adLockOptimistic

      And near the bottom of the function
      '====== comment out next line for ADO ===========
      Set db = Nothing

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

      FJ Questioner said:
      I copied the following into my new module (although I have no clue what
      it
      means). When I ran Compile, I got the error message: "User defined Type
      not
      defined" and
      the "rs As New ADODB.Recordset" in the 2nd line of the commented in
      section
      was highlighted (not in yellow but in blue as if I was about to copy
      that
      section).

      Incidentally I checked the appropriate Microsoft DAO x.x Object Library
      as
      you suggested.

      Any idea as to what's wrong?

      Thanks again.



      Function Concatenate(pstrSQL As String, _
      Optional pstrDelim As String = ", ") _
      As String
      'Created by Duane Hookom, 2003
      'this code may be included in any application/mdb providing
      ' this statement is left intact
      'example
      'tblFamily with FamID as numeric primary key
      'tblFamMem with FamID, FirstName, DOB,...
      'return a comma separated list of FirstNames
      'for a FamID
      ' John, Mary, Susan
      'in a Query
      'SELECT FamID,
      'Concatenate("SELECT FirstName FROM tblFamMem
      ' WHERE FamID =" & [FamID]) as FirstNames
      'FROM tblFamily
      '

      '======For DAO uncomment next 4 lines=======
      '====== comment out ADO below =======
      'Dim db As DAO.Database
      'Dim rs As DAO.Recordset
      'Set db = CurrentDb
      'Set rs = db.OpenRecordset(pstrSQL)

      '======For ADO uncomment next two lines=====
      '====== comment out DAO above ======
      Dim rs As New ADODB.Recordset
      rs.Open pstrSQL, CurrentProject.Connection, _
      adOpenKeyset, adLockOptimistic
      Dim strConcat As String 'build return string
      With rs
      If Not .EOF Then
      .MoveFirst
      Do While Not .EOF
      strConcat = strConcat & _
      .Fields(0) & pstrDelim
      .MoveNext
      Loop
      End If
      .Close
      End With
      Set rs = Nothing
      '====== uncomment next line for DAO ========
      'Set db = Nothing
      If Len(strConcat) > 0 Then
      strConcat = Left(strConcat, _
      Len(strConcat) - Len(pstrDelim))
      End If
      Concatenate = strConcat
      End Function


      :

      Ok

      Open Your database
      Select Modules
      Click on NEW
      Paste in the Concatenate code from the Sample
      --- Check the code
      SELECT Debug: Compile from the menu
      If you get errors then you will need to fix the cause. Most of the
      time
      the
      error will be due to lines wrapping.

      For Duane's code, you will probably have to make sure you have the DAO
      library
      Menu: Tools: References
      If you don't have Microsoft DAO x.x Object Library, scroll down the
      list,
      find it, and check it.
      Close the dialog window

      Duane's code has two places where you have to comment out a bit of
      code
      and
      comment in a different bit of code. He has comments on where to do
      that
      in
      the code. Comment out requires you to place an apostrophe at the
      start
      of
      the line(s) to be commented out. Comment in - remove the apostrophe.

      Select Debug: Compile again and see if the code compiles.

      NOW SAVE the module as MOD_Concatenate

      Try your query again.


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

      message
      Sorry, I just gave it a whirl but to ne effect. I'm going to need
      the
      step
      by
      step......



      :

      Well, you need to do so or the concatenate function will not work.

      Do you know how to do this or are you going to require step by step
      directions?

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

      message
      No I just wrote it in a regular query (as per Hookum's example).
      I'm
      not
      very familiar with VBA...

      :

      Did you copy the concatenate function and paste it into a VBA
      module
      in
      your
      database and save the module with a name other than concatenate?


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

FJ Questioner

Sorry John, Me again!

Here's the query I wrote.

LastNames: Concatenate("SELECT [LastName] FROM
  • WHERE [Unit
    No]&""&[Street Number ] &"" & [Street Name] =""" & [X] & """")

    Here's the error message I get.

    "Too few Parameters. Expected 1."

    Not sure if this is of interest but here's the address concatenation column.

    X: ([Unit No] & "" & [Street Number] & "" & [Street Name])

    I must be getting close ??

    Thanks.
    FJ



    John Spencer said:
    Your query string needs some work.

    First: X is a string and as such the query must identify it as a string by
    using quote marks around X.
    Second: X is unknown in the table List so you must rebuild X in the where
    clause.

    I assumed that you concatenated in a space between Street Name and Street
    Number and I assumed that you built X in the same order and manner.

    Something like the following is what I would expect to see.

    LastNames: Concatenate("SELECT [LastName] FROM
    • WHERE [Street name ] &
      "" "" & [Street Number] =""" & [X] & """")

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

      FJ Questioner said:
      OK got it. It seemed to compile ok but when I ran the query I got an
      error
      message:

      Runtime error 3075. Syntax error (missing operator) in query expression.

      It then highlighted this Module line in Yellow:
      Set rs = db.OpenRecordset(pstrSQL)

      BTW, the actual query I wrote was as follows:
      LastNames: Concatenate("SELECT [LastName] FROM
      • WHERE [X] =" & [X])

        List=table with names and address
        X=a column within the same query in which I concatenated the street name
        and
        number into a single string.

        I'm zeroing in on it !

        Thanks once again.

        FJ



        John Spencer said:
        That was one of the lines that needed to be commented out. You are using
        DAO, so the lines that Duane has for ADO need to be commented out and the
        lines for DAO need to be uncommented. Near the top of the code, it
        should
        look like:

        '======For DAO uncomment next 4 lines========
        '====== comment out ADO below ========
        Dim db As DAO.Database
        Dim rs As DAO.Recordset
        Set db = CurrentDb()
        Set rs = db.OpenRecordset(pstrSQL)

        '======For ADO uncomment next two lines========
        '====== comment out DAO above ========
        'Dim rs As New ADODB.Recordset
        'rs.Open pstrSQL, CurrentProject.Connection, adOpenKeyset,
        adLockOptimistic

        And near the bottom of the function
        '====== comment out next line for ADO ===========
        Set db = Nothing

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

        I copied the following into my new module (although I have no clue what
        it
        means). When I ran Compile, I got the error message: "User defined Type
        not
        defined" and
        the "rs As New ADODB.Recordset" in the 2nd line of the commented in
        section
        was highlighted (not in yellow but in blue as if I was about to copy
        that
        section).

        Incidentally I checked the appropriate Microsoft DAO x.x Object Library
        as
        you suggested.

        Any idea as to what's wrong?

        Thanks again.



        Function Concatenate(pstrSQL As String, _
        Optional pstrDelim As String = ", ") _
        As String
        'Created by Duane Hookom, 2003
        'this code may be included in any application/mdb providing
        ' this statement is left intact
        'example
        'tblFamily with FamID as numeric primary key
        'tblFamMem with FamID, FirstName, DOB,...
        'return a comma separated list of FirstNames
        'for a FamID
        ' John, Mary, Susan
        'in a Query
        'SELECT FamID,
        'Concatenate("SELECT FirstName FROM tblFamMem
        ' WHERE FamID =" & [FamID]) as FirstNames
        'FROM tblFamily
        '

        '======For DAO uncomment next 4 lines=======
        '====== comment out ADO below =======
        'Dim db As DAO.Database
        'Dim rs As DAO.Recordset
        'Set db = CurrentDb
        'Set rs = db.OpenRecordset(pstrSQL)

        '======For ADO uncomment next two lines=====
        '====== comment out DAO above ======
        Dim rs As New ADODB.Recordset
        rs.Open pstrSQL, CurrentProject.Connection, _
        adOpenKeyset, adLockOptimistic
        Dim strConcat As String 'build return string
        With rs
        If Not .EOF Then
        .MoveFirst
        Do While Not .EOF
        strConcat = strConcat & _
        .Fields(0) & pstrDelim
        .MoveNext
        Loop
        End If
        .Close
        End With
        Set rs = Nothing
        '====== uncomment next line for DAO ========
        'Set db = Nothing
        If Len(strConcat) > 0 Then
        strConcat = Left(strConcat, _
        Len(strConcat) - Len(pstrDelim))
        End If
        Concatenate = strConcat
        End Function


        :

        Ok

        Open Your database
        Select Modules
        Click on NEW
        Paste in the Concatenate code from the Sample
        --- Check the code
        SELECT Debug: Compile from the menu
        If you get errors then you will need to fix the cause. Most of the
        time
        the
        error will be due to lines wrapping.

        For Duane's code, you will probably have to make sure you have the DAO
        library
        Menu: Tools: References
        If you don't have Microsoft DAO x.x Object Library, scroll down the
        list,
        find it, and check it.
        Close the dialog window

        Duane's code has two places where you have to comment out a bit of
        code
        and
        comment in a different bit of code. He has comments on where to do
        that
        in
        the code. Comment out requires you to place an apostrophe at the
        start
        of
        the line(s) to be commented out. Comment in - remove the apostrophe.

        Select Debug: Compile again and see if the code compiles.

        NOW SAVE the module as MOD_Concatenate

        Try your query again.


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

        message
        Sorry, I just gave it a whirl but to ne effect. I'm going to need
        the
        step
        by
        step......



        :

        Well, you need to do so or the concatenate function will not work.

        Do you know how to do this or are you going to require step by step
        directions?

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

        message
        No I just wrote it in a regular query (as per Hookum's example).
        I'm
        not
        very familiar with VBA...

        :

        Did you copy the concatenate function and paste it into a VBA
        module
        in
        your
        database and save the module with a name other than concatenate?


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

John Spencer

Ok, there are a couple possibilities.
One - is the field LastName or is it Last Space Name. Or one of the other
field names is misspelled.
Second - X is not being recognized as the concatenation and you will have to
build that into the query string.

Since you are separating the fields with a zero-length string, you should be
able to use
Concatenate("SELECT [LastName] FROM
  • WHERE [Unit No] & [Street
    Number ] & [Street Name] ='" & [Unit No] & [Street Number] & [Street Name]
    & "'")


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

    FJ Questioner said:
    Sorry John, Me again!

    Here's the query I wrote.

    LastNames: Concatenate("SELECT [LastName] FROM
    • WHERE [Unit
      No]&""&[Street Number ] &"" & [Street Name] =""" & [X] & """")

      Here's the error message I get.

      "Too few Parameters. Expected 1."

      Not sure if this is of interest but here's the address concatenation
      column.

      X: ([Unit No] & "" & [Street Number] & "" & [Street Name])

      I must be getting close ??

      Thanks.
      FJ



      John Spencer said:
      Your query string needs some work.

      First: X is a string and as such the query must identify it as a string
      by
      using quote marks around X.
      Second: X is unknown in the table List so you must rebuild X in the where
      clause.

      I assumed that you concatenated in a space between Street Name and Street
      Number and I assumed that you built X in the same order and manner.

      Something like the following is what I would expect to see.

      LastNames: Concatenate("SELECT [LastName] FROM
      • WHERE [Street
        name ] &
        "" "" & [Street Number] =""" & [X] & """")

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

        FJ Questioner said:
        OK got it. It seemed to compile ok but when I ran the query I got an
        error
        message:

        Runtime error 3075. Syntax error (missing operator) in query
        expression.

        It then highlighted this Module line in Yellow:
        Set rs = db.OpenRecordset(pstrSQL)

        BTW, the actual query I wrote was as follows:
        LastNames: Concatenate("SELECT [LastName] FROM
        • WHERE [X] =" &
          [X])

          List=table with names and address
          X=a column within the same query in which I concatenated the street
          name
          and
          number into a single string.

          I'm zeroing in on it !

          Thanks once again.

          FJ



          :

          That was one of the lines that needed to be commented out. You are
          using
          DAO, so the lines that Duane has for ADO need to be commented out and
          the
          lines for DAO need to be uncommented. Near the top of the code, it
          should
          look like:

          '======For DAO uncomment next 4 lines========
          '====== comment out ADO below ========
          Dim db As DAO.Database
          Dim rs As DAO.Recordset
          Set db = CurrentDb()
          Set rs = db.OpenRecordset(pstrSQL)

          '======For ADO uncomment next two lines========
          '====== comment out DAO above ========
          'Dim rs As New ADODB.Recordset
          'rs.Open pstrSQL, CurrentProject.Connection, adOpenKeyset,
          adLockOptimistic

          And near the bottom of the function
          '====== comment out next line for ADO ===========
          Set db = Nothing

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

          message
          I copied the following into my new module (although I have no clue
          what
          it
          means). When I ran Compile, I got the error message: "User defined
          Type
          not
          defined" and
          the "rs As New ADODB.Recordset" in the 2nd line of the commented in
          section
          was highlighted (not in yellow but in blue as if I was about to copy
          that
          section).

          Incidentally I checked the appropriate Microsoft DAO x.x Object
          Library
          as
          you suggested.

          Any idea as to what's wrong?

          Thanks again.



          Function Concatenate(pstrSQL As String, _
          Optional pstrDelim As String = ", ") _
          As String
          'Created by Duane Hookom, 2003
          'this code may be included in any application/mdb providing
          ' this statement is left intact
          'example
          'tblFamily with FamID as numeric primary key
          'tblFamMem with FamID, FirstName, DOB,...
          'return a comma separated list of FirstNames
          'for a FamID
          ' John, Mary, Susan
          'in a Query
          'SELECT FamID,
          'Concatenate("SELECT FirstName FROM tblFamMem
          ' WHERE FamID =" & [FamID]) as FirstNames
          'FROM tblFamily
          '

          '======For DAO uncomment next 4 lines=======
          '====== comment out ADO below =======
          'Dim db As DAO.Database
          'Dim rs As DAO.Recordset
          'Set db = CurrentDb
          'Set rs = db.OpenRecordset(pstrSQL)

          '======For ADO uncomment next two lines=====
          '====== comment out DAO above ======
          Dim rs As New ADODB.Recordset
          rs.Open pstrSQL, CurrentProject.Connection, _
          adOpenKeyset, adLockOptimistic
          Dim strConcat As String 'build return string
          With rs
          If Not .EOF Then
          .MoveFirst
          Do While Not .EOF
          strConcat = strConcat & _
          .Fields(0) & pstrDelim
          .MoveNext
          Loop
          End If
          .Close
          End With
          Set rs = Nothing
          '====== uncomment next line for DAO ========
          'Set db = Nothing
          If Len(strConcat) > 0 Then
          strConcat = Left(strConcat, _
          Len(strConcat) - Len(pstrDelim))
          End If
          Concatenate = strConcat
          End Function


          :

          Ok

          Open Your database
          Select Modules
          Click on NEW
          Paste in the Concatenate code from the Sample
          --- Check the code
          SELECT Debug: Compile from the menu
          If you get errors then you will need to fix the cause. Most of the
          time
          the
          error will be due to lines wrapping.

          For Duane's code, you will probably have to make sure you have the
          DAO
          library
          Menu: Tools: References
          If you don't have Microsoft DAO x.x Object Library, scroll down
          the
          list,
          find it, and check it.
          Close the dialog window

          Duane's code has two places where you have to comment out a bit of
          code
          and
          comment in a different bit of code. He has comments on where to do
          that
          in
          the code. Comment out requires you to place an apostrophe at the
          start
          of
          the line(s) to be commented out. Comment in - remove the
          apostrophe.

          Select Debug: Compile again and see if the code compiles.

          NOW SAVE the module as MOD_Concatenate

          Try your query again.


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

          message
          Sorry, I just gave it a whirl but to ne effect. I'm going to need
          the
          step
          by
          step......



          :

          Well, you need to do so or the concatenate function will not
          work.

          Do you know how to do this or are you going to require step by
          step
          directions?

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

          in
          message
          No I just wrote it in a regular query (as per Hookum's
          example).
          I'm
          not
          very familiar with VBA...

          :

          Did you copy the concatenate function and paste it into a VBA
          module
          in
          your
          database and save the module with a name other than
          concatenate?


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

FJ Questioner

I think this is going to work because the error message I'm getting only
tells me I have invalid sytax and points to the phrase below. I suspect it
has to do with the sequence of single and double quotaion marks. Do I have
these right? (I left spaces between them in this example to show you what I
did:

=" ' & [Unit No] & [Street Number] & [Street Name] & "" '

Thanks. Hopefully this'll be the last iteration!


John Spencer said:
Ok, there are a couple possibilities.
One - is the field LastName or is it Last Space Name. Or one of the other
field names is misspelled.
Second - X is not being recognized as the concatenation and you will have to
build that into the query string.

Since you are separating the fields with a zero-length string, you should be
able to use
Concatenate("SELECT [LastName] FROM
  • WHERE [Unit No] & [Street
    Number ] & [Street Name] ='" & [Unit No] & [Street Number] & [Street Name]
    & "'")


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

    FJ Questioner said:
    Sorry John, Me again!

    Here's the query I wrote.

    LastNames: Concatenate("SELECT [LastName] FROM
    • WHERE [Unit
      No]&""&[Street Number ] &"" & [Street Name] =""" & [X] & """")

      Here's the error message I get.

      "Too few Parameters. Expected 1."

      Not sure if this is of interest but here's the address concatenation
      column.

      X: ([Unit No] & "" & [Street Number] & "" & [Street Name])

      I must be getting close ??

      Thanks.
      FJ



      John Spencer said:
      Your query string needs some work.

      First: X is a string and as such the query must identify it as a string
      by
      using quote marks around X.
      Second: X is unknown in the table List so you must rebuild X in the where
      clause.

      I assumed that you concatenated in a space between Street Name and Street
      Number and I assumed that you built X in the same order and manner.

      Something like the following is what I would expect to see.

      LastNames: Concatenate("SELECT [LastName] FROM
      • WHERE [Street
        name ] &
        "" "" & [Street Number] =""" & [X] & """")

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

        OK got it. It seemed to compile ok but when I ran the query I got an
        error
        message:

        Runtime error 3075. Syntax error (missing operator) in query
        expression.

        It then highlighted this Module line in Yellow:
        Set rs = db.OpenRecordset(pstrSQL)

        BTW, the actual query I wrote was as follows:
        LastNames: Concatenate("SELECT [LastName] FROM
        • WHERE [X] =" &
          [X])

          List=table with names and address
          X=a column within the same query in which I concatenated the street
          name
          and
          number into a single string.

          I'm zeroing in on it !

          Thanks once again.

          FJ



          :

          That was one of the lines that needed to be commented out. You are
          using
          DAO, so the lines that Duane has for ADO need to be commented out and
          the
          lines for DAO need to be uncommented. Near the top of the code, it
          should
          look like:

          '======For DAO uncomment next 4 lines========
          '====== comment out ADO below ========
          Dim db As DAO.Database
          Dim rs As DAO.Recordset
          Set db = CurrentDb()
          Set rs = db.OpenRecordset(pstrSQL)

          '======For ADO uncomment next two lines========
          '====== comment out DAO above ========
          'Dim rs As New ADODB.Recordset
          'rs.Open pstrSQL, CurrentProject.Connection, adOpenKeyset,
          adLockOptimistic

          And near the bottom of the function
          '====== comment out next line for ADO ===========
          Set db = Nothing

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

          message
          I copied the following into my new module (although I have no clue
          what
          it
          means). When I ran Compile, I got the error message: "User defined
          Type
          not
          defined" and
          the "rs As New ADODB.Recordset" in the 2nd line of the commented in
          section
          was highlighted (not in yellow but in blue as if I was about to copy
          that
          section).

          Incidentally I checked the appropriate Microsoft DAO x.x Object
          Library
          as
          you suggested.

          Any idea as to what's wrong?

          Thanks again.



          Function Concatenate(pstrSQL As String, _
          Optional pstrDelim As String = ", ") _
          As String
          'Created by Duane Hookom, 2003
          'this code may be included in any application/mdb providing
          ' this statement is left intact
          'example
          'tblFamily with FamID as numeric primary key
          'tblFamMem with FamID, FirstName, DOB,...
          'return a comma separated list of FirstNames
          'for a FamID
          ' John, Mary, Susan
          'in a Query
          'SELECT FamID,
          'Concatenate("SELECT FirstName FROM tblFamMem
          ' WHERE FamID =" & [FamID]) as FirstNames
          'FROM tblFamily
          '

          '======For DAO uncomment next 4 lines=======
          '====== comment out ADO below =======
          'Dim db As DAO.Database
          'Dim rs As DAO.Recordset
          'Set db = CurrentDb
          'Set rs = db.OpenRecordset(pstrSQL)

          '======For ADO uncomment next two lines=====
          '====== comment out DAO above ======
          Dim rs As New ADODB.Recordset
          rs.Open pstrSQL, CurrentProject.Connection, _
          adOpenKeyset, adLockOptimistic
          Dim strConcat As String 'build return string
          With rs
          If Not .EOF Then
          .MoveFirst
          Do While Not .EOF
          strConcat = strConcat & _
          .Fields(0) & pstrDelim
          .MoveNext
          Loop
          End If
          .Close
          End With
          Set rs = Nothing
          '====== uncomment next line for DAO ========
          'Set db = Nothing
          If Len(strConcat) > 0 Then
          strConcat = Left(strConcat, _
          Len(strConcat) - Len(pstrDelim))
          End If
          Concatenate = strConcat
          End Function


          :

          Ok

          Open Your database
          Select Modules
          Click on NEW
          Paste in the Concatenate code from the Sample
          --- Check the code
          SELECT Debug: Compile from the menu
          If you get errors then you will need to fix the cause. Most of the
          time
          the
          error will be due to lines wrapping.

          For Duane's code, you will probably have to make sure you have the
          DAO
          library
          Menu: Tools: References
          If you don't have Microsoft DAO x.x Object Library, scroll down
          the
          list,
          find it, and check it.
          Close the dialog window

          Duane's code has two places where you have to comment out a bit of
          code
          and
          comment in a different bit of code. He has comments on where to do
          that
          in
          the code. Comment out requires you to place an apostrophe at the
          start
          of
          the line(s) to be commented out. Comment in - remove the
          apostrophe.

          Select Debug: Compile again and see if the code compiles.

          NOW SAVE the module as MOD_Concatenate

          Try your query again.


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

          message
          Sorry, I just gave it a whirl but to ne effect. I'm going to need
          the
          step
          by
          step......



          :

          Well, you need to do so or the concatenate function will not
          work.

          Do you know how to do this or are you going to require step by
          step
          directions?

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

          in
          message
          No I just wrote it in a regular query (as per Hookum's
          example).
      Click to expand...
    Click to expand...
Click to expand...
 
J

John Spencer

Try the following (take out the spaces)

= ' "& [Unit No] & [Street Number] & [Street Name] & " ' "

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

FJ Questioner said:
I think this is going to work because the error message I'm getting only
tells me I have invalid sytax and points to the phrase below. I suspect it
has to do with the sequence of single and double quotaion marks. Do I
have
these right? (I left spaces between them in this example to show you what
I
did:

=" ' & [Unit No] & [Street Number] & [Street Name] & "" '

Thanks. Hopefully this'll be the last iteration!


John Spencer said:
Ok, there are a couple possibilities.
One - is the field LastName or is it Last Space Name. Or one of the
other
field names is misspelled.
Second - X is not being recognized as the concatenation and you will have
to
build that into the query string.

Since you are separating the fields with a zero-length string, you should
be
able to use
Concatenate("SELECT [LastName] FROM
  • WHERE [Unit No] & [Street
    Number ] & [Street Name] ='" & [Unit No] & [Street Number] & [Street
    Name]
    & "'")


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

    FJ Questioner said:
    Sorry John, Me again!

    Here's the query I wrote.

    LastNames: Concatenate("SELECT [LastName] FROM
    • WHERE [Unit
      No]&""&[Street Number ] &"" & [Street Name] =""" & [X] & """")

      Here's the error message I get.

      "Too few Parameters. Expected 1."

      Not sure if this is of interest but here's the address concatenation
      column.

      X: ([Unit No] & "" & [Street Number] & "" & [Street Name])

      I must be getting close ??

      Thanks.
      FJ



      :

      Your query string needs some work.

      First: X is a string and as such the query must identify it as a
      string
      by
      using quote marks around X.
      Second: X is unknown in the table List so you must rebuild X in the
      where
      clause.

      I assumed that you concatenated in a space between Street Name and
      Street
      Number and I assumed that you built X in the same order and manner.

      Something like the following is what I would expect to see.

      LastNames: Concatenate("SELECT [LastName] FROM
      • WHERE [Street
        name ] &
        "" "" & [Street Number] =""" & [X] & """")

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

        message
        OK got it. It seemed to compile ok but when I ran the query I got
        an
        error
        message:

        Runtime error 3075. Syntax error (missing operator) in query
        expression.

        It then highlighted this Module line in Yellow:
        Set rs = db.OpenRecordset(pstrSQL)

        BTW, the actual query I wrote was as follows:
        LastNames: Concatenate("SELECT [LastName] FROM
        • WHERE [X] =" &
          [X])

          List=table with names and address
          X=a column within the same query in which I concatenated the street
          name
          and
          number into a single string.

          I'm zeroing in on it !

          Thanks once again.

          FJ



          :

          That was one of the lines that needed to be commented out. You are
          using
          DAO, so the lines that Duane has for ADO need to be commented out
          and
          the
          lines for DAO need to be uncommented. Near the top of the code, it
          should
          look like:

          '======For DAO uncomment next 4 lines========
          '====== comment out ADO below ========
          Dim db As DAO.Database
          Dim rs As DAO.Recordset
          Set db = CurrentDb()
          Set rs = db.OpenRecordset(pstrSQL)

          '======For ADO uncomment next two lines========
          '====== comment out DAO above ========
          'Dim rs As New ADODB.Recordset
          'rs.Open pstrSQL, CurrentProject.Connection, adOpenKeyset,
          adLockOptimistic

          And near the bottom of the function
          '====== comment out next line for ADO ===========
          Set db = Nothing

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

          message
          I copied the following into my new module (although I have no clue
          what
          it
          means). When I ran Compile, I got the error message: "User
          defined
          Type
          not
          defined" and
          the "rs As New ADODB.Recordset" in the 2nd line of the commented
          in
          section
          was highlighted (not in yellow but in blue as if I was about to
          copy
          that
          section).

          Incidentally I checked the appropriate Microsoft DAO x.x Object
          Library
          as
          you suggested.

          Any idea as to what's wrong?

          Thanks again.



          Function Concatenate(pstrSQL As String, _
          Optional pstrDelim As String = ", ") _
          As String
          'Created by Duane Hookom, 2003
          'this code may be included in any application/mdb providing
          ' this statement is left intact
          'example
          'tblFamily with FamID as numeric primary key
          'tblFamMem with FamID, FirstName, DOB,...
          'return a comma separated list of FirstNames
          'for a FamID
          ' John, Mary, Susan
          'in a Query
          'SELECT FamID,
          'Concatenate("SELECT FirstName FROM tblFamMem
          ' WHERE FamID =" & [FamID]) as FirstNames
          'FROM tblFamily
          '

          '======For DAO uncomment next 4 lines=======
          '====== comment out ADO below =======
          'Dim db As DAO.Database
          'Dim rs As DAO.Recordset
          'Set db = CurrentDb
          'Set rs = db.OpenRecordset(pstrSQL)

          '======For ADO uncomment next two lines=====
          '====== comment out DAO above ======
          Dim rs As New ADODB.Recordset
          rs.Open pstrSQL, CurrentProject.Connection, _
          adOpenKeyset, adLockOptimistic
          Dim strConcat As String 'build return string
          With rs
          If Not .EOF Then
          .MoveFirst
          Do While Not .EOF
          strConcat = strConcat & _
          .Fields(0) & pstrDelim
          .MoveNext
          Loop
          End If
          .Close
          End With
          Set rs = Nothing
          '====== uncomment next line for DAO ========
          'Set db = Nothing
          If Len(strConcat) > 0 Then
          strConcat = Left(strConcat, _
          Len(strConcat) - Len(pstrDelim))
          End If
          Concatenate = strConcat
          End Function


          :

          Ok

          Open Your database
          Select Modules
          Click on NEW
          Paste in the Concatenate code from the Sample
          --- Check the code
          SELECT Debug: Compile from the menu
          If you get errors then you will need to fix the cause. Most of
          the
          time
          the
          error will be due to lines wrapping.

          For Duane's code, you will probably have to make sure you have
          the
          DAO
          library
          Menu: Tools: References
          If you don't have Microsoft DAO x.x Object Library, scroll
          down
          the
          list,
          find it, and check it.
          Close the dialog window

          Duane's code has two places where you have to comment out a bit
          of
          code
          and
          comment in a different bit of code. He has comments on where to
          do
          that
          in
          the code. Comment out requires you to place an apostrophe at
          the
          start
          of
          the line(s) to be commented out. Comment in - remove the
          apostrophe.

          Select Debug: Compile again and see if the code compiles.

          NOW SAVE the module as MOD_Concatenate

          Try your query again.


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

          in
          message
          Sorry, I just gave it a whirl but to ne effect. I'm going to
          need
          the
          step
          by
          step......



          :

          Well, you need to do so or the concatenate function will not
          work.

          Do you know how to do this or are you going to require step
          by
          step
          directions?

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

          "FJ Questioner" <[email protected]>
          wrote
          in
          message
          No I just wrote it in a regular query (as per Hookum's
          example).
    Click to expand...
Click to expand...
Click to expand...
 
F

FJ Questioner

OK so here's what I've got (spaces are added just for clarity)

LastNames: Concatenate("SELECT [Last Name] FROM
  • WHERE [Unit
    No]&[Street Number ] & [Street Name] ="' & [Unit No] & [Street Number] &
    [Street Name] & ""')

    Now I get the same error message: Runtime error 3061. Too few parameters.
    Expected 1.

    Incidentally, I got the same error mesage even when I tried the above with 2
    versions of the street concatenation. One with spaces and one without ie.

    X: ([Unit No] & "" & [Street Number] & "" & [Street Name])

    and

    X: ([Unit No] & [Street Number] & [Street Name])

    Thanks again,
    FJ

    John Spencer said:
    Try the following (take out the spaces)

    = ' "& [Unit No] & [Street Number] & [Street Name] & " ' "

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

    FJ Questioner said:
    I think this is going to work because the error message I'm getting only
    tells me I have invalid sytax and points to the phrase below. I suspect it
    has to do with the sequence of single and double quotaion marks. Do I
    have
    these right? (I left spaces between them in this example to show you what
    I
    did:

    =" ' & [Unit No] & [Street Number] & [Street Name] & "" '

    Thanks. Hopefully this'll be the last iteration!


    John Spencer said:
    Ok, there are a couple possibilities.
    One - is the field LastName or is it Last Space Name. Or one of the
    other
    field names is misspelled.
    Second - X is not being recognized as the concatenation and you will have
    to
    build that into the query string.

    Since you are separating the fields with a zero-length string, you should
    be
    able to use
    Concatenate("SELECT [LastName] FROM
    • WHERE [Unit No] & [Street
      Number ] & [Street Name] ='" & [Unit No] & [Street Number] & [Street
      Name]
      & "'")


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

      Sorry John, Me again!

      Here's the query I wrote.

      LastNames: Concatenate("SELECT [LastName] FROM
      • WHERE [Unit
        No]&""&[Street Number ] &"" & [Street Name] =""" & [X] & """")

        Here's the error message I get.

        "Too few Parameters. Expected 1."

        Not sure if this is of interest but here's the address concatenation
        column.

        X: ([Unit No] & "" & [Street Number] & "" & [Street Name])

        I must be getting close ??

        Thanks.
        FJ



        :

        Your query string needs some work.

        First: X is a string and as such the query must identify it as a
        string
        by
        using quote marks around X.
        Second: X is unknown in the table List so you must rebuild X in the
        where
        clause.

        I assumed that you concatenated in a space between Street Name and
        Street
        Number and I assumed that you built X in the same order and manner.

        Something like the following is what I would expect to see.

        LastNames: Concatenate("SELECT [LastName] FROM
        • WHERE [Street
          name ] &
          "" "" & [Street Number] =""" & [X] & """")

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

          message
          OK got it. It seemed to compile ok but when I ran the query I got
          an
          error
          message:

          Runtime error 3075. Syntax error (missing operator) in query
          expression.

          It then highlighted this Module line in Yellow:
          Set rs = db.OpenRecordset(pstrSQL)

          BTW, the actual query I wrote was as follows:
          LastNames: Concatenate("SELECT [LastName] FROM
          • WHERE [X] =" &
            [X])

            List=table with names and address
            X=a column within the same query in which I concatenated the street
            name
            and
            number into a single string.

            I'm zeroing in on it !

            Thanks once again.

            FJ



            :

            That was one of the lines that needed to be commented out. You are
            using
            DAO, so the lines that Duane has for ADO need to be commented out
            and
            the
            lines for DAO need to be uncommented. Near the top of the code, it
            should
            look like:

            '======For DAO uncomment next 4 lines========
            '====== comment out ADO below ========
            Dim db As DAO.Database
            Dim rs As DAO.Recordset
            Set db = CurrentDb()
            Set rs = db.OpenRecordset(pstrSQL)

            '======For ADO uncomment next two lines========
            '====== comment out DAO above ========
            'Dim rs As New ADODB.Recordset
            'rs.Open pstrSQL, CurrentProject.Connection, adOpenKeyset,
            adLockOptimistic

            And near the bottom of the function
            '====== comment out next line for ADO ===========
            Set db = Nothing

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

            message
            I copied the following into my new module (although I have no clue
            what
            it
            means). When I ran Compile, I got the error message: "User
            defined
            Type
            not
            defined" and
            the "rs As New ADODB.Recordset" in the 2nd line of the commented
            in
            section
            was highlighted (not in yellow but in blue as if I was about to
            copy
            that
            section).

            Incidentally I checked the appropriate Microsoft DAO x.x Object
            Library
            as
            you suggested.

            Any idea as to what's wrong?

            Thanks again.



            Function Concatenate(pstrSQL As String, _
            Optional pstrDelim As String = ", ") _
            As String
            'Created by Duane Hookom, 2003
            'this code may be included in any application/mdb providing
            ' this statement is left intact
            'example
            'tblFamily with FamID as numeric primary key
            'tblFamMem with FamID, FirstName, DOB,...
            'return a comma separated list of FirstNames
            'for a FamID
            ' John, Mary, Susan
            'in a Query
            'SELECT FamID,
            'Concatenate("SELECT FirstName FROM tblFamMem
            ' WHERE FamID =" & [FamID]) as FirstNames
            'FROM tblFamily
            '

            '======For DAO uncomment next 4 lines=======
            '====== comment out ADO below =======
            'Dim db As DAO.Database
            'Dim rs As DAO.Recordset
            'Set db = CurrentDb
            'Set rs = db.OpenRecordset(pstrSQL)

            '======For ADO uncomment next two lines=====
            '====== comment out DAO above ======
            Dim rs As New ADODB.Recordset
            rs.Open pstrSQL, CurrentProject.Connection, _
            adOpenKeyset, adLockOptimistic
            Dim strConcat As String 'build return string
            With rs
            If Not .EOF Then
            .MoveFirst
            Do While Not .EOF
            strConcat = strConcat & _
            .Fields(0) & pstrDelim
            .MoveNext
            Loop
            End If
            .Close
            End With
            Set rs = Nothing
            '====== uncomment next line for DAO ========
            'Set db = Nothing
            If Len(strConcat) > 0 Then
            strConcat = Left(strConcat, _
            Len(strConcat) - Len(pstrDelim))
            End If
            Concatenate = strConcat
            End Function


            :

            Ok

            Open Your database
            Select Modules
            Click on NEW
            Paste in the Concatenate code from the Sample
            --- Check the code
            SELECT Debug: Compile from the menu
            If you get errors then you will need to fix the cause. Most of
            the
            time
            the
            error will be due to lines wrapping.

            For Duane's code, you will probably have to make sure you have
            the
            DAO
            library
            Menu: Tools: References
            If you don't have Microsoft DAO x.x Object Library, scroll
            down
            the
            list,
            find it, and check it.
            Close the dialog window

            Duane's code has two places where you have to comment out a bit
            of
            code
            and
            comment in a different bit of code. He has comments on where to
            do
            that
            in
            the code. Comment out requires you to place an apostrophe at
            the
            start
            of
            the line(s) to be commented out. Comment in - remove the
            apostrophe.
    Click to expand...
    Click to expand...
    Click to expand...
 
J

John Spencer

LastNames: Concatenate("SELECT [Last Name] FROM
  • WHERE [Unit
    No]&[Street Number ] & [Street Name] ="' & [Unit No] & [Street Number] &
    [Street Name] & ""')

    It still appears to me as if you have the apostrophes and quotes switched.
    Let's try a slight differenct approach.
    LastNames: Concatenate("SELECT [Last Name] FROM

    • WHERE [Unit No]&[Street Number ] & [Street Name] ="
      & Chr(34) & [Unit No] & [Street Number] & [Street Name] & Chr(34))

      If that fails, then obviously I am not giving you the correct advice.
      --
      John Spencer
      Access MVP 2002-2005, 2007-2008
      Center for Health Program Development and Management
      University of Maryland Baltimore County
      ..
 

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

separating first and last names when they are in the same cell 4
separating data into new table 3
merging separate records into one field for mail merge 2
Word Create a Phone Book using MailMerge in word 2007 0
Multiple records in table to display single records 7
Creating Multiple Records from Single Delimited Field 1
SQL to combine columns of multiple rows into one row 9
SQL for specific pattern of output? 2

Top