where vba statement

[Spoulis19]

why the following VBA statement fails when I add the where part? "WHERE" & " (rsMySet.Fields(i).Name)>0"

I need to add when the field has numbers.

strSQL = "INSERT INTO TabularDepts ([Depts],

, [Description], [Qty])" & _
"SELECT" & "'" & rsMySet.Fields(i).Name & "'" & " AS Dept," & _
"[PivotTblInvDepts].[Code],[PivotTblInvDepts].[Description]," & _
 "[" & rsMySet.Fields(i).Name & "]" & _
"FROM PivotTblInvDepts;" & _
[U]"WHERE" & " (rsMySet.Fields(i).Name)>0"[/U]

 

[Kyle123]

You're missing a space on the end of each line and you need to remove the semicolon

 

[Micron]

Remove first semicolon yes, but missing one at the end I think. Also, why put [] around rsMySet.Fields(i).Name in one place but not the other? I find line continuation characters introduce more confusion that I don't need. Here's how I do it, although I would put more on each line than what's below - I wanted to keep it close to what you had. What this does for me is make it easier to see the construct, as long as a consistent method is adopted, such as putting all ending spaces at the end of a line (or not, as one chooses). I've wrapped both cases of rsMySet.Fields(i).Name in single quotes except for the last. You have quotes in the first instance, not in the second and not the third - but the third case looks numeric. It's odd that you can use this sql for more than one rsMySet.Fields field since you must be passing the recordset field number each time, but perhaps not the same field. Otherwise, you should expressly use the field name. If you ever add/remove fields in the query before the referenced field (i), the field count might be off.

strSQL = "INSERT INTO TabularDepts ([Depts],

, [Description], [Qty]) "
strSQL= strSQL & "SELECT '" & rsMySet.Fields(i).Name & "' AS Dept, " 
strSQL= strSQL & "[PivotTblInvDepts].[Code],[PivotTblInvDepts].[Description], '"
 strSQL= strSQL & rsMySet.Fields(i).Name & "' FROM PivotTblInvDepts WHERE "
strSQL= strSQL & rsMySet.Fields(i).Name)>0;"

 

[Spoulis19]

Dear Sir Micron,
thank you very much for your comments. I test the code and works like a charm until the the where statement. When I add the where statement the code
pops up a message <syntax error in from clause>. Have you any ideas?

Dim rsMySet As DAO.Recordset
Dim strSQL As String
Dim i As Integer

'Open the original matrix-style dataset
Set rsMySet = CurrentDb.OpenRecordset("PivotTblInvDepts")

'Start the count at the position number of the first column-oriented field
'Remember that Recordsets start at 0
For i = 3 To rsMySet.Fields.Count - 1


'*********************************************************************************************************************


strSQL = "INSERT INTO TabularDepts ([Depts],

, [Description], [Qty]) "
strSQL = strSQL & "SELECT '" & rsMySet.Fields(i).Name & "' AS Dept, "
strSQL = strSQL & "[PivotTblInvDepts].[Code],[PivotTblInvDepts].[Description],"
strSQL = strSQL & "[" & rsMySet.Fields(i).Name & "]" & "FROM PivotTblInvDepts"
strSQL = strSQL & "WHERE" & "rsMySet.Fields(i).Name >0;"

Kind Regards

 

[Kyle123]

Again, you're missing a space before your Where

 

[Spoulis19]

Dear Sir Kyle123, I add a space before Where and test the below code, but the code stops again and pops up a message <syntax error in from clause>

strSQL = "INSERT INTO TabularDepts ([Depts],

, [Description], [Qty]) "
strSQL = strSQL & "SELECT '" & rsMySet.Fields(i).Name & "' AS Dept, "
strSQL = strSQL & "[PivotTblInvDepts].[Code],[PivotTblInvDepts].[Description],"
strSQL = strSQL & "[" & rsMySet.Fields(i).Name & "]" & "FROM PivotTblInvDepts"
strSQL = strSQL & " WHERE" & "rsMySet.Fields(i).Name >0;"

Finally I test many cases and finally succeed the following snippet of code 

strSQL = strSQL & " " & " WHERE" & " " & "[" & rsMySet.Fields(i).Name & "]" & "> 0"



thank you all.

 

[Micron]

You were missing spaces before AND after the WHERE,
strSQL = strSQL & "[" & rsMySet.Fields(i).Name & "]" & "FROM PivotTblInvDepts"
strSQL = strSQL & "WHERE" & "rsMySet.Fields(i).Name >0;"

because you didn't adopt any "rule" about always putting them at the end OR beginning of a line, but my example was not missing them:
strSQL= strSQL & rsMySet.Fields(i).Name & "' FROM PivotTblInvDepts WHERE "
strSQL= strSQL & rsMySet.Fields(i).Name)>0;"

If what you have works and you're OK with it, then problem solved; but it's a bit weird looking to concatenate spaces when you don't have to. As noted, I didn't include [] around your last recordset variable and have no idea if they're needed.

 

[james_lankford]

strSQL = strSQL & " " & " WHERE" & " " & "[" & rsMySet.Fields(i).Name & "]" & "> 0"

would be easier just to write

strSQL = strSQL & " WHERE " & rsMySet.Fields(i).Name & " > 0 "

you should always do
debug.write strSQL
right after you build your sql string so you can see what it looks like

 

[Micron]

Good idea, JL. Just wanted to let all know of another way. I put a break on the line of code right after the last line of the sql construct. When execution gets to the break point, I query/question this in the immediate window: ?strSQL
hit Return and read the output there. To deal with the horizontal scroll bars that often result, I will pick a point and hit Ctrl+Enter to wrap the lines at those points. Sometimes I take the result shown in the immediate window and paste it as a new query in sql view if I'm having issues or want to see the results. I prefer this method over debug.print statements that I might forget to comment out.