Sql (in VBA) syntax

Scott Oh

Registered User.
Local time
Today, 02:58
Joined
Feb 22, 2011
Messages
16
I am scratching my head on this syntax. I am declaring strType to be one of three options. Then the variable strType will factor into the embedded query: strSQL. I would appreciate any assistance in this syntax issue. TY.

Dim strType As String

If strType = "Single Family" Then
strType = "SF"
ElseIf strType = "Multi Family" Then
strType = "MF"
ElseIf strType = "All" Then
strType = vbNullString
End If
If Len(strType) > 0 Then
strType = " AND (tblMainDetail.Type='" & strType & "')"
End If

strSQL = "SELECT...
"FROM...
"GROUP BY...
"HAVING (((qryNat_Chart.DateYr)=Year(Date())) AND ((qryNat_Chart.DateQtr)=Val(Format(Date(),""q""))-1) & strType & ""))" & vbCrLf & _
"ORDER BY
 


Please post the ENTIRE CODE (and use Code Tags please). We can't diagnose with only partial data.



codetag001.png
 
Scott Oh,

Not sure...what exact error are you receiving? When I print the HAVING clause out it comes out like this:
HAVING (((qryNat_Chart.DateYr)=Year(Date())) AND ((qryNat_Chart.DateQtr)=Val(Format(Date(),"q"))-1) & strType & "))

The extra " before the last )) is probably not doing any favors...also I have had problems when trying to get the double quote ( " ) to work...you may try to replace ""q"" with "'q'"...

Hope this helps
 
Bob,
I had a nice reply but got timed out. So much for creative writing skills. Here is the code that you requested. To elaborate, I am having a syntax issue on the HAVING line of my strSQL line. I'm hoping that the solution is something obvious but so far it is eluding me. Thank you.

Code:
Private Sub Command87_Click()
Dim strType As String
Dim strSQL As String
 
strType = Forms!frmMain2.Form.frmNational.Form.frmNat_ChartBar.Form.cboType
'------------------------ Identify Type ------------------------
If strType = "Single Family" Then
    strType = "SF"
ElseIf strType = "Multi Family" Then
    strType = "MF"
ElseIf strType = "All" Then
    strType = vbNullString
End If
If Len(strType) > 0 Then
    strType = " AND (tblMainDetail.Type='" & strType & "')"
End If

strSQL = "SELECT TOP 10 qryNat_Chart.ReviewName, qryNat_Chart.DateYr, qryNat_Chart.DateQtr, Sum(IIf(qryNat_Chart.Type=""DelinqDays120Plus"",qryNat_Chart.Rate,0)+IIf(qryNat_Chart.Type=""DelinqDays6089"",qryNat_Chart.Rate,0)+IIf(qryNat_Chart.Type=""DelinqDays90119"",qryNat_Chart.Rate,0)+IIf(qryNat_Chart.Type=""DelinqDays90Plus"",qryNat_Chart.Rate,0)) AS Rate, tblMainDetail.Type " & vbCrLf & _
         "FROM qryNat_Chart LEFT JOIN tblMainDetail ON qryNat_Chart.ReviewName = tblMainDetail.ReviewName " & vbCrLf & _
         "GROUP BY qryNat_Chart.ReviewName, qryNat_Chart.DateYr, qryNat_Chart.DateQtr, tblMainDetail.Type " & vbCrLf & _
         [COLOR=red]"HAVING (((qryNat_Chart.DateYr)=Year(Date())) AND ((qryNat_Chart.DateQtr)=Val(Format(Date(),""q""))-1) & strType & ""))" & vbCrLf & _
[/COLOR]         "ORDER BY Sum(IIf(qryNat_Chart.Type=""DelinqDays120Plus"",qryNat_Chart.Rate,0)+IIf(qryNat_Chart.Type=""DelinqDays6089"",qryNat_Chart.Rate,0)+IIf(qryNat_Chart.Type=""DelinqDays90119"",qryNat_Chart.Rate,0)+IIf(qryNat_Chart.Type=""DelinqDays90Plus"",qryNat_Chart.Rate,0)) DESC;"
 
