Help with syntax

[Al]

the following sql is giving me an error message:Run-time error '3075': Syntax
error in string in query expression'" AS Concentration FROM tblVitalSigns
WHERE [PatientID]='004444".
****************************************
vsql_app = "INSERT INTO DRpt_VitalsRawData ( PhysicalExamID, PatientID,
MedicationType, DateofExam, MedicationVol, " & _
"MedicationFrequency, BodyWt, Discontinued, Concentration ) " & _
"SELECT PhysicalExamID, PatientID, "" AS MedicationType, DateofExam, "" AS
MedicationVol, "" AS MedicationFrequency, " & _
"BodyWt, "" AS Discontinued, "" AS Concentration " & _
"FROM tblVitalSigns WHERE [PatientID] ='" & Me![txtPatientID] & "'"
db.Execute vsql_app, dbFailOnError
****************************************
I am running the above sql from a form, when a button is clicked. If I run
this, straight from a query, it works. the sql behind the query looks like
this:
********************
INSERT INTO DRpt_VitalsRawData ( PhysicalExamID, PatientID, MedicationType,
DateofExam, MedicationVol, MedicationFrequency, BodyWt, Discontinued,
Concentration )
SELECT tblVitalSigns.PhysicalExamID, tblVitalSigns.PatientID, "" AS
MedicationType, tblVitalSigns.DateofExam, "" AS MedicationVol, "" AS
MedicationFrequency, tblVitalSigns.BodyWt, "" AS Discontinued, "" AS
Concentration
FROM tblVitalSigns
WHERE (((tblVitalSigns.PatientID)="004444"))
************************************
any idea, what am I doing wrong?
thanks
Al

 

[kingston via AccessMonster.com]

I think your problem is with the use of double quotes in the middle of your
SQL string. To indicate an empty string within an SQL string, use '' (two
single quotes). So, "" AS MedicationType should be '' AS MedicationType, etc.

the following sql is giving me an error message:Run-time error '3075': Syntax
error in string in query expression'" AS Concentration FROM tblVitalSigns
WHERE [PatientID]='004444".
****************************************
vsql_app = "INSERT INTO DRpt_VitalsRawData ( PhysicalExamID, PatientID,
MedicationType, DateofExam, MedicationVol, " & _
"MedicationFrequency, BodyWt, Discontinued, Concentration ) " & _
"SELECT PhysicalExamID, PatientID, "" AS MedicationType, DateofExam, "" AS
MedicationVol, "" AS MedicationFrequency, " & _
"BodyWt, "" AS Discontinued, "" AS Concentration " & _
"FROM tblVitalSigns WHERE [PatientID] ='" & Me![txtPatientID] & "'"
db.Execute vsql_app, dbFailOnError
****************************************
I am running the above sql from a form, when a button is clicked. If I run
this, straight from a query, it works. the sql behind the query looks like
this:
********************
INSERT INTO DRpt_VitalsRawData ( PhysicalExamID, PatientID, MedicationType,
DateofExam, MedicationVol, MedicationFrequency, BodyWt, Discontinued,
Concentration )
SELECT tblVitalSigns.PhysicalExamID, tblVitalSigns.PatientID, "" AS
MedicationType, tblVitalSigns.DateofExam, "" AS MedicationVol, "" AS
MedicationFrequency, tblVitalSigns.BodyWt, "" AS Discontinued, "" AS
Concentration
FROM tblVitalSigns
WHERE (((tblVitalSigns.PatientID)="004444"))
************************************
any idea, what am I doing wrong?
thanks
Al

 

[Al]

No that is not the reason. I have a similar query that is working OK. I have
tryed it the way you suggested anyway and still did not work.
Al

I think your problem is with the use of double quotes in the middle of your
SQL string. To indicate an empty string within an SQL string, use '' (two
single quotes). So, "" AS MedicationType should be '' AS MedicationType, etc.

