ACCESS FEED: RE: [MS_AccessPros] Query not updateable

John,

I have used your new suggestion to include the rate and it worked. I
then used a modified version of the AD suggestion and I have the result I need.

Many thanks to you both.

Regards,

Robin Chapple

At 27/05/2012 06:32 PM, you wrote:
>Robin-
>
>
>
>The two queries aren't the same. The SQL uses your old IIF, and the second
>attempts to incorporate A.D.'s suggestion. The Criteria should read:
>
>
>
> >Format( DateSerial(Year(DateAdd("m", -6, Date())), 7, 1), "mm/dd/yyyy")
>
>
>
>Because you're directly comparing with what I assume is a Date/Time field
>(MonthDate), you can actually do:
>
>
>
> > DateSerial(Year(DateAdd("m", -6, Date())), 7, 1)
>
>
>
>You can do that because DateSerial returns a date/time value that the query
>engine will compare directly without going through string conversion.
>
>
>
>John Viescas, author
>
>Microsoft Office Access 2010 Inside Out
>
>Microsoft Office Access 2007 Inside Out
>
>Building Microsoft Access Applications
>
>Microsoft Office Access 2003 Inside Out
>
>SQL Queries for Mere Mortals
>
> <http://www.viescas.com/> http://www.viescas.com/
>
>(Paris, France)
>
>
>
>
>
>
>
>From: MS_Access_Professionals@yahoogroups.com
>[mailto:MS_Access_Professionals@yahoogroups.com] On Behalf Of Robin Chapple
>Sent: Sunday, May 27, 2012 9:59 AM
>To: MS_Access_Professionals@yahoogroups.com
>Subject: RE: [MS_AccessPros] Query not updateable
>
>
>
>
>
>
>Thanks again John,
>
>I apologise for my lack of ability. There is a value stored in tServiceRate.
>
>I have a problem with this new suggestion. It is so complicated I
>have added a screen shot here:
>
><http://www.rotary9790.org.au/test/test.asp>http://www.rotary9790.org.au/test/te
>st.asp
>
>Regards,
>
>Robin Chapple
>
>At 27/05/2012 03:31 PM, you wrote:
> >Robin-
> >
> >
> >
> >Unless the current rate is already stored in tServiceProjects, you
> >won't get any
> >rows. Here's how I meant for you to use DLookup:
> >
> >
> >
> >SELECT tServiceProjects.ClubID, tServiceProjects.MonthID,
> >tServiceProjects.MonthDate, tServiceProjects.Description,
> >tServiceProjects.SumDonated, tServiceProjects.MembersInvolved,
> >tServiceProjects.TotalHours, DLookUp("[Rate]","tServiceRate") As Rate,
> >tServiceProjects.ProjectID, tServiceProjects.LastUpdated,
> >[SumDonated]+([TotalHours]* DLookUp("[Rate]","tServiceRate")) AS
> ServiceValue
> >FROM tServiceProjects
> >WHERE
> >(((tServiceProjects.MonthDate)>IIf(Month(Date())>=7,Format(DateSeri
> al(Year(Date
>(
> >)),7,1),"mm/dd/yyyy"),Format(DateSerial(Year(Date())-1,7,1),"mm/dd/yyyy")))
> >
> >
> >
> >See also my reply about how to use A.D.'s expression.
> >
> >
> >
> >John Viescas, author
> >
> >Microsoft Office Access 2010 Inside Out
> >
> >Microsoft Office Access 2007 Inside Out
> >
> >Building Microsoft Access Applications
> >
> >Microsoft Office Access 2003 Inside Out
> >
> >SQL Queries for Mere Mortals
> >
> > <http://www.viescas.com/> http://www.viescas.com/
> >
> >(Paris, France)
> >
> >
> >
> >
> >
> >
> >
> >
> >
> >From: MS_Access_Professionals@yahoogroups.com
><mailto:MS_Access_Professionals%40yahoogroups.com>
> >[mailto:MS_Access_Professionals@yahoogroups.com
><mailto:MS_Access_Professionals%40yahoogroups.com> ] On Behalf Of
>Robin Chapple
> >Sent: Sunday, May 27, 2012 6:11 AM
> >To: MS_Access_Professionals@yahoogroups.com
><mailto:MS_Access_Professionals%40yahoogroups.com>
> >Subject: RE: [MS_AccessPros] Query not updateable
> >
> >
> >
> >
> >
> >
> >I have now read the Microsoft page again and saw this: " Any field
> >that is included in criteria must also be a field in domain;
> >otherwise, the DLookup function returns a Null. "
> >
> >I have renamed the field in "tServiceProjects' to be the same as the
> >field in "tSewrviceRate" but the result is the same, No records.
> >
> >SELECT tServiceProjects.ClubID, tServiceProjects.MonthID,
> >tServiceProjects.MonthDate, tServiceProjects.Description,
> >tServiceProjects.SumDonated, tServiceProjects.MembersInvolved,
> >tServiceProjects.TotalHours, tServiceProjects.Rate,
> >tServiceProjects.ProjectID, tServiceProjects.LastUpdated,
> >[SumDonated]+([TotalHours]*[Rate]) AS ServiceValue
> >FROM tServiceProjects
> >WHERE
> >(((tServiceProjects.MonthDate)>IIf(Month(Date())>=7,Format(DateSeri
> al(Year(Date
>(
> >)),7,1),"mm/dd/yyyy"),Format(DateSerial(Year(Date())-1,7,1),"mm/dd/yyyy")))
> >AND ((tServiceProjects.Rate)=DLookUp("[Rate]","tServiceRate")));
> >
> >Robin Chapple
> >
> >At 27/05/2012 11:48 AM, you wrote:
> >
> > >John,
> > >
> > >The table "tServicerate" has one field "Rate" so I edited the expression.
> > >
> > >I have rebuilt the project and applied the DLookup and no records are
> > >returned.
> > >
> > >SELECT tServiceProjects.ClubID, tServiceProjects.MonthID,
> > >tServiceProjects.MonthDate, tServiceProjects.Description,
> > >tServiceProjects.SumDonated, tServiceProjects.MembersInvolved,
> > >tServiceProjects.TotalHours, tServiceProjects.ServiceRate,
> > >tServiceProjects.ProjectID, tServiceProjects.LastUpdated,
> > >[SumDonated]+([TotalHours]*[ServiceRate]) AS ServiceValue
> > >FROM tServiceProjects
> > >WHERE
> > >(((tServiceProjects.MonthDate)>IIf(Month(Date())>=7,Format(DateSeri
> > al(Year(Date
> >()),7,1),"mm/dd/yyyy"),Format(DateSerial(Year(Date())-1,7,1),"mm/dd/yyyy")))
> > >
> > >AND ((tServiceProjects.ServiceRate)=DLookUp("Rate","tServiceRate")));
> > >
> > >The test data has 46 records, 44 in the fiscal year and 2 in the
> > >prior year to test the code. Before DLookup is added the 44 records
> > >are returned.
> > >
> > >What have I missed this time?
> > >
> > >Robin Chapple.
> > >
> > >
> > >At 26/05/2012 07:57 PM, you wrote:
> > > >Nothing.
> > > >
> > > >
> > > >
> > > >DLookup("Service Rate", "tServiceRate")
> > > >
> > > >
> > > >
> > > >
> > > >
> > > >John Viescas, author
> > > >
> > > >Microsoft Office Access 2010 Inside Out
> > > >
> > > >Microsoft Office Access 2007 Inside Out
> > > >
> > > >Building Microsoft Access Applications
> > > >
> > > >Microsoft Office Access 2003 Inside Out
> > > >
> > > >SQL Queries for Mere Mortals
> > > >
> > > > <http://www.viescas.com/> http://www.viescas.com/
> > > >
> > > >(Paris, France)
> > > >
> > > >
> > > >
> > > >
> > > >
> > > >
> > > >
> > > >From: MS_Access_Professionals@yahoogroups.com
><mailto:MS_Access_Professionals%40yahoogroups.com>
> ><mailto:MS_Access_Professionals%40yahoogroups.com>
> > > >[mailto:MS_Access_Professionals@yahoogroups.com
><mailto:MS_Access_Professionals%40yahoogroups.com>
> ><mailto:MS_Access_Professionals%40yahoogroups.com> ] On Behalf Of
> >Robin Chapple
> > > >Sent: Saturday, May 26, 2012 11:36 AM
> > > >To: MS_Access_Professionals@yahoogroups.com
><mailto:MS_Access_Professionals%40yahoogroups.com>
> ><mailto:MS_Access_Professionals%40yahoogroups.com>
> > > >Subject: RE: [MS_AccessPros] Query not updateable
> > > >
> > > >
> > > >
> > > >
> > > >
> > > >
> > > >John,
> > > >
> > > >I have read the page on the Microsoft site that states:
> > > >
> > > >Syntax
> > > >
> > > >expression.DLookup(Expr, Domain, Criteria)
> > > >
> > > >and mentions that criteria is optional. If criteria is not used what
> > > >do I type after the second comma?
> > > >
> > > >Robin Chapple
> > > >
> > > >At 26/05/2012 06:09 PM, you wrote:
> > > > >Robin-
> > > > >
> > > > >That makes no sense. Is there only one row in tServiceRate?
> What is that
> > > > >value? The "current" rate that you want applied to any new row?
> > > > >
> > > > >There's no way to set that in the Default Value property. You'll
> > > have to set
> > > > >the value in the form you use to edit the tServiceProjects table
> > > in the Load
> > > > >event of the form using DLookup.
> > > > >
> > > > >
> > > > >John Viescas, author
> > > > >Microsoft Office Access 2010 Inside Out
> > > > >Microsoft Office Access 2007 Inside Out
> > > > >Building Microsoft Access Applications
> > > > >Microsoft Office Access 2003 Inside Out
> > > > >SQL Queries for Mere Mortals
> > > > >http://www.viescas.com/
> > > > >(Paris, France)
> > > > >
> > > > >
> > > > >------------------
> > > > >
> > > > >From: MS_Access_Professionals@yahoogroups.com
><mailto:MS_Access_Professionals%40yahoogroups.com>
> ><mailto:MS_Access_Professionals%40yahoogroups.com>
> > > ><mailto:MS_Access_Professionals%40yahoogroups.com>
> > > > >[mailto:MS_Access_Professionals@yahoogroups.com
><mailto:MS_Access_Professionals%40yahoogroups.com>
> ><mailto:MS_Access_Professionals%40yahoogroups.com>
> > > ><mailto:MS_Access_Professionals%40yahoogroups.com> ] On Behalf Of
> > > >Robin Chapple
> > > > >Sent: Saturday, May 26, 2012 9:00 AM
> > > > >To: MS_Access_Professionals@yahoogroups.com
><mailto:MS_Access_Professionals%40yahoogroups.com>
> ><mailto:MS_Access_Professionals%40yahoogroups.com>
> > > ><mailto:MS_Access_Professionals%40yahoogroups.com>
> > > > >Subject: RE: [MS_AccessPros] Query not updateable
> > > > >
> > > > >
> > > > >Thanks John,
> > > > >
> > > > >I have been busy with another problem. Our server managed to scramble
> > > > >most of our 260 alias addresses!!
> > > > >
> > > > >This problem. There is not a direct link but if you look at the first
> > > > >image on this page:
> > > > >
> > > > ><http://www.rotary9790.org.au/test/test.asp>http://www.rotary9790.o
> > > > rg.au/test/t
> > > >e
> > > > >st.asp
> > > > >
> > > > >you will see that the 'rate', which is variable, is sourced form
> > > > >another table. In the second image you will see my attempt to make
> > > > >the default value of the field set at the current value of the 'rate'
> > > > >set in the 'ServiceRate' table.
> > > > >
> > > > >Image three will show that Access does not like that.
> > > > >
> > > > >Is there a way around my problem?
> > > > >
> > > > >Regards,
> > > > >
> > > > >Robin Chapple
> > > > >
> > > > >At 25/05/2012 04:34 PM, you wrote:
> > > > > >Robin-
> > > > > >
> > > > > >Is there any relationship between the two tables? That's what is
> > > > keeping the
> > > > > >query from being updatable.
> > > > > >
> > > > > >John Viescas, author
> > > > > >
> > > > > >Microsoft Office Access 2010 Inside Out
> > > > > >
> > > > > >Microsoft Office Access 2007 Inside Out
> > > > > >
> > > > > >Building Microsoft Access Applications
> > > > > >
> > > > > >Microsoft Office Access 2003 Inside Out
> > > > > >
> > > > > >SQL Queries for Mere Mortals
> > > > > >
> > > > > > <http://www.viescas.com/> http://www.viescas.com/
> > > > > >
> > > > > >(Paris, France)
> > > > > >
> > > > > >
> > > > > >
> > > > > >
> > > > > >
> > > > > >
> > > > > >
> > > > > >From: MS_Access_Professionals@yahoogroups.com
><mailto:MS_Access_Professionals%40yahoogroups.com>
> ><mailto:MS_Access_Professionals%40yahoogroups.com>
> > > ><mailto:MS_Access_Professionals%40yahoogroups.com>
> > > > > >[mailto:MS_Access_Professionals@yahoogroups.com
><mailto:MS_Access_Professionals%40yahoogroups.com>
> ><mailto:MS_Access_Professionals%40yahoogroups.com>
> > > ><mailto:MS_Access_Professionals%40yahoogroups.com> ] On Behalf Of
> > > >Robin Chapple
> > > > > >Sent: Friday, May 25, 2012 12:17 AM
> > > > > >To: MS_Access_Professionals@yahoogroups.com
><mailto:MS_Access_Professionals%40yahoogroups.com>
> ><mailto:MS_Access_Professionals%40yahoogroups.com>
> > > ><mailto:MS_Access_Professionals%40yahoogroups.com>
> > > > > >Subject: [MS_AccessPros] Query not updateable
> > > > > >
> > > > > >
> > > > > >
> > > > > >
> > > > > >
> > > > > >I have a query planned to provide a calculated field which is not
> > > > > updateable.
> > > > > >
> > > > > >Is there any technique that I can use that will allow the
> calculation
> > > > > >to stay and new entries to be made?
> > > > > >
> > > > > >SELECT tServiceProjects.ClubID, tServiceProjects.MonthID,
> > > > > >tServiceProjects.MonthDate, tServiceProjects.Description,
> > > > > >tServiceProjects.SumDonated, tServiceProjects.MembersInvolved,
> > > > > >tServiceProjects.TotalHours, tServiceProjects.ProjectID,
> > > > > >tServiceProjects.LastUpdated, [SumDonated]+([TotalHours]*[Rate]) AS
> > > > > >ServiceValue
> > > > > >FROM tServiceProjects, tServiceRate
> > > > > >WHERE
> > > > > >(((tServiceProjects.MonthDate)>IIf(Month(Date())>=7,Format(DateSeri
> > > > > al(Year(Date
> > > > >(
> > > > > >)),7,1),"mm/dd/yyyy"),Format(DateSerial(Year(Date())-1,7,1),"mm/dd/
> > > > > yyyy"))));
> > > > > >
> > > > > >Many thanks,
> > > > > >
> > > > > >Robin Chapple
> > > > > >
> > > > > >
> > > > > >
> > > > > >
> > > > > >
> > > > > >[Non-text portions of this message have been removed]
> > > > > >
> > > > > >
> > > > > >
> > > > > >------------------------------------
> > > > > >
> > > > > >Yahoo! Groups Links
> > > > > >
> > > > > >
> > > > > >
> > > > >
> > > > >[Non-text portions of this message have been removed]
> > > > >
> > > > >
> > > > >
> > > > >
> > > > >------------------------------------
> > > > >
> > > > >Yahoo! Groups Links
> > > > >
> > > > >
> > > > >
> > > >
> > > >[Non-text portions of this message have been removed]
> > > >
> > > >
> > > >
> > > >
> > > >
> > > >[Non-text portions of this message have been removed]
> > > >
> > > >
> > > >
> > > >------------------------------------
> > > >
> > > >Yahoo! Groups Links
> > > >
> > > >
> > > >
> > >
> > >
> > >
> > >
> > >------------------------------------
> > >
> > >Yahoo! Groups Links
> > >
> > >
> > >
> >
> >[Non-text portions of this message have been removed]
> >
> >
> >
> >
> >
> >[Non-text portions of this message have been removed]
> >
> >
> >
> >------------------------------------
> >
> >Yahoo! Groups Links
> >
> >
> >
>
>[Non-text portions of this message have been removed]
>
>
>
>
>
>[Non-text portions of this message have been removed]
>
>
>
>------------------------------------
>
>Yahoo! Groups Links
>
>
>