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"?
--
Doug Steele, Microsoft Access MVP
(no private e-mails, please)
Okay. I "rewrote" the code below, substituting my fields
and
table
for
the
fields and table in the code.
I tried to compile the code and got declaration errors.
"Remmed"
some
out.
Finally got to the code and the entire statement is too
long.
Tried
to
break
it up into three lines and now I am getting "end of
statement"
errors.
I don't want to keep pestering...is there anywhere I can
go for
these
errors
which everyone must run into? (deep sigh)
Thanks.
:
Assuming the table is named "Data", use:
SELECT DISTINCT Data.Pkg,
fConcatFld("Data","PKG","Code","String",[Pkg])
AS
Expr1
FROM Data;
--
Doug Steele, Microsoft Access MVP
(no e-mails, please!)
I'm sorry. I'd like a record to look like:
QWF 33,76,12
MNO 48,12
XYZ 82,22,34,87,77
(not necessarily using a comma as a separator)
:
What are you expecting to see given that data?
--
Doug Steele, Microsoft Access MVP
(no e-mails, please!)
message
Thanks, Doug. I think the second module is the more
appropriate,
but
there
is
no value, such as "Owner", to let the module know
when to
create
the
string.
The values in the Detail column are all different.
There
is,
however, a
column in the table which lets the user know how
many
detail
codes
are
in
each package.
PKG AMT CODE
QWF 3 33
QWF 3 76
QWF 3 12
MNO 2 48
MNO 2 12
XYZ 5 82
XYZ 5 22
XYZ 5 34
XYZ 5 87
XYZ 5 77
Can that field be used as a flag to create the
string?
- Danu
:
Take a look at
http://www.mvps.org/access/modules/mdl0004.htm
and/or
http://www.mvps.org/access/modules/mdl0008.htm at
"The
Access
Web"
