Query recordset is not updateable

[bigal.nz]

HI,

I have a query recordset that I can not update - the little message in the status bar says something to the effect of this when I try to update in the form.

My database is split frontend/backend and I can update the data tables on there own from the frontend.

I have also tried this from the query result set and then I cant update the data.

I have seen there are a few reasons for this, but none would appear to apply to this query:

SELECT MAS.StartDate, MAS.Code, MAS.Result, * 
FROM tbl_FV INNER JOIN MAS ON tbl_FV.ID=MAS.ID
WHERE City='Houston' AND DateDiff("d", DateStart, Now() ) -3<0;

Any ideas?

Thanks

Al

 

[sneuberg]

You can check this list. One of the field on the join needs to have a unique key. Suggest check that first.

 

[Galaxiom]

SELECT MAS.StartDate, MAS.Code, MAS.Result, * 
FROM tbl_FV INNER JOIN MAS ON tbl_FV.ID=MAS.ID
WHERE City='Houston' AND DateDiff("d", DateStart, Now() ) -3<0;

BTW That select is inefficient because it must process every record.

The following is much, much faster because it calculates once then compares the value in the field with that result. The DateStart field should be indexed so the engine can retrieve the matching records without reading the individual records.

SELECT MAS.StartDate, MAS.Code, MAS.Result, * 
FROM tbl_FV INNER JOIN MAS ON tbl_FV.ID=MAS.ID
WHERE City='Houston' AND DateStart < Date() - 3;

 

[bigal.nz]

Thanks to you both.

Snuberg you were right on the money.