asked on

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

SOLUTION

Shiju S

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

clock1

ASKER

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 S

Yes Please.

or
If Me.NewRecord And Not blnFlag Then

clock1

ASKER

Shiju,

Still populating the form with default values.

Shiju S

If any of the fields has null value, what is the expected value you would like to see?

Shiju S

If Me.NewRecord And blnFlag Then
Me!td = varmtd
Me!Bnr = varBnr
Me!Quelle = varQuelle
Else
Me!td = ""
Me!Bnr = ""
Me!Quelle = ""
End If

clock1

ASKER

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 S

Got it, What type of control/variable are td, Bnr and Quelle ?

clock1

ASKER

td is Date, Bnr is Number, Quelle is Text

My hunch is somehow the VBA check for blnFlag is not working as expected.

Shiju S

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 =""

clock1

ASKER

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 S

Just out of curiosity, where are you trying to display the values?
to any controls?

clock1

ASKER

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.

ASKER CERTIFIED SOLUTION

Gustav Brock

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

clock1

ASKER

Thanks