By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
446,292 Members | 1,379 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 446,292 IT Pros & Developers. It's quick & easy.

Access 2 fields

P: 4
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
Sep 5 '07 #1
Share this Question
Share on Google+
11 Replies


NeoPa
Expert Mod 15k+
P: 31,661
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.
Sep 6 '07 #2

P: 4
Expand|Select|Wrap|Line Numbers
  1. Private Sub Command88_Enter()
  2.  
  3. End Sub
  4.  
  5. Private Sub EXTENSION_AfterUpdate()
  6.   If Me.EXTENSION = "6" Then
  7.     Me.EXCHANGE = "85-"
  8.   Else
  9.     Me.EXCHANGE = Null
  10.   End If
  11. 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.
Sep 7 '07 #3

Scott Price
Expert 100+
P: 1,384
Expand|Select|Wrap|Line Numbers
  1. Private Sub EXTENSION_AfterUpdate()
  2. If Me.EXTENSION = "6" Then
  3. Me.EXCHANGE = "85-"
  4. Else
  5. Me.EXCHANGE = Null
  6. End If
  7. End Sub 
IF your Me.Extension field is Numeric data type... Try this:

Expand|Select|Wrap|Line Numbers
  1. If Me.Extension = 60000 to 69999 Then
  2. Me.Exchange = "85-"
  3. ElseIf Me.Extension = 40000 to 49999 Then
  4. Me.Exchange = "33-"
  5. End If
Otherwise, wrap the figures in ""'s

Regards,
Scott
Sep 7 '07 #4

missinglinq
Expert 2.5K+
P: 3,532
Expand|Select|Wrap|Line Numbers
  1. Private Sub EXTENSION_AfterUpdate()
  2. If Val(Me.extension) >= 60000 And Val(Me.extension) < 70000 Then
  3.  Me.exchange = "85-"
  4. ElseIf Val(Me.extension) >= 42000 And Val(Me.extension) < 50000 Then
  5.  Me.exchange = "33-"
  6. Else: Me.exchange = ""
  7. End If
  8. End Sub
  9.  
Welcome to TheScripts!

Linq ;0)>
Sep 7 '07 #5

P: 4
Expand|Select|Wrap|Line Numbers
  1. Private Sub EXTENSION_AfterUpdate()
  2. If Me.EXTENSION = "6" Then
  3. Me.EXCHANGE = "85-"
  4. Else
  5. Me.EXCHANGE = Null
  6. End If
  7. End Sub 
IF your Me.Extension field is Numeric data type... Try this:

Expand|Select|Wrap|Line Numbers
  1. If Me.Extension = 60000 to 69999 Then
  2. Me.Exchange = "85-"
  3. ElseIf Me.Extension = 40000 to 49999 Then
  4. Me.Exchange = "33-"
  5. 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
Sep 8 '07 #6

P: 4
Expand|Select|Wrap|Line Numbers
  1. Private Sub EXTENSION_AfterUpdate()
  2. If Val(Me.extension) >= 60000 And Val(Me.extension) < 70000 Then
  3.  Me.exchange = "85-"
  4. ElseIf Val(Me.extension) >= 42000 And Val(Me.extension) < 50000 Then
  5.  Me.exchange = "33-"
  6. Else: Me.exchange = ""
  7. End If
  8. End Sub
  9.  
Welcome to TheScripts!

Linq ;0)>
Ling ,
Thank you this is working for me in my form.

Intruder
Sep 8 '07 #7

Scott Price
Expert 100+
P: 1,384
Linq's worked because he actually tested it before giving it to you :-) Glad it's working for you!

Regards,
Scott
Sep 8 '07 #8

missinglinq
Expert 2.5K+
P: 3,532
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)>
Sep 8 '07 #9

Scott Price
Expert 100+
P: 1,384
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):
Expand|Select|Wrap|Line Numbers
  1. Select Case Me.Extension
  2.     Case 60000 To 69999
  3.    Me.Exchange = "85-"
  4.     Case 40000 To 49999
  5.    Me.Exchange = "33-"
  6.     Case Else
  7.     Me.Exchange = ""
  8. End Select
TO is also used in For---Next Loop statements to qualify the number of times to loop. I.e.
Expand|Select|Wrap|Line Numbers
  1. For I = 1 To 10
  2.     For J = 1 To 10
  3.         For K = 1 To 10
  4.             ...
  5.         Next K
  6.     Next J
  7. Next I
  8.  
It also has a few other uses, look it up in the Access VBA help file for more information.

Regards,
Scott
Sep 8 '07 #10

NeoPa
Expert Mod 15k+
P: 31,661
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
Sep 10 '07 #11

Scott Price
Expert 100+
P: 1,384
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
Sep 10 '07 #12

Post your reply

Sign in to post your reply or Sign up for a free account.