Null "value" ???

[Amadea]

Okay, here's a question. I've got a coded INSERT INTO statement that adds a record to a table based on user input. Some fields have data, some are blank, but that's okay. The table field is set to "allow zero length."

Things have been fine until today. The new record is added but the blank field seems to be read as if there was some data entered. I've looked at the table...no data. I've checked my code...no clues. I was using a hidden text box with no default value to hold the "nothing" value, but I changed the INSERT statement code to insert a blank value. I don't need the hidden text box now...right?

Here's something else. I put some data in the "blank" field of the new record and save. Then take it out and save. Now the field is read as blank!

I don't have much hair left in my head. What else should I be double checking? There must be something I'm missing???

Amadea

 

[shreked]

Amadea,
Have you tryed a repair & compact on the database

If the code has been working fine i would be looking at the database itself.

let me know if this helps


Shrek

 

[Amadea]

Morning. Thank you for the suggestion. I tried it and it doesn't seem to make a difference.

My code goes something like this:

INSERT INTO MyTableName (Field1, Field2, Field3)
VALUES ('" & Field1 & "', '" & Field2 & "', '')

When I highlight the code and run the SQL statement, I can see that the value in Field3 is going to be Null. I thought that was more or less the same as "blank."

My best guess is it's something awry in my Access program.
Do you have any other suggestions?

Amadea

 

[Amadea]

Me again. Well, I think I may have "fixed" it. Either your suggestion did work or by simply eliminating that field from the list altogether, I'm finally back where I was and it's working fine now.

Thank you very much for responding.

Amadea [keeping her fingers crossed, just in case]

 

[cmmrfrds]

Amadea, a NULL is not more or less than a blank. A blank is a known value - could be a space. An empty field is known, you can test for this. A NULL is unknown and is handled special in the relational database world. Definately, read up and understand null since it is important when working with databases.

Null + anything = null
The Variant data type in vba is the only data type that can be assigned a null.

You can convert null to something with the nz function.
nz(yourfield," &quot
nz(yourfield,0)
nz(yourfield,"something&quot

You will find these type of things when you research null.

 

[Amadea]

Thank you. Good advice. I will definately do as you suggest. Also, since what you say is true, it is probably best that I changed the INSERT INTO statement to skip the field I want "blank" rather than try to insert a "blank".

Thanks again.
Amadea

 

[cmmrfrds]

When you say "blank" do you mean blank or null. If you want the field to be blank i.e. a space or empty then use the nz(yourfield," &quot to insert a space in the insert statement. If you want it to be null then leave it out of the insert statement. Hope that helps.