Null value not recognised in conditional statement

[cath_hopes]

Hi there!

I've an If ... End If statement that's not working properly. Can someone advise how I should be coding this please?

If Me.postcode.value = Null
Msgbox "Postcode field must be populated."
Endif

I'm using Access 2007. The postcode is defined at table level as a text field.
I've also tried replacing the Null with " " but the message box line of code still isn't triggered.

Thanks in advance.

 

[KeithG]

What event do you have the code in?

 

[cath_hopes]

Private Sub Exit_Click()

 

[pbaldy]

Try

If Nz(Me.postcode, "") = "" Then

 

[KeithG]

try replacing Null with ""

 

[boblarson]

Also, in VBA it is

If IsNull(YourObjectHere) Then


because nothing is ever equal = to null. It CAN BE null but it isn't EQUAL TO null.

Here's a good thread with Pat Hartman's explanation:
http://www.access-programmers.co.uk/forums/showthread.php?t=104259&highlight=equal+null

 

[missinglinq]

Also, Exit is a Reserved Wordin Access, and shouldn't be used as the name of an object.

Linq

 

[cath_hopes]

Thanks Keith, Paul, Linq and Bob - I've gone for the 'If IsNull(Me.Postcode) Then' route and my code works fine now.
I understand about how Null works now and have noted Paul Hartman's explanation.

Regarding using the Access reserved word Exit as the name of an object: I originally used the Access 2007 button wizard to set up an Exit button, removed the macro it generated and then added my coding within the Exit_Click event procedure. So the name was automatically generated.
Otherwise, yes reserved words should be avoided.

Catherine

 

[boblarson]

I originally used the Access 2007 button wizard to set up an Exit button, removed the macro it generated and then added my coding within the Exit_Click event procedure. So the name was automatically generated.
Otherwise, yes reserved words should be avoided.

Yeah, the microsoft wizards can be a pain as they don't code, or name things, properly. If it generates a reserved word for a button, say Exit, then I would rename it and go change the code too.

 

[missinglinq]

Must be something new in ACC2007! In previous versions there's no such thing in the Command Button Wizard, there's only a Close Form button.

 

[boblarson]

Must be something new in ACC2007! In previous versions there's no such thing in the Command Button Wizard, there's only a Close Form button.

Linq -

Yes, there is in the previous versions. It is the wizard that creates the controls on the form when you select them and have the wizard button depressed so that the wizard starts and then you pick the action you want it to do and it will code it for you. But, the unfortunate thing is that they use bad coding for most of that, including using the DoCmd.DoMenuItem syntax.

 

[tehNellie]

Access 2002 here continually badgers me with a wizard whenever I stick a button on a form.

"You look like you're placing a button! What would you like me to do?"
"fsck off and let me code the damn thing properly."

 

[boblarson]

Access 2002 here continually badgers me with a wizard whenever I stick a button on a form.

"You look like you're placing a button! What would you like me to do?"
"fsck off and let me code the damn thing properly."

Then just unclick the Wizard button (this is A2K7, but the control wiz button looks similar on other versions too):