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

Sabtu, 01 Maret 2014

Re: [MS_AccessPros] RE: Query not updateable

Robin-

It's the DISTINCT keyword that's keeping your query from being updatable.

And to avoid confusion with the dates (yours works because there is not month = 29), you should do:

WHERE (((tRotaryYear.YearStart)> DateSerial(Year(DateAdd("m",-6,Date())), 6, 29) And (tRotaryYear.YearStart)< DateSerial(Year(DateAdd("m",+6,Date())), 6, 29)))

John Viescas, Author

Microsoft Access 2010 Inside Out

Microsoft Access 2007 Inside Out

Microsoft Access 2003 Inside Out

Building Microsoft Access Applications

SQL Queries for Mere Mortals

http://www.viescas.com/

(Paris, France)

On Mar 1, 2014, at 1:29 AM, Robin Chapple <robinski@mymail.net.au> wrote:

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