SQL Error?

[sambrierley]

Hi all,

this is my first attempt at SQL, i have copied the syntax directly from a website but im getting the error stating " A run SQL action requires an SQL statement"

Any help is appreciated

Dim varX As String
varX = "SELECT  Config.[Definition]" & _
        "FROM Config " & _
        "WHERE Config.[Parameter] = 'Mail Folder';"
       
     DoCmd.RunSQL varX
   MsgBox varX


End Sub

 

[bob fitz]

Have you tried:
DoCmd.OpenQuery varX

BTW you have declared the variable varX as a string but it's name would suggest to many that it is a variant. Might be better to call it strX

 

[sambrierley]

thanks for the reply bob


that seem to get it working to the next stage however no its saying it cant be found.im presuming then that the code is working but ive named something wrong, can you see it.

the table is called Config
the field i want to return is Definition
the criteria is where the Parameter value is Mail Folder.

p.s your correct with the variant i just copied and pasted it.

thanks

 

[Mile-O]

For info, the SQL you built was a SELECT query. All it does is select some records.

The command you used was DoCmd.RunSQL. This supposes the query will perform an action. Actions include UPDATE, INSERT, DELETE. They do not include SELECT.

 

[Mile-O]

Your SQL can't be found because the OpenQuery method is looking for a query. It doesn't recognise your SQL as the name of a query in your database.

Time to look into the QueryDef object.

 

[bob fitz]

You could create a saved query and reference that directly instead of coding your own SQL

 

[sambrierley]

Hi all.

thanks for your reply however they are going straight over my head!

if i pose you with what i am trying to achieve i would be grateful if someone could provide the correct syntax.

basically i need to look in the config table and return the definition where the parameter is Email Folder.

i then need to take this value and assign it to a variable that can be used in my code.

thanks for any help guys!

 

[Mile-O]

Dim rs As DAO.Recordset
Dim myVar As WhateverTypeItShouldBe
Set rs = CurrentDb.OpenRecordset("YourQuery")

myvar = rs.Fields(Whatever Field Number It Is)
rs.Close
Set rs  = Nothing

 

[vbaInet]

The main question is, why don't you do this in a query? What you want to do is look into using a Query.