Update Query
in [Queries]
|
Prev: Unable to use filter in datasheet view or add criteria to query in design view
Next: Select to reference an other record or row
From: John Spencer on 5 Mar 2010 11:51 Pending: IIF(Serve D] is Null and [Returned d] is Null, 1,0) John Spencer Access MVP 2002-2005, 2007-2010 The Hilltop Institute University of Maryland Baltimore County Iram wrote: > For cases that don't have neither a Served or Returned date can we create a > calculated field that counts them as well? > > "John W. Vinson" wrote: > >> On Thu, 4 Mar 2010 10:24:02 -0800, Iram <Iram(a)discussions.microsoft.com> >> wrote: >> >>> Hi John, >>> Btw, thanks for your time! >>> >>> This database is used to track a Process Servers Job. The In Date is the >>> date that he receives the papers to serve. He either serves the papers and >>> records the "Serve D" or he returns the papers back to his boss and records >>> the "Returned D". His boss wants to be able to run a report that showed which >>> cases were served or returned within the same month, within 30 days, 60 days >>> or 90 days, (new one) greater than 90 days. >> If only one of Serve D and Returned D will be NULL you might be able to use an >> expression like >> >> DispodWithin30Days: (DateDiff("d", [In Date], NZ([Serve D], [Returned D])) <= >> 30 >> >> You'll still want some sort of criterion to find those cases where the paper >> has neither been served nor returned: it's not clear what you want to do in >> that case. >> -- >> >> John W. Vinson [MVP] >> . >>
From: Iram on 5 Mar 2010 17:16 Hello fellas, I added the below calculated fields to the query and adjusted the names. DispodWithin30Days: (DateDiff("d",[In Date],NZ([Serve D],[Return D]))<=30) DispodWithin60Days: (DateDiff("d",[In Date],NZ([Serve D],[Return D]))<=60) DispodWithin90Days: (DateDiff("d",[In Date],NZ([Serve D],[Return D]))<=90) Problem, if there is a Serve D or a Return D within one month of the In Date than all three calculated fields become -1. This is because the Serve or Return date is less than 90 days and less than 60 and less than 30. How can I fix this? When I added the below calculation get an error... Pending: IIF(Serve D] is Null and [Return D] is Null, 1,0) Error: The expression you entered contains invalid syntax You may have entered an operand without an operator. Your help is greatly appreciated! Thanks. Iram/mcp "John Spencer" wrote: > Pending: IIF(Serve D] is Null and [Returned d] is Null, 1,0) > > John Spencer > Access MVP 2002-2005, 2007-2010 > The Hilltop Institute > University of Maryland Baltimore County > > Iram wrote: > > For cases that don't have neither a Served or Returned date can we create a > > calculated field that counts them as well? > > > > "John W. Vinson" wrote: > > > >> On Thu, 4 Mar 2010 10:24:02 -0800, Iram <Iram(a)discussions.microsoft.com> > >> wrote: > >> > >>> Hi John, > >>> Btw, thanks for your time! > >>> > >>> This database is used to track a Process Servers Job. The In Date is the > >>> date that he receives the papers to serve. He either serves the papers and > >>> records the "Serve D" or he returns the papers back to his boss and records > >>> the "Returned D". His boss wants to be able to run a report that showed which > >>> cases were served or returned within the same month, within 30 days, 60 days > >>> or 90 days, (new one) greater than 90 days. > >> If only one of Serve D and Returned D will be NULL you might be able to use an > >> expression like > >> > >> DispodWithin30Days: (DateDiff("d", [In Date], NZ([Serve D], [Returned D])) <= > >> 30 > >> > >> You'll still want some sort of criterion to find those cases where the paper > >> has neither been served nor returned: it's not clear what you want to do in > >> that case. > >> -- > >> > >> John W. Vinson [MVP] > >> . > >> > . >
From: John W. Vinson on 5 Mar 2010 18:56 On Fri, 5 Mar 2010 14:16:17 -0800, Iram <Iram(a)discussions.microsoft.com> wrote: >Problem, if there is a Serve D or a Return D within one month of the In Date >than all three calculated fields become -1. This is because the Serve or >Return date is less than 90 days and less than 60 and less than 30. How can I >fix this? > >When I added the below calculation get an error... >Pending: IIF(Serve D] is Null and [Return D] is Null, 1,0) > >Error: >The expression you entered contains invalid syntax >You may have entered an operand without an operator. Use more stringent criteria, and change the syntax of the IIF: DispodWithin30Days: (DateDiff("d",[In Date],NZ([Serve D],[Return D]))<=30) DispodWithin60Days: (DateDiff("d",[In Date],NZ([Serve D],[Return D]))>30 AND DateDiff("d",[In Date],NZ([Serve D],[Return D]))<=60) DispodWithin90Days: (DateDiff("d",[In Date],NZ([Serve D],[Return D]))>60 AND DateDiff("d",[In Date],NZ([Serve D],[Return D]))<=90) Pending: IIF(IsNull([Serve D]) AND IsNull( [Return D]),-1,0) You're not explicitly catching records that never get returned at all... you might want to add a new one checking for > 90. -- John W. Vinson [MVP]
From: Iram on 8 Mar 2010 12:11 Thank you John V. the calculated fields worked perfectly! Question: Why can't I see these calculated fields in a new repot which inlcude the calculated fields? In the query I see -1's and 0's but in the report I don't see these -1's and 0's, nothing is appearing. The report wizard made text boxes boxes for the calculated fields so then I removed them and place check boxes in there places and still nothing. Any suggestions? Thanks. Iram/mcp "John W. Vinson" wrote: > On Fri, 5 Mar 2010 14:16:17 -0800, Iram <Iram(a)discussions.microsoft.com> > wrote: > > >Problem, if there is a Serve D or a Return D within one month of the In Date > >than all three calculated fields become -1. This is because the Serve or > >Return date is less than 90 days and less than 60 and less than 30. How can I > >fix this? > > > >When I added the below calculation get an error... > >Pending: IIF(Serve D] is Null and [Return D] is Null, 1,0) > > > >Error: > >The expression you entered contains invalid syntax > >You may have entered an operand without an operator. > > Use more stringent criteria, and change the syntax of the IIF: > > DispodWithin30Days: (DateDiff("d",[In Date],NZ([Serve D],[Return D]))<=30) > DispodWithin60Days: (DateDiff("d",[In Date],NZ([Serve D],[Return D]))>30 AND > DateDiff("d",[In Date],NZ([Serve D],[Return D]))<=60) > DispodWithin90Days: (DateDiff("d",[In Date],NZ([Serve D],[Return D]))>60 AND > DateDiff("d",[In Date],NZ([Serve D],[Return D]))<=90) > > Pending: IIF(IsNull([Serve D]) AND IsNull( [Return D]),-1,0) > > You're not explicitly catching records that never get returned at all... you > might want to add a new one checking for > 90. > -- > > John W. Vinson [MVP] > . >
From: Iram on 8 Mar 2010 12:36
John, some how the -1's and 0's started to appear in the reports, all is good! I don't know what I would do with out you guys! I am really happy and appreciative of your guys efforts! Thanks John W. Vinson and John Spencer Iram/mcp "Iram" wrote: > Thank you John V. the calculated fields worked perfectly! > Question: Why can't I see these calculated fields in a new repot which > inlcude the calculated fields? In the query I see -1's and 0's but in the > report I don't see these -1's and 0's, nothing is appearing. The report > wizard made text boxes boxes for the calculated fields so then I removed them > and place check boxes in there places and still nothing. Any suggestions? > > > Thanks. > Iram/mcp > > "John W. Vinson" wrote: > > > On Fri, 5 Mar 2010 14:16:17 -0800, Iram <Iram(a)discussions.microsoft.com> > > wrote: > > > > >Problem, if there is a Serve D or a Return D within one month of the In Date > > >than all three calculated fields become -1. This is because the Serve or > > >Return date is less than 90 days and less than 60 and less than 30. How can I > > >fix this? > > > > > >When I added the below calculation get an error... > > >Pending: IIF(Serve D] is Null and [Return D] is Null, 1,0) > > > > > >Error: > > >The expression you entered contains invalid syntax > > >You may have entered an operand without an operator. > > > > Use more stringent criteria, and change the syntax of the IIF: > > > > DispodWithin30Days: (DateDiff("d",[In Date],NZ([Serve D],[Return D]))<=30) > > DispodWithin60Days: (DateDiff("d",[In Date],NZ([Serve D],[Return D]))>30 AND > > DateDiff("d",[In Date],NZ([Serve D],[Return D]))<=60) > > DispodWithin90Days: (DateDiff("d",[In Date],NZ([Serve D],[Return D]))>60 AND > > DateDiff("d",[In Date],NZ([Serve D],[Return D]))<=90) > > > > Pending: IIF(IsNull([Serve D]) AND IsNull( [Return D]),-1,0) > > > > You're not explicitly catching records that never get returned at all... you > > might want to add a new one checking for > 90. > > -- > > > > John W. Vinson [MVP] > > . > > |