Syntax error "=" (2 Viewers)

[ClaraBarton]

This Works:

If Me.selAlpha & "" = "" Then

This does not work:

If Me.Controls(Me.selAlpha).Caption = "All Accounts" Then
        Me.selAlpha & "" = ""

I'm using Pat Hartman's form:

I know I have a syntax error but I've looked at it and looked at it and I can't see it. Why does the first work but the second will not compile at the second line?

 

[theDBguy]

Maybe try?

Me.Controls("""" & Me.selAlpha & """").Caption

Just a thought...

 

[RonPaii]

Or simpler

Me.selAlpha.Caption

 

[DHookom]

I think the error is:
Me.selAlpha & "" = ""
What exactly are you attempting to do. There should only be the variable or a control name to the left of the =.

 

[tvanstiphout]

As to the "Why" question:
Your first statement:
If Me.selAlpha & "" = "" Then
Is interpreted as:
If Me.selAlpha.Value & "" = "" Then
The & "" part is often used to convert a Null value into an empty string, so that empty string can be compared to "", and the result is True. Compare that to leaving it Null, and any comparison with Null is Null per definition.

Your second statement:
If Me.Controls(Me.selAlpha).Caption = "All Accounts" Then
Me.selAlpha & "" = ""
Is very different. Now it's not a comparison, but an assignment. The objective is to assign the empty string to the selAlpha control. The way to do that is:
Me.selAlpha = ""

QED.

 

[ClaraBarton]

I want it in a string:

     If Me.selAlpha & "" = "" Then
    Else
        strStart = Left(Me.Controls("B" & Trim(Int(Me.selAlpha))).Caption, 1)
        strEnd = Right(Me.Controls("B" & Trim(Int(Me.selAlpha.Value))).Caption, 1)
    End If
'' Finish Where clause
    If strStart & "" = "" Then
        strWhere = ""
    Else
        strWhere = " WHERE Left(" & strKeyField & ",1) >= '" & strStart & "' AND Left(" & strKeyField & ",1) <= '" & strEnd & "' "
    End If

It all works like beautiful magic when correct.

 

[ClaraBarton]

Tom:

 If Me.Controls(Me.selAlpha).Caption = "All Accounts" Then
        Me.selAlpha = ""

I thought that made sense but now I get "Object doesn't support this method"
What I'm trying to do is simply clear the filter but I can't do a form filter because there are others..
It should be a very simple thing that's completely escaping me.

 

[tvanstiphout]

is selAlpha a Label control? There are only very few control types that have a Caption property.

 

[ClaraBarton]

selAlpha is an option control. See all the letters and numbers at the bottom of the form? It worked fine until I added an "All Accounts" button.

 

[ClaraBarton]

The original form used A-Z to clear the filter but I added numbers and A-Z didn't work so well. Thus the All Accounts and I'll get rid of the A-Z

 

[MajP]

I do not think that syntax looks correct.
This assumes that you dropped all your controls in correct order. An options group has a controls collection like you show so in your case the first dropped control is "A" and has an index of 1.

So to get the caption of the clicked control

If Me.SelAlpha.controls(Me.selAlpha.value).Caption = "All Accounts"

you want the controls belonging to your option group

As written does not make sense.

If Me.Controls(Me.selAlpha).Caption

That will give you a random control on the form not a control in the controls collection. If the first control on your form is for example the delete button then when you click the first control in the option it will return a value of 1.
me.controls(1).caption is the caption of the first control on the form not in the option. If that control does not have a caption it fails.
I doubt as written it ever worked or you got lucky.

 

[tvanstiphout]

"selAlpha is an option control"
We have to use precise language. Access does not have an "option control". It has an Option Group, and such group can have 3 styles, including one with Toggle Buttons. It appears that's what you have used, and indeed a Toggle Button has a Caption property.

When creating an Option Group and using the wizard, by default the Values are 1 -> N:

In your case, what is the Value of the All Accounts button? You can find the Option Value property on the Data tab.
That value is then poked into your expression:
If Me.Controls(Me.selAlpha).Caption = "All Accounts" Then

 

[MajP]

Here is a demo with the correct syntax showing why the OPs original syntax will be a random control on the form and not the caption of the clicked control. It will fail if that random control does not have a caption property.

It is
Me.SelAlpha.Controls(me.selalpha.value)
not
Me.Controls(Me.selalpha.value)

By random I mean some control on the form that has the same index as the index in the option group, and not the control in the option group with that index. What should be noted is that there can be multiple controls collections. A form has a controls collection, and most controls have a controls collection. Even a textbox has a controls collection (as far as I know it can only have one control in that collection, the attached label)

 

[ClaraBarton]

Ok I get it! So I'm back to this:

If Me.selAlpha.Value = 30 Then
        Me.selAlpha & "" = ""
    Else
        strStart = Left(Me.Controls("B" & Trim(Int(Me.selAlpha))).Caption, 1)
        strEnd = Right(Me.Controls("B" & Trim(Int(Me.selAlpha.Value))).Caption, 1)
    End If

How do I get this:

If strStart & "" = "" Then
        strWhere = strWhere & strWhere2

A null string. I want a null string if option 30 is selected.
AND BTW, debug returns 30 for Me.selAlpha.Value.

 

[ClaraBarton]

Why does this work:

If Me.selAlpha & "" = "" Then
    Else
        strStart = Left(Me.Controls("B" & Trim(Int(Me.selAlpha))).Caption, 1)
        strEnd = Right(Me.Controls("B" & Trim(Int(Me.selAlpha.Value))).Caption, 1)
    End If

Returns null

And this does not:

    If Me.selAlpha.Value = 30 Or Me.selAlpha & "" = "" Then

    Else
        strStart = Left(Me.Controls("B" & Trim(Int(Me.selAlpha))).Caption, 1)
        strEnd = Right(Me.Controls("B" & Trim(Int(Me.selAlpha.Value))).Caption, 1)
    End If

Shouldn't they be the same thing?

 

[RonPaii]

I have a form with a 3 option control named obManu. The values of each option are 3, 2, 1 in the order of the options on the form. The following code gives me the caption. Each option has 2 controls, 1st is the option the 2nd is the label. Though the label has the caption I needed to call the option.

Me.ogManu.Controls.Item(Switch(Me.ogManu.Value = 1, 5, Me.ogManu.Value = 2, 3, True, 1)).Caption

 

[The_Doc_Man]

I'm looking at the idea of Me.Controls( Me.anything) as a syntax element. (And expanding on the comment of @DHookom).

This is a cut/paste of your statement in post #1

If Me.Controls(Me.selAlpha).Caption = "All Accounts" Then

As DHookom stated, the syntax of Me.Controls() requires EITHER a control's index number (because Access collections like this have 0-based numbers) or a string that is the name of a control in that collection.

To answer why that doesn't work, you need to put a breakpoint on that line and see what value is returned by Me.selAlpha - perhaps by opening the Immediate Window and using Debug.Print Me.selAlpha to see the returned value.

Verify that it IS the name or index of a control occurring in Me.Controls() collection. If it is NOT a collection member, Me.Controls( Me.anything ) returns a null, and IF null = something always returns FALSE ... so the TRUE branch of the IF statement would not execute.

Sorry if that got a little pedantic, but you asked why it wouldn't work.

 

[MajP]

This is a cut/paste of your statement in post #1

If Me.Controls(Me.selAlpha).Caption = "All Accounts" Then

As DHookom stated, the syntax of Me.Controls() requires EITHER a control's index number (because Access collections like this have 0-based numbers) or a string that is the name of a control in that collection.

To answer why that doesn't work, you need to put a breakpoint on that line and see what value is returned by Me.selAlpha - perhaps by opening the Immediate Window and using Debug.Print Me.selAlpha to see the returned value.

Verify that it IS the name or index of a control occurring in Me.Controls() collection. If it is NOT a collection member, Me.Controls( Me.anything ) returns a null, and IF null = something always returns FALSE ... so the TRUE branch of the IF statement would not execute.

I already explained how this works / does not work in detail. Did you not read?

 

[The_Doc_Man]

Something about your original explanation didn't immediately click with me. You mentioned what it WOULD do. I just went ahead and explained as I saw it, limiting the question to "what is wrong with the original statement?"

 

[ClaraBarton]

Click on any letter or number in the form except all accounts in the options at the bottom