With Forms!frmMain2.Form.frmNational.Form.frmNat_ChartBar.Form.OLEUnbound0
    .RowSource = "TRANSFORM Sum(strSQL.Rate) AS SumOfRate " & vbCrLf & _
                 "SELECT strSQL.DateYr " & vbCrLf & _
                 "FROM strSQL " & vbCrLf & _
                 "GROUP BY strSQL.DateYr " & vbCrLf & _
                 "PIVOT strSQL.ReviewName;"
End With
 
With Me.frmNat_ChartBarDS.Form
    .RecordSource = strSQL
End With
 
Me.Requery
Me.frmNat_ChartBarDS.Form.Requery
End Sub
 
Yeah, as gbnz said, change the quotes:


"HAVING (((qryNat_Chart.DateYr)=Year(Date())) AND ((qryNat_Chart.DateQtr)=Val(Format(Date(),'q'))-1) " & Chr(34) & strType & Chr(34) & ")) " & _

And take our the & VbCrLf parts as you do not need it and it may play havoc with the running of the SQL in code. Just do the line extensions as I have shown and make sure to add a space at the end of each line except the last (like I've shown).
 
Last edited:
Bob, gbnz,
Thank you for your assistance with this code. Altering the code as you both suggested did in fact allow me to refresh my graph successfully. As with some threads, the answer brings up a few follow up questions that I hope you are able to comment on.
  1. What is the purpose of the Char(34)?
  2. I am getting an error on my line: .RecordSource = strSQL
    Syntax error (missing operator) in query expression '(((qryNat_Chart.DateYr)=Year(Date())) AND ((qryNat_Chart.DateQtr)=Val(Format(Date(),'q'))-1) ""))'.
  3. I've counted up the right paren and left paren and think that maybe this is the prob. I've tried placing a ( paren in various spots but not seeing this work. Seeing as this syntax error doesn't include the Char(34) in the message makes me realize that I'm just not understanding something important here.
 
What is the purpose of the Char(34)?
Chr(34) is a double quote but it makes it very easy to put it in because if you use double quotes themselves then you need to use triple double quotes (""") for each side and I find that messy but it is a personal preference thing.

I am getting an error on my line: .RecordSource = strSQL


Syntax error (missing operator) in query expression '(((qryNat_Chart.DateYr)=Year(Date())) AND ((qryNat_Chart.DateQtr)=Val(Format(Date(),'q'))-1) ""))'.
My suggestion - get rid of the parens. They aren't required unless you have to separate ANDs with ORs. Access puts them in but they really aren't needed. So I would change to this:

qryNat_Chart.DateYr = Year(Date())) AND qryNat_Chart.DateQtr = Val(Format(Date(),'q'))-1)

And as I did that I can see that we were missing an ampersand after the last paren in Val(...) and the "))" part. But without those parens it makes it less messy as well.
 
Bob, Thank you again for your patience in this. I feel that I'm following the directions but still coming up short on making this work. I've taken out the parems but getting errored out at the .RecordSource = strSQL line. My attempts at altering the code has not been successful. Can you assist again?

"HAVING qryNat_Chart.DateYr = Year(Date()) AND qryNat_Chart.DateQtr = Val(Format(Date(),'q'))-1 & Chr(34) & strType & Chr(34) & " & _
 
Okay, you still are adding a quote at the end with & " & _ and it needs to be:

"HAVING qryNat_Chart.DateYr = Year(Date()) AND qryNat_Chart.DateQtr = Val(Format(Date(),'q'))-1 & Chr(34) & strType & Chr(34) & _
 
I appreciate your patience with this Bob. I have altered the code (minus the quote) and reinserted into my code but with this, the whole strSQL is now red. I understand the Char(34) equating to double quotes so the count of quotes would be 5. Would this be correct?

strSQL = "SELECT TOP 10 qryNat_Chart.ReviewName, qryNat_Chart.DateYr, qryNat_Chart.DateQtr, Sum(IIf(qryNat_Chart.Type=""DelinqDays120Plus"",qryNat_Chart.Rate,0)+IIf(qryNat_Chart.Type=""DelinqDays6089"",qryNat_Chart.Rate,0)+IIf(qryNat_Chart.Type=""DelinqDays90119"",qryNat_Chart.Rate,0)+IIf(qryNat_Chart.Type=""DelinqDays90Plus"",qryNat_Chart.Rate,0)) AS Rate, tblMainDetail.Type, tblMainDetail.Program, tblMainDetail.Sector, tblMainDetail.Portfolio " & _
"FROM qryNat_Chart LEFT JOIN tblMainDetail ON qryNat_Chart.ReviewName = tblMainDetail.ReviewName " & _
"GROUP BY qryNat_Chart.ReviewName, qryNat_Chart.DateYr, qryNat_Chart.DateQtr, tblMainDetail.Type, tblMainDetail.Program, tblMainDetail.Sector, tblMainDetail.Portfolio " & _
"HAVING qryNat_Chart.DateYr = Year(Date()) AND qryNat_Chart.DateQtr = Val(Format(Date(),'q'))-1 & Chr(34) & strType & Chr(34) & _
"ORDER BY Sum(IIf(qryNat_Chart.Type=""DelinqDays120Plus"",qryNat_Chart.Rate,0)+IIf(qryNat_Chart.Type=""DelinqDays6089"",qryNat_Chart.Rate,0)+IIf(qryNat_Chart.Type=""DelinqDays90119"",qryNat_Chart.Rate,0)+IIf(qryNat_Chart.Type=""DelinqDays90Plus"",qryNat_Chart.Rate,0)) DESC;"
 
You were using double quotes for surrounding text and in VBA you have to change that to either single quotes, or concatenate in triple double quotes or CHR(34). So, I used single quotes (see red) to do it. Also at the end of your Having statement we did have to add a single space to make sure that we didn't run on into the Order By.
Code:
[FONT=Courier New][SIZE=3]strSQL = "SELECT TOP 10 qryNat_Chart.ReviewName, qryNat_Chart.DateYr, qryNat_Chart.DateQtr, Sum(IIf(qryNat_Chart.Type=[B][COLOR=red]'[/COLOR][/B]DelinqDays120Plus[B][COLOR=red]'[/COLOR][/B],qr yNat_Chart.Rate,0)+IIf(qryNat_Chart.Type=[B][COLOR=red]'[/COLOR][/B]DelinqD ays6089[B][COLOR=red]'[/COLOR][/B],qryNat_Chart.Rate,0)+IIf(qryNat_Chart.Type=[B][COLOR=red]’[/COLOR][/B]DelinqDays90119[B][COLOR=red]’[/COLOR][/B],qryNat_Chart.Rate,0)+IIf(qr yNat_Chart.Type=[B][COLOR=red]'[/COLOR][/B]DelinqDays90Plus[B][COLOR=red]'[/COLOR][/B],qryNat_Chart. Rate,0)) AS Rate, tblMainDetail.Type, tblMainDetail.Program, tblMainDetail.Sector, tblMainDetail.Portfolio " & _[/SIZE][/FONT]
[SIZE=3][FONT=Courier New]"FROM qryNat_Chart LEFT JOIN tblMainDetail ON qryNat_Chart.ReviewName = tblMainDetail.ReviewName " & _[/FONT][/SIZE]
[SIZE=3][FONT=Courier New]"GROUP BY qryNat_Chart.ReviewName, qryNat_Chart.DateYr, qryNat_Chart.DateQtr, tblMainDetail.Type, tblMainDetail.Program, tblMainDetail.Sector, tblMainDetail.Portfolio " & _[/FONT][/SIZE]
[SIZE=3][FONT=Courier New]"HAVING qryNat_Chart.DateYr = Year(Date()) AND qryNat_Chart.DateQtr = Val(Format(Date(),'q'))-1 & Chr(34) & strType & Chr(34) & [B][COLOR=red]" " [/COLOR][/B]& _[/FONT][/SIZE]
[SIZE=3][FONT=Courier New]"ORDER BY Sum(IIf(qryNat_Chart.Type=[B][COLOR=red]'[/COLOR][/B]DelinqDays120Plus[B][COLOR=red]'[/COLOR][/B],qryNat_Chart.Rate,0)+IIf(qryNat_Chart.Type=[B][COLOR=red]'[/COLOR][/B]DelinqDays6089[B][COLOR=red]'[/COLOR][/B],qryNat_Chart.Rate,0)+IIf(qryNat_Chart.Type=[B][COLOR=red]'[/COLOR][/B]DelinqDays90119[B][COLOR=red]'[/COLOR][/B],qryNat_Chart.Rate,0)+IIf(qryNat_Chart.Type=[B][COLOR=red]'[/COLOR][/B]DelinqDays90Plus[B][COLOR=red]'[/COLOR][/B],qryNat_Chart. Rate,0)) DESC;"[/FONT][/SIZE]
 
 
 
 
 
 
.
 
Bob, Sorry for the delay in responding but my work laptop simply crashed on Friday and I had to get a temporary laptop Monday morning to access this database. To further respond, I am very appreciative of your advise and starting to feel guilty to keep coming back for more. However... (you probably saw this coming), when I used your latest code my whole strSQL turned red. I appears that VBA is having a conflict with the " " & _ as it turns a normal black when I modify to " & _. From your previous reply I am aware of your comment on the quote part. I apologize for not getting this but continue to ask for guidance. I hope that you have the time to reevaluate this code.
 
Bob, Sorry for the delay in responding but my work laptop simply crashed on Friday and I had to get a temporary laptop Monday morning to access this database. To further respond, I am very appreciative of your advise and starting to feel guilty to keep coming back for more. However... (you probably saw this coming), when I used your latest code my whole strSQL turned red. I appears that VBA is having a conflict with the " " & _ as it turns a normal black when I modify to " & _. From your previous reply I am aware of your comment on the quote part. I apologize for not getting this but continue to ask for guidance. I hope that you have the time to reevaluate this code.

Post the current code you have now which has that error.
 
Actually, I just tried it in my VBA window and found the problems. Try using this one:
Code:
strSQL = "SELECT TOP 10 qryNat_Chart.ReviewName, qryNat_Chart.DateYr, qryNat_Chart.DateQtr, Sum(IIf(qryNat_Chart.Type='DelinqDays120Plus',qr yNat_Chart.Rate,0)+IIf(qryNat_Chart.Type='DelinqD ays6089',qryNat_Chart.Rate,0)+IIf(qryNat_Chart.Type=’DelinqDays90119’,qryNat_Chart.Rate,0)+IIf(qr yNat_Chart.Type='DelinqDays90Plus',qryNat_Chart. Rate,0)) AS Rate, tblMainDetail.Type, tblMainDetail.Program, tblMainDetail.Sector, tblMainDetail.Portfolio " & _
"FROM qryNat_Chart LEFT JOIN tblMainDetail ON qryNat_Chart.ReviewName = tblMainDetail.ReviewName " & _
"GROUP BY qryNat_Chart.ReviewName, qryNat_Chart.DateYr, qryNat_Chart.DateQtr, tblMainDetail.Type, tblMainDetail.Program, tblMainDetail.Sector, tblMainDetail.Portfolio " & _
"HAVING qryNat_Chart.DateYr = " & Year(Date) & " AND qryNat_Chart.DateQtr = " & Val(Format(Date, "q")) - 1 & Chr(34) & strType & Chr(34) & " " & _
"ORDER BY Sum(IIf(qryNat_Chart.Type='DelinqDays120Plus',qryNat_Chart.Rate,0)+IIf(qryNat_Chart.Type='DelinqDays6089',qryNat_Chart.Rate,0)+IIf(qryNat_Chart.Type='DelinqDays90119',qryNat_Chart.Rate,0)+IIf(qryNat_Chart.Type='DelinqDays90Plus',qryNat_Chart. Rate,0)) DESC;" _
 
Bob,
Your suggestion was right on the money. I appreciate your time and patience for all of my questions in this thread. Code works great now.
 

Users who are viewing this thread

Back
Top Bottom