Prev: Delete Query not Working
Next: Changing constants in a query to user input values
From: AllenK on 2 Jun 2010 15:21
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!
From: Bob Barrows on 2 Jun 2010 21:30
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"


From: AllenK on 3 Jun 2010 08:12
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"
>
>
> .
>
 | 
Pages: 1
Prev: Delete Query not Working
Next: Changing constants in a query to user input values