Missing operator error

[cnsxad]

I am new to this forum. And new to Access and VBA. :hi:

I have a very simple code for running a report but I keep getting the following error message-
"Syntax error (missing operator) in query expression 'MR Number'."
I can not find my

Here is my code.

Private Sub DVt_Proph__Report_Click()
DoCmd.SetWarnings False
DoCmd.RunSQL "Select MR Number, GWTG Number, DC Date, Measure, Order Set Not Used, Order Not Written, Order Not Entered, No Documentation of Prophylaxis, Attending Service, Unit, Comments into tbl DVT Proph from Stroke where Measure = ""DVT Prophylaxis"" and DC Date between begdate and enddate and MR Number is not null", -1
Dim stDocName As String
stDocName = "DVT Proph"
DoCmd.OpenReport stDocName, acViewPreview
'DoCmd.OutputTo acReport, stDocName, acFormatRTF, "\\File2c\shared\StrokePI\DVT Proph temp.rtf", True
DoCmd.SetWarnings True
End Sub

Any help would be greatly appreciated!! Thank you in advance!
cnsxad

[CreganTur]

Welcome to the forum, it's always great to see new faces!

This issue is caused by a couple of different things. Mainly, it has to do with the fact that 'Number' is recognized as a special operator by SQL, not a field name, so that's causing some confusion. SQL thinks that you're trying to declare a 'double' datatype when it sees this.

The slightly bigger issue, that is causing this problem, is the fact that it is never good practice to have spaces in your field names. Common solutions are to use title casing to make your field names readable, or liberal use of the underscore character. So, 'MR Number' could/should be changed to either MRNumber or MR_Number. Doing this will fix the issue by making it so that 'Number' isn't standing alone looking shifty :whistle:

This means that you'll need to go through your tables and look at renaming the fields to get rid of the spaces. This will fix the problem, but it might involve a lot of work.

If renaming the field names in the tables is not an option, then there is one more thing you can try, but it'll be a little labor intensive too. You'll need to chage your whole SQL statement to use the tblName.[fldName] convention. So 'MR Number' becomes Stroke.[MR Number] (I'm guessing that Stroke is your table name, since it's in the FROM section of the SQL). Enclosing the field names in brackets might keep SQL from seeing certain words as operators.

Other operators that I noticed in your code: 'Date', 'Set', 'Not'... might be others.

HTH

NinjaEdithttp://img293.imageshack.us/img293/9060/ninja3od8.gif: Open a query in Design View and then open the Help file. Search for 'SQL Reserved Words' - This will show you a list of all of the words that SQL uses as operators. Doesn't mean that you can't use these words, but if you use these words in a Field or Table name, you will have to either concatenate them or enclose them in brackets.

[cnsxad]

Thank you so much for your fast reply.

I have tried your first suggestion and my code now looks like this-

Private Sub DVt_Proph__Report_Click()
DoCmd.SetWarnings False
DoCmd.RunSQL "Select MR, GWTG, DCDate, Measure, OrderSetNotUsed, OrderNotWritten, OrderNotEntered, NoDocumentationofProphylaxis, ContraindicationnotdocumentedbyMD, AttendingService, Unit, Comments into tbl DVT Proph from Stroke where Measure = ""DVT Prophylaxis"" and DCDate between begdate and enddate and MR is not null", -1
Dim stDocName As String
stDocName = "DVT Proph"
DoCmd.OpenReport stDocName, acViewPreview
'DoCmd.OutputTo acReport, stDocName, acFormatRTF, "\\File2c\shared\StrokePI\DVT Proph temp.rtf", True
DoCmd.SetWarnings True
End Sub

But now I get this error message-"Run-time error '3067' Query input must contain at least one table or query."

I have 2 tables in this code. I want it to search from table named "Stroke" and place the data into "tbl DVT Proph". What am I doing wrong now?

cnsxad

[CreganTur]

I want it to search from table named "Stroke" and place the data into "tbl DVT Proph". What am I doing wrong now?

That means you want to use an append query, and your SQL doesn't look like it's in the right format for that.

Here's the correct syntax for an append query based on the query you provided above.

INSERT INTO tbl DVT Proph (MR, GWTG, DCDate, Measure, OrderSetNotUsed,
OrderNotWritten, OrderNotEntered, NoDocumentationofProphylaxis,
ContraindicationnotdocumentedbyMD, AttendingService, Unit, Comments)
SELECT MR, GWTG, DCDate, Measure, OrderSetNotUsed, OrderNotWritten, OrderNotEntered,
NoDocumentationofProphylaxis, ContraindicationnotdocumentedbyMD, AttendingService, Unit,
Comments
FROM Stroke
WHERE Measure = ""DVT Prophylaxis"" and DCDate between begdate and enddate and MR is not null;


I have tried your first suggestion
I see that you changed, for example, 'MR Number' to 'MR'. Now, did you change the field name in tbl Stroke from 'MR Number' to 'MR' also? If you did not, then you're referring to a field that does not exist in that table. Make sure your SQL is referring to fields that actually exist in the tables, otherwise you'll have a whole new set of problems.

