Append query with empty values

[EagleTempest]

VB6 and Access.

I'm learning SQL and just realized with a simple append query such as

"INSERT INTO WindowComponents VALUES (2,1,'Awn',610,610,3,'Pencil','Yes')"

that all values must have a value. If I omit 3 as in

"INSERT INTO WindowComponents VALUES (2,1,'Awn',610,610,,'Pencil','Yes')"

I get a syntax error. However I would prefer not to have to always have a value.

If I use the more exact approach as:

"INSERT INTO WindowComponents (QuoteNum, WindowNum, Type, Width, Height, MuntinSqrs, MuntinType, FixedSash) VALUES (2,1,'Awn',610,610,,'Pencil','Yes')"

will this allow blank values? I have the Required property for each field in Access set to No.

 

[dhookom]

Try:

"INSERT INTO WindowComponents (QuoteNum, WindowNum, Type, Width, Height, MuntinSqrs, MuntinType, FixedSash) VALUES (2,1,'Awn',610,610,Null,'Pencil','Yes')"

Duane
MS Access MVP
Find out how to get great answers faq219-2884.

 

[PHV]

The more exact approach is this:
"INSERT INTO WindowComponents (QuoteNum, WindowNum, Type, Width, Height, MuntinType, FixedSash) VALUES (2,1,'Awn',610,610,'Pencil','Yes')"

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ222-2244

 

[EagleTempest]

In Access I set a column's type to Number with it's Required property to No.

I realize that SQL allows '' (2 single quotes) for blank text input but having ,, for blank Number input doesn't work. It really want a 0. That why I keep getting a Syntax error in my SQL statement.

 

[lespaul]

Then you need to leave that field out of your insert field list like PHV suggested.

Leslie

 

[EagleTempest]

Actually, I just make a function to check if it's blank. If so it returns a 0. I have a few fields like this.

I'm saving info from a flexgrid and I prefer to leave a field blank rather than enter No. This way it is visually easier to see what options for each piece are selected.