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
+ 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
11 Replies

 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 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. Sep 7 '07 #3

 Expert 100+ P: 1,384 Expand|Select|Wrap|Line Numbers 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: Expand|Select|Wrap|Line Numbers 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 Sep 7 '07 #4

 Expert 2.5K+ P: 3,532 Expand|Select|Wrap|Line Numbers 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)> Sep 7 '07 #5

 P: 4 Expand|Select|Wrap|Line Numbers 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: Expand|Select|Wrap|Line Numbers 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 Sep 8 '07 #6

 P: 4 Expand|Select|Wrap|Line Numbers 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 Sep 8 '07 #7

 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

 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

 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 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. Expand|Select|Wrap|Line Numbers 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 Sep 8 '07 #10

 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

 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.