How to phrase If/Then clauses in the control source field

G

Guest

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!
 
T

tina

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
 
G

Guest

I tried it but it gave me a syntax error message. Is
there a general if/then template?
 
E

Ernie

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
 
M

M.L. Sco Scofield

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
 
T

tina

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.
 
T

tina

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 said:
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


I tried it but it gave me a syntax error message. Is
there a general if/then template?
Click to expand...
Click to expand...
 
M

M.L. Sco Scofield

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 said:
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 said:
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
Click to expand...
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


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


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!


.
Click to expand...
Click to expand...
Click to expand...
 
M

M.L. Sco Scofield

"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
 
A

anonymous

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!
 
M

M.L. Sco Scofield

anonymous,

1 - How about a name? Even if it's just a "newsgroup" name. It's hard to
tell all of the "anonymous"s apart.

2 - You didn't really say. Did you finally get it working?

3 - You weren't a "bother." Just an annoyance. :)

To help us help you in the future:

1 - Try to use the right group. This was more of a code with a form problem.
The "microsoft.public.access.formscoding" group might have been a better
group than "tablesdesign."

2 - Be straight with your experience and what you need. It's OK to not know
things. It is not OK to hide it.

3 - Unless you've taken other database and programming classes, having just
started using Access a week ago, I suspect that you're not really ready to
be working with code.

4 - To be honest, this explains why you're breaking basic design rules with
your "combined" field in the first place.

5 - My recommendation on the book was based on a bad assumption. I
incorrectly assumed that because you were venturing into code, that you had
a *very* solid understanding of databases and Access. Based on only using
Access for a week, you should probably start with some more basic books.
Take a look at "Database Design for Mere Mortals" by Michael Hernandez and
"Microsoft Office Access 2003 Inside Out" by John Viescas. After you get a
solid Access foundation, then start looking into code.

Good luck anonymous.

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
 
T

tina

no problem. come on back to the newsgroups whenever you have a question, and
just remember to try to give specific details about the circumstances -
you'll have a better chance of getting a useful answer the first time
around! there are a lot of really knowledgeable folks here, especially the
MVPs, who will be a great resource for you. good luck as you begin your
"learning Access" journey! :)
 
J

James

Hello,
Thanks for the help once again. The code worked
well, and I was able to adapt it a little for the modules
of the other fields. Thanks for all the help and advice!
 
M

M.L. Sco Scofield

You're welcome James.

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
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Similar Threads

Strings: Parsing data into other fields in the same table 2
Excel Substitution Help (a little complex) 2
How to reference a field of a recordset with a variable 4
Subform in datasheet view with field displaying whether file exist 1
Button that finds record based on two fields 0
Database to Track Projects 2
Field Code: SEQ and Cross Referencing 1
parsing datetime field into month, day, year, time 2

Top