Sql statement help

[tuxalot]

Hi All,

Could you please take a look at this sql statement and tell me where I have gone wrong? I am getting the error "characters found after end of SQL statement". I am working on getting a listbox to sort by column headings when a label is clicked.

    strSql = "SELECT tblMachinery.ID, tblMachinery.MCodedID, tblDept.DeptName, tblMachinery.MName, " & _
             "tblMachinery.MLocation, tblMachinery.MDateCreated, tblMachinery.MDateAudit, " & _
             "tblMachinery.MRandomNumber, tblMachinery.MUploaded, IIf([MUploaded]=-1,""Y"",""N"") " & _
             "AS yesnoU, tblMachinery.MTags, IIf([MTags]=-1,""Y"",""N"") AS yesnoT " & _
             "FROM tblDept INNER JOIN tblMachinery ON tblDept.DeptID = tblMachinery.MDept " & _
             "ORDER BY tblMachinery.MCodedID;"

Any help appreciated.

 

[vbaInet]

Here it is:

    strSql = "SELECT tblMachinery.ID, tblMachinery.MCodedID, tblDept.DeptName, tblMachinery.MName, " & _
             "tblMachinery.MLocation, tblMachinery.MDateCreated, tblMachinery.MDateAudit, " & _
             "tblMachinery.MRandomNumber, tblMachinery.MUploaded, IIf([MUploaded]=-1, [COLOR=Red]'[/COLOR]Y[COLOR=Red]'[/COLOR],[COLOR=Red]'[/COLOR]N[COLOR=Red]'[/COLOR]) " & _
             "AS yesnoU, tblMachinery.MTags, IIf([MTags]=-1,[COLOR=Red]'[/COLOR]Y[COLOR=Red]'[/COLOR],[COLOR=Red]'[/COLOR]N[COLOR=Red]'[/COLOR]) AS yesnoT " & _
             "FROM tblDept INNER JOIN tblMachinery ON tblDept.DeptID = tblMachinery.MDept " & _
             "ORDER BY tblMachinery.MCodedID;"

The bits that were changed are highlighted in red.

 

[tuxalot]

Hi vbaInet,

Thanks for your followup. Unfortunately I tried that and got the same result. Any other suggestions?

 

[vbaInet]

What are you doing with the sql statement afterwards? Can I see that line too.

Where exactly is the error being highlighted?

 

[tuxalot]

The thought is to have a series of labels atop the listbox when clicked would sort A to Z and Z to A accordingly. Here is the first IF statement:

    If Me.lblID.Caption = "Name" Then

        Me.lblID.Caption = "Name " & strUp
        'strSql = strSql & "tblMachinery.MCodedID; "
        MsgBox strSql

        Me!lstMachinery.RowSource = strSql
        Me!lstMachinery.Requery
    End If

strUp = ChrW(9650) and shows a solid up triangle.

Thanks for your help.

 

[vbaInet]

Why are you appending "tblMachinery.MCodedID; " to the already built-up sql statement?

 

[tuxalot]

that bit is commented out for testing. strSQL will ultimately end in "ORDER BY tblMachinery.", then depending on label state (caption), will append "MCodedID; " or "MCodedID DESC; " and so on. Basically this way I only need to write the strSQL once and each label/state can append the bit that will sort by that column.

Hope this makes sense.

 

[vbaInet]

Upload a stripped down version of your db so I can have a better understanding what you're doing.

 

[tuxalot]

Attached is a stripped down db with the listbox and relevant tables. Thanks again for your assistance.

 

[vbaInet]

Have a look at the attached.

fyi: There's also the option of using a subform for this.

 

[tuxalot]

Hi vbaInet,

Works great. Nice clean code as well. I appreciate your help with this.

Tux

 

[vbaInet]

You're welcome!

 

[tuxalot]

I have each label heading working with your code. Thanks again for that. Now I need a way to get the labels back to their default name (caption) when any of the other labels are clicked to remove the up or down arrow. I was thinking about putting the default label caption in the tag, followed by a random character like a number "1". So the tag property for the Name label in my database would be Name1. The 1's would identify the collection and using a loop set the lbl.caption = lbl.tag -1. Would this be a good approach? How would I code that?

 

