check for null in module??

[tlyczko]

I have this code to run in a form button OnClick event to make sure the
start and end dates for reports have been entered, in case the end user
goes right to the buttons without typing any dates, in which case the
before/after update events don't work.

Public Function CheckDateEntry(dtmFirstDay As Date, dtmLastDay As Date)
'If IsNull(dtmFirstDay) = True Or dtmFirstDay = "" Or Len(dtmFirstDay)
= 0 Or IsNull(dtmLastDay) = True Or dtmLastDay = "" Or Len(dtmLastDay)
= 0 Then
If Nz(dtmFirstDay, "") = "" Or Len(dtmFirstDay) = 0 Or Nz(dtmLastDay,
"") = "" Or Len(dtmLastDay) = 0 Then
CheckDateEntry = True
Call MsgBox("Please enter the start and end dates for this
report.", vbExclamation Or vbSystemModal, "Required Data Entry")
Else
CheckDateEntry = False
End If
End Function

In the button OnClick code, this is called as
If CheckDataEntry (Me.dtmStartDate1, Me.dtmEndDate1) = False then
do the report printing
End If

Neither of the above checks works -- I constantly get error 94, invalid
use of null.

Have I written something incorrectly??

I have 5 tabs with 6 buttons each, all referencing start/end date on
the tabs, I would like some quick/reusable way to validate the
start/end dates.

Thank you, Tom

[Allen Browne]

The only VBA data type that can be Null is the Variant.
Trying to assign a Null value to a variable of type Date will fail.
Therefore the function fails on the very first line.

Since it must accept nulls, change it to:
Public Function CheckDateEntry(varFirstDay As Variant, varLastDay As
Variant)

I did not test the rest of your code, but you will probaby want to call it
with:
If Not CheckDataEntry (Me.dtmStartDate1.Value, Me.dtmEndDate1.Value)
Then
If you don't explicitly pass the value, the entire text box gets passed into
the function, i.e. varFirstDay will be an object of type TextBox.

--
Allen Browne - Microsoft MVP. Perth, Western Australia.
Tips for Access users - http://allenbrowne.com/tips.html
Reply to group, rather than allenbrowne at mvps dot org.

"tlyczko" <tl*****@gmail.com> wrote in message
news:11**********************@c74g2000cwc.googlegr oups.com...

I have this code to run in a form button OnClick event to make sure the
start and end dates for reports have been entered, in case the end user
goes right to the buttons without typing any dates, in which case the
before/after update events don't work.

Public Function CheckDateEntry(dtmFirstDay As Date, dtmLastDay As Date)
'If IsNull(dtmFirstDay) = True Or dtmFirstDay = "" Or Len(dtmFirstDay)
= 0 Or IsNull(dtmLastDay) = True Or dtmLastDay = "" Or Len(dtmLastDay)
= 0 Then
If Nz(dtmFirstDay, "") = "" Or Len(dtmFirstDay) = 0 Or Nz(dtmLastDay,
"") = "" Or Len(dtmLastDay) = 0 Then
CheckDateEntry = True
Call MsgBox("Please enter the start and end dates for this
report.", vbExclamation Or vbSystemModal, "Required Data Entry")
Else
CheckDateEntry = False
End If
End Function

In the button OnClick code, this is called as
If CheckDataEntry (Me.dtmStartDate1, Me.dtmEndDate1) = False then
do the report printing
End If

Neither of the above checks works -- I constantly get error 94, invalid
use of null.

Have I written something incorrectly??

I have 5 tabs with 6 buttons each, all referencing start/end date on
the tabs, I would like some quick/reusable way to validate the
start/end dates.

Thank you, Tom

[tlyczko]

Ohhhhhhhhhhhhh...you are a genius. Thank you. :) :) :) :)

I did not know this about VBA programming etc. and this will be
something new that I have learned.

Thank you also for the extra tip on the Value property.

Thank you, :) tom

Allen Browne wrote:

The only VBA data type that can be Null is the Variant.
Trying to assign a Null value to a variable of type Date will fail.
Therefore the function fails on the very first line.

