If...Then...Else

[Shark Jr.]

I am having an issue with an "If...Then...Else" statement, can anyone help?

*-*-*-*-*-*
Dim db As Database
Dim LPassThrough As QueryDef
Dim Lrs As DAO.Recordset
Dim LSQL As String
Dim strSDate As String
Dim strEDate As String
Dim strMarket As String

strSDate = MSSQLDateFormat(Forms!frmMain.cboSDate)
strEDate = MSSQLDateFormat(Forms!frmMain.cboEDate)
strMarket = "%" ' This is where I'm having the issue
' I want If Forms!frmMain.cboAlpha_Market Is Null Then "%"
' Else Forms!frmMain.cboAlpha_Market

*-*-*-*-*-*

 

[Stuart McCall]

I am having an issue with an "If...Then...Else" statement, can anyone help?

*-*-*-*-*-*
Dim db As Database
Dim LPassThrough As QueryDef
Dim Lrs As DAO.Recordset
Dim LSQL As String
Dim strSDate As String
Dim strEDate As String
Dim strMarket As String

strSDate = MSSQLDateFormat(Forms!frmMain.cboSDate)
strEDate = MSSQLDateFormat(Forms!frmMain.cboEDate)
strMarket = "%" ' This is where I'm having the issue
' I want If Forms!frmMain.cboAlpha_Market Is Null Then "%"
' Else Forms!frmMain.cboAlpha_Market

*-*-*-*-*-*

"Is Null" works only in queries, not VBA. In code, use the IsNull function:

If IsNull(Forms!frmMain.cboAlpha_Market) Then

 

[Tom van Stiphout]

On Thu, 6 Dec 2007 20:34:31 -0000, "Stuart McCall"

Almost true. Here is a possible use of Is Null in VBA:
If Eval("Forms![Order List]![Shipped Date] Is Null") Then Beep
Contrived, I admit.

-Tom.

 

[Tony Toews [MVP]]

On Thu, 6 Dec 2007 20:34:31 -0000, "Stuart McCall"

Almost true. Here is a possible use of Is Null in VBA:
If Eval("Forms![Order List]![Shipped Date] Is Null") Then Beep
Contrived, I admit.

Glad you added "contrived". <smile>

Tony
--
Tony Toews, Microsoft Access MVP
Please respond only in the newsgroups so that others can
read the entire thread of messages.
Microsoft Access Links, Hints, Tips & Accounting Systems at
http://www.granite.ab.ca/accsmstr.htm
Tony's Microsoft Access Blog - http://msmvps.com/blogs/access/

 

[Stuart McCall]

On Thu, 6 Dec 2007 20:34:31 -0000, "Stuart McCall"

Almost true. Here is a possible use of Is Null in VBA:
If Eval("Forms![Order List]![Shipped Date] Is Null") Then Beep
Contrived, I admit.

-Tom.

"Is Null" works only in queries, not VBA. In code, use the IsNull
function:

If IsNull(Forms!frmMain.cboAlpha_Market) Then

Ah, another pedantic correction. Touché!

 

[BJChurch1775]

In relation to If...Then...Else frustrations, can anyone help me work out the
kinks below?

Private Sub MOS_Comp_Date()
If [MOS Grad Dt] = IsNull Then [MOS Comp Dt] = [MOS Grad Dt]
Else
If [MOS Grad Dt 2] = IsNull Then [MOS Comp Dt] = [MOS Grad Dt]
Else
If [MOS Grad Dt 3] = IsNull Then [MOS Comp Dt] = [MOS Grad Dt 2]
Else
If [MOS Grad Dt 4] = IsNull Then [MOS Comp Dt] = [MOS Grad
Dt 3]
Else
[MOS Comp Dt] = [MOS Grad Dt 4]
End If
End If
End If
End If
End Sub


[MOS Comp Dt] is an unbound control (text box) on a report that is suposed
to return the result of the ITE statement above. I have tried many
variations and the control still shows blank on the report.

basically, there are one to four schools someone must attend. Some only do
one other must do all four. I need to know when any given person will
complete school based on the last date entered. For some that date is in the
[MOS Grad Dt] field, for others it is in the [MOS Grad Dt 4] field. I hope
this makes sense - thanks!

 

[Rick Brandt]

In relation to If...Then...Else frustrations, can anyone help me work
out the kinks below?

