Please help: Error with simple SQL statement

[jverri01]

Hi. I am building a process that retrieves a value from a combo box, and based on the value, one of two SQL processes occur. If the value from the box is "-ALL-" then all records from a table are selcted for output. If a specific value is entered that is NOT "-ALL-", a separate process occurs (records from the table that match the value are stored as DIVISION).

This is the piece of code i have in place, and I kep getting the error message
"Run Time error 3141 - The SELECT statement includes a reserved word or an argument name that is misspelled or missing, or the punctuation is incorrect"

And, yes - the variable strSQL is declared...

Can anyone look at this code and tell me what I am doing wrong with Syntax or use? Thanks in advance.

If [Forms]![manuallyadjustdata]![Combo10] = "-ALL-" Then
strSQL = "SELECT * As DIVISION FROM [tbl_Div].[Div_Name]"
DoCmd.RunSQL strSQL
Else
strSQL = "SELECT [tbl_Div].[Div_Name] As DIVISION From [tbl_Div] WHERE [tbl_Div].[Div_Name] = " & strDiv & ""
DoCmd.RunSQL strSQL
End If

[Rabbit]

Hi. I am building a process that retrieves a value from a combo box, and based on the value, one of two SQL processes occur. If the value from the box is "-ALL-" then all records from a table are selcted for output. If a specific value is entered that is NOT "-ALL-", a separate process occurs (records from the table that match the value are stored as DIVISION).

This is the piece of code i have in place, and I kep getting the error message
"Run Time error 3141 - The SELECT statement includes a reserved word or an argument name that is misspelled or missing, or the punctuation is incorrect"

And, yes - the variable strSQL is declared...

Can anyone look at this code and tell me what I am doing wrong with Syntax or use? Thanks in advance.

If [Forms]![manuallyadjustdata]![Combo10] = "-ALL-" Then
strSQL = "SELECT * As DIVISION FROM [tbl_Div].[Div_Name]"
DoCmd.RunSQL strSQL
Else
strSQL = "SELECT [tbl_Div].[Div_Name] As DIVISION From [tbl_Div] WHERE [tbl_Div].[Div_Name] = " & strDiv & ""
DoCmd.RunSQL strSQL
End If

Please use Code tags next time.

There are 2 problems.

1) You can't assign an alias to *. What it would mean to do so is to name every field the same name.

2) Div_Name seems to be a text value in which case you'll have to surround the strDiv with single quotes. i.e.

"'" & strDiv & "'"

[jverri01]

Please use Code tags next time.

There are 2 problems.

1) You can't assign an alias to *. What it would mean to do so is to name every field the same name.

2) Div_Name seems to be a text value in which case you'll have to surround the strDiv with single quotes. i.e.

"'" & strDiv & "'"

Thanks.

I made a few modifications. I am still getting a bunch of error messages. My code is a big mess. I am not sure i am going about this in the most efficient manner. Is there a tutorial, or any threads you could point me to that may help me build this out better?

What I am doing:

I have a Form with 4 combo boxes (call them A, B, C, D) and a command button.
The top combo box (A) is a date value.
The bottom 3 combo boxes (B,C,D) have a list of values, and also include an option to select "ALL"
The user will make a selection in each box.
After making selections, user clicks command button.
Command button retrieves values from combo boxes, and:
For each associated table (each combo box points to a diff table), retrieves matching records.

So, the code I am trying to build will:
Find records that match A AND B AND C AND D.

I want the system to take the retrieved records and create a new table.

Any help? My code is a big mess.

[Rabbit]

Post your entire code.

[jverri01]

Post your entire code.

Private Sub Command16_Click()
 
Dim strDate As Date
Dim strDiv As String
Dim strLoc As String
Dim strVP As String
Dim strSQL As String
 
If IsNull(Combo0) Then
strDate = " "
Else
strDate = [Forms]![manuallyadjustdata]![Combo0]
End If
 
If IsNull(Combo10) Then
strDiv = " "
Else
strDiv = [Forms]![manuallyadjustdata]![Combo10]
End If
 
If IsNull(Combo12) Then
strLoc = " "
Else
strLoc = [Forms]![manuallyadjustdata]![Combo12]
End If
 
If IsNull(Combo14) Then
strVP = " "
Else
strVP = [Forms]![manuallyadjustdata]![Combo14]
End If
 
strDiv = Me!comboX
 
