Data Validation for Int Field Not Updating When Null or Empty

[TrivialPants]

Hi!



I have a SQL table INT field that is being used as the control source for an editable Text Box in an Access application. I have the following validation rule applied to the text box:


In ("1","2",=Null)


However, the portion where I try to equate the Text box to Null doesn't work. I have also tried the empty string (=""). 1 and 2 will update the associated SQL table with the appropriate value, and any other number will not be allowed (the box will only take 1 & 2). I want it to be able to take a blank space and equate the SQL table to NULL . I have checked and the SQL table will allow NULL for that field.

Does anyone have any advice?

[orange]

Have you tried In ("1","2"," ")? If a blank (" ") is what you want.
Null and blank(" ") and zero length string ("") are different things.

[Micron]

Null is "unknown". Nothing is equal to null. Null is not equal to anything, thus it can't be compared - only discovered or set.
In ("1","2",Null) might work. Otherwise, suspect an IIF expression or IsNull (vba) or Is Null (sql) is required.

[TrivialPants]

I can compare


In ("1","2","3") --or any other number where 3 is. How could I go about setting the related SQL field to NULL if the third value is matched in the Text Box?

[orange]

Show us the related SQL.
Why use an In(......) if you only want to change a value for a specific character/value?

Perhaps you should tell readers what you are trying to accomplish in plain, simple English.

[TrivialPants]

In Plain English:

I want to have 3 possible values.

1 - The Order is being processed
2 - The Order is shipped
3 - This will set the value for the SQL Field (an integer) to NULL, but will accept 3 if I can't get the access form to accept a more intuitive value or a drop-down list.
The NULL means that the order is not being shipped or processed, and I would like to give users the ability to set it in case they accidentally one so all orders that aren't processed are set to NULL instead of NULL and some third other value (such as 3).

[TrivialPants]

Okay,

So I changed my Form's data validation & the process a little bit.

First, the process. I want to still have three values:

"0" - The order is not processed, but this is an integer value of 0 that shows that it manually set to be so. All orders are defaulted to NULL (0 and NULL are equivalent here).
"1" - The order is set to be processed. It is a work in progress.
"YYYYMMDD' - this is an 8-digit integer that I will convert in SQL to a date-time value to show when the order had an ASN sent.


The Data Validation:


([Wave]=0) Or ([Wave]=1) Or (Len([Wave])=8)


This works, but I can see a problem with the date being entered in the wrong format, not YYYYMMDD. I am checking for length, but users could still enter a date like MMDDYYYY or something else which SQL wouldn't recognize. Is it possible to enforce a date picker on this same Text Box to allow for further data validation (as well as the values of "0" or "1" to be entered)


Thank you!

[orange]

Seems, in plain English, you have some sort of order fulfillment business. Which would seem there is a Customer/Client who makes a request/Order for some product/item. Your organization assembles/builds/picks the Item(s) for the Order. So at any point in time - from the Customer's view the Order is a) in process but not yet shipped, b) shipped and c) something that hasn't been clearly described, but you want to have as a long integer representing YYYYMMDD and ill convert to SQL later???
Others may know what an ASN is, but to me it's jargonese.

Sounds like your issue is with OrderStatus and how to identify same. First thought would be a tblStatus with
StatusID
StatusName
and possibly StatusDescription.
When you assign a status to an Order in tblOrderStatus you could automatically include the Date() or Date and Time (Now() and the PersonID of who updated/added the status.
Good luck.