Help with If, Then, Else strings please

[JohnB]

Hi.

This should be easy but I can't get it to work.

I have a subform with a command button and the choice of form that it opens
depends on the contents of two fields on the subform. Here is the code I'm
using (it's just the relevant part):

If Me.cboPlacementStage = "PGCE Final" And Me.txtPlacementStartYear >=
"2007" Then
stDocName = "frmGrades3"
Else
stDocName = "frmGrades1"
End If

If Me.cboPlacementStage = "PGCE First" And Me.txtPlacementStartYear

= "2007" Then

stDocName = "frmGrades3"
Else
stDocName = "frmGrades1"
End If

I hope you get the idea. The thing is, frmGrades3 is opened correctly when
the lower If, Then, Else, End If block is satisfied but frmGrades1 is opened
in error even though the first block is satisfied. When I cut the lower block
out and paste it above the upper block, the situation is reversed. It seems
that the lower block always opens the correct form, while the upper block
always opens the wrong form, despite the conditions for both blocks being
satisfied. I've obviously put this together wrongly.

Can anyone tell me what's wrong with this approach and also, how do you do
this when you have a whole load of If Then conditions that you need to check
for?

Thanks.

 

[Evi]

I'm sending in Rich Text in the hope that the layout will make this clearer to you. Sorry everyone.

1. If the contents of Me.txtPlacementStartYear is a number, then lose the quote marks from around 2007.

But lets assume that it is text for now. Below are a couple of layouts.
The lines starting with an apostrophe are comments. They won't run as code. If you paste them into a module, they will turn green.

It's a bit unclear what you want to happen so here are a couple of layouts
If Me.txtPlacementStartYear >= "2007"
'the 2007 condition must be true for all the choices below
'if that bit isn't true then nothing will happen
If Me.cboPlacementStage = "PGCE Final" OR Me.cboPlacementStage = "PGCE First Then
stDocName = "frmGrades3
Else
stDocName = "frmGrades1"
End If
End If

If you want "frmGrades1" to open no matter what txtPlacementStartYear says then we need to change it around.

If Me.txtPlacementStartYear >= "2007"
'the 2007 condition must be true for all the choices below
If Me.cboPlacementStage = "PGCE Final" OR Me.cboPlacementStage = "PGCE First Then
'not only does >="2007 have to be true but the above also has to be true
stDocName = "frmGrades3
End If
Else
stDocName = "frmGrades1"
'frmGrades1 will open even if txtPlacementStartYear is less than 2007
End If

See if the structure below works better to make it clearer to you what is happening.
I'm going to use Select Case because it is possible that you may want to add more documents to this list. I've also included the Case Else phrase because you may want to add another document there in future.



If Me.txtPlacementStartYear >= "2007"
'that condition is always necessary

Select Case Me.cboPlacementStage
'the whole thing hinges on the value of cboPlacementStage

Case "PGCE Final"
'cboPlacementStage says "PGCE Final"
stDocName = "frmGrades3"

Case "PGCE First"
stDocName = "frmGrades3"

Case Else
'if cboPlacementStage says anything other than the above
stDocName = "frmGrades1"
'that's the end of your choices if txtPlacementStartYear>="2007"
End Select
Else
'do whatever you want to do if txtPlacementStartYear is less than 2007

End If

Evi

 

[JohnB]

Thank you Evi.

That's a very comprehensive reply. I will have to take some time to study
it. Interesting about the "2007". Yes it is intended to be a year number but
I've got it as a text field in the Table, because I never thought I'd be
doing any maths on that field. It seems to work fine, for a range of numbers
that I've tried, either with or without the quotes. Should I be concerned
about this?

Many thanks again for your reply.

 

[Evi]

If you've got it as a text field then your code is correct as far as the
quote marks are concerned. Whether you should be concerned about this,
depends on where you want to go with your database.

Text formats for numbers are usually found in places like phone numbers
where you want to put spaces and leading 0s in it and never want to do any
addition on it or House Numbers which may have to include 33a or Flat 22.
I would have thought that a year would be better off as a number format.
If you ever want to change it to one, this can be done with relative speed
using an update query and the Val function which turns any text number into
a real number.
Because of what it is, you are unlikely to run into the usual problem with
sorting text numbers, (if you sort 1, 2, 3, 21 the 21 will come right after
the 2 and before the 3). but as a general rule, I would advise making number
fields into numbers.

The advantage of having your year as a text field is that you will become an
expert on doing text search and filter strings which is always slightly more
tricky than with numbers

Evi

Thank you Evi.

That's a very comprehensive reply. I will have to take some time to study
it. Interesting about the "2007". Yes it is intended to be a year number but
I've got it as a text field in the Table, because I never thought I'd be
doing any maths on that field. It seems to work fine, for a range of numbers
that I've tried, either with or without the quotes. Should I be concerned
about this?

Many thanks again for your reply.

add more documents to this list. I've also included the Case Else phrase
because you may want to add another document there in future.

 

[JohnB]

Thank you.

I see you are busy today and have answered my other post too! I'll reply to
that there.

As far as this text field is concerned, all I'm worried about really is
whether any code I write can do what I want both now and in the future. The
code will need to determine which records have a year earlier than 2007 in
that field, which have the year 2007 and which have a year later than 2007. I
have tested my code using values of 2004, 2003, 2007 and 2008.

Do you think that it will be OK if it is a text field and I continue using
the quotesm or is the database going to get confused when the year value in
that field becomes 2010, 2011, 2012 etc? I suppose I could test that myself
but I've finished at work now, so it will need to wait until monday.

Thanks again for your help. Now for that other post.