the following sql is giving me an error message:Run-time error '3075': Syntax
error in string in query expression'" AS Concentration FROM tblVitalSigns
WHERE [PatientID]='004444".
****************************************
vsql_app = "INSERT INTO DRpt_VitalsRawData ( PhysicalExamID, PatientID,
MedicationType, DateofExam, MedicationVol, " & _
"MedicationFrequency, BodyWt, Discontinued, Concentration ) " & _
"SELECT PhysicalExamID, PatientID, "" AS MedicationType, DateofExam, "" AS
MedicationVol, "" AS MedicationFrequency, " & _
"BodyWt, "" AS Discontinued, "" AS Concentration " & _
"FROM tblVitalSigns WHERE [PatientID] ='" & Me![txtPatientID] & "'"
db.Execute vsql_app, dbFailOnError
****************************************
I am running the above sql from a form, when a button is clicked. If I run
this, straight from a query, it works. the sql behind the query looks like
this:
********************
INSERT INTO DRpt_VitalsRawData ( PhysicalExamID, PatientID, MedicationType,
DateofExam, MedicationVol, MedicationFrequency, BodyWt, Discontinued,
Concentration )
SELECT tblVitalSigns.PhysicalExamID, tblVitalSigns.PatientID, "" AS
MedicationType, tblVitalSigns.DateofExam, "" AS MedicationVol, "" AS
MedicationFrequency, tblVitalSigns.BodyWt, "" AS Discontinued, "" AS
Concentration
FROM tblVitalSigns
WHERE (((tblVitalSigns.PatientID)="004444"))
************************************
any idea, what am I doing wrong?
thanks
Al

 

[Al]

OK never mind my last post. You were right. I just made an (") mistake. It
works. but why from the vb code it has to be single quote (') and from sql it
takes double qoute (") I never understood the rule?
thanks a lot.
Al

I think your problem is with the use of double quotes in the middle of your
SQL string. To indicate an empty string within an SQL string, use '' (two
single quotes). So, "" AS MedicationType should be '' AS MedicationType, etc.

the following sql is giving me an error message:Run-time error '3075': Syntax
error in string in query expression'" AS Concentration FROM tblVitalSigns
WHERE [PatientID]='004444".
****************************************
vsql_app = "INSERT INTO DRpt_VitalsRawData ( PhysicalExamID, PatientID,
MedicationType, DateofExam, MedicationVol, " & _
"MedicationFrequency, BodyWt, Discontinued, Concentration ) " & _
"SELECT PhysicalExamID, PatientID, "" AS MedicationType, DateofExam, "" AS
MedicationVol, "" AS MedicationFrequency, " & _
"BodyWt, "" AS Discontinued, "" AS Concentration " & _
"FROM tblVitalSigns WHERE [PatientID] ='" & Me![txtPatientID] & "'"
db.Execute vsql_app, dbFailOnError
****************************************
I am running the above sql from a form, when a button is clicked. If I run
this, straight from a query, it works. the sql behind the query looks like
this:
********************
INSERT INTO DRpt_VitalsRawData ( PhysicalExamID, PatientID, MedicationType,
DateofExam, MedicationVol, MedicationFrequency, BodyWt, Discontinued,
Concentration )
SELECT tblVitalSigns.PhysicalExamID, tblVitalSigns.PatientID, "" AS
MedicationType, tblVitalSigns.DateofExam, "" AS MedicationVol, "" AS
MedicationFrequency, tblVitalSigns.BodyWt, "" AS Discontinued, "" AS
Concentration
FROM tblVitalSigns
WHERE (((tblVitalSigns.PatientID)="004444"))
************************************
any idea, what am I doing wrong?
thanks
Al

 

[kingston via AccessMonster.com]

In VB, you are trying to create a string, and in VB whatever is within a set
of double quotes is interpreted as part of the string. One way to see if a
long string is valid is to look at pairs of double quotation marks:

"my string" & variable & "more string" & variable2...

Parts that are inside a pair of double quotes are literally interpreted as
part of the string. Also all parts must be concatenated with an ampersand (&)
.. So in order to indicate a zero-length string, you need to create a
variable that is set to "" or you can use a pair of single quotes within the
string (so that it doesn't confuse the VB compiler).

OK never mind my last post. You were right. I just made an (") mistake. It
works. but why from the vb code it has to be single quote (') and from sql it
takes double qoute (") I never understood the rule?
thanks a lot.
Al

I think your problem is with the use of double quotes in the middle of your
SQL string. To indicate an empty string within an SQL string, use '' (two

[quoted text clipped - 30 lines]