If [Forms]![manuallyadjustdata]![Combo10] = "-ALL-" Then
strSQL = "SELECT * FROM [tbl_Div].[Div_Name]"
DoCmd.RunSQL strSQL
Else
strSQL = "SELECT [tbl_Div].[Div_Name] As DIVISION From [tbl_Div] WHERE [tbl_Div].[Div_Name] = " '" & strDiv & "'"
DoCmd.RunSQL strSQL
End If
 
'DoCmd.RunSQL "SELECT SponProd.[Prod_Date] As PRODDATE FROM SponProd WHERE SponProd.[Prod_Date] = #" & strDate & "#"
 
'If strDiv = "-ALL-" Then
'strDiv = strDivAll
'strDivAll = "SELECT * As DIVISION FROM Div.Div_Name"
'DoCmd.RunSQL strDivAll
'Else
'If strDiv <> "-ALL-" Then
'strDiv = strDivRandom
'strDivRandom = "SELECT Div.Div_Name As DIVISION FROM Div WHERE Div.Div_Name =" & strDiv & ""
'DoCmd.RunSQL strDivRandom
'End If
'End If
 
'If strLoc = "-ALL-" Then
'DoCmd.RunSQL "SELECT * AS LOCATION FROM Loc.[Loc_Name]"
'Else
'DoCmd.RunSQL "SELECT Loc.[Loc_Name] AS LOCATION FROM Loc WHERE Loc.[Loc_Name] = " & strLoc & ""
'End If
 
'If strVP = "-ALL-" Then
'DoCmd.RunSQL "SELECT * AS VPCODE FROM VPList.[VP_Code]"
'Else
'DoCmd.RunSQL "SELECT VPList.[VP_Code] AS VPCODE FROM VPList WHERE VPList.[VP_Code] = " & strDiv & ""
'End If
 
DoCmd.RunSQL "DELETE * FROM RESULTS"
 
DoCmd.RunSQL "INSERT INTO RESULTS (PRODDATE, DIV, LOCATION, VPCODE)"
 
Dim stDocName As String
 
    stDocName = "Macro6"
    DoCmd.RunMacro stDocName
 
End Sub
 

[jverri01]

Private Sub Command16_Click()
 
Dim strDate As Date
Dim strDiv As String
Dim strLoc As String
Dim strVP As String
Dim strSQL As String
 
If IsNull(Combo0) Then
strDate = " "
Else
strDate = [Forms]![manuallyadjustdata]![Combo0]
End If
 
If IsNull(Combo10) Then
strDiv = " "
Else
strDiv = [Forms]![manuallyadjustdata]![Combo10]
End If
 
If IsNull(Combo12) Then
strLoc = " "
Else
strLoc = [Forms]![manuallyadjustdata]![Combo12]
End If
 
If IsNull(Combo14) Then
strVP = " "
Else
strVP = [Forms]![manuallyadjustdata]![Combo14]
End If
 
strDiv = Me!comboX
 
If [Forms]![manuallyadjustdata]![Combo10] = "-ALL-" Then
strSQL = "SELECT * FROM [tbl_Div].[Div_Name]"
DoCmd.RunSQL strSQL
Else
strSQL = "SELECT [tbl_Div].[Div_Name] As DIVISION From [tbl_Div] WHERE [tbl_Div].[Div_Name] = " '" & strDiv & "'"
DoCmd.RunSQL strSQL
End If
 
'DoCmd.RunSQL "SELECT SponProd.[Prod_Date] As PRODDATE FROM SponProd WHERE SponProd.[Prod_Date] = #" & strDate & "#"
 
'If strDiv = "-ALL-" Then
'strDiv = strDivAll
'strDivAll = "SELECT * As DIVISION FROM Div.Div_Name"
'DoCmd.RunSQL strDivAll
'Else
'If strDiv <> "-ALL-" Then
'strDiv = strDivRandom
'strDivRandom = "SELECT Div.Div_Name As DIVISION FROM Div WHERE Div.Div_Name =" & strDiv & ""
'DoCmd.RunSQL strDivRandom
'End If
'End If
 
'If strLoc = "-ALL-" Then
'DoCmd.RunSQL "SELECT * AS LOCATION FROM Loc.[Loc_Name]"
'Else
'DoCmd.RunSQL "SELECT Loc.[Loc_Name] AS LOCATION FROM Loc WHERE Loc.[Loc_Name] = " & strLoc & ""
'End If
 
'If strVP = "-ALL-" Then
'DoCmd.RunSQL "SELECT * AS VPCODE FROM VPList.[VP_Code]"
'Else
'DoCmd.RunSQL "SELECT VPList.[VP_Code] AS VPCODE FROM VPList WHERE VPList.[VP_Code] = " & strDiv & ""
'End If
 
