SQL where clause in Access VB

J

JC

Hello,

I am having trouble with the following SQL in VB in access. I want to
append the following data to an existing table in my database. The
following SQL gives me an error when trying to run. I want to use an
open unbound, form, text box to feed the criteria to the SQL statement.

Please help if you can. Below is the DoSql string:

The Where clause is giving me the problem...

DoCmd.RunSQL "INSERT INTO tblHurnsHistory ( SyStudentID, StudentName,
SSN, adTrmCode, GPA_creditsEarned, StatusDesc, StatusDate, LDA,
ProgVersCode, TermCode, term_credits, Grads_creditsEarned, TotalCrd,
creditsAttempt, GPA, CreditsRemaining, SumOfReceived, SumOfRefunded,
FundSource, Comment )" & _
"SELECT tblTotalCvueData.SyStudentID, tblTotalCvueData.StudentName,
tblTotalCvueData.SSN, tblTotalCvueData.adTrmCode,
tblTotalCvueData.GPA_creditsEarned, tblTotalCvueData.StatusDesc,
tblTotalCvueData.StatusDate, tblTotalCvueData.LDA,
tblTotalCvueData.ProgVersCode, tblTotalCvueData.TermCode,
tblTotalCvueData.term_credits, tblTotalCvueData.Grads_creditsEarned,
tblTotalCvueData.TotalCrd, tblTotalCvueData.creditsAttempt,
tblTotalCvueData.GPA, tblTotalCvueData.CreditsRemaining,
tblTotalLedger.SumOfReceived, tblTotalLedger.SumOfRefunded,
tblTotalLedger.FundSource, tblTotalCvueData.Comment" & _
"FROM tblTotalCvueData LEFT JOIN tblTotalLedger ON
tblTotalCvueData.SyStudentID = tblTotalLedger.SyStudentID" & _

"WHERE (tblTotalCvueData.adTrmCode=([Forms]![frmProcess]![txtTerm]))"

DoCmd.SetWarnings True

Thanks in advance,

Justin
 
J

Jeff L

It should be
"WHERE tblTotalCvueData.adTrmCode = '" & [Forms]![frmProcess]![txtTerm]
& "'"
for text value or
"WHERE tblTotalCvueData.adTrmCode = " & [Forms]![frmProcess]![txtTerm]
for numeric value

Hope that helps!
 
J

JC

Thanks for the reply Jeff, but I still get a missing operator error.
The debugger points me to the WHERE clause.

Remember this is for access only, not running this query against a SQL
server table, so I do not need the ' '.

Let me know if you ahve any more ideas.

Thanks

Justin

Jeff said:
It should be
"WHERE tblTotalCvueData.adTrmCode = '" & [Forms]![frmProcess]![txtTerm]
& "'"
for text value or
"WHERE tblTotalCvueData.adTrmCode = " & [Forms]![frmProcess]![txtTerm]
for numeric value

Hope that helps!

Hello,

I am having trouble with the following SQL in VB in access. I want to
append the following data to an existing table in my database. The
following SQL gives me an error when trying to run. I want to use an
open unbound, form, text box to feed the criteria to the SQL statement.

Please help if you can. Below is the DoSql string:

The Where clause is giving me the problem...

DoCmd.RunSQL "INSERT INTO tblHurnsHistory ( SyStudentID, StudentName,
SSN, adTrmCode, GPA_creditsEarned, StatusDesc, StatusDate, LDA,
ProgVersCode, TermCode, term_credits, Grads_creditsEarned, TotalCrd,
creditsAttempt, GPA, CreditsRemaining, SumOfReceived, SumOfRefunded,
FundSource, Comment )" & _
"SELECT tblTotalCvueData.SyStudentID, tblTotalCvueData.StudentName,
tblTotalCvueData.SSN, tblTotalCvueData.adTrmCode,
tblTotalCvueData.GPA_creditsEarned, tblTotalCvueData.StatusDesc,
tblTotalCvueData.StatusDate, tblTotalCvueData.LDA,
tblTotalCvueData.ProgVersCode, tblTotalCvueData.TermCode,
tblTotalCvueData.term_credits, tblTotalCvueData.Grads_creditsEarned,
tblTotalCvueData.TotalCrd, tblTotalCvueData.creditsAttempt,
tblTotalCvueData.GPA, tblTotalCvueData.CreditsRemaining,
tblTotalLedger.SumOfReceived, tblTotalLedger.SumOfRefunded,
tblTotalLedger.FundSource, tblTotalCvueData.Comment" & _
"FROM tblTotalCvueData LEFT JOIN tblTotalLedger ON
tblTotalCvueData.SyStudentID = tblTotalLedger.SyStudentID" & _

"WHERE (tblTotalCvueData.adTrmCode=([Forms]![frmProcess]![txtTerm]))"

DoCmd.SetWarnings True

Thanks in advance,

Justin
Click to expand...
Click to expand...
 
J

Jeff L

I looked at your original post and it looks like you need a space
before the Where Clause.

Thanks for the reply Jeff, but I still get a missing operator error.
The debugger points me to the WHERE clause.

Remember this is for access only, not running this query against a SQL
server table, so I do not need the ' '.

Let me know if you ahve any more ideas.

Thanks

Justin

Jeff said:
It should be
"WHERE tblTotalCvueData.adTrmCode = '" & [Forms]![frmProcess]![txtTerm]
& "'"
for text value or
"WHERE tblTotalCvueData.adTrmCode = " & [Forms]![frmProcess]![txtTerm]
for numeric value

Hope that helps!

Hello,

I am having trouble with the following SQL in VB in access. I want to
append the following data to an existing table in my database. The
following SQL gives me an error when trying to run. I want to use an
open unbound, form, text box to feed the criteria to the SQL statement.

Please help if you can. Below is the DoSql string:

The Where clause is giving me the problem...

DoCmd.RunSQL "INSERT INTO tblHurnsHistory ( SyStudentID, StudentName,
SSN, adTrmCode, GPA_creditsEarned, StatusDesc, StatusDate, LDA,
ProgVersCode, TermCode, term_credits, Grads_creditsEarned, TotalCrd,
creditsAttempt, GPA, CreditsRemaining, SumOfReceived, SumOfRefunded,
FundSource, Comment )" & _
"SELECT tblTotalCvueData.SyStudentID, tblTotalCvueData.StudentName,
tblTotalCvueData.SSN, tblTotalCvueData.adTrmCode,
tblTotalCvueData.GPA_creditsEarned, tblTotalCvueData.StatusDesc,
tblTotalCvueData.StatusDate, tblTotalCvueData.LDA,
tblTotalCvueData.ProgVersCode, tblTotalCvueData.TermCode,
tblTotalCvueData.term_credits, tblTotalCvueData.Grads_creditsEarned,
tblTotalCvueData.TotalCrd, tblTotalCvueData.creditsAttempt,
tblTotalCvueData.GPA, tblTotalCvueData.CreditsRemaining,
tblTotalLedger.SumOfReceived, tblTotalLedger.SumOfRefunded,
tblTotalLedger.FundSource, tblTotalCvueData.Comment" & _
"FROM tblTotalCvueData LEFT JOIN tblTotalLedger ON
tblTotalCvueData.SyStudentID = tblTotalLedger.SyStudentID" & _

"WHERE (tblTotalCvueData.adTrmCode=([Forms]![frmProcess]![txtTerm]))"

DoCmd.SetWarnings True

Thanks in advance,

Justin
Click to expand...
Click to expand...
Click to expand...
 
Top