If this is your first visit, be sure to check out the FAQ by clicking the link above. You may have to register before you can post: click the register link above to proceed. To start viewing messages, select the forum that you want to visit from the selection below. |
|
|
|
Thread Tools | Display Modes |
|
#1
|
|||
|
|||
How to phrase If/Then clauses in the control source field
Hello,
This is a continuation of an earlier thread about Parsing strings from one field into many. I making a database,that contains five fields of overlap and to make the data entry easier I am trying to automate some of it. I have a code field, and then four other fields whose information can be derived from the code ((codelooks like this: 01MP, and has four parts. The first number refers to the year 2000, the second number refers the season of the year (spring), the third to type of media (a magazine), and the fourth the genre (politics).)) Anyway, my problem lies in the fact that the code only has one digit for the year, and the time span is from 1996 to present. so how could one phrase an expression that essentially says: if the first digit of the code string is =6 but =9, add the value to 1990, and if the value is =0 but =5 add it to 2000 (a short term solution; the code thing will eventually be revised). Thanks for any help or info you can provide! |
#2
|
|||
|
|||
How to phrase If/Then clauses in the control source field
Dim intYear As Integer
intYear = Left(CodeString, 1) Select Case intYear Case 6 To 9 FieldOrVariable = 1990 + intYear Case 0 To 5 FieldOrVariable = 2000 + intYear End Select you'll have to tweak it to work in your specific circumstances, but that should get you started. hth wrote in message ... Hello, This is a continuation of an earlier thread about Parsing strings from one field into many. I making a database,that contains five fields of overlap and to make the data entry easier I am trying to automate some of it. I have a code field, and then four other fields whose information can be derived from the code ((codelooks like this: 01MP, and has four parts. The first number refers to the year 2000, the second number refers the season of the year (spring), the third to type of media (a magazine), and the fourth the genre (politics).)) Anyway, my problem lies in the fact that the code only has one digit for the year, and the time span is from 1996 to present. so how could one phrase an expression that essentially says: if the first digit of the code string is =6 but =9, add the value to 1990, and if the value is =0 but =5 add it to 2000 (a short term solution; the code thing will eventually be revised). Thanks for any help or info you can provide! |
#3
|
|||
|
|||
How to phrase If/Then clauses in the control source field
I tried it but it gave me a syntax error message. Is
there a general if/then template? -----Original Message----- Dim intYear As Integer intYear = Left(CodeString, 1) Select Case intYear Case 6 To 9 FieldOrVariable = 1990 + intYear Case 0 To 5 FieldOrVariable = 2000 + intYear End Select you'll have to tweak it to work in your specific circumstances, but that should get you started. hth wrote in message ... Hello, This is a continuation of an earlier thread about Parsing strings from one field into many. I making a database,that contains five fields of overlap and to make the data entry easier I am trying to automate some of it. I have a code field, and then four other fields whose information can be derived from the code ((codelooks like this: 01MP, and has four parts. The first number refers to the year 2000, the second number refers the season of the year (spring), the third to type of media (a magazine), and the fourth the genre (politics).)) Anyway, my problem lies in the fact that the code only has one digit for the year, and the time span is from 1996 to present. so how could one phrase an expression that essentially says: if the first digit of the code string is =6 but =9, add the value to 1990, and if the value is =0 but =5 add it to 2000 (a short term solution; the code thing will eventually be revised). Thanks for any help or info you can provide! . |
#4
|
|||
|
|||
How to phrase If/Then clauses in the control source field
What Tina posted belongs in VBA code. If you really prefer
to put this into your qbe pane then you would type something like this: NewYear: iif(left(codefield,1) = "0", 2000, iif(left (codefield,1) = "1", 2001, iif( etc... Note that "Year" is reserved in access, substitute whatever field you want to store the year in where I have "NewYear" HTH -----Original Message----- I tried it but it gave me a syntax error message. Is there a general if/then template? -----Original Message----- Dim intYear As Integer intYear = Left(CodeString, 1) Select Case intYear Case 6 To 9 FieldOrVariable = 1990 + intYear Case 0 To 5 FieldOrVariable = 2000 + intYear End Select you'll have to tweak it to work in your specific circumstances, but that should get you started. hth wrote in message ... Hello, This is a continuation of an earlier thread about Parsing strings from one field into many. I making a database,that contains five fields of overlap and to make the data entry easier I am trying to automate some of it. I have a code field, and then four other fields whose information can be derived from the code ((codelooks like this: 01MP, and has four parts. The first number refers to the year 2000, the second number refers the season of the year (spring), the third to type of media (a magazine), and the fourth the genre (politics).)) Anyway, my problem lies in the fact that the code only has one digit for the year, and the time span is from 1996 to present. so how could one phrase an expression that essentially says: if the first digit of the code string is =6 but =9, add the value to 1990, and if the value is =0 but =5 add it to 2000 (a short term solution; the code thing will eventually be revised). Thanks for any help or info you can provide! . . |
#5
|
|||
|
|||
How to phrase If/Then clauses in the control source field
Well anonymous,
If you're going to be dabbling with these kinds of things, I highly recommend getting some Access books and doing some studying. I'd recommend getting "Beginning Access 2002 VBA" by Wrox. (The 2003 book is not out yet.) You've gotten some perfectly usable answers in both your first thread and this thread. (BTW, please don't start a new thread for the same problem. You should have posted to the first thread saying you didn't understand their answer and ask for clarification.) The problem with the answers you've gotten is that they are not complete. They unfortunately assumed that you had a basic understand of Access and a little VBA coding. In one of your posts you ask for an If-Then "template." There is no such thing. The "syntax" for an If-Then structure is very clearly explained in the help file. If you need more than that, read the book I mentioned above. There is no syntax error in the code that Tina posted. Any syntax errors you are getting are from how you are trying to use the code. Which, BTW, you never mentioned. To understand how to use the code Tina posted, (and the code in the replies in the other thread,) you need to read the above book. In the mean time, (and *not* a replacement for you getting and reading the above book,) I'm going to make some guesses from the subject line of your post. 1 - You want a finished solution, not some pointers. 2 - Tina missed the words "control source" in the subject line or maybe she would have given you a "complete" solution. 3 - She was expecting you to replace "FieldOrVariable" and "CodeString" in her code with *your* actual names. Here is how Tina's code needs to be completed to work in a control source: '~~~ Start Code ~~~ Public Function ReturnYear(CodeString) Dim intYear As Integer intYear = Left(CodeString, 1) Select Case intYear Case 6 To 9 ReturnYear = 1990 + intYear Case 0 To 5 ReturnYear = 2000 + intYear End Select End Function '~~~ End Code ~~~ 1 - Copy and paste this code into the body of a standard code module (From the database window, select Modules and click new.) 2 - Close the code window and save the code module as basMyFunctions. 3 - In the control source of your text box, put: =ReturnYear([YourFieldName])) And replace "YourFieldName" with *your* field name that has the code in it. 4 - Purchase "Beginning Access 2002 VBA" 5 - Read it. Bottom line, as someone else mentioned, you shouldn't be messing with fields that are combinations of separate information. Separate information should be in *separate* fields. Hitting the tab key during data entry is *not* that big a deal. And it is *not* something you should program around for some kind of perceived convenience. Good luck. BTW, did I mention you need to buy and read "Beginning Access 2002 VBA"???... Sco M.L. "Sco" Scofield, Microsoft Access MVP, MCSD, MCP, MSS, A+ Useful Metric Conversion #17 of 19: 1 billion billion picolos = 1 gigolo Miscellaneous Access and VB "stuff" at www.ScoBiz.com wrote in message ... I tried it but it gave me a syntax error message. Is there a general if/then template? -----Original Message----- Dim intYear As Integer intYear = Left(CodeString, 1) Select Case intYear Case 6 To 9 FieldOrVariable = 1990 + intYear Case 0 To 5 FieldOrVariable = 2000 + intYear End Select you'll have to tweak it to work in your specific circumstances, but that should get you started. hth wrote in message ... Hello, This is a continuation of an earlier thread about Parsing strings from one field into many. I making a database,that contains five fields of overlap and to make the data entry easier I am trying to automate some of it. I have a code field, and then four other fields whose information can be derived from the code ((codelooks like this: 01MP, and has four parts. The first number refers to the year 2000, the second number refers the season of the year (spring), the third to type of media (a magazine), and the fourth the genre (politics).)) Anyway, my problem lies in the fact that the code only has one digit for the year, and the time span is from 1996 to present. so how could one phrase an expression that essentially says: if the first digit of the code string is =6 but =9, add the value to 1990, and if the value is =0 but =5 add it to 2000 (a short term solution; the code thing will eventually be revised). Thanks for any help or info you can provide! . |
#6
|
|||
|
|||
How to phrase If/Then clauses in the control source field
yes, Sco, i did miss the reference in the header. the post began by talking
about "Parsing strings from one field into many", so i assumed the poster was working on code to break out data from a single table field into several fields in another table (not *show* data in a form's calculated control). and i assumed he pretty much knew how to break out the data, and was just looking for a more elegant function to replace whatever he was already using. well, you know what they say about "assuming..."! g at any rate, your instructions were about as complete as could be asked for; i'm glad you stepped in to help further. "M.L. Sco Scofield" wrote in message ... Well anonymous, If you're going to be dabbling with these kinds of things, I highly recommend getting some Access books and doing some studying. I'd recommend getting "Beginning Access 2002 VBA" by Wrox. (The 2003 book is not out yet.) You've gotten some perfectly usable answers in both your first thread and this thread. (BTW, please don't start a new thread for the same problem. You should have posted to the first thread saying you didn't understand their answer and ask for clarification.) The problem with the answers you've gotten is that they are not complete. They unfortunately assumed that you had a basic understand of Access and a little VBA coding. In one of your posts you ask for an If-Then "template." There is no such thing. The "syntax" for an If-Then structure is very clearly explained in the help file. If you need more than that, read the book I mentioned above. There is no syntax error in the code that Tina posted. Any syntax errors you are getting are from how you are trying to use the code. Which, BTW, you never mentioned. To understand how to use the code Tina posted, (and the code in the replies in the other thread,) you need to read the above book. In the mean time, (and *not* a replacement for you getting and reading the above book,) I'm going to make some guesses from the subject line of your post. 1 - You want a finished solution, not some pointers. 2 - Tina missed the words "control source" in the subject line or maybe she would have given you a "complete" solution. 3 - She was expecting you to replace "FieldOrVariable" and "CodeString" in her code with *your* actual names. Here is how Tina's code needs to be completed to work in a control source: '~~~ Start Code ~~~ Public Function ReturnYear(CodeString) Dim intYear As Integer intYear = Left(CodeString, 1) Select Case intYear Case 6 To 9 ReturnYear = 1990 + intYear Case 0 To 5 ReturnYear = 2000 + intYear End Select End Function '~~~ End Code ~~~ 1 - Copy and paste this code into the body of a standard code module (From the database window, select Modules and click new.) 2 - Close the code window and save the code module as basMyFunctions. 3 - In the control source of your text box, put: =ReturnYear([YourFieldName])) And replace "YourFieldName" with *your* field name that has the code in it. 4 - Purchase "Beginning Access 2002 VBA" 5 - Read it. Bottom line, as someone else mentioned, you shouldn't be messing with fields that are combinations of separate information. Separate information should be in *separate* fields. Hitting the tab key during data entry is *not* that big a deal. And it is *not* something you should program around for some kind of perceived convenience. Good luck. BTW, did I mention you need to buy and read "Beginning Access 2002 VBA"???... Sco M.L. "Sco" Scofield, Microsoft Access MVP, MCSD, MCP, MSS, A+ Useful Metric Conversion #17 of 19: 1 billion billion picolos = 1 gigolo Miscellaneous Access and VB "stuff" at www.ScoBiz.com wrote in message ... I tried it but it gave me a syntax error message. Is there a general if/then template? -----Original Message----- Dim intYear As Integer intYear = Left(CodeString, 1) Select Case intYear Case 6 To 9 FieldOrVariable = 1990 + intYear Case 0 To 5 FieldOrVariable = 2000 + intYear End Select you'll have to tweak it to work in your specific circumstances, but that should get you started. hth wrote in message ... Hello, This is a continuation of an earlier thread about Parsing strings from one field into many. I making a database,that contains five fields of overlap and to make the data entry easier I am trying to automate some of it. I have a code field, and then four other fields whose information can be derived from the code ((codelooks like this: 01MP, and has four parts. The first number refers to the year 2000, the second number refers the season of the year (spring), the third to type of media (a magazine), and the fourth the genre (politics).)) Anyway, my problem lies in the fact that the code only has one digit for the year, and the time span is from 1996 to present. so how could one phrase an expression that essentially says: if the first digit of the code string is =6 but =9, add the value to 1990, and if the value is =0 but =5 add it to 2000 (a short term solution; the code thing will eventually be revised). Thanks for any help or info you can provide! . |
#7
|
|||
|
|||
How to phrase If/Then clauses in the control source field
Thanks for the kind words Tina.
One of my biggest problems is still reading both the subject and body *and* connecting them. I frequently read what I want to. And you know where that goes... Keep up the good work. Sco M.L. "Sco" Scofield, Microsoft Access MVP, MCSD, MCP, MSS, A+ Useful Metric Conversion #17 of 19: 1 billion billion picolos = 1 gigolo Miscellaneous Access and VB "stuff" at www.ScoBiz.com "tina" wrote in message ... yes, Sco, i did miss the reference in the header. the post began by talking about "Parsing strings from one field into many", so i assumed the poster was working on code to break out data from a single table field into several fields in another table (not *show* data in a form's calculated control). and i assumed he pretty much knew how to break out the data, and was just looking for a more elegant function to replace whatever he was already using. well, you know what they say about "assuming..."! g at any rate, your instructions were about as complete as could be asked for; i'm glad you stepped in to help further. "M.L. Sco Scofield" wrote in message ... Well anonymous, If you're going to be dabbling with these kinds of things, I highly recommend getting some Access books and doing some studying. I'd recommend getting "Beginning Access 2002 VBA" by Wrox. (The 2003 book is not out yet.) You've gotten some perfectly usable answers in both your first thread and this thread. (BTW, please don't start a new thread for the same problem. You should have posted to the first thread saying you didn't understand their answer and ask for clarification.) The problem with the answers you've gotten is that they are not complete. They unfortunately assumed that you had a basic understand of Access and a little VBA coding. In one of your posts you ask for an If-Then "template." There is no such thing. The "syntax" for an If-Then structure is very clearly explained in the help file. If you need more than that, read the book I mentioned above. There is no syntax error in the code that Tina posted. Any syntax errors you are getting are from how you are trying to use the code. Which, BTW, you never mentioned. To understand how to use the code Tina posted, (and the code in the replies in the other thread,) you need to read the above book. In the mean time, (and *not* a replacement for you getting and reading the above book,) I'm going to make some guesses from the subject line of your post. 1 - You want a finished solution, not some pointers. 2 - Tina missed the words "control source" in the subject line or maybe she would have given you a "complete" solution. 3 - She was expecting you to replace "FieldOrVariable" and "CodeString" in her code with *your* actual names. Here is how Tina's code needs to be completed to work in a control source: '~~~ Start Code ~~~ Public Function ReturnYear(CodeString) Dim intYear As Integer intYear = Left(CodeString, 1) Select Case intYear Case 6 To 9 ReturnYear = 1990 + intYear Case 0 To 5 ReturnYear = 2000 + intYear End Select End Function '~~~ End Code ~~~ 1 - Copy and paste this code into the body of a standard code module (From the database window, select Modules and click new.) 2 - Close the code window and save the code module as basMyFunctions. 3 - In the control source of your text box, put: =ReturnYear([YourFieldName])) And replace "YourFieldName" with *your* field name that has the code in it. 4 - Purchase "Beginning Access 2002 VBA" 5 - Read it. Bottom line, as someone else mentioned, you shouldn't be messing with fields that are combinations of separate information. Separate information should be in *separate* fields. Hitting the tab key during data entry is *not* that big a deal. And it is *not* something you should program around for some kind of perceived convenience. Good luck. BTW, did I mention you need to buy and read "Beginning Access 2002 VBA"???... Sco M.L. "Sco" Scofield, Microsoft Access MVP, MCSD, MCP, MSS, A+ Useful Metric Conversion #17 of 19: 1 billion billion picolos = 1 gigolo Miscellaneous Access and VB "stuff" at www.ScoBiz.com wrote in message ... I tried it but it gave me a syntax error message. Is there a general if/then template? -----Original Message----- Dim intYear As Integer intYear = Left(CodeString, 1) Select Case intYear Case 6 To 9 FieldOrVariable = 1990 + intYear Case 0 To 5 FieldOrVariable = 2000 + intYear End Select you'll have to tweak it to work in your specific circumstances, but that should get you started. hth wrote in message ... Hello, This is a continuation of an earlier thread about Parsing strings from one field into many. I making a database,that contains five fields of overlap and to make the data entry easier I am trying to automate some of it. I have a code field, and then four other fields whose information can be derived from the code ((codelooks like this: 01MP, and has four parts. The first number refers to the year 2000, the second number refers the season of the year (spring), the third to type of media (a magazine), and the fourth the genre (politics).)) Anyway, my problem lies in the fact that the code only has one digit for the year, and the time span is from 1996 to present. so how could one phrase an expression that essentially says: if the first digit of the code string is =6 but =9, add the value to 1990, and if the value is =0 but =5 add it to 2000 (a short term solution; the code thing will eventually be revised). Thanks for any help or info you can provide! . |
#8
|
|||
|
|||
How to phrase If/Then clauses in the control source field
hon, you don't give nearly enough specific information for someone to
provide you with a specific solution. even now, you said you got an error message, but didn't provide specific details. assuming that Sco's assumptions about your specifics are correct (and since he obviously read the previous thread and i didn't), he gave you very specific and correct instructions - and good advice. hopefully you'll find both instructions and advice helpful. good luck. wrote in message ... I tried it but it gave me a syntax error message. Is there a general if/then template? -----Original Message----- Dim intYear As Integer intYear = Left(CodeString, 1) Select Case intYear Case 6 To 9 FieldOrVariable = 1990 + intYear Case 0 To 5 FieldOrVariable = 2000 + intYear End Select you'll have to tweak it to work in your specific circumstances, but that should get you started. hth wrote in message ... Hello, This is a continuation of an earlier thread about Parsing strings from one field into many. I making a database,that contains five fields of overlap and to make the data entry easier I am trying to automate some of it. I have a code field, and then four other fields whose information can be derived from the code ((codelooks like this: 01MP, and has four parts. The first number refers to the year 2000, the second number refers the season of the year (spring), the third to type of media (a magazine), and the fourth the genre (politics).)) Anyway, my problem lies in the fact that the code only has one digit for the year, and the time span is from 1996 to present. so how could one phrase an expression that essentially says: if the first digit of the code string is =6 but =9, add the value to 1990, and if the value is =0 but =5 add it to 2000 (a short term solution; the code thing will eventually be revised). Thanks for any help or info you can provide! . |
#9
|
|||
|
|||
How to phrase If/Then clauses in the control source field
"Hon"??? :-)
Sco M.L. "Sco" Scofield, Microsoft Access MVP, MCSD, MCP, MSS, A+ Useful Metric Conversion #17 of 19: 1 billion billion picolos = 1 gigolo Miscellaneous Access and VB "stuff" at www.ScoBiz.com "tina" wrote in message ... hon, you don't give nearly enough specific information for someone to provide you with a specific solution. even now, you said you got an error message, but didn't provide specific details. assuming that Sco's assumptions about your specifics are correct (and since he obviously read the previous thread and i didn't), he gave you very specific and correct instructions - and good advice. hopefully you'll find both instructions and advice helpful. good luck. wrote in message ... I tried it but it gave me a syntax error message. Is there a general if/then template? -----Original Message----- Dim intYear As Integer intYear = Left(CodeString, 1) Select Case intYear Case 6 To 9 FieldOrVariable = 1990 + intYear Case 0 To 5 FieldOrVariable = 2000 + intYear End Select you'll have to tweak it to work in your specific circumstances, but that should get you started. hth wrote in message ... Hello, This is a continuation of an earlier thread about Parsing strings from one field into many. I making a database,that contains five fields of overlap and to make the data entry easier I am trying to automate some of it. I have a code field, and then four other fields whose information can be derived from the code ((codelooks like this: 01MP, and has four parts. The first number refers to the year 2000, the second number refers the season of the year (spring), the third to type of media (a magazine), and the fourth the genre (politics).)) Anyway, my problem lies in the fact that the code only has one digit for the year, and the time span is from 1996 to present. so how could one phrase an expression that essentially says: if the first digit of the code string is =6 but =9, add the value to 1990, and if the value is =0 but =5 add it to 2000 (a short term solution; the code thing will eventually be revised). Thanks for any help or info you can provide! . |
#10
|
|||
|
|||
How to phrase If/Then clauses in the control source field
Hello Sco and Tina,
Thank you both for all your help! I'm really sorry I didn't understand what you were saying the first time around and ended up bothering the both of you so much. I just began using Access last week, and I had't learnt enough to utilize your advice. I apologize for all the trouble and I will get "Beginning Access Book" you recommended. Once again, I really appreciate all the help you've given me and I'm sorry to have bothered you so much! |
|
Thread Tools | |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Thread Starter | Forum | Replies | Last Post |
Control source | RJF | General Discussion | 4 | August 4th, 2004 04:32 PM |
NUMBERING the pages | Bob | New Users | 7 | June 14th, 2004 12:20 AM |
Supress blank lines in DOCPROPERTY field | Mary | Formatting Long Documents | 10 | May 25th, 2004 07:27 PM |
Need help with Control Source usage please... | New Users | 1 | May 20th, 2004 09:59 PM | |
Missing field in data source | AGI | Mailmerge | 1 | May 19th, 2004 10:05 AM |