Runtime error 3075

[wmrainer]

I'm getting this error....
Run-time error '3075';

Syntax error (missing operator) in query expression 'REGIS ID'.

When I run the Debug it highlights this piece of code.

dbs.Execute ("SELECT DISTINCT CUSTNO, NAME, EMAIL, REGIS ID, '' AS CLASS NOS INTO MC EMAIL " _
& "FROM MC Test2")


Any ideas?
Thanks!

 

[wmrainer]

NOW Runtime error 3464

I'm plodding through this so slowly....
I got that error cleared up, but now had another pop up.

Run-time error '3464';
Data type mismatch in criteria expression.

The code I'm using is attached it wouldn't let me just put it in here.

Thanks for any help! :banghead:

 

[SOS]

1. I reformatted it for you.
2. You need brackets around NAME (you did in the recordset callout of it but not in the SQL part because NAME is an Access Reserved Word).
3. If the data is not text, but number you don't include quotes. I used CHR(34) to simplify it.
4. With such a long string it is best to assign to a variable and then put that in the open recordset call. Makes it easier to read and maintain.

Dim strSQL As String
strSQL = "SELECT CLASSNO, CUSTNO, NAME, EMAIL, REGID " & _
         "FROM MCTEST2 " & _
         "WHERE CUSTNO=" & Chr(34) & rstInsert![CUSTNO] & Chr(34) & " AND [NAME]=" & Chr(34) & rstInsert![Name] & Chr(34) & _
         " AND EMAIL=" & Chr(34) & rstInsert![Email] & Chr(34) & " AND REGID=" & Chr(34) & rstInsert![REGID] & Chr(34) & ")"
Set rst = dbs.OpenRecordset(strSQL, dbFailOnError)

 

[wmrainer]

Hi SOS,
I ended up just changing the name field which should have helped, but I'm still getting the error?

Run-time error '3464':
Data type mismatch in criteria expression.

I think this is just a wrong field name, but can't for the life of me see it. I've uploaded the whole new code here.

Thanks a bunch for all your help!

 

[SOS]

Is regionID really text (which is the way you are saying it is in your code because you have quotes around it) or is it a number? If a number then this part

" AND REGID=" & "'" & rstInsert![REGID] & "'")

would be:

" AND REGID=" & rstInsert![REGID] )

without the quotes and that would go for any other fields which are numeric and not text. And dates would get the # sign instead of quotes if there are any of those.

 

[wmrainer]

Is this right? CUSTNO and REGID are numbers.

And now I'm getting the syntax error 3075?

 

[wmrainer]

Whole error:

Syntax error (missing operator) in query expression 'CUSTNO=1019' AND FULLNAME='JOHN SMITH'.......

 

[SOS]

No, it isn't right. You left in something that shouldn't be there and you can not use the extra & "'" & as you can see in the corrected version below.

Set rst = dbs.OpenRecordset("SELECT CLASSNO, CUSTNO, FULLNAME, EMAIL, REGID " _
& "FROM MCTEST2 " _
& "WHERE CUSTNO=" & rstInsert![CUSTNO] & " AND FULLNAME='" & rstInsert![FullName] &"' AND EMAIL='" & rstInsert! & "' AND REGID=" & rstInsert![REGID])

 

[wmrainer]

So I copied your code completely and am still getting the same error. Could it be an issue with the table or something else?

 

[SOS]

So I copied your code completely and am still getting the same error. Could it be an issue with the table or something else?

Don't know. The way I do it is to create the query in the QBE Grid and then move it to VBA from the SQL View. You have to make a few changes but it is fairly easy to do.