Compile error

[Don]

Whenever I attempt to write a bit of code on a form control, I get a "Compile
Error: Expecting Case" error message right after "SELECT". If I let Access
create code on the data tab and then copy and paste that code into the code
builder on the Event tab, I get the same error. Why do I keep getting this
error and how do I correct it. I get the error on this code:

SELECT projstatus.projdesc, projstatus.signed, projstatus.start,
projstatus.todate, projstatus.finished FROM projstatus;

the compilers highlights the "projstatus" right after SELECT and tells me
"Case expected"

 

[Allen Browne]

Sounds like you are trying to write a SQL statement in the VBA code window.

VBA is vastly different from SQL. You can use SQL statements as strings in
VBA, but VBA does not interpret SQL.

 

[Arvin Meyer MVP]

Because that "code" is not code, it is a select statement which is a string
that isn't being executed. The error message is telling you that the Select
Case statement is missing the Case part of the code structure like:

Select Case some_string_variable
Case Is "Whatever"
'Do something
Case Is "something else"
'Do something else
Case Else
End Select

 

[Maurice]

See previous answers from Allen and Arvin. Are you trying to write your own
select statement? In that case the select statement has to be in a string
variable and between apostrophs.

something like:

dim strSQL as String
strSQL="SELECT projstatus.projdesc, projstatus.signed, projstatus.start,
projstatus.todate, projstatus.finished FROM projstatus;"

Which will provide you with a strSQL statement that can be executed / run as
follows:

docmd.runsql strsql

this will give you the output of a regular select query.
Just another point of view