DoCmd.RunSQL "DELETE * FROM RESULTS"
 
DoCmd.RunSQL "INSERT INTO RESULTS (PRODDATE, DIV, LOCATION, VPCODE)"
 
Dim stDocName As String
 
    stDocName = "Macro6"
    DoCmd.RunMacro stDocName
 
End Sub
 

May just be better for me to "begin anew" ??

[jverri01]

May just be better for me to "begin anew" ??

Sorry - by the way - all the Macro does is open the RESULTS table...

[Rabbit]

A couple of things to note.

1) A DoCmd.RunSQL "SELECT ... " doesn't do anything. In fact, it throws an error because it doesn't do anything. A DoCmd.RunSQL does not bring up the results of a query.

2) An append query is written as:

INSERT INTO TableName (Column List) VALUES (Value List)

Your INSERT INTO statement is written incorrectly.

[jverri01]

A couple of things to note.

1) A DoCmd.RunSQL "SELECT ... " doesn't do anything. In fact, it throws an error because it doesn't do anything. A DoCmd.RunSQL does not bring up the results of a query.

2) An append query is written as:

INSERT INTO TableName (Column List) VALUES (Value List)

Your INSERT INTO statement is written incorrectly.

OK. Where to begin?

As opposed to using DoCmd.RunSQl "SELECT..."

How about:

strSQL = "SELECT * FROM [tbl_Div].[Div_Name]"
DoCmd.RunSQL strSQL
 

Will this work?

[Rabbit]

OK. Where to begin?

As opposed to using DoCmd.RunSQl "SELECT..."

How about:

strSQL = "SELECT * FROM [tbl_Div].[Div_Name]"
DoCmd.RunSQL strSQL
 

Will this work?

That's the same thing. A SELECT query doesn't work with RunSQL no matter how you phrase it. You want them to see the results of the SQL much like if you ran it as a query correct? The only way to do so is to use a query or a seperate form. In which case you'll have to change query defs or the record source of a form.

[jverri01]

That's the same thing. A SELECT query doesn't work with RunSQL no matter how you phrase it. You want them to see the results of the SQL much like if you ran it as a query correct? The only way to do so is to use a query or a seperate form. In which case you'll have to change query defs or the record source of a form.

I'm sorry, Rabbit. I don;t understand this:

The only way to do so is to use a query or a seperate form. In which case you'll have to change query defs or the record source of a form

I am trying to build out an IF...THEN Block

If response in comboA = X Then Run some SQL Statement(s)
If response in comboB = Y Then Run some other SQL Statement(s)

Am I going about this wrong?

i just want to collect responses from Combo Boxes, and use values to populate table with like records

Can you help me build a shell for this, that I can then work with to modify?

Thanks. J.

[Rabbit]

I'm sorry, Rabbit. I don;t understand this:

The only way to do so is to use a query or a seperate form. In which case you'll have to change query defs or the record source of a form

I am trying to build out an IF...THEN Block

If response in comboA = X Then Run some SQL Statement(s)
If response in comboB = Y Then Run some other SQL Statement(s)

Am I going about this wrong?

i just want to collect responses from Combo Boxes, and use values to populate table with like records

Can you help me build a shell for this, that I can then work with to modify?

Thanks. J.

It's not your reasoning that's incorrect. It's the implementation. You can't use a non-action query like a SELECT sql statement with DoCmd.RunSQL.

It sounds like you want to append records to a table in which case you should be using an append SQL statement and not a SELECT statement.

INSERT INTO TableName SELECT * FROM OtherTable.

A SELECT statement by itself throws an error because it doesn't do anything.

[jverri01]

It's not your reasoning that's incorrect. It's the implementation. You can't use a non-action query like a SELECT sql statement with DoCmd.RunSQL.

It sounds like you want to append records to a table in which case you should be using an append SQL statement and not a SELECT statement.

INSERT INTO TableName SELECT * FROM OtherTable.

A SELECT statement by itself throws an error because it doesn't do anything.

Rabbit,

I have tried reworking this. I am still getting an error: Incomplete Query Clause

My re-worked code:

If Me!Combo0 = " " Then
MsgBox "Please make a valid selection from the list"
Else
strSQL = "INSERT INTO RESULTS SELECT * FROM [tbl_SponProd].{Prod_Date]"
DoCmd.RunSQL strSQL
End If
 

Am I missing something..?

[Rabbit]

