IsNull

I query 3 fields in table (T_mDefaults) and load those values into a form when adding a new record.  Presently, if any of the 3 fields IsNull - I specify a default value that the form receives otherwise I use the value in the field.

Using code sample below, if any of the 3 fields in T_mDefaults IsNull, how can I have no value load into the form for that field?

 ' Store td, Bnr, and Quelle from T_mDefaults
    Dim varmtd As Date
    Dim varBnr As String
    Dim varQuelle As String
   
    strSQL = "SELECT * FROM T_mDefaults"
    Set dbs = CurrentDb
    Set rst = dbs.OpenRecordset(strSQL)
   
    If IsNull(rst.Fields("mtd")) Then  'If no default value for mtd
        varmtd = #1/1/1500#
    Else
        varmtd = rst.Fields("mtd")
    End If
    If IsNull(rst.Fields("mBnr")) Then  'If no default value for mBnr
        varBnr = 2
    Else
        varBnr = rst.Fields("mBnr")
    End If
    If IsNull(rst.Fields("mQuelle")) Then   'If no default value for mQuelle
        varQuelle = "Unbekannt"
    Else
        varQuelle = rst.Fields("mQuelle")
    End If
    rst.Close
       
    If Me.NewRecord Then
        Me!td = varmtd
        Me!Bnr = varBnr
        Me!Quelle = varQuelle
    End If
clock1Asked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Shiju SasidharanAssoc Project ManagerCommented:
' Store td, Bnr, and Quelle from T_mDefaults
    Dim varmtd As Date
    Dim varBnr As String
    Dim varQuelle As String
Dim blnFlag  As Boolean


   
    strSQL = "SELECT * FROM T_mDefaults"
    Set dbs = CurrentDb
    Set rst = dbs.OpenRecordset(strSQL)
    blnFlag=True
    If IsNull(rst.Fields("mtd")) Then  'If no default value for mtd
        varmtd = #1/1/1500#
       blnFlag=False
    Else
        varmtd = rst.Fields("mtd")
    End If
    If IsNull(rst.Fields("mBnr")) Then  'If no default value for mBnr
        varBnr = 2
       blnFlag=False
    Else
        varBnr = rst.Fields("mBnr")
    End If
    If IsNull(rst.Fields("mQuelle")) Then   'If no default value for mQuelle
        varQuelle = "Unbekannt"
       blnFlag=False
    Else
        varQuelle = rst.Fields("mQuelle")
    End If
    rst.Close
       
    If Me.NewRecord And   blnFlag Then
        Me!td = varmtd
        Me!Bnr = varBnr
        Me!Quelle = varQuelle
    End If
0
clock1Author Commented:
Shiju,

if any of the 3 fields IsNull - the default values is still populating the form.  Do I need to say something like:

If Me.NewRecord And blnFlag = False Then...
0
Shiju SasidharanAssoc Project ManagerCommented:
Yes Please.

or
 If Me.NewRecord And Not blnFlag Then
0
The Ultimate Tool Kit for Technolgy Solution Provi

Broken down into practical pointers and step-by-step instructions, the IT Service Excellence Tool Kit delivers expert advice for technology solution providers. Get your free copy for valuable how-to assets including sample agreements, checklists, flowcharts, and more!

clock1Author Commented:
Shiju,

Still populating the form with default values.
0
Shiju SasidharanAssoc Project ManagerCommented:
If any of the fields has null value, what is the expected value you would like to see?
0
Shiju SasidharanAssoc Project ManagerCommented:
If Me.NewRecord And   blnFlag Then
        Me!td = varmtd
        Me!Bnr = varBnr
        Me!Quelle = varQuelle
   Else
        Me!td = ""
        Me!Bnr = ""
        Me!Quelle = ""
 End If
0
clock1Author Commented:
If any of the fields has null value, I do not wish to see any value in those fields.  Just Blank.

Also -

Can't use "" for Date Type fields.
0
Shiju SasidharanAssoc Project ManagerCommented:
Got it, What type of control/variable are  td, Bnr and Quelle ?
0
clock1Author Commented:
td is Date, Bnr is Number, Quelle is Text

My hunch is somehow the VBA check for blnFlag is not working as expected.
0
Shiju SasidharanAssoc Project ManagerCommented:
blnFlag will help you identify whether any of the fields have null values. You can use that boolean value to show your final results accordingly.

for td you can set value to Nothing or vbNull
for Bnr you can  0 or -1 depending on your choice of meaning full value
and for Quelle =""
0
clock1Author Commented:
Sorry, this just doesn't help me at this point.
As mentioned earlier, when I attempt using Not blnFlag, values still getting written.

I will have to try solving this on my own from here.

Thanks for the attempt.
0
Shiju SasidharanAssoc Project ManagerCommented:
Just out of curiosity, where are you trying to display the values?
to any controls?
0
clock1Author Commented:
Displaying the values on a form that writes to a table.  Sorry, but this dialogue is interrupting my work.  Thanks for your attempts to help.
0
Gustav BrockCIOCommented:
.. load those values into a form when adding a new record.  
.. if any of the 3 fields IsNull - I specify a default value that the form receives otherwise I use the value in the field.

When adding a new record, all fields will be Null (except if you have specified a DefaultValue for a field either in the table or in the form).

So what you should do is to set the DefaultValue property for the three fields in the form in the OnLoad event:

         Me!td.DefaultValue = #1/1/1500#
         Me!Bnr.DefaultValue = 2
         Me!Quelle.DefaultValue = "Unbekannt"

/gustav
0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
clock1Author Commented:
Thanks
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft Access

From novice to tech pro — start learning today.