[tuxalot]

I was thinking something along the lines of (this does not work):

Dim ctl As Access.Control
Dim lblMe As String

lblMe = Screen.ActiveControl.Name
MsgBox lblMe

For Each ctl In Me.Controls
If TypeOf ctl Is label And Right$(ctl.Name, 1) = "1" And ctl.Caption <> "" & lblMe Then
With ctl
.Caption = .Tag
End With
End If
Next ctl

 

[vbaInet]

Remember this line of code I wrote, strsql = Left(..., Instr(...)) ?

When you loop through the controls use this same line of code to remove the ChrW().

Also, don't run the code everytime. Only run it if the calling label's current caption doesn't contain ChrW()

 

[tuxalot]

Tried this:

'Reset all relevant labels to default appearance
For Each ctl In Me.Controls

If TypeOf ctl Is label And InStr(1, ctl.Caption, " ChrW()") - 1 Then
With ctl
'do stuff
End With
End If
Next ctl

getting "Object does not support this property or method" at the bold line

 

[tuxalot]

Now I have this, not the cleanest code but I'm still learning. However it removes the arrow symbol on the currently selected label which it should not:

For Each ctl In Me.TabCtl0

If TypeOf ctl Is label Then
With ctl
If InStr(1, .Caption, "" & strUP) <> 0 Then
.Caption = Left(.Tag, InStr(1, .Tag, "1") - 1)
End If
If InStr(1, .Caption, "" & strDown) <> 0 Then
.Caption = Left(.Tag, InStr(1, .Tag, "1") - 1)
End If
End With
End If
Next ctl

 

[tuxalot]

Here's what I settled on. I added a number "1" at the end of the tags that I wanted changed as part of this procedure.

Dim strUP, strDown, strSql As String

strUP = ChrW(9650) 'Works with Arial to show solid UP triangle
strDown = ChrW(9660) 'Works with Arial to show solid Down triangle

strCaption = Me.lblAudited.Caption

'Reset all relevant labels to default appearance
For Each ctl In Me.TabCtl0

If TypeOf ctl Is label Then
With ctl
If .Tag Like "*1" Then
.Caption = Left(.Tag, InStr(1, .Tag, "1") - 1)
End If
End With
End If
Next ctl

Me.lblAudited.Caption = strCaption

With Me.lstMachinery
strSql = Left(.RowSource, InStr(1, .RowSource, " ORDER") - 1)

Select Case Me.lblAudited.Caption
Case "Audited"
Me.lblAudited.Caption = "Audited " & strUP
.RowSource = strSql & " ORDER BY tblMachinery.MDateAudit ASC;"
.Requery
Case "Audited " & strUP
Me.lblAudited.Caption = "Audited " & strDown
.RowSource = strSql & " ORDER BY tblMachinery.MDateAudit DESC;"
.Requery
Case "Audited " & strDown
Me.lblAudited.Caption = "Audited " & strUP
.RowSource = strSql & " ORDER BY tblMachinery.MDateAudit ASC;"
.Requery
End Select
End With

 

[vbaInet]

Good work tuxalot.

Instead of "wasting" your Tag property, here's what I would use:

' Only loop through controls if it doesn't contain up or down
If Right(Me.[COLOR=Red]Label[/COLOR].Caption, 1) <> strUp And Right(Me.[COLOR=Red]Label[/COLOR].Caption, 1) <> strDown Then
    For Each ctl In Me.TabCtl0
        If ctl.ControlType = acLabel Then
            If ctl.Name <> Me.[COLOR=Red]Label[/COLOR].Name Then
                ' No need to check, just replace
                ctl.Caption = Replace(ctl.Caption, " " & strUp, "")
                ctl.Caption = Replace(ctl.Caption, " " & strDown, "")
            End If
        End If
    Next ctl
End If

It may need tweaking because I've not tested or compiled it.

Also, I would advise that you create a function and call the function on the Click event of every label. It's arguments should be the name of the label and the name of the sort field.

 

[tuxalot]

vbaInet - I used your code and it worked great. Thanks for that. I have not a clue how to re-code this as a function. I need to learn how to pass arguments and the like.

Thank you so much for your assistance.

Tux