Access 2 fields

[Intruder]

I have records with multiple fields.
Field Extension is 5 digits
Field Exchange Needs to be filled out depending of the value of Extension

If Extension is 60000 thru 69999 then Exchange should show 85-

If Extension is 41000 thru 41999 then Exchange should show 33-

I am having a problem because of the between value that I am looking for



Thank you in Advance

[NeoPa]

I'm afraid this question is incomplete.
Please rephrase it so that we have all the information. We can try to help with the answers but writing a question that makes sense is your job.

MODERATOR.

[Intruder]

Private Sub Command88_Enter()
 
End Sub
 
Private Sub EXTENSION_AfterUpdate()
  If Me.EXTENSION = "6" Then
    Me.EXCHANGE = "85-"
  Else
    Me.EXCHANGE = Null
  End If
End Sub

Two fields Exchange and Extension
I am working with Telephone numbers. The end result should be that if I enter any number between 60000 and 69999 the result should be to put 85- in the field Exchange. Also any 42000 thru 49999 number should result in 33- in the Exchange field. The above only does this if the single digit 6 is entered. I have tried several ways to do this "6????" and "6[0-9][0-9][0-9][0-9]" are just two examples of what I have tried.

[Scott Price]

Private Sub EXTENSION_AfterUpdate()
If Me.EXTENSION = "6" Then
Me.EXCHANGE = "85-"
Else
Me.EXCHANGE = Null
End If
End Sub 

IF your Me.Extension field is Numeric data type... Try this:

If Me.Extension = 60000 to 69999 Then
Me.Exchange = "85-"
ElseIf Me.Extension = 40000 to 49999 Then
Me.Exchange = "33-"
End If

Otherwise, wrap the figures in ""'s

Regards,
Scott

[missinglinq]

Private Sub EXTENSION_AfterUpdate()
If Val(Me.extension) >= 60000 And Val(Me.extension) < 70000 Then
 Me.exchange = "85-"
ElseIf Val(Me.extension) >= 42000 And Val(Me.extension) < 50000 Then
 Me.exchange = "33-"
Else: Me.exchange = ""
End If
End Sub
 

Welcome to TheScripts!

Linq ;0)>

[Intruder]

Private Sub EXTENSION_AfterUpdate()
If Me.EXTENSION = "6" Then
Me.EXCHANGE = "85-"
Else
Me.EXCHANGE = Null
End If
End Sub 

IF your Me.Extension field is Numeric data type... Try this:

If Me.Extension = 60000 to 69999 Then
Me.Exchange = "85-"
ElseIf Me.Extension = 40000 to 49999 Then
Me.Exchange = "33-"
End If

Otherwise, wrap the figures in ""'s

Regards,
Scott

Scott, thanks for your suggestion. The third post worked for my application. I could not get the correct inputs with the above solution.
Intruder

[Intruder]

Private Sub EXTENSION_AfterUpdate()
If Val(Me.extension) >= 60000 And Val(Me.extension) < 70000 Then
 Me.exchange = "85-"
ElseIf Val(Me.extension) >= 42000 And Val(Me.extension) < 50000 Then
 Me.exchange = "33-"
Else: Me.exchange = ""
End If
End Sub
 

Welcome to TheScripts!

Linq ;0)>

Ling ,
Thank you this is working for me in my form.

Intruder

[Scott Price]

Linq's worked because he actually tested it before giving it to you :-) Glad it's working for you!

Regards,
Scott

[missinglinq]

Glad we could help, Intruder!

Scott's code is what some call "air code" which is to say off the top of the poster's head! It's OK to post this in response to a question, but it should be qualified as "air code" or "untested" so the the OP is warned!

You and Scott both have a great weekend!

Linq ;0)>

[Scott Price]

And "air code" comes off the top of "air heads" :-) LOL...

Just an FYI, the TO operator in VBA doesn't work the way I was trying to use it included in an IF---Then statement, that was why that code wouldn't work!

However, it will work in a Select Case statement in this way (go figure... M$ must have a good reason for making perfectly goofy logic where it will work in Select, but not in If---Then):

Select Case Me.Extension
    Case 60000 To 69999
   Me.Exchange = "85-"
    Case 40000 To 49999
   Me.Exchange = "33-"
    Case Else
    Me.Exchange = ""
End Select

TO is also used in For---Next Loop statements to qualify the number of times to loop. I.e.

For I = 1 To 10
    For J = 1 To 10
        For K = 1 To 10
            ...
        Next K
    Next J
Next I
 

It also has a few other uses, look it up in the Access VBA help file for more information.

Regards,
Scott

[NeoPa]

And "air code" comes off the top of "air heads" :-) LOL...
Regards,
Scott

Scott,
I'm not going to go along with the Air-head thing. You're a valuable member of the team who simply put down what they could in the time they had available.
We expect questioners to check out their posts thoroughly. If we expected that of all the experts then that would limit the amount of answers that could be posted and the number of experts that could find the requisite time to post. The OPs can be expected to test out the answers as it is they that are being assisted (I'm not discouraging experts from testing their suggestions here - simply explaining that it is not seen as their responsibility).

As to M$ being responsible for your getting the If syntax all wrong...I allowed myself a little giggle at that :D

[Scott Price]

Scott,
I'm not going to go along with the Air-head thing. You're a valuable member of the team who simply put down what they could in the time they had available.
We expect questioners to check out their posts thoroughly. If we expected that of all the experts then that would limit the amount of answers that could be posted and the number of experts that could find the requisite time to post. The OPs can be expected to test out the answers as it is they that are being assisted (I'm not discouraging experts from testing their suggestions here - simply explaining that it is not seen as their responsibility).

As to M$ being responsible for your getting the If syntax all wrong...I allowed myself a little giggle at that :D

You're too kind, NP :-) However, I should know better than to post a snippet of code that SHOULD work without making sure that it DOES work...

Still haven't found out why M$ made me make that syntactical mistake though LOL

Regards,
Scott