ACCESS FEED: RE: [MS_AccessPros] Query not updateable

Sabtu, 26 Mei 2012

RE: [MS_AccessPros] Query not updateable

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@yahoogroups.com] On Behalf Of Robin Chapple
>Sent: Saturday, May 26, 2012 11:36 AM
>To: MS_Access_Professionals@yahoogroups.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@yahoogroups.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>
> >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@yahoogroups.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>
> > >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
>
>
>