Can't figure out the syntax error

[bubai]

In my Access table I have a ON/OFF field name "rowlock". I have a form to fill up the table. The form has filter property RowLock = FALSE. Hence it will show only those records for which "rowlock" filed on the table is false. I have set criteria to turn the field to True only if all of the fields are filled up in the form. I used the following code on the Before update event of the form to satisfy the criteria, but the program crashed and says "Runtime Error 94. Invalid use of Null" and highlights the code.

Me.rowlock = (Nz(Me.txtOP_SmallQty, 0) > 0 And Nz(Me.cboCNFID, 0) > 0 And Nz(Me.txtOP_CNF_Bill, (-1)) > (-1) And Nz(Me.txtOP_Invoice_, "") <> "" And Nz(Me.txtOP_InvoiceDt, "") <> "" And Nz(Year(Me.txtBL_Dt), 1901) <> 1901 And Nz(Me.txtB_E_No, "") <> "" And Nz(Me.txtOP_B_E_Dt, "") <> "" And Nz(Me.txtOP_Bond_, "") <> "" And Nz(Me.txtOP_Bond_Dt, "") <> "" And Nz(Me.txtIn_BondDt, "") <> "" And (Me.chkGoodsArrived <> False) And (Me.chkDocArrived <> False))

The code used to work when the criteria was a bit fewer.
However, I have find out which part of the code is doing the problem. It is

And Nz(Year(Me.txtBL_Dt), 1901) <> 1901

It is a date field. I could have wrote it as

And Nz(Me.txtBL_Dt), "") <> ""

But I wanted for a provision for unknown values so that for the value I don't have I could put 01/01/1900 and the criteria won't interact with this.


Could you tell me what is wrong with this part of the code?

[Micron]

I'm guessing that one or both of the last fields are Null at some point. Not sure what sort of values you are dealing with or where you are doing this but I'd be looking for a simpler method. Perhaps Is Null (query) or IsNull (code) and loop through the controls, because if I interpret that correctly, it only takes one to be Null to make the whole thing False.

[Welshgasman]

I'd say the same, and if the control is boolean, why not just check for True ?
Most people I believe, would set the Tag property of required controls with a character "R" perhaps and then check all controls for that and then check value.?

That way, if you add another control, you just set it's tag property, rather than editing code, possible in multiple places?
Use the debugger to hover over each of those controls to see what they actually are?

[bubai]

I'm guessing that one or both of the last fields are Null at some point. Not sure what sort of values you are dealing with or where you are doing this but I'd be looking for a simpler method. Perhaps Is Null (query) or IsNull (code) and loop through the controls, because if I interpret that correctly, it only takes one to be Null to make the whole thing False.

Hi there!
Yes you are right about the last two fields. Both of them are On/Off fields (Check Boxes). But if they are selected, they returns True (as Me.chkDocArrived <> False will become True <> False ie True).
Can you give an example of your suggestion?
However, I have find out which part of the code is doing the problem. It is

And Nz(Year(Me.txtBL_Dt), 1901) <> 1901

It is a date field. I could have wrote it as

And Nz(Me.txtBL_Dt), "") <> ""

But I wanted for a provision for unknown values so that for the value I don't have I could put 01/01/1900 and the criteria won't interact with that.
So can you tell me what is wrong with this part of the code?

[CJ_London]

in a table, a boolean field can only be true or false. On a form however they can also be null for unbound fields and new records. For a new record, they will save as false when saved.

To overcome this, set the default value to false

[Welshgasman]

Hi there!
Yes you are right about the last two fields. Both of them are On/Off fields (Check Boxes). But if they are selected, they returns True (as Me.chkDocArrived <> False will become True <> False ie True).
Can you give an example of your suggestion?
However, I have find out which part of the code is doing the problem. It is

And Nz(Year(Me.txtBL_Dt), 1901) <> 1901

It is a date field. I could have wrote it as

And Nz(Me.txtBL_Dt), "") <> ""

But I wanted for a provision for unknown values so that for the value I don't have I could put 01/01/1900 and the criteria won't interact with that.
So can you tell me what is wrong with this part of the code?

Date fields are actually numbers?, so use 0

[Micron]

1901 would have to be #1901# if the field is a date field. The "simpler method" I had in mind is what WGM wrote: set tag property for those fields (I use Reqd) and loop over the form controls. Loosely:

If the tag property is "Reqd" Then
..If IsNull the control then
....do something

I'm sure I've posted code for this purpose in here somewhere if you decide you need it.