Select Case problem

[boaterjohn]

I have a access table called Fruits. I have 2 fields in this table:
fld 1 = "Fruit" fld 2 = "LName" fld 3 = "Fruit Code"
Using the following Below: When I enter "Fruit" field, it stays blank. It should show "Grapes". I'm obviously missing something in this code.
Any help, appreciated
Thanx,
boaterjohn
Private Sub Fruit_Enter()
Select Case Fruit
Case Left([LNAME], 1) = "G" and Right([Fruit Code], 2)
Me.Fruit = "Grapes"
End Select
End Sub

 

[pr2-eugin]

Looking at your code, I have a question and few suggestions..

Question : What is the Control? Is it a ComboBox/TextBox?

Suggestion - 1 : The choice of your method might not be great.. As Enter is triggered when the focus is obtained by the control, that is it. It will not be triggered after you change a value/if you type another value deleting the previously entered value. So try a better method maybe After Update?

Suggestion - 2 : The code you have,

:
        Case Left([LNAME], 1) = "G"[B][COLOR=Red] and[/COLOR][/B] Right([Fruit Code], 2)
:

will always return a Boolean value True/False. Because you are using the logical operator AND. What I believe you want to do is a concatenation of the LNAME and the CODE. So, use & to concatenate strings.. So if you have values like LNAME = "Grapes", FruitCode = 1234 then you want is Case 'G34', change your code to..

Private Sub Fruit_AfterUpdate()
    Select Case Fruit
        Case Left([LNAME], 1) [B][COLOR=Red]&[/COLOR][/B] Right([Fruit Code], 2) [COLOR=Green]'This might have value like G34[/COLOR]
            Me.Fruit = "Grapes"
    End Select
End Sub

 

[Brianwarnock]

I don't understand the use of a Select Case here, it looks more like a
If.. Then..
Than a Case statement


Mind you I cannot understand the base problem, ie what the poster is trying t o do.

Brian

 

[boaterjohn]

Looking at your code, I have a question and few suggestions..

Question : What is the Control? Is it a ComboBox/TextBox?

Suggestion - 1 : The choice of your method might not be great.. As Enter is triggered when the focus is obtained by the control, that is it. It will not be triggered after you change a value/if you type another value deleting the previously entered value. So try a better method maybe After Update?

Suggestion - 2 : The code you have,

:
        Case Left([LNAME], 1) = "G"[B][COLOR=red] and[/COLOR][/B] Right([Fruit Code], 2)
:

will always return a Boolean value True/False. Because you are using the logical operator AND. What I believe you want to do is a concatenation of the LNAME and the CODE. So, use & to concatenate strings.. So if you have values like LNAME = "Grapes", FruitCode = 1234 then you want is Case 'G34', change your code to..

Private Sub Fruit_AfterUpdate()
    Select Case Fruit
        Case Left([LNAME], 1) [B][COLOR=red]&[/COLOR][/B] Right([Fruit Code], 2) [COLOR=green]'This might have value like G34[/COLOR]
            Me.Fruit = "Grapes"
    End Select
End Sub

It worked like a charm. Thanx for your help for this code.
Boaterjohn