A Microsoft Office (Excel, Word) forum. OfficeFrustration

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.

Go Back   Home » OfficeFrustration forum » Microsoft Access » Database Design
Site Map Home Register Authors List Search Today's Posts Mark Forums Read  

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



 
 
Thread Tools Display Modes
  #1  
Old August 11th, 2004, 04:15 AM
external usenet poster
 
Posts: n/a
Default 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!
Ads
  #2  
Old August 11th, 2004, 04:31 AM
tina
external usenet poster
 
Posts: n/a
Default 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  
Old August 11th, 2004, 07:17 AM
external usenet poster
 
Posts: n/a
Default 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  
Old August 11th, 2004, 07:28 AM
Ernie
external usenet poster
 
Posts: n/a
Default 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  
Old August 11th, 2004, 08:18 AM
M.L. Sco Scofield
external usenet poster
 
Posts: n/a
Default 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  
Old August 11th, 2004, 04:50 PM
tina
external usenet poster
 
Posts: n/a
Default 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!



.



  #7  
Old August 11th, 2004, 04:58 PM
tina
external usenet poster
 
Posts: n/a
Default 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!


.





  #8  
Old August 12th, 2004, 06:37 AM
M.L. Sco Scofield
external usenet poster
 
Posts: n/a
Default 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!


.







  #9  
Old August 12th, 2004, 06:37 AM
M.L. Sco Scofield
external usenet poster
 
Posts: n/a
Default 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  
Old August 13th, 2004, 06:03 AM
anonymous
external usenet poster
 
Posts: n/a
Default 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

Posting Rules
You may not post new threads
You may not post replies
You may not post attachments
You may not edit your posts

vB code is On
Smilies are On
[IMG] code is Off
HTML code is Off
Forum Jump

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


All times are GMT +1. The time now is 10:24 PM.


Powered by vBulletin® Version 3.6.4
Copyright ©2000 - 2019, Jelsoft Enterprises Ltd.
Copyright 2004-2019 OfficeFrustration.
The comments are property of their posters.