concatenate 3075 error

L

Ldappa

I've read all the threads but cannot figure out why i'm getting this message.
Below is my code I copied from "Concat2k" when I downloaded it.

No matter what I try I still get the error, is it because there are some
blank records in the Bannerintake table?

SELECT INTAKE.STUDENTID, INTAKE.LNFN, INTAKE.studentidnum,
Concatenate("SELECT mathcourse FROM bannerintake WHERE studentidnum =" &
[studentidnum]) AS [Mathcourses Taken], Concatenate("SELECT courseinst FROM
bannerintake WHERE studentidnum =" & [studentidnum]) AS [COURSE Intst]
FROM INTAKE;

thanks for your help.
lorna
 
S

Sylvain Lafontaine

There is no Concatenate() function in Access VBA or SQL, so I suppose that
this is an user function defined in the file Concat2k and that you have
forgot to copie it over to your own database file.

--
Sylvain Lafontaine, ing.
MVP - Windows Live Platform
Email: sylvain2009 sylvainlafontaine com (fill the blanks, no spam please)
Independent consultant and remote programming for Access and SQL-Server
(French)
 
L

ldappa

Nope, I copied the basConcatenate file from the Concat2k download but it
brings up an error message "syntax error(missing operator) in query
expression 'studentidnum='. then if i debug it goes to the line in the
basConcatenate that reads "Set rs = db.OpenRecordset(pstrSQL)"


Sylvain Lafontaine said:
There is no Concatenate() function in Access VBA or SQL, so I suppose that
this is an user function defined in the file Concat2k and that you have
forgot to copie it over to your own database file.

--
Sylvain Lafontaine, ing.
MVP - Windows Live Platform
Email: sylvain2009 sylvainlafontaine com (fill the blanks, no spam please)
Independent consultant and remote programming for Access and SQL-Server
(French)


Ldappa said:
I've read all the threads but cannot figure out why i'm getting this
message.
Below is my code I copied from "Concat2k" when I downloaded it.

No matter what I try I still get the error, is it because there are some
blank records in the Bannerintake table?

SELECT INTAKE.STUDENTID, INTAKE.LNFN, INTAKE.studentidnum,
Concatenate("SELECT mathcourse FROM bannerintake WHERE studentidnum =" &
[studentidnum]) AS [Mathcourses Taken], Concatenate("SELECT courseinst
FROM
bannerintake WHERE studentidnum =" & [studentidnum]) AS [COURSE Intst]
FROM INTAKE;

thanks for your help.
lorna
Click to expand...
Click to expand...
 
S

Sylvain Lafontaine

From where have you got this Concat2k file?

--
Sylvain Lafontaine, ing.
MVP - Windows Live Platform
Email: sylvain2009 sylvainlafontaine com (fill the blanks, no spam please)
Independent consultant and remote programming for Access and SQL-Server
(French)


ldappa said:
Nope, I copied the basConcatenate file from the Concat2k download but it
brings up an error message "syntax error(missing operator) in query
expression 'studentidnum='. then if i debug it goes to the line in the
basConcatenate that reads "Set rs = db.OpenRecordset(pstrSQL)"


Sylvain Lafontaine said:
There is no Concatenate() function in Access VBA or SQL, so I suppose
that this is an user function defined in the file Concat2k and that you
have forgot to copie it over to your own database file.

--
Sylvain Lafontaine, ing.
MVP - Windows Live Platform
Email: sylvain2009 sylvainlafontaine com (fill the blanks, no spam
please)
Independent consultant and remote programming for Access and SQL-Server
(French)


Ldappa said:
I've read all the threads but cannot figure out why i'm getting this
message.
Below is my code I copied from "Concat2k" when I downloaded it.

No matter what I try I still get the error, is it because there are some
blank records in the Bannerintake table?

SELECT INTAKE.STUDENTID, INTAKE.LNFN, INTAKE.studentidnum,
Concatenate("SELECT mathcourse FROM bannerintake WHERE studentidnum =" &
[studentidnum]) AS [Mathcourses Taken], Concatenate("SELECT courseinst
FROM
bannerintake WHERE studentidnum =" & [studentidnum]) AS [COURSE Intst]
FROM INTAKE;

thanks for your help.
lorna
Click to expand...
Click to expand...
Click to expand...
 
S

Sylvain Lafontaine

Also, could you show us the exact content of the string pstrSQL? Use «
Debug.Print pstrSQL » for example.

--
Sylvain Lafontaine, ing.
MVP - Windows Live Platform
Email: sylvain2009 sylvainlafontaine com (fill the blanks, no spam please)
Independent consultant and remote programming for Access and SQL-Server
(French)


ldappa said:
Nope, I copied the basConcatenate file from the Concat2k download but it
brings up an error message "syntax error(missing operator) in query
expression 'studentidnum='. then if i debug it goes to the line in the
basConcatenate that reads "Set rs = db.OpenRecordset(pstrSQL)"


Sylvain Lafontaine said:
There is no Concatenate() function in Access VBA or SQL, so I suppose
that this is an user function defined in the file Concat2k and that you
have forgot to copie it over to your own database file.

--
Sylvain Lafontaine, ing.
MVP - Windows Live Platform
Email: sylvain2009 sylvainlafontaine com (fill the blanks, no spam
please)
Independent consultant and remote programming for Access and SQL-Server
(French)


Ldappa said:
I've read all the threads but cannot figure out why i'm getting this
message.
Below is my code I copied from "Concat2k" when I downloaded it.

No matter what I try I still get the error, is it because there are some
blank records in the Bannerintake table?

SELECT INTAKE.STUDENTID, INTAKE.LNFN, INTAKE.studentidnum,
Concatenate("SELECT mathcourse FROM bannerintake WHERE studentidnum =" &
[studentidnum]) AS [Mathcourses Taken], Concatenate("SELECT courseinst
FROM
bannerintake WHERE studentidnum =" & [studentidnum]) AS [COURSE Intst]
FROM INTAKE;

thanks for your help.
lorna
Click to expand...
Click to expand...
Click to expand...
 
J

John Spencer MVP

If StudentIDNum is null then you will get an error.

Two choices:
Limit the records returned by the main query.
SELECT INTAKE.STUDENTID, INTAKE.LNFN, INTAKE.studentidnum,
Concatenate("SELECT mathcourse FROM bannerintake WHERE studentidnum =" &
[studentidnum]) AS [Mathcourses Taken], Concatenate("SELECT courseinst FROM
bannerintake WHERE studentidnum =" & [studentidnum]) AS [COURSE Intst]
FROM INTAKE
WHERE Intake.StudentIdNum is NOT NULL

OR pass an invalid value using NZ function so nothing gets returned.
Assumption: There is not StudentIdNum that is equal to -1.

SELECT INTAKE.STUDENTID, INTAKE.LNFN, INTAKE.studentidnum,
Concatenate("SELECT mathcourse FROM bannerintake WHERE studentidnum =" &
[studentidnum]) AS [Mathcourses Taken]
, Concatenate("SELECT courseinst FROM
bannerintake WHERE studentidnum =" & NZ([studentidnum],-1)) AS [COURSE Intst]
FROM INTAKE;


John Spencer
Access MVP 2002-2005, 2007-2009
The Hilltop Institute
University of Maryland Baltimore County
 
L

ldappa

Thanks John..
John Spencer MVP said:
If StudentIDNum is null then you will get an error.

Two choices:
Limit the records returned by the main query.
SELECT INTAKE.STUDENTID, INTAKE.LNFN, INTAKE.studentidnum,
Concatenate("SELECT mathcourse FROM bannerintake WHERE studentidnum =" &
[studentidnum]) AS [Mathcourses Taken], Concatenate("SELECT courseinst
FROM
bannerintake WHERE studentidnum =" & [studentidnum]) AS [COURSE Intst]
FROM INTAKE
WHERE Intake.StudentIdNum is NOT NULL

OR pass an invalid value using NZ function so nothing gets returned.
Assumption: There is not StudentIdNum that is equal to -1.

SELECT INTAKE.STUDENTID, INTAKE.LNFN, INTAKE.studentidnum,
Concatenate("SELECT mathcourse FROM bannerintake WHERE studentidnum =" &
[studentidnum]) AS [Mathcourses Taken]
, Concatenate("SELECT courseinst FROM
bannerintake WHERE studentidnum =" & NZ([studentidnum],-1)) AS [COURSE
Intst]
FROM INTAKE;


John Spencer
Access MVP 2002-2005, 2007-2009
The Hilltop Institute
University of Maryland Baltimore County
I've read all the threads but cannot figure out why i'm getting this
message.
Below is my code I copied from "Concat2k" when I downloaded it.

No matter what I try I still get the error, is it because there are some
blank records in the Bannerintake table?

SELECT INTAKE.STUDENTID, INTAKE.LNFN, INTAKE.studentidnum,
Concatenate("SELECT mathcourse FROM bannerintake WHERE studentidnum =" &
[studentidnum]) AS [Mathcourses Taken], Concatenate("SELECT courseinst
FROM bannerintake WHERE studentidnum =" & [studentidnum]) AS [COURSE
Intst]
FROM INTAKE;

thanks for your help.
lorna
Click to expand...
Click to expand...
 
L

ldappa

this code was submitted by
"Created by Duane Hookom, 2003"
Sylvain Lafontaine said:
From where have you got this Concat2k file?

--
Sylvain Lafontaine, ing.
MVP - Windows Live Platform
Email: sylvain2009 sylvainlafontaine com (fill the blanks, no spam please)
Independent consultant and remote programming for Access and SQL-Server
(French)


ldappa said:
Nope, I copied the basConcatenate file from the Concat2k download but it
brings up an error message "syntax error(missing operator) in query
expression 'studentidnum='. then if i debug it goes to the line in the
basConcatenate that reads "Set rs = db.OpenRecordset(pstrSQL)"


Sylvain Lafontaine said:
There is no Concatenate() function in Access VBA or SQL, so I suppose
that this is an user function defined in the file Concat2k and that you
have forgot to copie it over to your own database file.

--
Sylvain Lafontaine, ing.
MVP - Windows Live Platform
Email: sylvain2009 sylvainlafontaine com (fill the blanks, no spam
please)
Independent consultant and remote programming for Access and SQL-Server
(French)


I've read all the threads but cannot figure out why i'm getting this
message.
Below is my code I copied from "Concat2k" when I downloaded it.

No matter what I try I still get the error, is it because there are
some
blank records in the Bannerintake table?

SELECT INTAKE.STUDENTID, INTAKE.LNFN, INTAKE.studentidnum,
Concatenate("SELECT mathcourse FROM bannerintake WHERE studentidnum ="
&
[studentidnum]) AS [Mathcourses Taken], Concatenate("SELECT courseinst
FROM
bannerintake WHERE studentidnum =" & [studentidnum]) AS [COURSE Intst]
FROM INTAKE;

thanks for your help.
lorna
Click to expand...
Click to expand...
Click to expand...
 
L

ldappa

Thanks soooo much worked like a charm!!!!

Lorna
John Spencer MVP said:
If StudentIDNum is null then you will get an error.

Two choices:
Limit the records returned by the main query.
SELECT INTAKE.STUDENTID, INTAKE.LNFN, INTAKE.studentidnum,
Concatenate("SELECT mathcourse FROM bannerintake WHERE studentidnum =" &
[studentidnum]) AS [Mathcourses Taken], Concatenate("SELECT courseinst
FROM
bannerintake WHERE studentidnum =" & [studentidnum]) AS [COURSE Intst]
FROM INTAKE
WHERE Intake.StudentIdNum is NOT NULL

OR pass an invalid value using NZ function so nothing gets returned.
Assumption: There is not StudentIdNum that is equal to -1.

SELECT INTAKE.STUDENTID, INTAKE.LNFN, INTAKE.studentidnum,
Concatenate("SELECT mathcourse FROM bannerintake WHERE studentidnum =" &
[studentidnum]) AS [Mathcourses Taken]
, Concatenate("SELECT courseinst FROM
bannerintake WHERE studentidnum =" & NZ([studentidnum],-1)) AS [COURSE
Intst]
FROM INTAKE;


John Spencer
Access MVP 2002-2005, 2007-2009
The Hilltop Institute
University of Maryland Baltimore County
I've read all the threads but cannot figure out why i'm getting this
message.
Below is my code I copied from "Concat2k" when I downloaded it.

No matter what I try I still get the error, is it because there are some
blank records in the Bannerintake table?

SELECT INTAKE.STUDENTID, INTAKE.LNFN, INTAKE.studentidnum,
Concatenate("SELECT mathcourse FROM bannerintake WHERE studentidnum =" &
[studentidnum]) AS [Mathcourses Taken], Concatenate("SELECT courseinst
FROM bannerintake WHERE studentidnum =" & [studentidnum]) AS [COURSE
Intst]
FROM INTAKE;

thanks for your help.
lorna
Click to expand...
Click to expand...
 

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

Concatenate Error: It's just blank! 1
Concatenate - Error 3
Concatenate Error 10
Concatenate, data type mismatch error 2
Concatenate Error Help 11
Error Message trying to concatenate from multiple records to a field 9
Syntax error 3075 in VBA/SQL involving two LEFT JOINS 5
Query error: Undefined function 'Concatenate' in expression 3

Top