invalid . (dot) or ! (1 Viewer)

[scottrie]

I have done VBCode in Access 2003 for years and am finally making the jump and am coding in Access 2013. I am getting the error

The expression you entered has an invalid . (dot) or ! operator or invalid parenthesis.

and I cannot figure out why.

Here is my code:

Function CreateUSHistory()

mySQL = ""

mySQL = "INSERT INTO [US History] ( Student_ID, Period, Teacher ) "
mySQL = mySQL & "SELECT AllStudents.Student_ID, Mid$([AllStudents]![Col1_Class_Name_4],2,1) AS Period, AllStudents!Col1_Teacher_4 "
mySQL = mySQL & "FROM AllStudents "
mySQL = mySQL & "WHERE (((AllStudents.Col1_Class_Name_4) Like '*US History*'));"


'For x = 1 To 13
' GenerateSQL mySQL, "US History", "*US History*", x
RunActionSQL mySQL
'Next x

End Function

The SQL is copied from a query that does run successfully. I modified the " to '. The loop is disabled until I can get the simple query to run and then the function will run 13 queries in a row with the counter changing.

Scott

 

[CJ_London]

change the bangs (!) to dots (.) e,g,

Mid$([AllStudents].[Col1_Class_Name_4],2,1)

 

[scottrie]

I made the suggested change but still have the same result. Included is the updated SQL code.

mySQL = "INSERT INTO [US History] ( Student_ID, Period, Teacher ) "
mySQL = mySQL & "SELECT AllStudents.Student_ID, Mid$([AllStudents].[Col1_Class_Name_4],2,1) AS Period, [AllStudents].[Col1_Teacher_4] "
mySQL = mySQL & "FROM AllStudents "
mySQL = mySQL & "WHERE (((AllStudents.Col1_Class_Name_4) Like '*US History*'));"

As a side note: why would it be different that what shows up in the SQL view of the Query?

 

[scottrie]

Also, this is entered into a Module as opposed to the VBA of a form.

 

[CJ_London]

Also, this is entered into a Module as opposed to the VBA of a form.

that shouldn't make any difference since you are just building a query string

why would it be different that what shows up in the SQL view of the Query?

it won't so far as I am aware. The norm is to use dots to separate table and field names.

I made the suggested change but still have the same result.

If you put your code into a sql window and run it, what do you get?

The only other thing that might make a difference is to use mid rather than mid$ - see this thread

http://bytes.com/topic/access/answers/192869-mid-vs-mid-proper-use-if-these-functions

 

[TJPoorman]

Lose the Bracket Bang Bracket and replace with .(dot). Like this:

Mid$([AllStudents.Col1_Class_Name_4],2,1)

Same thing with [AllStudents].[Col1_Teacher_4].
[AllStudents.Col1_Teacher_4]

 

[TJPoorman]

Also, since you are only SELECTing from one table you don't HAVE to prefix the fields with the table name.

Function CreateUSHistory()

mySQL = ""

mySQL = "INSERT INTO [US History] (Student_ID, Period, Teacher) "
mySQL = mySQL & "SELECT Student_ID, Mid$([Col1_Class_Name_4],2,1) AS Period, Col1_Teacher_4 "
mySQL = mySQL & "FROM AllStudents "
mySQL = mySQL & "WHERE Col1_Class_Name_4 Like '*US History*';"


'For x = 1 To 13
' GenerateSQL mySQL, "US History", "*US History*", x
RunActionSQL mySQL
'Next x

End Function

 

[spikepl]

Just to add to the fun - field names like

Col1_Class_Name_4
Col1_Teacher_4

reek of data normalization issues.