Private Sub MOS_Comp_Date()
If [MOS Grad Dt] = IsNull Then [MOS Comp Dt] = [MOS Grad Dt]
Else
If [MOS Grad Dt 2] = IsNull Then [MOS Comp Dt] = [MOS Grad Dt]
Else
If [MOS Grad Dt 3] = IsNull Then [MOS Comp Dt] = [MOS Grad
Dt 2] Else
If [MOS Grad Dt 4] = IsNull Then [MOS Comp Dt] = [MOS
Grad
Dt 3]
Else
[MOS Comp Dt] = [MOS Grad Dt 4]
End If
End If
End If
End If
End Sub

If IsNull([MOS Grad Dt]) Then
etc..

 

[BJChurch1775]

If IsNull([MOS Grad Dt]) Then
etc..

So I try below (I "think" I did it right):

Private Sub Report_Open(Cancel As Integer)
If IsNull([MOS Grad Dt]) Then [MOS Comp Dt] = [MOS Grad Dt]
Else
If IsNull([MOS Grad Dt 2]) Then [MOS Comp Dt] = [MOS Grad Dt]
Else
If IsNull([MOS Grad Dt 3]) Then [MOS Comp Dt] = [MOS Grad Dt 2]
Else
If IsNull([MOS Grad Dt 4]) Then [MOS Comp Dt] = [MOS Grad Dt
3]
Else
[MOS Comp Dt] = [MOS Grad Dt 4]
End If
End If
End If
End If
End Sub

But now I get the error "Else without If"

?!?!?!?! Did I put something in the wrong spot??

 

[John W. Vinson]

In relation to If...Then...Else frustrations, can anyone help me work out the
kinks below?

IsNull isn't a value that you can compare - it's a Function which returns True
if its argument is NULL, False otherwise.


Private Sub MOS_Comp_Date()
If IsNull([MOS Grad Dt]) Then
[MOS Comp Dt] = [MOS Grad Dt]
Elseif IsNull([MOS Grad Dt 2]) Then
[MOS Comp Dt] = [MOS Grad Dt]
Elseif IsNul(l[MOS Grad Dt 3]) Then
[MOS Comp Dt] = [MOS Grad Dt 2]
Elseif IsNull([MOS Grad Dt 4]) Then
[MOS Comp Dt] = [MOS Grad Dt 3]
Else
[MOS Comp Dt] = [MOS Grad Dt 4]
End If
End Sub

I must say, though, that if your table has these four fields, it is *NOT*
properly normalized and should almost certainly be revised; and that storing
the same date in two different fields is redundant and probably also not a
good idea!


John W. Vinson [MVP]

 

[BJChurch1775]

On Tue, 18 Dec 2007 17:53:00 -0800, BJChurch1775
IsNull isn't a value that you can compare - it's a Function which returns True
if its argument is NULL, False otherwise.


Private Sub MOS_Comp_Date()
If IsNull([MOS Grad Dt]) Then
[MOS Comp Dt] = [MOS Grad Dt]
Elseif IsNull([MOS Grad Dt 2]) Then
[MOS Comp Dt] = [MOS Grad Dt]
Elseif IsNul(l[MOS Grad Dt 3]) Then
[MOS Comp Dt] = [MOS Grad Dt 2]
Elseif IsNull([MOS Grad Dt 4]) Then
[MOS Comp Dt] = [MOS Grad Dt 3]
Else
[MOS Comp Dt] = [MOS Grad Dt 4]
End If
End Sub

No error messages, but the result is all blanks?

I must say, though, that if your table has these four fields, it is *NOT*
properly normalized and should almost certainly be revised; and that storing
the same date in two different fields is redundant and probably also not a
good idea!

I actually worked through this problem several times. Sometimes there is
only one school to go to and I need the beginning and end dates for that
school. Other times there are four different schools and I need the
beginning and end dates of all four. For this particular report I need the
ultimate end date, whether only one school is involved or all four are
involved. For historical data pruposes, I must maintain the information for
all four schools so I cannot simply eliminate the first through third. So it
appears that I have redundant data but each field represents a different
school and I cannot overwrite the old with the new.

All help is GREATLY appreciated.

BTW - I am using Access 2000 if that makes a difference - NOT my choice -
government is too cheap to front for anything updated.

 

[John W. Vinson]

