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/test.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(DateSerial(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@yahoogroups.com] On Behalf Of Robin Chapple
>Sent: Sunday, May 27, 2012 6:11 AM
>To: MS_Access_Professionals@yahoogroups.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(DateSerial(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@yahoogroups.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>
> > >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@yahoogroups.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>
> > > >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@yahoogroups.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>
> > > > >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]

__._,_.___

Reply to sender | Reply to group | Reply via web post | Start a New Topic

Messages in this topic (26)

Recent Activity:

• New Members 7
• New Files 2

Visit Your Group

Switch to: Text-Only, Daily Digest • Unsubscribe • Terms of Use

.

__,_._,___