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 SasidharanSenior IT DeveloperCommented:
' 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
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...
Shiju SasidharanSenior IT DeveloperCommented:
Yes Please.

or
 If Me.NewRecord And Not blnFlag Then
Active Protection takes the fight to cryptojacking

While there were several headline-grabbing ransomware attacks during in 2017, another big threat started appearing at the same time that didn’t get the same coverage – illicit cryptomining.

clock1Author Commented:
Shiju,

Still populating the form with default values.
Shiju SasidharanSenior IT DeveloperCommented:
If any of the fields has null value, what is the expected value you would like to see?
Shiju SasidharanSenior IT DeveloperCommented:
If Me.NewRecord And   blnFlag Then
        Me!td = varmtd
        Me!Bnr = varBnr
        Me!Quelle = varQuelle
   Else
        Me!td = ""
        Me!Bnr = ""
        Me!Quelle = ""
 End If
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.
Shiju SasidharanSenior IT DeveloperCommented:
Got it, What type of control/variable are  td, Bnr and Quelle ?
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.
Shiju SasidharanSenior IT DeveloperCommented:
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 =""
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.
Shiju SasidharanSenior IT DeveloperCommented:
Just out of curiosity, where are you trying to display the values?
to any controls?
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.
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

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
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.