Private Sub MOS_Comp_Date()
If IsNull([MOS Grad Dt]) Then
[MOS Comp Dt] = [MOS Grad Dt]
Elseif IsNull([MOS Grad Dt 2]) Then
[MOS Comp Dt] = [MOS Grad Dt]
Elseif IsNul(l[MOS Grad Dt 3]) Then
[MOS Comp Dt] = [MOS Grad Dt 2]
Elseif IsNull([MOS Grad Dt 4]) Then
[MOS Comp Dt] = [MOS Grad Dt 3]
Else
[MOS Comp Dt] = [MOS Grad Dt 4]
End If
End Sub

No error messages, but the result is all blanks?

What are the values in MOS Grad Dt, Dt 2, Dt 3 and Dt 4? What result do you
expect? What event calls this Sub?

I actually worked through this problem several times. Sometimes there is
only one school to go to and I need the beginning and end dates for that
school. Other times there are four different schools and I need the
beginning and end dates of all four.

Then you have a One (student?? whatever this table models) to Many (schools)
relationship. "Fields are expensive, records are cheap" - someday you might
have *FIVE* schools and completely break your data model!!

These Grad Dt values should be in separete *records* of another table related
one to many to this table. Doing so will let you use a very simple Max
expression in a Totals query to find the last date.

If you really want the last non-Null value in the four fields, AND can
reliably and certainly count on there never being a NULL out of order, try

Private Sub MOS_Comp_Date()
[MOS Comp Dt] = NZ([MOS Grad Dt 4], NZ([MOS Grad Dt 3], NZ([MOS Grad Dt 2], _
NZ([MOS Grad Dt]))))
End Sub

This also assumes that Grad Dt 4 is always later than Grad Dt 3, and so on,
which might not be the case.

For this particular report I need the
ultimate end date, whether only one school is involved or all four are
involved. For historical data pruposes, I must maintain the information for
all four schools so I cannot simply eliminate the first through third. So it
appears that I have redundant data but each field represents a different
school and I cannot overwrite the old with the new.

All help is GREATLY appreciated.

BTW - I am using Access 2000 if that makes a difference - NOT my choice -
government is too cheap to front for anything updated.

Nothing wrong with 2000 so long as you have applied all the service packs. It
sort of oinked when first released but it's pretty reliable now. Nothing in
the later versions will affect this particular problem.

John W. Vinson [MVP]

 

[John Spencer]

How about using a generic VBA function?

You can copy and paste the function below into a VBA module and save the
module (do not name the module fGetRowMax)

You could call it in a query or in the report and it will return the maximum
(latest) date of the four dates.

gGetRowMax( [MOS Grad Dt], [MOS Grad Dt2] , [MOS Grad Dt3] , [MOS Grad
Dt4] )

By the way, as noted elsewhere in this thread, the design of your table
structure is faulty.

'============== COPY and Paste this function ==============
Public Function fGetRowMax(ParamArray Values()) As Variant
'John Spencer UMBC CHPDM
'Last Update: April 5, 2000
'Returns the Maximum of a group of values passed to it.
'Sample call:
' fGetRowMax("-21","TEST","2", "3","4","5","6","7",0) returns "TEST"
' fGetRowMax(-21,2,3,4,5,6,7,0) returns 7
'Handles text, date, & number fields.

Dim i As Long, vMax As Variant
vMax = Null

For i = LBound(Values) To UBound(Values)
If IsNull(Values(i)) = False Then
If Values(i) <= vMax Then
Else
vMax = Values(i)
End If
End If
Next

fGetRowMax = vMax

End Function

'============== End of Code ==============

--
John Spencer
Access MVP 2002-2005, 2007
Center for Health Program Development and Management
University of Maryland Baltimore County
..

 

[Tony Toews [MVP]]

Then you have a One (student?? whatever this table models) to Many (schools)
relationship. "Fields are expensive, records are cheap" - someday you might
have *FIVE* schools and completely break your data model!!

One conversion from one system to another brought a huge sigh of
relief from the clerks in a county office. The old system allowed for
only three or four owners of a property. (I forget exactly how many
now.) The fields were named Name1, Name2, ..., Address1, Address2,...
City1, City2.

Turns out that some kind of German nobility had bought some land in
the area as an investment. They had to send various tax notices and
such to his eight children back in Germany after he died. And this
was a royal pain in the ***. (Sorry couldn't help myself there.)

So the folks in the office were very happy when we brought in our
normalized solution.

Tony
--
Tony Toews, Microsoft Access MVP
Please respond only in the newsgroups so that others can
read the entire thread of messages.
Microsoft Access Links, Hints, Tips & Accounting Systems at
http://www.granite.ab.ca/accsmstr.htm
Tony's Microsoft Access Blog - http://msmvps.com/blogs/access/