CHECK FOR EMPTY DATE OF BIRTH ON A TABLE

[villica]

How do check if the date of birth on a table is empty. I have tried isnull but it does not work. Is there anyother way. thanks for your suggestions

 

[ghubbell]

Hi Villica,
I'm guessing you are doing this testing in a query? Your criteria under your date column must be:

Is Null

That's it! Gord
ghubbell@total.net

 

[villica]

Gord thank you for your reply, I am writing a function and with in the functions and I have and if statement

if isnull(dob) then
msgbox "no dob"
endif


any other suggestions

 

[ghubbell]

As a simple test I just did this in the "On Exit" event of a field named (poorly I will add) "Date":

If IsNull(Me![Date]) Then MsgBox "No Date"

Notice that I have square brackets and a bang "!" for this badly named field. If it had been named MyDate I could have done:

If IsNull(Me.MyDate) Then MsgBox "No Date"

Give it a test although yours should work....?! Gord
ghubbell@total.net

 

[villica]

thanks Gord, I tested your code. It does not work, I am passing the dob as parameter and I tried exactly what you suggested. If I put me in front of it, I get an syntax error and without it does not display the message. It does work when you have on a form, but I am testing the value that is on a table, I passed the value as parameter, is there any othe way. thank you Gord, I am been trying for days now different combinations and it does not seem to work. any other suggestinos. I really appreciate it .

 

[ghubbell]

Ok Villica, Can you post your code here so we can have a look-see? Gord
ghubbell@total.net

 

[villica]

Here is my function Gord, maybe you can tell why I can test the dob for null. I tried the is missing function but it does not work either. I am open to any other suggestions.
thank you Gord


Public Function AGECAT(DOB As Date, TREATYNO As String, TYPEHOURS As String) As String

DoCmd.SetWarnings True

Dim lnage As Double
Dim lcagecat As String

lnage = ((lcdate - [DOB]) / 365.25)


If IsMissing(DOB) Then
lcagecat = "no dob"

Else
If (lnage > 0 And lnage <= 3.6) Then
lcagecat = &quot;0 - Infant (0-3.5 yrs)&quot;
End If

If (lnage > 3.6 And lnage <= 5.6) Then
lcagecat = &quot;1 - Toddler (3.6-5.5 yrs)&quot;
End If

End If

AGECAT = lcagecat

End Function

 

[ghubbell]

Hi Villica,
See if this works with your data:

Public Function AGECAT(DOB As Date, TREATYNO As String, TYPEHOURS As String) As String
DoCmd.SetWarnings True
Dim lnage As Double
Dim lcagecat As String
lnage = ((lcdate - [DOB]) / 365.25)
If (DOB) = #12:00:00 AM# Then '<-Seems to be the value when date is empty.
lcagecat = &quot;no dob&quot;
Else
If (lnage > 0 And lnage <= 3.6) Then lcagecat = &quot;0 - Infant (0-3.5 yrs)&quot;
If (lnage > 3.6 And lnage <= 5.6) Then lcagecat = &quot;1 - Toddler (3.6-5.5 yrs)&quot;
End If
AGECAT = lcagecat
End Function

Let's know if it works!
Gord
ghubbell@total.net

 

[Cagliostro]

try following
if exists(select SomeThing from YourTable) then ... John Fill

 

[villica]

Hi John, is exists an access function. I was not able to find anything on the help.

 

[Cagliostro]

does it help? I do not know very well Access, but in SQL Server it works:
if (select count(*) from xxx)>0 then ... John Fill