I need to insert a conditional " and "

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

I have a form with five text boxes that are unbound. The first four are
called DescNE, DescNW, DescSW and DescSE.

I am useing the following code to populate the the final field Description.

Where more than one of the short descriptions is filled in I want to insert
the word and between the short descriptions. The problem is that the and's
show up even if there is no adjecent short descriptions. How can I make the
" and " conditional?

Thanks
If Len(Me.DescNE = 0) And Len(Me.DescNW > 0) And Len(Me.DescSW = 0) And
Len(Me.DescSE = 0) Then Me.Description = stDescNW

If Len(Me.DescNE > 0) And Len(Me.DescNW = 0) And Len(Me.DescSW = 0) And
Len(Me.DescSE = 0) Then Me.Description = stDescNE

If Len(Me.DescNE = 0) And Len(Me.DescNW = 0) And Len(Me.DescSW > 0) And
Len(Me.DescSE = 0) Then Me.Description = stDescSW

If Len(Me.DescNE = 0) And Len(Me.DescNW = 0) And Len(Me.DescSW = 0) And
Len(Me.DescSE > 0) Then Me.Description = stDescNE

If Len(Me.DescNE > 0) And Len(Me.DescNW > 0) And Len(Me.DescSW = 0) And
Len(Me.DescSE = 0) Then Me.Description = stDescNE + " " + stDescNW

If Len(Me.DescNE > 0) And Len(Me.DescNW = 0) And Len(Me.DescSW = 0) And
Len(Me.DescSE > 0) Then Me.Description = stDescNE + " " + stDescSE

If Len(Me.DescNE = 0) And Len(Me.DescNW > 0) And Len(Me.DescSW > 0) And
Len(Me.DescSE = 0) Then Me.Description = stDescNW + " " + stDescSW

If Len(Me.DescNE = 0) And Len(Me.DescNW = 0) And Len(Me.DescSW > 0) And
Len(Me.DescSE > 0) Then Me.Description = stDescSE + " " + stDescSW

If Len(Me.DescNE > 0) And Len(Me.DescNW > 0) And Len(Me.DescSW > 0) And
Len(Me.DescSE > 0) Then Me.Description = stDescNE + " and " + stDescNW + "
and " + stDescSW + " and " + stDescSE
 
I don't see where stDesc___ variables are being filled. I assume
they're being filled prior to this code. If so...
Instead of adding the ands at the end, add them each time you fill a
variable. For example, stDescNe = Me.DescNE & " and " At the end of the
whole thing check for a lingering and with:
If Right$(Me.Description, Len(Me.Descrpition) - 5) = " and " Then
Me.Description = Left$(Me.Description, Len(Me.Description) - 5)
Endif

HTH,
Barry
 
This will do it.

Me.Descripton = Me.DescNE & IIf(Len(Me.DescNE) > 0, " And " & Me.DescNw, _
Me.DescNW) & IIf(Len(Me.DescNW) > 0, " And " & Me.DescSW, _
Me.DescSW) & IIf(Len(Me.DescSW) > 0, " And " & Me.DescSE, _
Me.DescSE)

If Len(Me.DescNE = 0) And Len(Me.DescNW > 0) And Len(Me.DescSW = 0) And
Len(Me.DescSE = 0) Then Me.Description = stDescNW
 
Bill Cunningham said:
I have a form with five text boxes that are unbound. The first four are
called DescNE, DescNW, DescSW and DescSE.

I am useing the following code to populate the the final field
Description.

Where more than one of the short descriptions is filled in I want to
insert
the word and between the short descriptions. The problem is that the
and's
show up even if there is no adjecent short descriptions. How can I make
the
" and " conditional?
Click to expand...

Try testing for nulls instead of zero-length strings.

Keith.
www.keithwilby.com
 
His test is ok. Text box controls can sometimes evaluate to a zero length
string. Checking the length of a Null will return 0. So the test will work
for Null or ""
 
I tried your original code and was unable to make it work. I'm pretty sure
it is typed correctly but will check again. I'm not getting any error
messages at all, the Me.Description text box is just not updating. Maybe I
have the code in the wrong place.

Any ideas?
 
Where do you have the code.?

It needs to be the onchange of all of the combo boxes

or

in a subroutine that is called in the onchange or after update of ALL
of the combo boxes.
 
Not in the Change event, in the After Update event. For a Check box it would
not matter, but you have to be careful when you use the Change event. It
literally means what is says. It fires for every change. If you have a text
box and type in the letter A, it fires, then you type in B, it fires again.

Since it should fire after each check box and in the current event, I would
recommend a function in the General section of the form module that would be
called in the After Update event of each check box and in the form Current
event.
 
Klatuu,

You are probably correct that is the saffer place to set it. Most of
the boxes I set up have allow not in list set to NO. and few of my
users use the keyboard, they tend to always pull down the dropdown so
it doesn't tend to make a lot of difference.

But after update is the better location.
 
Back
Top