A Microsoft Office (Excel, Word) forum. OfficeFrustration

If this is your first visit, be sure to check out the FAQ by clicking the link above. You may have to register before you can post: click the register link above to proceed. To start viewing messages, select the forum that you want to visit from the selection below.

Go Back   Home » OfficeFrustration forum » Microsoft Access » Running & Setting Up Queries
Site Map Home Register Authors List Search Today's Posts Mark Forums Read  

2007 Concatenation problems



 
 
Thread Tools Display Modes
  #1  
Old June 2nd, 2010, 08:21 PM posted to microsoft.public.access.queries
AllenK
external usenet poster
 
Posts: 5
Default 2007 Concatenation problems

I am using Mr. Hookums formula he gave me for concatenation in Access 2003
and it works fine. As soon as I copy it into 2007 I get error messages. I
have tried converting the database to 2007, but get errors. I created 2007
database and imported all files from 2003. Switchboard and concatenation no
longer work.
Here is what I am getting for messages.

Microsoft Visual Basic "Compile error: User-defined type not defined" and
here is the code, first three lines below are highlighted yellow;
Function Concatenate(pstrSQL As String, _
Optional pstrDelim As String = ", ") _
As String

Dim rs As New ADODB.Recordset
rs.Open pstrSQL, CurrentProject.Connection, _
adOpenKeyset, adLockOptimistic
Dim strConcat As 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

If Len(strConcat) 0 Then
strConcat = Left(strConcat, _
Len(strConcat) - Len(pstrDelim))
End If
Concatenate = strConcat
End Function


Here is the query I am trying to run;
OE Number: Concatenate("SELECT comp_no FROM OE_Interchanges_tbl WHERE
part_no ='" & [part_no] & "'")

--
Thank you to all who have helped me!
  #2  
Old June 3rd, 2010, 02:30 AM posted to microsoft.public.access.queries
Bob Barrows
external usenet poster
 
Posts: 475
Default 2007 Concatenation problems

AllenK wrote:
I am using Mr. Hookums formula he gave me for concatenation in Access
2003 and it works fine. As soon as I copy it into 2007 I get error
messages. I have tried converting the database to 2007, but get
errors. I created 2007 database and imported all files from 2003.
Switchboard and concatenation no longer work.
Here is what I am getting for messages.

Microsoft Visual Basic "Compile error: User-defined type not defined"
and here is the code, first three lines below are highlighted yellow;
Function Concatenate(pstrSQL As String, _
Optional pstrDelim As String = ", ") _
As String

Dim rs As New ADODB.Recordset


You need to set a reference to the ADO library ... open the module
containing the code, go to Tools|References, and scroll down and select the
latest version of the Microsoft ActiveX Objects installed on your machine
(probably 2.8)
--
Microsoft MVP - ASP/ASP.NET - 2004-2007
Please reply to the newsgroup. This email account is my spam trap so I
don't check it very often. If you must reply off-line, then remove the
"NO SPAM"


  #3  
Old June 3rd, 2010, 01:12 PM posted to microsoft.public.access.queries
AllenK
external usenet poster
 
Posts: 5
Default 2007 Concatenation problems

It worked! Thank you sooooo much!!!!
--
Thank you to all who have helped me!


"Bob Barrows" wrote:

AllenK wrote:
I am using Mr. Hookums formula he gave me for concatenation in Access
2003 and it works fine. As soon as I copy it into 2007 I get error
messages. I have tried converting the database to 2007, but get
errors. I created 2007 database and imported all files from 2003.
Switchboard and concatenation no longer work.
Here is what I am getting for messages.

Microsoft Visual Basic "Compile error: User-defined type not defined"
and here is the code, first three lines below are highlighted yellow;
Function Concatenate(pstrSQL As String, _
Optional pstrDelim As String = ", ") _
As String

Dim rs As New ADODB.Recordset


You need to set a reference to the ADO library ... open the module
containing the code, go to Tools|References, and scroll down and select the
latest version of the Microsoft ActiveX Objects installed on your machine
(probably 2.8)
--
Microsoft MVP - ASP/ASP.NET - 2004-2007
Please reply to the newsgroup. This email account is my spam trap so I
don't check it very often. If you must reply off-line, then remove the
"NO SPAM"


.

 




Thread Tools
Display Modes

Posting Rules
You may not post new threads
You may not post replies
You may not post attachments
You may not edit your posts

vB code is On
Smilies are On
[IMG] code is Off
HTML code is Off
Forum Jump


All times are GMT +1. The time now is 05:08 PM.


Powered by vBulletin® Version 3.6.4
Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
Copyright ©2004-2024 OfficeFrustration.
The comments are property of their posters.