Insert into table, dealing with null values

[striker73]

It is my understanding when I run a line of code, like this:

"INSERT INTO MyTable (field1, field2, field3) VALUES ("abc", "def", )"

...that a null value will be stored in field3. I'm trying this out, but I seem to always get an "Syntax error on INSERT INTO table" error. I have tried:

"INSERT INTO MyTable (field1, field2, field3) VALUES ("abc", "def", Null)"

...as well as ...

"INSERT INTO MyTable (field1, field2, field3) VALUES ("abc", "def", "&quot

...but nothing seems to work. Any ideas on how to get beyond this? Thanks!

 

[Junior1544]

why try to insert a constant null?? why not have the field in the table, and default it to null... then just leave it alone?? that would make sence to me...

--Junior JHauge@jmjpc.net
Life is change. To deny change is to deny life.

 

[striker73]

Well I am trying to get my code to be able to deal with two case: 1) there is data for the field, and 2) no data for the field. My INSERT statement looks something like this:

DoCmd.RunSQL "INSERT INTO MyTable (field1, field2, field3) VALUES (" & iif(isnull(value1), "", value1) & ", value2, value3)"

The fields in my table are dates, and I went ahead and set the default to Null, but how do you suggest I leave them alone? As far as my code goes? What should I type in the red value above?

 

[Junior1544]

see, i think this is getting more complicated then need be...

make the code so it bring all values over, and if a value is null, it should just bring the value of null over... i asked what i did because the way you were saying it is because you were looking to assign a value to null on a new record... in that case don't assign it, just leave it be... but you want it to handle if a user puts null as a value, i know when i've writen some things, it handled no value's without a problem... another thought, it to make the default value not null, maybe a space or some thing... then the code wont have a problem... just a thought...

--Junior JHauge@jmjpc.net
Life is change. To deny change is to deny life.

 

[striker73]

Well I think I fixed it. It's probably not the most simple fix, but is what I did was a series of if statements. I have a string called fields and a string called values. If the value is null, then I won't add the field or the value to my insert statement. Otherwise I add it to both fields and values and run my insert statement. So if the value3 is null, then I leave it out all together, like this:

"INSERT INTO MyTable (field1, field2) VALUES (value1, value2)

Thanks for your help!

 

[Junior1544]

sorry i couldn't help better... though if your doing it that way, and have many fields... i would look into the case command...
--Junior JHauge@jmjpc.net
Life is change. To deny change is to deny life.

 

[DonQuichote]

In the first message, I see that you have double quotes inside the string. If they are really there, the line is not valid VBA. As SQL also knows single quotes, it is better to use single quotes when giving a string value in SQL.

Does this solve the problem?

 

[striker73]

No, I wasn't copying my code verbatim. I was using single quotes for strings. Thanks though.