Rabbit,

I have tried reworking this. I am still getting an error: Incomplete Query Clause

My re-worked code:

If Me!Combo0 = " " Then
MsgBox "Please make a valid selection from the list"
Else
strSQL = "INSERT INTO RESULTS SELECT * FROM [tbl_SponProd].{Prod_Date]"
DoCmd.RunSQL strSQL
End If
 

Am I missing something..?

INSERT INTO RESULTS SELECT * FROM [tbl_SponProd].{Prod_Date]

First off, the FROM clause must be a table, not a field.
Secondly, if you want to return just one field that has to be in the SELECT clause.

Right now you're inserting the results of the SELECT statement into the table RESULTS. The SELECT statement is selecting all fields (*) from the field Prod_Date in table tbl_SponProd. Which doesn't make sense.

[jverri01]

INSERT INTO RESULTS SELECT * FROM [tbl_SponProd].{Prod_Date]

First off, the FROM clause must be a table, not a field.
Secondly, if you want to return just one field that has to be in the SELECT clause.

Right now you're inserting the results of the SELECT statement into the table RESULTS. The SELECT statement is selecting all fields (*) from the field Prod_Date in table tbl_SponProd. Which doesn't make sense.

Rabbit,

I have tried re-working the code several ways, all of which produce error messages.

For example, this source code gives me an error that the field does not exist:

If Me!Combo0 = " " Then
MsgBox "Please make a valid selection from the list"
Else
strSQL = "INSERT INTO RESULTS SELECT [tbl_SponProd].[Prod_Date] FROM [tbl_SponProd] WHERE [Prod_Date] = " & Me!Combo0
DoCmd.RunSQL strSQL
End If

[jverri01]

Rabbit,

I have tried re-working the code several ways, all of which produce error messages.

For example, this source code gives me an error that the field does not exist:

If Me!Combo0 = " " Then
MsgBox "Please make a valid selection from the list"
Else
strSQL = "INSERT INTO RESULTS SELECT [tbl_SponProd].[Prod_Date] FROM [tbl_SponProd] WHERE [Prod_Date] = " & Me!Combo0
DoCmd.RunSQL strSQL
End If

Yet, the field does exist.

The table is named: tbl_SponProd
The field is named: Prod_Date

So, because both contain special characters, I have included them in brackets [ ]

J.

[Rabbit]

Yet, the field does exist.

The table is named: tbl_SponProd
The field is named: Prod_Date

So, because both contain special characters, I have included them in brackets [ ]

J.

Yes but does the field exist in the table RESULTS?

[jverri01]

Yes but does the field exist in the table RESULTS?

OK. Thank you. I went in to the RESULTS table, and made sure there were comparable field names for those I am pulling from the other table. That helped.

Now, as I initiate the process, any time I choose an option OTHER THAN "-ALL-", the system prompts me with an Input Box, asks for a parameter value for whichever option I select from the drop-down.

So, this code works fine (if user selects "-ALL-" from drop-down):

If Me!Combo12 = "-ALL-" Then
strSQL = "INSERT INTO RESULTS SELECT [tbl_Loc].[Loc_Name] FROM [tbl_Loc]"
DoCmd.RunSQL strSQL
Else
 

But, if I choose any other option from the list, it takes that value and asks (with an Input Box) for the parameter value. Can you see a problem with the code here? I am guessing it is related to the way I have the control reference at the end of the line?

strSQL = "INSERT INTO RESULTS SELECT [tbl_Loc].[Loc_Name] FROM [tbl_Loc] WHERE [Loc_Name]= " & Me!Combo12 & ""
DoCmd.RunSQL strSQL
End If
 
 

I am attempting here to tell the system to push out records that match the value selected in the drop-down...Help?

Thanks,

J.

[Rabbit]

OK. Thank you. I went in to the RESULTS table, and made sure there were comparable field names for those I am pulling from the other table. That helped.

Now, as I initiate the process, any time I choose an option OTHER THAN "-ALL-", the system prompts me with an Input Box, asks for a parameter value for whichever option I select from the drop-down.

So, this code works fine (if user selects "-ALL-" from drop-down):

If Me!Combo12 = "-ALL-" Then
strSQL = "INSERT INTO RESULTS SELECT [tbl_Loc].[Loc_Name] FROM [tbl_Loc]"
DoCmd.RunSQL strSQL
Else
 

But, if I choose any other option from the list, it takes that value and asks (with an Input Box) for the parameter value. Can you see a problem with the code here? I am guessing it is related to the way I have the control reference at the end of the line?

