DMin and IsNull

[tone21]

I would appreciate it if someone could look at my code. I just need a "WHERE IsNull" statement and cannot figure out how to do it.

On the Before Update event of my form I am grabbing the next available "AssetNumber" from my table "Assets", but want the next Min "AssetNumber" where the "DateAssigned" IsNull.

Private Sub Form_BeforeUpdate(Cancel As Integer)
If Me.NewRecord Then
Me.AssetNumber = Nz(DMin("AssetNumber", "Assets", 0))


End If
End Sub

Thanks!

 

[namliam]

use a select and recordset ...

Regards

 

[Mile-O]

Me.AssetNumber = Nz(DMin("AssetNumber", "Assets", 0))

Have a look at your brackets, too. I suspect you are really wanting to write:

Me.AssetNumber = Nz(DMin("AssetNumber", "Assets"), 0)

 

[tone21]

Like this? (Still a rookie)

I have this in the Before Update event of my form:

Dim db as DAO.Database
Dim rs as DAO.Recordset

Set rs = db.OpenRecordset("SELECT Min(AssetNumber)AS A FROM Assets WHERE IsNull[DateAssigned]")

How do I tell it to put that value into the "AssetNumber" field of my "NewRecords" table (which is what my form is bound to and holds the new record info)?


Thank you!

 

[tone21]

Thanks to all of you-it worked! I've been struggling with this for weeks! I appreciate your fast responses and clear instructions.

Toni