Douglas J. Steele said:
You've got loSQL defined: what are you doing with it?
What happens if you paste
SELECT DISTINCT Data.Pkg,
fConcatFld("Data","PKG","Code","String",[Pkg]) AS Expr1 FROM Data
into a query and run the query?
--
Doug Steele, Microsoft Access MVP
(no e-mails, please!)
Entered """". Closed the module. Tried to rerun it from a macro.
Received
the
error message "The expression you entered has a function containing the
wrong
number of arguments."
Function fConcatFld(stTable As String, _ = Data (table)
stForFld As String, _ = pkg (lookup value)
stFldToConcat As String, _ = code (field to
concatenate)
stForFldType As String, _ = string (data type)
vForFldVal As Variant) _ = pkg (value on which to
return string
As String
loSQL = "SELECT DISTINCT Data.pkg, " & _
"fConcatFld(""Data"",""pkg"",""code"",""String"",[pkg]) " & _
"AS Expr1 FROM Data"
Aren't all the arguments accounted for?
:
I copied and pasted...
But I will go in again and "rewrite" 4 double quotes.
:
Did you copy-and-paste from the webpage, or did you retype?
cQ is defined as
Const cQ = """"
That's 4 double quotes in a row.
--
Doug Steele, Microsoft Access MVP
(no e-mails, please!)
Yes. I get the statement using Debug.Print.
Now, when I compile I am getting a Constant Expression Required
error
message at the first 'cQ' in the first select statement.
:
Yes, after rereading your directions initially, I found the typo
and
fixed
it. I will get back to this and see what my issue is within the
code.
Thanks for your help and your patience. I will let you know what
has
happened one way or another.
:
Do not change the function! It works as is: I wouldn't have
posted
the
formula I did if I wasn't able to get it working in my sample
database.
The
function doesn't need changes for your usage. vForFldVal in
what I
gave
you
is [Pkg]. It's a variant because there's no way to know whether
you're
going
to be passing it a numeric, text or date value.
Use Debug.Print to print the content of loSQL to the Immediate
Window
(Ctrl-G). What appears there should be:
SELECT DISTINCT Data.Pkg,
fConcatFld("Data","PKG","Code","String",[Pkg]) AS
Expr1 FROM Data
Note that there was a typo in what I posted earlier. As I said,
to
put
a
double quote inside a string, you need to double the quote
symbol.
One
of
the quote symbols wasn't doubled:
loSQL = "SELECT DISTINCT Data.Pkg, " & _
"fConcatFld(""Data"",""PKG"",""Code"",""String"",[Pkg]) " &
_
"AS Expr1 FROM Data"
--
Doug Steele, Microsoft Access MVP
(no e-mails, please!)
Okay.
Added the quotes. Compiled it and received the message that
the
function
could not be found. It is the ONLY function in the db so I
took
the
table
and
the module and the macro to run the module and moved it to a
new
db.
Compiled
it there and received the message that there is the wrong
number
of
arguments. Went to the area where the function is declared
and
removed
vForFldVal As Variant because there is no variant.
Recompiled and still receiving wrong number of arguments. In
the
function
declarations there are four declarations. In the SQL
statement,
there
are
four arguments except for Expr1. Could that have something to
do
with
it?
There are many Access users here but they use high level
queries
and
no
coding so I am basically alone on this. Thank you for your
help.
:
It's because you're not setting the SQL string properly.
loSQL = "SELECT DISTINCT Data.Pkg, " & _
"fConcatFld(""Data"",""PKG"",""Code",""String"",[Pkg]) " &
_
"AS Expr1 FROM Data"
Note that in order to put quoted strings inside the string,
you
need
to
double the quotes.
--
Doug Steele, Microsoft Access MVP
(no e-mails, please!)
Okay. I reset the statements to include "DAO". I went into
References
and
added the MS 3.6 Object Library.
The SQL statement you sent earlier I setup as:
loSQL = SELECT DISTINCT Data.Pkg,
fConcatFld("Data","PKG","Code","String",[Pkg]) AS Expr1
FROM
Data;
It is at this point I am receiving a compile error and/or
a
syntax
error.
I receive a syntax error. The error starts at loSQL and
ends
at
AS,
which
also happens to be where the display breaks the statement.
If I put a set of quotes just before SELECT, I get an
end-of-statement
error
at "Data".
I seem to be going in circles. Again, thank you for your
help.
(This is
when
a good VBA book would come in very handy.)
How do you know which libraries should be referenced and
when? I
had no
idea
that the 3.6 Object Library was needed.
- Danu
:
I'm afraid I don't understand what sort of problems
you're
having.
That
code
works perfectly: I ran it to ensure that what I suggested
would
work.
There
is one change that's required on newer versions of
Access,
though.
Change
Dim lodb As Database, lors As Recordset
to
Dim lodb As DAO.Database, lors As DAO.Recordset
If that doesn't compile, then perhaps you don't have a
reference
set
to
the
DAO library. With any code module open, select Tools |
References
from
the
menu bar, scroll through the list of available references
until
you
find
the
one for Microsoft DAO 3.6 Object Library, and select it.
What "statement is too long"?