Take a second look at my first post, especially where it talks about wrapping your field names in brackets if you cannot fix your field names (and table names) to remove the spaces.

[cnsxad]

Yes, I did change the field names in tbl Stroke to MR & GWTG ect. and removed all of the spaces.

I tried your append query and also tried wrapping the field names in brackets preceeded by the table name. Now I am getting this error-Compil error: Syntax error."

Here is what my code looks like now

Private Sub DVT_Proph__Report_Click()
DoCmd.SetWarnings False
INSERT INTO tbl DVT Proph (Stroke.[MR], Stroke.[GWTG], Stroke.[DCDate], Stroke.[Measure], Stroke.[OrderSetNotUsed], Stroke.[OrderNotWritten], Stroke.[OrderNotEntered], Stroke.[NoDocumentationofProphylaxis], Stroke.[ContraindicationnotdocumentedbyMD], Stroke.[AttendingService], Strokd.[Unit], Stroke.[Comments]) SELECT Stroke.[MR], Stroke.[GWTG], Stroke.[DCDate], Stroke.[Measure], Stroke.[OrderSetNotUsed], Stroke.[OrderNotWritten], Stroke.[OrderNotEntered], Stroke.[NoDocumentationofProphylaxis], Stroke.[ContraindicationnotdocumentedbyMD], Stroke.[AttendingService], Stroke.[Unit], Stroke.[Comments] FROM Stroke WHERE Measure = ""DVT Prophylaxis"" And Stroke.[DCDate] between begdate And enddate And Stroke.[MR] Is Not null;
stDocName = "DVT Proph"
DoCmd.OpenReport stDocName, acViewPreview
'DoCmd.OutputTo acReport, stDocName, acFormatRTF, "\\File2c\shared\StrokePI\DVT Proph temp.rtf", True
DoCmd.SetWarnings True
End Sub

I am probably not including a needed statement. This probably proves my lack of knowledge in VBA & Access. I appoligize and thank you for all of you help.

Any other suggestions?
cnsxad

[CreganTur]

2 things:

1) the syntax error is probably due to the fact that you don't have the DoCmd.RunSQL property in that code.

2) try making the SQL statement so that it is all on a single line.

I've attached a txt file that creates a variable, then assigns the SQL to the value of the variable, and then the DoCmd.RunSQL property.

Make sure that you have word wrap turned off, then C&P it into your code.

NinjaEdithttp://img293.imageshack.us/img293/9060/ninja3od8.gif: BTW, wrapping your code with VBA tags (click the VBA button when you're writing a post) will format the code according to VBIDE and make it a lot easier to read.

[cnsxad]

I don't see the attached text file.

I have corrected the 2 things in your previous reply. Now I have this error-"Complile error: Syntax error"

Here is my code

Private Sub DVt_Proph__Report_Click()
DoCmd.SetWarnings False
DoCmd.RunSQL (INSERT INTO tbl DVT Proph (Stroke.[MR], Stroke.[GWTG], Stroke.[DCDate], Stroke.[Measure], Stroke.[OrderSetNotUsed], Stroke.[OrderNotWritten], Stroke.[OrderNotEntered], Stroke.[NoDocumentationofProphylaxis], Stroke.[ContraindicationnotdocumentedbyMD], Stroke.[AttendingService], Strokd.[Unit], Stroke.[Comments]) SELECT Stroke.[MR], Stroke.[GWTG], Stroke.[DCDate], Stroke.[Measure], Stroke.[OrderSetNotUsed], Stroke.[OrderNotWritten], Stroke.[OrderNotEntered], Stroke.[NoDocumentationofProphylaxis], Stroke.[ContraindicationnotdocumentedbyMD], Stroke.[AttendingService], Stroke.[Unit], Stroke.[Comments] FROM Stroke WHERE Measure = ""DVT Prophylaxis"" And Stroke.[DCDate] between begdate And enddate And Stroke.[MR] Is Not null;

stDocName = "DVT Proph"
DoCmd.OpenReport stDocName, acViewPreview
'DoCmd.OutputTo acReport, stDocName, acFormatRTF, "\\File2c\shared\StrokePI\DVT Proph temp.rtf", True
DoCmd.SetWarnings True
End Sub


The line that I have in Bold, is highlighted yellow in MVB. Thanks for the VBA tags tip. Any other suggestions?
cnsxad

[CreganTur]

DoCmd.RunSQL "SQL code"

Wrap the entirety of the SQL code with quotation marks, not parentheses; one before the INSERT, one after the ;

Also, where you have INSERT INTO tbl Dvt Proph ( FieldNames)

all of the field names in the parantheses need to be just the field names- remove the "Stroke." form all of them, but only the field names inside ().

[cnsxad]

I inserted the " where you stated and received yet another errror message. I am going to go back to my very simple report where I do not insert into a table. I was trying to mimick a report from an old co-worker. But I am not really sure what the purpose of saving it when the report can just be reran. Thank you for all of your assistance!!

cnsxad