ACCESS FEED: Re: [MS_AccessPros] RE: Query not updateable

Jumat, 28 Februari 2014

Re: [MS_AccessPros] RE: Query not updateable

Thanks Bill,

Your plan did not work but this did:

SELECT tClubPostHolders.MemberID, tClubPostHolders.ClubPostID, tClubPostHolders.YearID, tClubPostHolders.ClubID, tClubPostHolders.LastUpdated, tClubPostHolders.tblClubPostHoldersID
FROM tRotaryYear RIGHT JOIN tClubPostHolders ON tRotaryYear.YearID = tClubPostHolders.YearID
WHERE (((tRotaryYear.YearStart)>"29/06/" & Year(DateAdd("m",-6,Date())) And (tRotaryYear.YearStart)<"29/06/" & Year(DateAdd("m",+6,Date()))))
ORDER BY tClubPostHolders.ClubPostID;

Just luck on my part. I'd like to know why'

Cheers,

Robin

At 1/03/2014 09:58 AM, you wrote:

Robin
I'm not sure but it might be because of the tRotaryYear.YearStart in the SELECT clause. Try taking it out to see if that makes the query updateable.

Bill

---In MS_Access_Professionals@yahoogroups.com, <robinski@mymail.net.au> wrote:

I am using Access 2013 on a Win7 system. Our database is on a remote server.

In the past group members have helped my with my club membership
database. We have a fiscal year which starts on 1st July.

The result has been two queries to show "Post Holders" next fiscal
year and this fiscal year.

This to give "Next Fiscal Year"

SELECT tClubPostHolders.MemberID, tClubPostHolders.ClubPostID,
tClubPostHolders.YearID, tClubPostHolders.ClubID,
tClubPostHolders.LastUpdated, tClubPostHolders.tblClubPostHoldersID
FROM tRotaryYear RIGHT JOIN tClubPostHolders ON tRotaryYear.YearID =
tClubPostHolders.YearID
WHERE (((tRotaryYear.YearStart)>"29/06/" & Year(DateAdd("m",+6,Date()))))
ORDER BY tClubPostHolders.ClubPostID;

This to give "This Fiscal Year"

SELECT DISTINCT tClubPostHolders.ClubPostID,
tClubPostHolders.MemberID, tClubPostHolders.YearID,
tClubPostHolders.ClubID, tRotaryYear.YearStart,
tClubPostHolders.tblClubPostHoldersID
FROM tRotaryYear RIGHT JOIN tClubPostHolders ON tRotaryYear.YearID =
tClubPostHolders.YearID
WHERE
(((tRotaryYear.YearStart)=IIf(Month(Date())>=7,DateSerial(Year(Date()),7,1),DateSerial(Year(Date())-1,7,1))));

These worked as planned. We have now progressed to giving club
secretaries access to add and edit records. I find that the "Next
Year" query can take adds and edits but the "This Year" is not updateable.

Can I fix that?

Many thanks,

Robin Chapple