strSQL = "INSERT INTO RESULTS SELECT [tbl_Loc].[Loc_Name] FROM [tbl_Loc] WHERE [Loc_Name]= " & Me!Combo12 & ""
DoCmd.RunSQL strSQL
End If
 
 

I am attempting here to tell the system to push out records that match the value selected in the drop-down...Help?

Thanks,

J.

I see nothing wrong with it as long as Combo12 is the name of the control on the main form that calls this event. And that Loc_Name is a number although from the looks of it, it's a text value in which case you have to surround it with quotes.

[Loc_Name] = '" & Me!Combo12 & "'"

What is the input box asking you to define?

[jverri01]

I see nothing wrong with it as long as Combo12 is the name of the control on the main form that calls this event. And that Loc_Name is a number although from the looks of it, it's a text value in which case you have to surround it with quotes.

[Loc_Name] = '" & Me!Combo12 & "'"

What is the input box asking you to define?

Rabbit,

I took your suggestion and added the single quotes. That corrected the problem. i tested it, worked fine, except it kept hanging up right at the end. I was about to ask your advice, but I noticed that I had not set echo back to Yes in the macro. I fixed that, and I now have a seemingly functional process.

Here is my final source code for this piece of the puzzle:

Private Sub Command16_Click()
 
Dim strDate As Date
Dim strDiv As String
Dim strLoc As String
Dim strVP As String
Dim strSQL As String
 
If IsNull(Combo0) Then
strDate = " "
Else
strDate = [Forms]![manuallyadjustdata]![Combo0]
End If
 
If IsNull(Combo10) Then
strDiv = " "
Else
strDiv = [Forms]![manuallyadjustdata]![Combo10]
End If
 
If IsNull(Combo12) Then
strLoc = " "
Else
strLoc = [Forms]![manuallyadjustdata]![Combo12]
End If
 
If IsNull(Combo14) Then
strVP = " "
Else
strVP = [Forms]![manuallyadjustdata]![Combo14]
End If
 
SetWarnings = False
 
DoCmd.RunSQL "DELETE * FROM RESULTS"
 
If Me!Combo0 = " " Then
MsgBox "Please make a valid selection from the list"
Else
strSQL = "INSERT INTO RESULTS SELECT [tbl_SponProd].[Prod_Date] FROM [tbl_SponProd] WHERE [Prod_Date] = #" & Me!Combo0 & "#"
DoCmd.RunSQL strSQL
End If
 
If Me!Combo10 = "-ALL-" Then
strSQL = "INSERT INTO RESULTS SELECT [tbl_Div].[Div_Name] FROM [tbl_Div]"
DoCmd.RunSQL strSQL
Else
strSQL = "INSERT INTO RESULTS SELECT [tbl_Div].[Div_Name] FROM [tbl_Div] WHERE [Div_Name] = '" & Me!Combo10 & "'"
DoCmd.RunSQL strSQL
End If
 
If Me!Combo12 = "-ALL-" Then
strSQL = "INSERT INTO RESULTS SELECT [tbl_Loc].[Loc_Name] FROM [tbl_Loc]"
DoCmd.RunSQL strSQL
Else
strSQL = "INSERT INTO RESULTS SELECT [tbl_Loc].[Loc_Name] FROM [tbl_Loc] WHERE [Loc_Name] = '" & Me!Combo12 & "'"
DoCmd.RunSQL strSQL
End If
 
If Me!Combo14 = "-ALL-" Then
strSQL = "INSERT INTO RESULTS SELECT [tbl_VPList].[VP_Code] FROM [tbl_VPList]"
DoCmd.RunSQL strSQL
Else
strSQL = "INSERT INTO RESULTS SELECT [tbl_VPList].[VP_Code] FROM [tbl_VPList] WHERE [VP_Code] = '" & Me!Combo14 & "'"
DoCmd.RunSQL strSQL
End If
 
SetWarnings = True
 
Dim stDocName As String
 
    stDocName = "Macro6"
    DoCmd.RunMacro stDocName
 
End Sub

Thanks again for your help. Tomorrow, i have to begin developing the next piece - which will involve programmatically locking certain records on/in the datasheet (RESULTS table), and linking certain records in that datasheet back to the original tables (so any modifications to the datasheet will drill-back to the primary data).

I'll start a new thread tomorrow, see how much of that I can accomplish.

I'm guessing I'll again need your assistance!

You're the best, rabbit.

Many Thanks!

J.

[Rabbit]

Good Luck.