RE: [MS_AccessPros] Query not updateable

 

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

__._,_.___

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

Messages in this topic (27)

Recent Activity:

• New Members 7
• New Files 2

Visit Your Group

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

.

__,_._,___