Insert Set value using Append query

[ali32j]

Hi All

I am looking to simply insert a set value "To Be Contacted" into a field if its empty.

Can i use an append query to append a record with the above statement rather than drawing from another table?

INSERT INTO CustomerDetails ( ContactNote, ContactNote )
SELECT CustomerDetails.ContactNote, CustomerDetails.ContactNote
FROM CustomerDetails
WHERE (((CustomerDetails.ContactNote) Is Null));

THis statement is my starting point, but as you can see i need to alter the first INSERT INTO statement to refelct this

Alternatively does anyone else know of a method of updating after new records have been added with the field ContactNote empty?

Thanks

Ali

 

[lespaul]

It looks like what you really want to do is an UPDATE.

If the ContactNote field is empty in an existing record you want to do update it with the value "To Be Contacted", is that right? An update query takes an existing record and updates it with new information (an INSERT or APPEND record adds a new record to a table)

UPDATE TableName SET FieldName = "NewValue" WHERE SomeCondition = True

so in your case you would want to do something like:

UPDATE TableName SET ContactNote = "To Be Contacted" WHERE ContactNote is Null);

now is it really null or is it an empty string? An empty string is something and null is nothing.

Leslie

In an open world there's no need for windows and gates

http://www.ubuntu.com

 

[ali32j]

Hi lespaul

Many thanks, should have checked this, works perfectly

Not sure what you mean about empty string or null? i am checking if a record has no entry, if so to enter the statement

Appreciate your help

Ali

 

[lespaul]

if a record has a field that meets the requirement:

Where SomeField is Null

that same record WON'T meet this requirement:

WHERE SomeField = ""

So, you should check that the ContactNote field is really a NULL and not an empty string. A null is not an empty string...an empty string is SOMETHING (it's a string that is empty); a NULL is nothing, there is nothing there, not an empty string, not a blank, nothing....see the difference?



Leslie

In an open world there's no need for windows and gates

http://www.ubuntu.com