Since it must accept nulls, change it to:
Public Function CheckDateEntry(varFirstDay As Variant, varLastDay As
Variant)

I did not test the rest of your code, but you will probaby want to call it
with:
If Not CheckDataEntry (Me.dtmStartDate1.Value, Me.dtmEndDate1.Value)
Then
If you don't explicitly pass the value, the entire text box gets passed into
the function, i.e. varFirstDay will be an object of type TextBox.

--
Allen Browne - Microsoft MVP. Perth, Western Australia.
Tips for Access users - http://allenbrowne.com/tips.html
Reply to group, rather than allenbrowne at mvps dot org.

"tlyczko" <tl*****@gmail.com> wrote in message
news:11**********************@c74g2000cwc.googlegr oups.com...

I have this code to run in a form button OnClick event to make sure the
start and end dates for reports have been entered, in case the end user
goes right to the buttons without typing any dates, in which case the
before/after update events don't work.

Public Function CheckDateEntry(dtmFirstDay As Date, dtmLastDay As Date)
'If IsNull(dtmFirstDay) = True Or dtmFirstDay = "" Or Len(dtmFirstDay)
= 0 Or IsNull(dtmLastDay) = True Or dtmLastDay = "" Or Len(dtmLastDay)
= 0 Then
If Nz(dtmFirstDay, "") = "" Or Len(dtmFirstDay) = 0 Or Nz(dtmLastDay,
"") = "" Or Len(dtmLastDay) = 0 Then
CheckDateEntry = True
Call MsgBox("Please enter the start and end dates for this
report.", vbExclamation Or vbSystemModal, "Required Data Entry")
Else
CheckDateEntry = False
End If
End Function

In the button OnClick code, this is called as
If CheckDataEntry (Me.dtmStartDate1, Me.dtmEndDate1) = False then
do the report printing
End If

Neither of the above checks works -- I constantly get error 94, invalid
use of null.

Have I written something incorrectly??

I have 5 tabs with 6 buttons each, all referencing start/end date on
the tabs, I would like some quick/reusable way to validate the
start/end dates.

Thank you, Tom

[David W. Fenton]

"Allen Browne" <Al*********@SeeSig.Invalid> wrote in
news:44**********************@per-qv1-newsreader-01.iinet.net.au:

Since it must accept nulls, change it to:
Public Function CheckDateEntry(varFirstDay As Variant,
varLastDay As
Variant)

I did not test the rest of your code, but you will probaby want to
call it with:
If Not CheckDataEntry (Me.dtmStartDate1.Value,
Me.dtmEndDate1.Value)
Then
If you don't explicitly pass the value, the entire text box gets
passed into the function, i.e. varFirstDay will be an object of
type TextBox.

That's not strictly true. It's only a ByRef/ByVal issue. If you pass
the .Value, it is going to be an implicit ByVal, but if you do it
without the explicit .Value property, it will be ByRef. You can
avoid needing to specify .Value every time you pass values by simply
making the function definition into:

Public Function CheckDateEntry(ByVal varFirstDay As Variant, _
ByVal varLastDay As Variant)

I'm not certain it matters, though.

--
David W. Fenton http://www.dfenton.com/
usenet at dfenton dot com http://www.dfenton.com/DFA/

[Allen Browne]

Great.

This might help you avoid other issues also:
Common errors with Null
at:
http://allenbrowne.com/casu-12.html
--
Allen Browne - Microsoft MVP. Perth, Western Australia.
Tips for Access users - http://allenbrowne.com/tips.html
Reply to group, rather than allenbrowne at mvps dot org.

"tlyczko" <tl*****@gmail.com> wrote in message
news:11**********************@c74g2000cwc.googlegr oups.com...

Ohhhhhhhhhhhhh...you are a genius. Thank you. :) :) :) :)

I did not know this about VBA programming etc. and this will be
something new that I have learned.

Thank you also for the extra tip on the Value property.

Thank you, :) tom