query error
Hi, I am running a query based on 3 queries q1 has this PurchDoc Item Rel Vendor Vendor name Order Type PO Date Material Short Text MRPCn Del Date Deliv Date PO Quantity GR Quantity QtyRed RemQty PD release Reschdate CC Created on Exc Net Order Value ID 46353228 190 F 10000019 RA LALLI NB 11/6/2006 109-0718-13-101 PASSACINGHIA AK7 6/13/2013 44 42 0 0 1/2/2010 11,396.00 14632 q2 has this PurchDoc Item Rel Vendor Vendor name Order Type PO Date Material Short Text MRPCn Del Date Deliv Date PO Quantity GR Quantity QtyRed RemQty PD release Reschdate CC AB date Exc Net Order Value ID 46353228 190 F 10000019 RA LALLI NB 11/6/2006 109-0718-13-101 PASSACINGHIA AK7 6/13/2013 6/13/2013 44 42 12 0 1/2/2010 11/21/2012 AB 3/9/2010 10 11,396.00 14638 46353228 190 F 10000019 RA LALLI NB 11/6/2006 109-0718-13-101 PASSACINGHIA AK7 6/13/2013 6/13/2013 44 42 14 0 1/2/2010 11/21/2012 AB 3/9/2010 10 11,396.00 14636 46353228 190 F 10000019 RA LALLI NB 11/6/2006 109-0718-13-101 PASSACINGHIA AK7 6/13/2013 6/13/2013 44 42 18 0 1/2/2010 11/21/2012 AB 3/9/2010 10 11,396.00 14637 q3 has this PurchDoc Item Rel Vendor Vendor name Order Type PO Date Material Short Text MRPCn Del Date Deliv Date PO Quantity GR Quantity QtyRed RemQty PD release Reschdate CC CH Date Exc Net Order Value ID 46353228 190 F 10000019 RA LALLI NB 11/6/2006 109-0718-13-101 PASSACINGHIA AK7 6/13/2013 6/13/2013 44 42 10 2 1/2/2010 11/21/2012 CH 3/9/2010 10 11,396.00 14635 46353228 190 F 10000019 RA LALLI NB 11/6/2006 109-0718-13-101 PASSACINGHIA AK7 6/13/2013 6/13/2013 44 42 14 0 1/2/2010 CH 3/9/2010 11,396.00 14633 46353228 190 F 10000019 RA LALLI NB 11/6/2006 109-0718-13-101 PASSACINGHIA AK7 6/13/2013 6/13/2013 44 42 18 0 1/2/2010 CH 3/9/2010 11,396.00 14634 then i run this query SELECT q1.PurchDoc, q1.Item, q1.Rel, q1.Vendor, q1.[Vendor name], q1. [PO Date], q1.Material, q1.[Short Text], q1.MRPCn, q1.[Del Date], q1. [PO Quantity], q1.[GR Quantity], ([q1]![PO Quantity]-[q1]![GR Quantity]) AS [Open Qty], q1.[PD release], IIf(IsNull([q1]! [Exc]),IIf(IsNull([q2]![Exc]),IIf(IsNull([q3]![Exc]),Null,[q3]![Exc]), [q2]![Exc]),[q1]![Exc]) AS [Exc Code], IIf(IsNull([q1]! [Reschdate]),IIf(IsNull([q2]![Reschdate]),IIf(IsNull([q3]! [Reschdate]),Null,[q3]![Reschdate]),[q2]![Reschdate]),[q1]! [Reschdate]) AS [Resch Date], q2.[AB Date], q3.[CH Date], q1.[Net Order Value], (([q1]![Net Order Value]/[q1]![PO Quantity])*[Open Qty]) AS [Open Value], Date() AS [Extraction Date] FROM (q1 LEFT JOIN q2 ON (q1.Item=q2.Item) AND (q1.PurchDoc=q2.PurchDoc)) LEFT JOIN q3 ON (q2.Item=q3.Item) AND (q2.PurchDoc=q3.PurchDoc) GROUP BY q1.PurchDoc, q1.Item, q1.Rel, q1.Vendor, q1.[Vendor name], q1. [PO Date], q1.Material, q1.[Short Text], q1.MRPCn, q1.[Del Date], q1. [PO Quantity], q1.[GR Quantity], q1.[PD release], q2.[AB Date], q3.[CH Date], q1.[Net Order Value], q1.Exc, q2.Exc, q3.Exc, q1.Reschdate, q2.Reschdate, q3.Reschdate ORDER BY q1.Vendor; I am getting this duplicate value PurchDoc Item Rel Vendor Vendor name PO Date Material Short Text MRPCn Del Date PO Quantity GR Quantity Open Qty PD release Exc Code Resch Date AB Date CH Date Net Order Value Open Value Extraction Date 46353228 190 F 10000019 RA LALLI 11/6/2006 109-0718-13-101 PASSACINGHIA AK7 6/13/2013 44 42 2 1/2/2010 10 11/21/2012 3/9/2010 3/9/2010 $11,396.00 $518.00 3/19/2010 46353228 190 F 10000019 RA LALLI 11/6/2006 109-0718-13-101 PASSACINGHIA AK7 6/13/2013 44 42 2 1/2/2010 10 11/21/2012 3/9/2010 3/9/2010 $11,396.00 $518.00 3/19/2010 I should only get on entry not two. Any ideas?
|
0 |
|
pat67
3/19/2010 8:14:58 PM
access.queries
6343 articles. 
1 followers.
12 Replies
1209 Views
I would suspect that some of the fields you are grouping by and not showing have a different value for the rows returned. Try displaying those fields. You can determine if you need them in the query or not. John Spencer Access MVP 2002-2005, 2007-2010 The Hilltop Institute University of Maryland Baltimore County pat67 wrote: > Hi, I am running a query based on 3 queries > > then i run this query > > SELECT q1.PurchDoc, q1.Item, q1.Rel, q1.Vendor, q1.[Vendor name], q1. > [PO Date], q1.Material, q1.[Short Text], q1.MRPCn, q1.[Del Date], q1. > [PO Quantity], q1.[GR Quantity], ([q1]![PO Quantity]-[q1]![GR > Quantity]) AS [Open Qty], q1.[PD release], IIf(IsNull([q1]! > [Exc]),IIf(IsNull([q2]![Exc]),IIf(IsNull([q3]![Exc]),Null,[q3]![Exc]), > [q2]![Exc]),[q1]![Exc]) AS [Exc Code], IIf(IsNull([q1]! > [Reschdate]),IIf(IsNull([q2]![Reschdate]),IIf(IsNull([q3]! > [Reschdate]),Null,[q3]![Reschdate]),[q2]![Reschdate]),[q1]! > [Reschdate]) AS [Resch Date], q2.[AB Date], q3.[CH Date], q1.[Net > Order Value], (([q1]![Net Order Value]/[q1]![PO Quantity])*[Open Qty]) > AS [Open Value], Date() AS [Extraction Date] > FROM (q1 LEFT JOIN q2 ON (q1.Item=q2.Item) AND > (q1.PurchDoc=q2.PurchDoc)) LEFT JOIN q3 ON (q2.Item=q3.Item) AND > (q2.PurchDoc=q3.PurchDoc) > GROUP BY q1.PurchDoc, q1.Item, q1.Rel, q1.Vendor, q1.[Vendor name], q1. > [PO Date], q1.Material, q1.[Short Text], q1.MRPCn, q1.[Del Date], q1. > [PO Quantity], q1.[GR Quantity], q1.[PD release], q2.[AB Date], q3.[CH > Date], q1.[Net Order Value], q1.Exc, q2.Exc, q3.Exc, q1.Reschdate, > q2.Reschdate, q3.Reschdate > ORDER BY q1.Vendor; > > > I am getting this duplicate value > > Any ideas?
|
|
0 |
|
John
3/20/2010 1:03:12 PM
On Mar 20, 9:03=A0am, John Spencer <spen...@chpdm.edu> wrote: > I would suspect that some of the fields you are grouping by and not showi= ng > have a different value for the rows returned. =A0Try displaying those fie= lds. > You can determine if you need them in the query or not. > > John Spencer > Access MVP 2002-2005, 2007-2010 > The Hilltop Institute > University of Maryland Baltimore County > > > > pat67 wrote: > > Hi, I am running a query based on 3 queries > > > then i run this query > > > SELECT q1.PurchDoc, q1.Item, q1.Rel, q1.Vendor, q1.[Vendor name], q1. > > [PO Date], q1.Material, q1.[Short Text], q1.MRPCn, q1.[Del Date], q1. > > [PO Quantity], q1.[GR Quantity], ([q1]![PO Quantity]-[q1]![GR > > Quantity]) AS [Open Qty], q1.[PD release], IIf(IsNull([q1]! > > [Exc]),IIf(IsNull([q2]![Exc]),IIf(IsNull([q3]![Exc]),Null,[q3]![Exc]), > > [q2]![Exc]),[q1]![Exc]) AS [Exc Code], IIf(IsNull([q1]! > > [Reschdate]),IIf(IsNull([q2]![Reschdate]),IIf(IsNull([q3]! > > [Reschdate]),Null,[q3]![Reschdate]),[q2]![Reschdate]),[q1]! > > [Reschdate]) AS [Resch Date], q2.[AB Date], q3.[CH Date], q1.[Net > > Order Value], (([q1]![Net Order Value]/[q1]![PO Quantity])*[Open Qty]) > > AS [Open Value], Date() AS [Extraction Date] > > FROM (q1 LEFT JOIN q2 ON (q1.Item=3Dq2.Item) AND > > (q1.PurchDoc=3Dq2.PurchDoc)) LEFT JOIN q3 ON (q2.Item=3Dq3.Item) AND > > (q2.PurchDoc=3Dq3.PurchDoc) > > GROUP BY q1.PurchDoc, q1.Item, q1.Rel, q1.Vendor, q1.[Vendor name], q1. > > [PO Date], q1.Material, q1.[Short Text], q1.MRPCn, q1.[Del Date], q1. > > [PO Quantity], q1.[GR Quantity], q1.[PD release], q2.[AB Date], q3.[CH > > Date], q1.[Net Order Value], q1.Exc, q2.Exc, q3.Exc, q1.Reschdate, > > q2.Reschdate, q3.Reschdate > > ORDER BY q1.Vendor; > > > I am getting this duplicate value > > > Any ideas?- Hide quoted text - > > - Show quoted text - the problem is that the fields i am grouping and not showing are only there because when i did not include them, the query errored. because of the 2 iif statements
|
|
0 |
|
pat67
3/20/2010 3:48:15 PM
On Mar 20, 11:48=A0am, pat67 <pbus...@comcast.net> wrote: > On Mar 20, 9:03=A0am, John Spencer <spen...@chpdm.edu> wrote: > > > > > > > I would suspect that some of the fields you are grouping by and not sho= wing > > have a different value for the rows returned. =A0Try displaying those f= ields. > > You can determine if you need them in the query or not. > > > John Spencer > > Access MVP 2002-2005, 2007-2010 > > The Hilltop Institute > > University of Maryland Baltimore County > > > pat67 wrote: > > > Hi, I am running a query based on 3 queries > > > > then i run this query > > > > SELECT q1.PurchDoc, q1.Item, q1.Rel, q1.Vendor, q1.[Vendor name], q1. > > > [PO Date], q1.Material, q1.[Short Text], q1.MRPCn, q1.[Del Date], q1. > > > [PO Quantity], q1.[GR Quantity], ([q1]![PO Quantity]-[q1]![GR > > > Quantity]) AS [Open Qty], q1.[PD release], IIf(IsNull([q1]! > > > [Exc]),IIf(IsNull([q2]![Exc]),IIf(IsNull([q3]![Exc]),Null,[q3]![Exc])= , > > > [q2]![Exc]),[q1]![Exc]) AS [Exc Code], IIf(IsNull([q1]! > > > [Reschdate]),IIf(IsNull([q2]![Reschdate]),IIf(IsNull([q3]! > > > [Reschdate]),Null,[q3]![Reschdate]),[q2]![Reschdate]),[q1]! > > > [Reschdate]) AS [Resch Date], q2.[AB Date], q3.[CH Date], q1.[Net > > > Order Value], (([q1]![Net Order Value]/[q1]![PO Quantity])*[Open Qty]= ) > > > AS [Open Value], Date() AS [Extraction Date] > > > FROM (q1 LEFT JOIN q2 ON (q1.Item=3Dq2.Item) AND > > > (q1.PurchDoc=3Dq2.PurchDoc)) LEFT JOIN q3 ON (q2.Item=3Dq3.Item) AND > > > (q2.PurchDoc=3Dq3.PurchDoc) > > > GROUP BY q1.PurchDoc, q1.Item, q1.Rel, q1.Vendor, q1.[Vendor name], q= 1. > > > [PO Date], q1.Material, q1.[Short Text], q1.MRPCn, q1.[Del Date], q1. > > > [PO Quantity], q1.[GR Quantity], q1.[PD release], q2.[AB Date], q3.[C= H > > > Date], q1.[Net Order Value], q1.Exc, q2.Exc, q3.Exc, q1.Reschdate, > > > q2.Reschdate, q3.Reschdate > > > ORDER BY q1.Vendor; > > > > I am getting this duplicate value > > > > Any ideas?- Hide quoted text - > > > - Show quoted text - > > the problem is that the fields i am grouping and not showing are only > there because when i did not include them, the query errored. because > of the 2 iif statements- Hide quoted text - > > - Show quoted text - i should tell you that when i take those iif statements out, the data is fine. the problem is that i need to see a resch. date and an exc code.
|
|
0 |
|
pat67
3/20/2010 3:50:09 PM
So, group by the expression you are using instead of by the fields. SELECT q1.PurchDoc, q1.Item, q1.Rel, q1.Vendor, q1.[Vendor name] , q1.[PO Date], q1.Material, q1.[Short Text], q1.MRPCn, q1.[Del Date] , q1.[PO Quantity], q1.[GR Quantity] , ([q1]![PO Quantity]-[q1]![GR Quantity]) AS [Open Qty], q1.[PD release], IIf(IsNull([q1]![Exc]),IIf(IsNull([q2]![Exc]),IIf(IsNull([q3]![Exc]),Null,[q3]![Exc]), [q2]![Exc]),[q1]![Exc]) AS [Exc Code] , IIf(IsNull([q1]![Reschdate]),IIf(IsNull([q2]![Reschdate]),IIf(IsNull([q3]! [Reschdate]),Null,[q3]![Reschdate]),[q2]![Reschdate]),[q1]! [Reschdate]) AS [Resch Date] , q2.[AB Date], q3.[CH Date], q1.[NetOrder Value] , [q1]![Net Order Value]/[q1]![PO Quantity]*[Open Qty] AS [Open Value] , Date() AS [Extraction Date] FROM (q1 LEFT JOIN q2 ON (q1.Item=q2.Item) AND (q1.PurchDoc=q2.PurchDoc)) LEFT JOIN q3 ON (q2.Item=q3.Item) AND (q2.PurchDoc=q3.PurchDoc) GROUP BY q1.PurchDoc, q1.Item, q1.Rel, q1.Vendor, q1.[Vendor name], q1. [PO Date], q1.Material, q1.[Short Text], q1.MRPCn, q1.[Del Date], q1. [PO Quantity], q1.[GR Quantity], q1.[PD release], q2.[AB Date], q3.[CH Date], q1.[Net Order Value] , IIf(IsNull([q1]![Exc]),IIf(IsNull([q2]![Exc]), IIf(IsNull([q3]![Exc]),Null,[q3]![Exc]), [q2]![Exc]),[q1]![Exc]) , IIf(IsNull([q1]![Reschdate]),IIf(IsNull([q2]![Reschdate]),IIf(IsNull([q3]! [Reschdate]),Null,[q3]![Reschdate]),[q2]![Reschdate]),[q1]! [Reschdate]) ORDER BY q1.Vendor; Also, you could use the NZ function and simplify your expressions significantly. NZ([q1]![Exc],NZ([q2]![Exc],[q3]![Exc])) as [Exc Code] NZ([q1]![Reschdate],NZ([q2]![Reschdate],[q3]![Reschdate])) as [Resch Date] John Spencer Access MVP 2002-2005, 2007-2010 The Hilltop Institute University of Maryland Baltimore County pat67 wrote: > On Mar 20, 11:48 am, pat67 <pbus...@comcast.net> wrote: >> On Mar 20, 9:03 am, John Spencer <spen...@chpdm.edu> wrote: >>> I would suspect that some of the fields you are grouping by and not showing >>> have a different value for the rows returned. Try displaying those fields. >>> You can determine if you need them in the query or not. >>> John Spencer >>> Access MVP 2002-2005, 2007-2010 >>> The Hilltop Institute >>> University of Maryland Baltimore County >>> pat67 wrote: >rrored. because >> of the 2 iif statements- Hide quoted text - >> >> - Show quoted text - > > i should tell you that when i take those iif statements out, the data > is fine. the problem is that i need to see a resch. date and an exc > code.
|
|
0 |
|
John
3/20/2010 4:29:30 PM
On Mar 20, 12:29=A0pm, John Spencer <spen...@chpdm.edu> wrote: > So, group by the expression you are using instead of by the fields. > > SELECT q1.PurchDoc, q1.Item, q1.Rel, q1.Vendor, q1.[Vendor name] > , q1.[PO Date], q1.Material, q1.[Short Text], q1.MRPCn, q1.[Del Date] > , q1.[PO Quantity], q1.[GR Quantity] > , ([q1]![PO Quantity]-[q1]![GR Quantity]) AS [Open Qty], q1.[PD release], > > IIf(IsNull([q1]![Exc]),IIf(IsNull([q2]![Exc]),IIf(IsNull([q3]![Exc]),Null= ,[=ADq3]![Exc]), > [q2]![Exc]),[q1]![Exc]) AS [Exc Code] > > , IIf(IsNull([q1]![Reschdate]),IIf(IsNull([q2]![Reschdate]),IIf(IsNull([q= 3]! > [Reschdate]),Null,[q3]![Reschdate]),[q2]![Reschdate]),[q1]! > [Reschdate]) AS [Resch Date] > > , q2.[AB Date], q3.[CH Date], q1.[NetOrder Value] > , [q1]![Net Order Value]/[q1]![PO Quantity]*[Open Qty] AS [Open Value] > , Date() AS [Extraction Date] > FROM (q1 LEFT JOIN q2 ON (q1.Item=3Dq2.Item) AND > (q1.PurchDoc=3Dq2.PurchDoc)) LEFT JOIN q3 ON (q2.Item=3Dq3.Item) AND > (q2.PurchDoc=3Dq3.PurchDoc) > > GROUP BY q1.PurchDoc, q1.Item, q1.Rel, q1.Vendor, q1.[Vendor name], q1. > [PO Date], q1.Material, q1.[Short Text], q1.MRPCn, q1.[Del Date], q1. > [PO Quantity], q1.[GR Quantity], q1.[PD release], q2.[AB Date], q3.[CH > Date], q1.[Net Order Value] > , IIf(IsNull([q1]![Exc]),IIf(IsNull([q2]![Exc]), > IIf(IsNull([q3]![Exc]),Null,[q3]![Exc]), [q2]![Exc]),[q1]![Exc]) > > , IIf(IsNull([q1]![Reschdate]),IIf(IsNull([q2]![Reschdate]),IIf(IsNull([q= 3]! > [Reschdate]),Null,[q3]![Reschdate]),[q2]![Reschdate]),[q1]! > [Reschdate]) > ORDER BY q1.Vendor; > > Also, you could use the NZ function and simplify your expressions signifi= cantly. > NZ([q1]![Exc],NZ([q2]![Exc],[q3]![Exc])) as [Exc Code] > NZ([q1]![Reschdate],NZ([q2]![Reschdate],[q3]![Reschdate])) as [Resch Date= ] > > John Spencer > Access MVP 2002-2005, 2007-2010 > The Hilltop Institute > University of Maryland Baltimore County > > > > pat67 wrote: > > On Mar 20, 11:48 am, pat67 <pbus...@comcast.net> wrote: > >> On Mar 20, 9:03 am, John Spencer <spen...@chpdm.edu> wrote: > >>> I would suspect that some of the fields you are grouping by and not s= howing > >>> have a different value for the rows returned. =A0Try displaying those= fields. > >>> You can determine if you need them in the query or not. > >>> John Spencer > >>> Access MVP 2002-2005, 2007-2010 > >>> The Hilltop Institute > >>> University of Maryland Baltimore County > >>> pat67 wrote: > >rrored. because > >> of the 2 iif statements- Hide quoted text - > > >> - Show quoted text - > > > i should tell you that when i take those iif statements out, the data > > is fine. the problem is that i need to see a resch. date and an exc > > code.- Hide quoted text - > > - Show quoted text - ok. let me try that
|
|
0 |
|
pat67
3/20/2010 6:15:13 PM
On Mar 20, 12:29=A0pm, John Spencer <spen...@chpdm.edu> wrote: > So, group by the expression you are using instead of by the fields. > > SELECT q1.PurchDoc, q1.Item, q1.Rel, q1.Vendor, q1.[Vendor name] > , q1.[PO Date], q1.Material, q1.[Short Text], q1.MRPCn, q1.[Del Date] > , q1.[PO Quantity], q1.[GR Quantity] > , ([q1]![PO Quantity]-[q1]![GR Quantity]) AS [Open Qty], q1.[PD release], > > IIf(IsNull([q1]![Exc]),IIf(IsNull([q2]![Exc]),IIf(IsNull([q3]![Exc]),Null= ,[=ADq3]![Exc]), > [q2]![Exc]),[q1]![Exc]) AS [Exc Code] > > , IIf(IsNull([q1]![Reschdate]),IIf(IsNull([q2]![Reschdate]),IIf(IsNull([q= 3]! > [Reschdate]),Null,[q3]![Reschdate]),[q2]![Reschdate]),[q1]! > [Reschdate]) AS [Resch Date] > > , q2.[AB Date], q3.[CH Date], q1.[NetOrder Value] > , [q1]![Net Order Value]/[q1]![PO Quantity]*[Open Qty] AS [Open Value] > , Date() AS [Extraction Date] > FROM (q1 LEFT JOIN q2 ON (q1.Item=3Dq2.Item) AND > (q1.PurchDoc=3Dq2.PurchDoc)) LEFT JOIN q3 ON (q2.Item=3Dq3.Item) AND > (q2.PurchDoc=3Dq3.PurchDoc) > > GROUP BY q1.PurchDoc, q1.Item, q1.Rel, q1.Vendor, q1.[Vendor name], q1. > [PO Date], q1.Material, q1.[Short Text], q1.MRPCn, q1.[Del Date], q1. > [PO Quantity], q1.[GR Quantity], q1.[PD release], q2.[AB Date], q3.[CH > Date], q1.[Net Order Value] > , IIf(IsNull([q1]![Exc]),IIf(IsNull([q2]![Exc]), > IIf(IsNull([q3]![Exc]),Null,[q3]![Exc]), [q2]![Exc]),[q1]![Exc]) > > , IIf(IsNull([q1]![Reschdate]),IIf(IsNull([q2]![Reschdate]),IIf(IsNull([q= 3]! > [Reschdate]),Null,[q3]![Reschdate]),[q2]![Reschdate]),[q1]! > [Reschdate]) > ORDER BY q1.Vendor; > > Also, you could use the NZ function and simplify your expressions signifi= cantly. > NZ([q1]![Exc],NZ([q2]![Exc],[q3]![Exc])) as [Exc Code] > NZ([q1]![Reschdate],NZ([q2]![Reschdate],[q3]![Reschdate])) as [Resch Date= ] > > John Spencer > Access MVP 2002-2005, 2007-2010 > The Hilltop Institute > University of Maryland Baltimore County > > > > pat67 wrote: > > On Mar 20, 11:48 am, pat67 <pbus...@comcast.net> wrote: > >> On Mar 20, 9:03 am, John Spencer <spen...@chpdm.edu> wrote: > >>> I would suspect that some of the fields you are grouping by and not s= howing > >>> have a different value for the rows returned. =A0Try displaying those= fields. > >>> You can determine if you need them in the query or not. > >>> John Spencer > >>> Access MVP 2002-2005, 2007-2010 > >>> The Hilltop Institute > >>> University of Maryland Baltimore County > >>> pat67 wrote: > >rrored. because > >> of the 2 iif statements- Hide quoted text - > > >> - Show quoted text - > > > i should tell you that when i take those iif statements out, the data > > is fine. the problem is that i need to see a resch. date and an exc > > code.- Hide quoted text - > > - Show quoted text - I am getting and error that says invaling use of a . ! or () in the IIf(IsNull([q1]![Reschdate]),IIf(IsNull([q2]! [Reschdate]),IIf(IsNull([q3]! [Reschdate]),Null,[q3]![Reschdate]),[q2]![Reschdate]),[q1]! [Reschdate]) AS [Resch Date] statement i can't seem to find it
|
|
0 |
|
pat67
3/20/2010 6:19:53 PM
On Mar 20, 2:19=A0pm, pat67 <pbus...@comcast.net> wrote: > On Mar 20, 12:29=A0pm, John Spencer <spen...@chpdm.edu> wrote: > > > > > > > So, group by the expression you are using instead of by the fields. > > > SELECT q1.PurchDoc, q1.Item, q1.Rel, q1.Vendor, q1.[Vendor name] > > , q1.[PO Date], q1.Material, q1.[Short Text], q1.MRPCn, q1.[Del Date] > > , q1.[PO Quantity], q1.[GR Quantity] > > , ([q1]![PO Quantity]-[q1]![GR Quantity]) AS [Open Qty], q1.[PD release= ], > > > IIf(IsNull([q1]![Exc]),IIf(IsNull([q2]![Exc]),IIf(IsNull([q3]![Exc]),Nu= ll,[=AD=ADq3]![Exc]), > > [q2]![Exc]),[q1]![Exc]) AS [Exc Code] > > > , IIf(IsNull([q1]![Reschdate]),IIf(IsNull([q2]![Reschdate]),IIf(IsNull(= [q3]! > > [Reschdate]),Null,[q3]![Reschdate]),[q2]![Reschdate]),[q1]! > > [Reschdate]) AS [Resch Date] > > > , q2.[AB Date], q3.[CH Date], q1.[NetOrder Value] > > , [q1]![Net Order Value]/[q1]![PO Quantity]*[Open Qty] AS [Open Value] > > , Date() AS [Extraction Date] > > FROM (q1 LEFT JOIN q2 ON (q1.Item=3Dq2.Item) AND > > (q1.PurchDoc=3Dq2.PurchDoc)) LEFT JOIN q3 ON (q2.Item=3Dq3.Item) AND > > (q2.PurchDoc=3Dq3.PurchDoc) > > > GROUP BY q1.PurchDoc, q1.Item, q1.Rel, q1.Vendor, q1.[Vendor name], q1. > > [PO Date], q1.Material, q1.[Short Text], q1.MRPCn, q1.[Del Date], q1. > > [PO Quantity], q1.[GR Quantity], q1.[PD release], q2.[AB Date], q3.[CH > > Date], q1.[Net Order Value] > > , IIf(IsNull([q1]![Exc]),IIf(IsNull([q2]![Exc]), > > IIf(IsNull([q3]![Exc]),Null,[q3]![Exc]), [q2]![Exc]),[q1]![Exc]) > > > , IIf(IsNull([q1]![Reschdate]),IIf(IsNull([q2]![Reschdate]),IIf(IsNull(= [q3]! > > [Reschdate]),Null,[q3]![Reschdate]),[q2]![Reschdate]),[q1]! > > [Reschdate]) > > ORDER BY q1.Vendor; > > > Also, you could use the NZ function and simplify your expressions signi= ficantly. > > NZ([q1]![Exc],NZ([q2]![Exc],[q3]![Exc])) as [Exc Code] > > NZ([q1]![Reschdate],NZ([q2]![Reschdate],[q3]![Reschdate])) as [Resch Da= te] > > > John Spencer > > Access MVP 2002-2005, 2007-2010 > > The Hilltop Institute > > University of Maryland Baltimore County > > > pat67 wrote: > > > On Mar 20, 11:48 am, pat67 <pbus...@comcast.net> wrote: > > >> On Mar 20, 9:03 am, John Spencer <spen...@chpdm.edu> wrote: > > >>> I would suspect that some of the fields you are grouping by and not= showing > > >>> have a different value for the rows returned. =A0Try displaying tho= se fields. > > >>> You can determine if you need them in the query or not. > > >>> John Spencer > > >>> Access MVP 2002-2005, 2007-2010 > > >>> The Hilltop Institute > > >>> University of Maryland Baltimore County > > >>> pat67 wrote: > > >rrored. because > > >> of the 2 iif statements- Hide quoted text - > > > >> - Show quoted text - > > > > i should tell you that when i take those iif statements out, the data > > > is fine. the problem is that i need to see a resch. date and an exc > > > code.- Hide quoted text - > > > - Show quoted text - > > I am getting and error that says invaling use of a . ! or () in the > IIf(IsNull([q1]![Reschdate]),IIf(IsNull([q2]! > [Reschdate]),IIf(IsNull([q3]! > [Reschdate]),Null,[q3]![Reschdate]),[q2]![Reschdate]),[q1]! > [Reschdate]) AS [Resch Date] =A0 statement > > i can't seem to find it- Hide quoted text - > > - Show quoted text - Ok. I fixed where there were spaces. now I am getting an error saying "you tried to execute a query that does not include the expression 'IIf(IsNull([q1]![Exc]),IIf(IsNull([q2]![Exc]),IIf(IsNull([q3]! [Exc]),Null,[=ADq3]![Exc]),[q2]![Exc]),[q1]![Exc]) AS [Exc Code] ' as part of the aggregate function. any suggestions?
|
|
0 |
|
pat67
3/20/2010 6:58:12 PM
As a guess you should not have the words "AS [Exc Code]" in the Group By clause. If something does not work (especially if you have an syntax error) it helps to post the SQL statement by copying and pasting it. John Spencer Access MVP 2002-2005, 2007-2010 The Hilltop Institute University of Maryland Baltimore County pat67 wrote: > On Mar 20, 2:19 pm, pat67 <pbus...@comcast.net> wrote: >> On Mar 20, 12:29 pm, John Spencer <spen...@chpdm.edu> wrote:
|
|
0 |
|
John
3/20/2010 7:30:46 PM
On Mar 20, 3:30=A0pm, John Spencer <spen...@chpdm.edu> wrote: > As a guess you should not have the words "AS [Exc Code]" in the Group By > clause. =A0If something does not work (especially if you have an syntax e= rror) > it helps to post the SQL statement by copying and pasting it. > > John Spencer > Access MVP 2002-2005, 2007-2010 > The Hilltop Institute > University of Maryland Baltimore County > > > > pat67 wrote: > > On Mar 20, 2:19 pm, pat67 <pbus...@comcast.net> wrote: > >> On Mar 20, 12:29 pm, John Spencer <spen...@chpdm.edu> wrote:- Hide quo= ted text - > > - Show quoted text - i used the sql you gave me above.
|
|
0 |
|
pat67
3/22/2010 1:54:52 PM
If it doesn't work then I am stumped as to the cause of the problem. I don't understand why you are using a totals query at all since I don't see any summing, averaging, minimum, or maximum. Why not use something like the following: SELECT DISTINCT q1.PurchDoc, q1.Item, q1.Rel, q1.Vendor, q1.[Vendor name] , q1.[PO Date], q1.Material, q1.[Short Text], q1.MRPCn, q1.[Del Date] , q1.[PO Quantity], q1.[GR Quantity] , ([q1]![PO Quantity]-[q1]![GR Quantity]) AS [Open Qty], q1.[PD release], , NZ([q1]![Exc],NZ([q2]![Exc],[q3]![Exc])) as [Exc Code] , NZ([q1]![Reschdate],NZ([q2]![Reschdate],[q3]![Reschdate])) as [Resch Date] , q2.[AB Date], q3.[CH Date], q1.[NetOrder Value] , [q1]![Net Order Value]/[q1]![PO Quantity]*[Open Qty] AS [Open Value] , Date() AS [Extraction Date] FROM (q1 LEFT JOIN q2 ON (q1.Item=q2.Item) AND (q1.PurchDoc=q2.PurchDoc)) LEFT JOIN q3 ON (q2.Item=q3.Item) AND (q2.PurchDoc=q3.PurchDoc) ORDER BY q1.Vendor; John Spencer Access MVP 2002-2005, 2007-2010 The Hilltop Institute University of Maryland Baltimore County pat67 wrote:
|
|
0 |
|
John
3/22/2010 2:48:58 PM
On Mar 22, 10:48=A0am, John Spencer <spen...@chpdm.edu> wrote: > If it doesn't work then I am stumped as to the cause of the problem. =A0I= don't > understand why you are using a totals query at all since I don't see any > summing, averaging, minimum, or maximum. > > Why not use something like the following: > > SELECT DISTINCT q1.PurchDoc, q1.Item, q1.Rel, q1.Vendor, q1.[Vendor name] > , q1.[PO Date], q1.Material, q1.[Short Text], q1.MRPCn, q1.[Del Date] > , q1.[PO Quantity], q1.[GR Quantity] > , ([q1]![PO Quantity]-[q1]![GR Quantity]) AS [Open Qty], q1.[PD release], > > , NZ([q1]![Exc],NZ([q2]![Exc],[q3]![Exc])) as [Exc Code] > , NZ([q1]![Reschdate],NZ([q2]![Reschdate],[q3]![Reschdate])) as [Resch Da= te] > > , q2.[AB Date], q3.[CH Date], q1.[NetOrder Value] > , [q1]![Net Order Value]/[q1]![PO Quantity]*[Open Qty] AS [Open Value] > , Date() AS [Extraction Date] > FROM (q1 LEFT JOIN q2 ON (q1.Item=3Dq2.Item) AND > (q1.PurchDoc=3Dq2.PurchDoc)) LEFT JOIN q3 ON (q2.Item=3Dq3.Item) AND > (q2.PurchDoc=3Dq3.PurchDoc) > > ORDER BY q1.Vendor; > > John Spencer > Access MVP 2002-2005, 2007-2010 > The Hilltop Institute > University of Maryland Baltimore County > > > > pat67 wrote:- Hide quoted text - > > - Show quoted text - That last one works, but i still get duplicates. It's hard to explain. When i extract the data into access, the data is in rows i.e. 3 rows or more for every Po and line item. what i am trying to do is take the information specific to each row and put it together with the information that is the same from each row in order to make one row or maybe tow if there are two deliveries or so. the issue is when some data is missing, i get bad info out of access. in essence i am trying to correct the problems we have in our erp system in access. thanks for your help. i will continue to try and figure it out.
|
|
0 |
|
pat67
3/22/2010 4:06:34 PM
If you used SELECT DISTINCT then you should have no duplicate rows. A duplicate would have every field value equal to the corresponding field. If one field in a row is slightly different then there is not a duplicate as far as Distinct is concerned. I hope you can find a solution to your problem. And Null values can cause problems. You could try to force all nulls to some specific value using the NZ function. Perhaps something like the following will help you solve your problem. NZ(MyTable.MyText,"") as MyText Nz(MyTable.MyNumber,0) as MyNumber Nz(MyTable.MyDate,#1/1/1899#) as MyDate Or specifically - NZ(NZ([q1]![Exc],NZ([q2]![Exc],[q3]![Exc])),"") as [Exc Code] John Spencer Access MVP 2002-2005, 2007-2010 The Hilltop Institute University of Maryland Baltimore County pat67 wrote: > On Mar 22, 10:48 am, John Spencer <spen...@chpdm.edu> wrote: >> If it doesn't work then I am stumped as to the cause of the problem. I don't >> understand why you are using a totals query at all since I don't see any >> summing, averaging, minimum, or maximum. >> >> Why not use something like the following: >> >> SELECT DISTINCT q1.PurchDoc, q1.Item, q1.Rel, q1.Vendor, q1.[Vendor name] >> , q1.[PO Date], q1.Material, q1.[Short Text], q1.MRPCn, q1.[Del Date] >> , q1.[PO Quantity], q1.[GR Quantity] >> , ([q1]![PO Quantity]-[q1]![GR Quantity]) AS [Open Qty], q1.[PD release], >> >> , NZ([q1]![Exc],NZ([q2]![Exc],[q3]![Exc])) as [Exc Code] >> , NZ([q1]![Reschdate],NZ([q2]![Reschdate],[q3]![Reschdate])) as [Resch Date] >> >> , q2.[AB Date], q3.[CH Date], q1.[NetOrder Value] >> , [q1]![Net Order Value]/[q1]![PO Quantity]*[Open Qty] AS [Open Value] >> , Date() AS [Extraction Date] >> FROM (q1 LEFT JOIN q2 ON (q1.Item=q2.Item) AND >> (q1.PurchDoc=q2.PurchDoc)) LEFT JOIN q3 ON (q2.Item=q3.Item) AND >> (q2.PurchDoc=q3.PurchDoc) >> >> ORDER BY q1.Vendor; >> >> John Spencer >> Access MVP 2002-2005, 2007-2010 >> The Hilltop Institute >> University of Maryland Baltimore County >> >> >> >> pat67 wrote:- Hide quoted text - >> >> - Show quoted text - > > That last one works, but i still get duplicates. It's hard to explain. > When i extract the data into access, the data is in rows i.e. 3 rows > or more for every Po and line item. what i am trying to do is take the > information specific to each row and put it together with the > information that is the same from each row in order to make one row or > maybe tow if there are two deliveries or so. the issue is when some > data is missing, i get bad info out of access. in essence i am trying > to correct the problems we have in our erp system in access. thanks > for your help. i will continue to try and figure it out.
|
|
0 |
|
John
3/22/2010 6:18:22 PM
Similar Artilces:
The messaging interface has returned an unknown error
I just upgraded to Office XP and it give me this message any time I try to add a calendar item: The messaging interface has returned an unknown error. I can also not send or receive email. I have downloaded the service packages, and installed them. Now, I can't even get Outlook to open completely. I see my folders, but it just stalls on opening and doesn't allow me to click on anything. Can anyone help? ...
internal support function returned an error
Hello, For some reason, every time I try to send a particular message I get "An internal support function returned an error" warning. Any idea what I can do to fix this problem? -- Thanks, Bob Sorry, I forgot to indicate I am using Outlook 2007 on XPSP2. "BBran" <bbran@nowhere.com> wrote in message news:elIab3XhJHA.760@TK2MSFTNGP03.phx.gbl... > Hello, > > For some reason, every time I try to send a particular message I get "An > internal support function returned an error" warning. Any idea what I can > do to fix this problem? &...
Credit Cards 'Technical Error'
RMS V 1.3 - Since we have made our switch to RMS V 1.3, we have been receiving 'Technical Errors' on transactions. The Technical Error received on 1 transaction will cause a blockade in processing the remaining transactions while additionally assigning the 'Valid' transactions with a Technical Error. When the issue was brought to Microsoft, they kicked us some scripts to address the error after the fact. New West developed their new Card Vault program because of our situation. Microsoft does not know what causes the error, their suggestion was to look to New West for an ...
Emailing from a query 07-06-07
I need to send emails from an access database, but I want to send them from a query so that I only email the cetian people. Access says that it can't send an email from a query. Any ideas? Sorry, I figured out the problem. "William5271" wrote: > I need to send emails from an access database, but I want to send them from a > query so that I only email the cetian people. Access says that it can't send > an email from a query. > > Any ideas? On Wed, 12 Sep 2007 10:52:03 -0700, lydia wrote: > So how did you do it. I'd love to know. > > &...
Error message when searching for an item not in a listbox
I use a listbox on a userform to display a chemical database. I use the following code to search for an item (entered into txtFindChemical). The code finds the chemical in the database and populates text boxes with the chemicals information Private Sub cmdFindChemical_Click() Dim Chemical As Variant lstChemicalDatabase.Value = txtFindChemical.Value Chemical = Sheets("Chemicals").Cells.Find(What:=lstChemicalDatabase.Value, LookIn:=xlFormulas, _ LookAt:=1, SearchDirection:=xlNext, MatchCase:=False).Offset(0, 0).Address With Sheet...
error on outlook express #4
when I send messages in outlook express like letters and small attachments it work fine , but when sending attachments like videos I get the message (Your server has unexpectedly terminated the connection. Possible causes for this include server problems, network problems, or a long period of inactivity. Subject 'toot tone', Account: +++++++++++++++, Server: ++++++++++++, Protocol: SMTP, Port: 25, Secure(SSL): No, Error Number: 0x800CCC0F) ...
Querying a dataset filled from XML file
I have a simple application that uses an XML file to store it's data. That means I don't have a seperate database. In order to read the data I created a DataSet that reads my XML file with the .ReadXml() method. 1. I want to manipulate the resulting dataset, preferably with SQL queries. But the documentation states that I need to have a data connection to do this. But if I am only using an XML file, how do I do this without a data connection? 2. When I fill the dataset I have no trouble reading the column names, but how do I read the attributes? For instance, this is a portion...
Need help with query summing hours
Tring to get total worked hours and total maintenance hours summary. I have a table with the clock hours WorkID WorkDate WorkEmployeeID WorkHours And have a maintenace table MaintID MaintDate MaintEmployeeID MaintHours Needing help combining the information to get summary something like this.. Date - Employee - Clock Hours - Maintenance Hours 1/1/10 Joe Billy Bob 8 6 1/1/10 Billy Joe Bob 8 7 Whay type of query do I need to build, any help would be great. Thanks -- Message posted via http://www.accessmo...
eConnect Error with Tax on SOP Invoices
I'm running eConnect 9.0.2.0. I get an error (see below) when trying to process the below XML file. Everything looks fine to me in the fine. I believe it is something to do with Tax. When I take out the tax amounts it is all fine. Can anyone help? Regards Konrad eConnect Error: Procedure taSopHdrIvcInsert Error Number = 866 Error Description = Cannot pass in tax and markdown amounts when creating taxes on the fly Node Identifier Parameters: taSopHdrIvcInsert SOPNUMBE=103999 SOPTYPE=3 Related Error Code Parameters for Node : taSopHdrIvcInsert ...
how to set up a query to show only user based selections
I have created a table with data types yes/no. I am trying to create a query that ONLY shows when the user has selected yes. I am trying to create a report that only shows the options selected versus showing all of the options. The users select this from a form that has option buttons. Any help will be greatlt appreciated! Submitted via EggHeadCafe - Software Developer Portal of Choice WMI - VB 6.0 Windows Management Instrumentation http://www.eggheadcafe.com/tutorials/aspnet/5fb2b0ca-919a-406b-8566-a9d07f76c868/wmi--vb-60-windows-mana.aspx Yes is stored as a -1 (minus one) so ...
passing parameters from a from to a query
Hi, I'm trying to pass params from my form to a query. In the query I ref the form value in the criteria: [form]![id] I've tested my query directly and it works perfectly, however when I call my query from my form (open at time of call), I am still prompted to supply the param. I have tried encasing my form value in quotes, but of course then it is seen as a string literal. Is there a simple way to tell the query that [form]![ID] isn't a prompt? (I have looked at a zillion posting here but haven't found one for this answer) Thank you!!! granola911...
Error when clicking on 'Items' in GP 10
I'm in the process of testing a GP 10 conversion, and if I click on the 'Items' option in the 'Inventory' category on the Navigation Pane, the following error is displayed: [Microsoft][SQL Native Client][SQL Server]Cannot insert the value NULL into column 'BASEUOFM', table 'tempdb.dbo.##0015859'; column does not allow nulls. INSERT fails. Has anyone seen this before? -- Bud Cool Microsoft Certified Business Management Solutions Specialist, GP 9.0 Financials Currently supporting: GP 9.0, SP2 Yes, someone else posted this same (or very similar) quest...
Error handler
I used the following code to open a file. 2 users at 2 different sites have different path to the same excel file. i used an error handler to overcome this. but it seems not working a run time error 1004 appears when it tries to open the file from the first path and in fact the correct path is the secon one Workbooks.Open FileName:= _ "I:\_Other_Site\DIESE HARNESS OFFSHORING\00 HARNESS DESIGN ROUTING\02 Work Request Generator\Harness Routing WR template vE.xls" _ , UpdateLinks:=0 On Error Resume Next Workbooks.Open FileName:= _ "...
URGENT error converting a lead to a contact
I am getting an error converting a lead to a contact. Does anyone know what the default datamappings should be between a lead and a contact. I am wondering if that is the problem. The error is Microsoft CRM Platform Error Report: Error Description: An unexpected error occurred. Error Details: Object Type was not provided on attribute 'ownerid' Error Number: 0x80040216 Source File: D:\crm\Build\3297 \src\platform\include\OMCommon\CrmPropertyBagUtil.inl Line Number: 457 Date: 07-29-2004 Time: 9:32:16 AM Type: Platform Error (2) Server: amisql Query String: /SFA/le...
Server Error 403-Forbidden: Access Denied
Not sure if this is the right place or not. Went to microsoft update and got the following message Server Error 403-Forbidden: Access Denied You do not have permission to view this directory or page using the credentials you supplied. OS is WinXP pro/SP3. Is Automatic Updates enabled? If not, can you enable it, reboot & find it still enabled? What anti-virus application or security suite is installed and is your subscription current? What anti-spyware applications (other than Defender)? What third-party firewall (if any)? Has a(another) Norton or McAfee applicatio...
Conditional formatting error
I have a form with multiple date fields, whenever the date entered is within 6 months of today's date, I have conditional formatting set to show the date in red. Taken From Dialog Box: "Field Value is - Less than - Date()-182.5" There are instances where the formatting is being applied and others where it is not. In some cases on the same form. I don't know where to go to get the form code to past here so help there would also be appreciated. Thanks, Dan Hi. To get the code, open the form in design view, click on one of the date fields, open t...
Outlook ckecks files for errors
Hi, About 50% of the time Outlook thinks it has been closed improperly and it hasn't been. I know for a fact that I closed it using the "X" in the upper right hand corner. This wouldn't be much of a problem except it takes at least 15 mins for Outlook to check the file and it hogs all the resources, I can't even surf the net or open a program. I'd apprecaite any suggestions in helping Outlook realize it isn't crashing. O/S - Windows XP Pro (SP3) Outlook is part of Office 2003 (SP3) 2.4Ghz; 1GB of Ram Thanks Pamala If the startup scanner is ...
Getting an outlook error
I am looking for any help with the following error. The user opens outlook and begins working. if the computer sits Idle for 2-3 minutes the error message "OUTLOOK.exe has detected and error and will shut down. You will need to restart outlook." oputlook then closes down and an error log is created which I can't find. I have reloaded outlook and office Pro and checked all I can think of. I get this same error when I reply or forward a message and try to edit the text. Any suggestions would be appreciated. Ed >-----Original Message----- >I am looking for any help ...
Auto filter Query??
Hi hope this is possible. I would like to run a macro that uses a custom filter to do the following a Date is enetered into j18 (start date) and a date entered into k18 (end date) is it possible to use auto filter to run a custom macro where as the criterias are >= j18 and < k18 How would i accomplish this, any help with some code would be useful i tried the following without much success Sub daterange() Selection.AutoFilter Field:=2, Criteria1:=">=Cell(j18)", Operator:= _ xlAnd, Criteria2:="<Cell(j18)" End Sub HYCH Steve On Mar 3, 3...
Query to return a module
Hello, I have inherited a database which contains complex calculations that are done in 'modules'. They are all under one function called 'returncost'. I would like to display this field on my query but do not how to do this. Can anyone help, please? Thanks, Johanna On Fri, 29 Feb 2008 08:03:00 -0800, Johanna Gronlund <JohannaGronlund@discussions.microsoft.com> wrote: >Hello, > >I have inherited a database which contains complex calculations that are >done in 'modules'. They are all under one function called 'returncost'. I >would ...
Error C4251
I have created a dll that exports a class. If I compile with dynamicly linked MFC everything is OK. But if I compile with static linked MFC, I get this error on every class member that is a CString or CFile class instance. My class has no base class. What do I have to do to get rid of the warning? Can it be safely ignored? All the class members that are flagged with the warning are private members, and are not exposed to any clients in any way. From the help: 'identifier' : class 'type' needs to have dll-interface to be used by clients of class 'type2' A base class or...
CRM webbase License Error.
I have a question of the crm webbase. If i going to the webpage of CRM then i get a License Error. Can someone help me Thanks. -- Bart www.pc-check.nl Computer Reparatie Service. ...
Error Printing SOP (Error in equation '(c) Force Functional')
Hi, I have problem in SOP. while print one of the item getting error (Error in equation '(c) Force Functional'). Rgs saf Hi Saf, I would reckon this is modified report. Please review all equations of calculated field (c) Force Functional in Report Writer. Let us know you go! Cheers, Dennis Araullo, MACS Microsoft Certified Technology Specialist GP,CRM,AX MCITP Installation and Configuration AX 2009 MCITP Database Administrator SQL 2008 Citrix Certified Enterprise Administrator "saifu" wrote: > Hi, > > I have problem ...
Error number: 0x80090008 02-08-10
I've been working with Microsoft for over a month without resolution and it has now come down to reinstalling my operating system which I really would like to avoid. I noticed in December that I was not getting any update notifications so after about 2 weeks I went to the website for updates and when I click express I now get Error number: 0x80090008. This even comes up when I try to update windows defender software. I can't believe that there is no reference to what this error number refers to. Anyone have any suggestions? Thanks. Windows version would be...? W...
Error: Account setup is temporarily unavailable
I use Money 2005 Standard. I followed the screenshot instructions. I disabled the online services for the bank accounts that did not show a password box. I am stuck at the instructions where I am supposed to add the accounts. Instead of prompting me to enter user name/password, I am getting: Account setup is temporarily unavailable. Please try again later. I tried this all morning on Fri. I have 3 bank accounts that need to do this and they all have this same error. On Jul 20, 8:56 am, labear <lab...@discussions.microsoft.com> wrote: > I use Money 2005 Standard. I foll...
I just upgraded to Office XP and it give me this message any time I try to add a calendar item: The messaging interface has returned an unknown error. I can also not send or receive email. I have downloaded the service packages, and installed them. Now, I can't even get Outlook to open completely. I see my folders, but it just stalls on opening and doesn't allow me to click on anything. Can anyone help? ...
internal support function returned an error
Hello, For some reason, every time I try to send a particular message I get "An internal support function returned an error" warning. Any idea what I can do to fix this problem? -- Thanks, Bob Sorry, I forgot to indicate I am using Outlook 2007 on XPSP2. "BBran" <bbran@nowhere.com> wrote in message news:elIab3XhJHA.760@TK2MSFTNGP03.phx.gbl... > Hello, > > For some reason, every time I try to send a particular message I get "An > internal support function returned an error" warning. Any idea what I can > do to fix this problem? &...
Credit Cards 'Technical Error'
RMS V 1.3 - Since we have made our switch to RMS V 1.3, we have been receiving 'Technical Errors' on transactions. The Technical Error received on 1 transaction will cause a blockade in processing the remaining transactions while additionally assigning the 'Valid' transactions with a Technical Error. When the issue was brought to Microsoft, they kicked us some scripts to address the error after the fact. New West developed their new Card Vault program because of our situation. Microsoft does not know what causes the error, their suggestion was to look to New West for an ...
Emailing from a query 07-06-07
I need to send emails from an access database, but I want to send them from a query so that I only email the cetian people. Access says that it can't send an email from a query. Any ideas? Sorry, I figured out the problem. "William5271" wrote: > I need to send emails from an access database, but I want to send them from a > query so that I only email the cetian people. Access says that it can't send > an email from a query. > > Any ideas? On Wed, 12 Sep 2007 10:52:03 -0700, lydia wrote: > So how did you do it. I'd love to know. > > &...
Error message when searching for an item not in a listbox
I use a listbox on a userform to display a chemical database. I use the following code to search for an item (entered into txtFindChemical). The code finds the chemical in the database and populates text boxes with the chemicals information Private Sub cmdFindChemical_Click() Dim Chemical As Variant lstChemicalDatabase.Value = txtFindChemical.Value Chemical = Sheets("Chemicals").Cells.Find(What:=lstChemicalDatabase.Value, LookIn:=xlFormulas, _ LookAt:=1, SearchDirection:=xlNext, MatchCase:=False).Offset(0, 0).Address With Sheet...
error on outlook express #4
when I send messages in outlook express like letters and small attachments it work fine , but when sending attachments like videos I get the message (Your server has unexpectedly terminated the connection. Possible causes for this include server problems, network problems, or a long period of inactivity. Subject 'toot tone', Account: +++++++++++++++, Server: ++++++++++++, Protocol: SMTP, Port: 25, Secure(SSL): No, Error Number: 0x800CCC0F) ...
Querying a dataset filled from XML file
I have a simple application that uses an XML file to store it's data. That means I don't have a seperate database. In order to read the data I created a DataSet that reads my XML file with the .ReadXml() method. 1. I want to manipulate the resulting dataset, preferably with SQL queries. But the documentation states that I need to have a data connection to do this. But if I am only using an XML file, how do I do this without a data connection? 2. When I fill the dataset I have no trouble reading the column names, but how do I read the attributes? For instance, this is a portion...
Need help with query summing hours
Tring to get total worked hours and total maintenance hours summary. I have a table with the clock hours WorkID WorkDate WorkEmployeeID WorkHours And have a maintenace table MaintID MaintDate MaintEmployeeID MaintHours Needing help combining the information to get summary something like this.. Date - Employee - Clock Hours - Maintenance Hours 1/1/10 Joe Billy Bob 8 6 1/1/10 Billy Joe Bob 8 7 Whay type of query do I need to build, any help would be great. Thanks -- Message posted via http://www.accessmo...
eConnect Error with Tax on SOP Invoices
I'm running eConnect 9.0.2.0. I get an error (see below) when trying to process the below XML file. Everything looks fine to me in the fine. I believe it is something to do with Tax. When I take out the tax amounts it is all fine. Can anyone help? Regards Konrad eConnect Error: Procedure taSopHdrIvcInsert Error Number = 866 Error Description = Cannot pass in tax and markdown amounts when creating taxes on the fly Node Identifier Parameters: taSopHdrIvcInsert SOPNUMBE=103999 SOPTYPE=3 Related Error Code Parameters for Node : taSopHdrIvcInsert ...
how to set up a query to show only user based selections
I have created a table with data types yes/no. I am trying to create a query that ONLY shows when the user has selected yes. I am trying to create a report that only shows the options selected versus showing all of the options. The users select this from a form that has option buttons. Any help will be greatlt appreciated! Submitted via EggHeadCafe - Software Developer Portal of Choice WMI - VB 6.0 Windows Management Instrumentation http://www.eggheadcafe.com/tutorials/aspnet/5fb2b0ca-919a-406b-8566-a9d07f76c868/wmi--vb-60-windows-mana.aspx Yes is stored as a -1 (minus one) so ...
passing parameters from a from to a query
Hi, I'm trying to pass params from my form to a query. In the query I ref the form value in the criteria: [form]![id] I've tested my query directly and it works perfectly, however when I call my query from my form (open at time of call), I am still prompted to supply the param. I have tried encasing my form value in quotes, but of course then it is seen as a string literal. Is there a simple way to tell the query that [form]![ID] isn't a prompt? (I have looked at a zillion posting here but haven't found one for this answer) Thank you!!! granola911...
Error when clicking on 'Items' in GP 10
I'm in the process of testing a GP 10 conversion, and if I click on the 'Items' option in the 'Inventory' category on the Navigation Pane, the following error is displayed: [Microsoft][SQL Native Client][SQL Server]Cannot insert the value NULL into column 'BASEUOFM', table 'tempdb.dbo.##0015859'; column does not allow nulls. INSERT fails. Has anyone seen this before? -- Bud Cool Microsoft Certified Business Management Solutions Specialist, GP 9.0 Financials Currently supporting: GP 9.0, SP2 Yes, someone else posted this same (or very similar) quest...
Error handler
I used the following code to open a file. 2 users at 2 different sites have different path to the same excel file. i used an error handler to overcome this. but it seems not working a run time error 1004 appears when it tries to open the file from the first path and in fact the correct path is the secon one Workbooks.Open FileName:= _ "I:\_Other_Site\DIESE HARNESS OFFSHORING\00 HARNESS DESIGN ROUTING\02 Work Request Generator\Harness Routing WR template vE.xls" _ , UpdateLinks:=0 On Error Resume Next Workbooks.Open FileName:= _ "...
URGENT error converting a lead to a contact
I am getting an error converting a lead to a contact. Does anyone know what the default datamappings should be between a lead and a contact. I am wondering if that is the problem. The error is Microsoft CRM Platform Error Report: Error Description: An unexpected error occurred. Error Details: Object Type was not provided on attribute 'ownerid' Error Number: 0x80040216 Source File: D:\crm\Build\3297 \src\platform\include\OMCommon\CrmPropertyBagUtil.inl Line Number: 457 Date: 07-29-2004 Time: 9:32:16 AM Type: Platform Error (2) Server: amisql Query String: /SFA/le...
Server Error 403-Forbidden: Access Denied
Not sure if this is the right place or not. Went to microsoft update and got the following message Server Error 403-Forbidden: Access Denied You do not have permission to view this directory or page using the credentials you supplied. OS is WinXP pro/SP3. Is Automatic Updates enabled? If not, can you enable it, reboot & find it still enabled? What anti-virus application or security suite is installed and is your subscription current? What anti-spyware applications (other than Defender)? What third-party firewall (if any)? Has a(another) Norton or McAfee applicatio...
Conditional formatting error
I have a form with multiple date fields, whenever the date entered is within 6 months of today's date, I have conditional formatting set to show the date in red. Taken From Dialog Box: "Field Value is - Less than - Date()-182.5" There are instances where the formatting is being applied and others where it is not. In some cases on the same form. I don't know where to go to get the form code to past here so help there would also be appreciated. Thanks, Dan Hi. To get the code, open the form in design view, click on one of the date fields, open t...
Outlook ckecks files for errors
Hi, About 50% of the time Outlook thinks it has been closed improperly and it hasn't been. I know for a fact that I closed it using the "X" in the upper right hand corner. This wouldn't be much of a problem except it takes at least 15 mins for Outlook to check the file and it hogs all the resources, I can't even surf the net or open a program. I'd apprecaite any suggestions in helping Outlook realize it isn't crashing. O/S - Windows XP Pro (SP3) Outlook is part of Office 2003 (SP3) 2.4Ghz; 1GB of Ram Thanks Pamala If the startup scanner is ...
Getting an outlook error
I am looking for any help with the following error. The user opens outlook and begins working. if the computer sits Idle for 2-3 minutes the error message "OUTLOOK.exe has detected and error and will shut down. You will need to restart outlook." oputlook then closes down and an error log is created which I can't find. I have reloaded outlook and office Pro and checked all I can think of. I get this same error when I reply or forward a message and try to edit the text. Any suggestions would be appreciated. Ed >-----Original Message----- >I am looking for any help ...
Auto filter Query??
Hi hope this is possible. I would like to run a macro that uses a custom filter to do the following a Date is enetered into j18 (start date) and a date entered into k18 (end date) is it possible to use auto filter to run a custom macro where as the criterias are >= j18 and < k18 How would i accomplish this, any help with some code would be useful i tried the following without much success Sub daterange() Selection.AutoFilter Field:=2, Criteria1:=">=Cell(j18)", Operator:= _ xlAnd, Criteria2:="<Cell(j18)" End Sub HYCH Steve On Mar 3, 3...
Query to return a module
Hello, I have inherited a database which contains complex calculations that are done in 'modules'. They are all under one function called 'returncost'. I would like to display this field on my query but do not how to do this. Can anyone help, please? Thanks, Johanna On Fri, 29 Feb 2008 08:03:00 -0800, Johanna Gronlund <JohannaGronlund@discussions.microsoft.com> wrote: >Hello, > >I have inherited a database which contains complex calculations that are >done in 'modules'. They are all under one function called 'returncost'. I >would ...
Error C4251
I have created a dll that exports a class. If I compile with dynamicly linked MFC everything is OK. But if I compile with static linked MFC, I get this error on every class member that is a CString or CFile class instance. My class has no base class. What do I have to do to get rid of the warning? Can it be safely ignored? All the class members that are flagged with the warning are private members, and are not exposed to any clients in any way. From the help: 'identifier' : class 'type' needs to have dll-interface to be used by clients of class 'type2' A base class or...
CRM webbase License Error.
I have a question of the crm webbase. If i going to the webpage of CRM then i get a License Error. Can someone help me Thanks. -- Bart www.pc-check.nl Computer Reparatie Service. ...
Error Printing SOP (Error in equation '(c) Force Functional')
Hi, I have problem in SOP. while print one of the item getting error (Error in equation '(c) Force Functional'). Rgs saf Hi Saf, I would reckon this is modified report. Please review all equations of calculated field (c) Force Functional in Report Writer. Let us know you go! Cheers, Dennis Araullo, MACS Microsoft Certified Technology Specialist GP,CRM,AX MCITP Installation and Configuration AX 2009 MCITP Database Administrator SQL 2008 Citrix Certified Enterprise Administrator "saifu" wrote: > Hi, > > I have problem ...
Error number: 0x80090008 02-08-10
I've been working with Microsoft for over a month without resolution and it has now come down to reinstalling my operating system which I really would like to avoid. I noticed in December that I was not getting any update notifications so after about 2 weeks I went to the website for updates and when I click express I now get Error number: 0x80090008. This even comes up when I try to update windows defender software. I can't believe that there is no reference to what this error number refers to. Anyone have any suggestions? Thanks. Windows version would be...? W...
Error: Account setup is temporarily unavailable
I use Money 2005 Standard. I followed the screenshot instructions. I disabled the online services for the bank accounts that did not show a password box. I am stuck at the instructions where I am supposed to add the accounts. Instead of prompting me to enter user name/password, I am getting: Account setup is temporarily unavailable. Please try again later. I tried this all morning on Fri. I have 3 bank accounts that need to do this and they all have this same error. On Jul 20, 8:56 am, labear <lab...@discussions.microsoft.com> wrote: > I use Money 2005 Standard. I foll...