UtterAccess.com
We have detected that you are using an unsupported web browser. We recommend you to upgrade your web browser to get the most from UtterAccess forums.
Why should and how can I upgrade?
X   Site Message
(Message will auto close in 2 seconds)

Welcome Guest ( Log In | Register )

 
Reply to this topicStart new topic
> Syntax error in query when it reference a variable name    
 
   
Lin100
post Mar 28 2007, 01:25 PM
Post #1

UtterAccess Veteran
Posts: 493



Run-time error 3450
Syntax error in query. Incomplete query clause
////////////////////////////////////////////
Yellow highlight at the code dbs.Execute
When I placed the cursor over the Month_Text inside the dbs.Execute statement it showed a value
of "MAR_2007" (Include the double quotes).
/////////////////////////////////////////////
Private Sub Import_Into_Table()
Dim dbs As Database
Dim Month_Text As String

Set dbs = CurrentDb

Month_Text = Forms!Search!Month_Year_Text

'Run a make-table query to create a table where the name
'has a "month year" format as in 'JAN 2007' or 'DEC 2007'
dbs.Execute "SELECT Q_SELECT_FROM_FTD_LEVEL1_AND_FTD_OPER_UNIT_TREE.* " & _
"INTO '" & Month_Text & "' " & _
"FROM Q_SELECT_FROM_FTD_LEVEL1_AND_FTD_OPER_UNIT_TREE;"

End Sub
Go to the top of the page
 
+
Steve Schapel
post Mar 28 2007, 02:17 PM
Post #2

UtterAccess VIP
Posts: 3,881
From: New Zealand



Lin,
If your table name will literally have a space in it, such as MAR 2007 then you will need to enclose in []s. Is that what you have actually entered in the Forms!Search!Month_Year_Text textbox?
Try it like this...
Month_Text = "[" & Forms!Search!Month_Year_Text & "]"
dbs.Execute "SELECT Q_SELECT_FROM_FTD_LEVEL1_AND_FTD_OPER_UNIT_TREE.*" & _
" INTO " & Month_Text & _
" FROM Q_SELECT_FROM_FTD_LEVEL1_AND_FTD_OPER_UNIT_TREE;"
Go to the top of the page
 
+
Lin100
post Mar 28 2007, 02:46 PM
Post #3

UtterAccess Veteran
Posts: 493



Thank you SteveSchapel for your help.
Go to the top of the page
 
+
ShelleyClark
post Apr 2 2007, 01:08 PM
Post #4

UtterAccess Addict
Posts: 110
From: Texas



Could someone please elaborate on this error. I'm getting it on a subform. The query works fine, but when I try to run the subform I'm getting the error "incomplete query clause". I've never come across this one before.
Go to the top of the page
 
+
Steve Schapel
post Apr 2 2007, 01:52 PM
Post #5

UtterAccess VIP
Posts: 3,881
From: New Zealand



Shelley,
The first thing is to identify what query the error message refers to. Quite possibly the query that serves as the Record Source of the subform. Do you have any code running on, for example, the Load event or the Current event (or some such) of the subform, which would affect the subform's record source? If you can post back with the SQL view of the query that you think is causing the error, it may help to identify.
Go to the top of the page
 
+
ShelleyClark
post Apr 4 2007, 11:46 AM
Post #6

UtterAccess Addict
Posts: 110
From: Texas



I don't have any code running on the sub-form, I looked at the properities on the form and I didn't see any events or codes entered there. How do I post the SQL view here?
Go to the top of the page
 
+
ShelleyClark
post Apr 4 2007, 11:51 AM
Post #7

UtterAccess Addict
Posts: 110
From: Texas



Sorry - I figured out the SQL thing - here it is:
SELECT JobMaterialsUsed.MaterialID, JobMaterialsUsed.TimesheetID, JobMaterialsUsed.MaterialNoID, JobMaterialList.MaterialName, JobMaterialsUsed.QtyUsed, JobMaterialsUsed.PriceChargedPerUnit, JobMaterialList.UnitOfMeasure, JobMaterialList.CostPerUnit, JobMaterialsUsed.Note, [QtyUsed]*[PriceChargedPerUnit] AS TotalPrice
FROM JobMaterialList RIGHT JOIN JobMaterialsUsed ON JobMaterialList.ItemNo = JobMaterialsUsed.MaterialNoID;
Go to the top of the page
 
+

Thank you for your support! Reply to this topicStart new topic

Jump To Forum:
 



RSS Search  ·  Go to Top  ·  Lo-Fi Version Time is now: 24th November 2014 - 12:00 AM