Delete and Append wont work

[steallan]

Hi
First time posting hope you can help Im totally stumped on two counts.

First I run a delete query on a table (that has NO relationships). the query removes a record if a particular field is blank. However it leaves some, about 40 that are blank, and removes 256.
Why does it think these 40 field entres are not blank when they so are? Any ideas?

Second ive got a append query that wont work. Says there is a key violation and it will not copy any data. However i can find nothing wrong, all the datatypes of updated fields are the same and im not copying from or two primary keys.
I am copying from a selection of records in a query, but i tried putting these records in their own table, then runnig the append query, got the same error. Any ideas here?

Thanks sorry for the length

 

[KenHigg]

Hi
First time posting hope you can help Im totally stumped on two counts.

First I run a delete query on a table (that has NO relationships). the query removes a record if a particular field is blank. However it leaves some, about 40 that are blank, and removes 256.
Why does it think these 40 field entres are not blank when they so are? Any ideas?

You may have spaces in the fld. You may try isnull or like ""

Second ive got a append query that wont work. Says there is a key violation and it will not copy any data. However i can find nothing wrong, all the datatypes of updated fields are the same and im not copying from or two primary keys.
I am copying from a selection of records in a query, but i tried putting these records in their own table, then runnig the append query, got the same error. Any ideas here?

Thanks sorry for the length

Does this table have any relationships?

Ken

 

[mresann]

You aren't giving the condition about the blank field. I'm guessing that the particular field is a text datatype. The null field factor plays into this more than likely. When you test for the condition of the blank field, you may have to test for both null fields and for zero-length text fields (which, although blank, is not null).

This where clause would check for both occurances (for a text field).

WHERE Nz([Myfield]) = True OR Len([MyField]) = 0

If the field is a number-based or boolean field, you shouldn't need the Len statement.

 

[KenHigg]

You aren't giving the condition about the blank field. I'm guessing that the particular field is a text datatype. The null field factor plays into this more than likely. When you test for the condition of the blank field, you may have to test for both null fields and for zero-length text fields (which, although blank, is not null).

This where clause would check for both occurances (for a text field).

WHERE Nz([Myfield]) = True OR Len([MyField]) = 0

If the field is a number-based or boolean field, you shouldn't need the Len statement.

Isn't this the same as what I just said?

kh

 

[steallan]

Hi
Thanks for the help

the field that is the criteria for my delete query is text based, With your advice i can probably fix that now

The append: Now the table im copying the records to does have relationships, but not in the fields im moving data to. Will these relationships mess it up anyway?

 

[KenHigg]

I think that is your problem... Look back through the relationships and see if you have a fk pointing back to some other pk...

Ken

 

[steallan]

thanks mate it works now,

Could i ask you one last question?

in a relationship you can set cascade update. what does this do? i was (hopefully) assuming it would replace an inputted value with the corresponding id number if the value is in the linked table.

Anyway maybe it doesn't matter much i can forget breaking up the table and just keep it as one, or delete then replace the relationships.

thanks a million

 

[KenHigg]

In a relationship you can set cascade update. what does this do?

If you have say tblCustomers with maybe a fld name custName set a the pk and a tblOrders with a fk fld, say custName that points back to tblCustomers, if you changed a customer name in tblCustomers, cascade update would change all the custName flds in tblOrders that had the same value.

Hope that explanation helps.

Ken

 

[mresann]

Isn't this the same as what I just said?

kh

Yes...we were answering at the same time, you beat me to it though mate. Gotta get my track shoes fixed to compete with you Access guns

 

[KenHigg]

Yes...we were answering at the same time, you beat me to it though mate. Gotta get my track shoes fixed to compete with you Access guns

Cool - Wanted to make sure I didn't miss something -

kh