ACCESS FEED: RE: [MS_AccessPros] Query not updateable

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(DateSerial(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]