Pete- Yes. In fact, you should always explicitly declare parameters in queries even though you can get away with not doing it most of the time. You must always declare parameters in a Crosstab.
John Viescas, author "Building Microsoft Access Applications" "Microsoft Office Access 2003 Inside Out" "Running Microsoft Access 2000" "SQL Queries for Mere Mortals" http://www.viescas.com/ > -----Original Message----- > From: ms_access@yahoogroups.com > [mailto:[EMAIL PROTECTED] On Behalf Of Bradshaw, Peter A C301 > Sent: Wednesday, July 27, 2005 12:37 PM > To: ms_access@yahoogroups.com > Subject: RE: [ms_access] Append query violation error > > > Excellent! It worked! > > Does a PARAMETER statement work similarly as a Dim statement declares > variable types? > > - PETE > > -----Original Message----- > From: ms_access@yahoogroups.com > [mailto:[EMAIL PROTECTED] On Behalf Of John Viescas > Sent: Wednesday, July 27, 2005 12:56 PM > To: ms_access@yahoogroups.com > Subject: RE: [ms_access] Append query violation error > > > Pete- > > Try adding an explicit declaration of the parameter to the LL11 > query: > > PARAMETERS [enter today's date] DateTime; > > That should be the first statement in the query just before the > SELECT > statement. > > John Viescas, author > "Building Microsoft Access Applications" > "Microsoft Office Access 2003 Inside Out" > "Running Microsoft Access 2000" > "SQL Queries for Mere Mortals" > http://www.viescas.com/ > > > -----Original Message----- > > From: ms_access@yahoogroups.com > > [mailto:[EMAIL PROTECTED] On Behalf Of Bradshaw, > Peter A C301 > > Sent: Wednesday, July 27, 2005 9:45 AM > > To: ms_access@yahoogroups.com > > Subject: [ms_access] Append query violation error > > > > > > Hi All, > > > > I wrote the following query and it works well for some "error > > inventory" > > reporting. Basically, it looks at query LL11 and "sums" the > > results of > > a series of IIf functions. The LL11 query asks for today's > > date and the > > last business date as inputs from the user, then based on the > type of > > error (business or IT) and the difference between the ]error > > date] field > > and today's date entered by the user, it creates an array of > > 1's and 0's > > which are then "summed" in the LL12 query which is as follows: > > > > SELECT LL11_Inventory_Source_for_calculations.todaysdate, > > Sum(LL11_Inventory_Source_for_calculations.NewInventory) AS > > SumOfNewInventory, > > Sum(LL11_Inventory_Source_for_calculations.BusinessInventory) > AS > > SumOfBusinessInventory, > > Sum(LL11_Inventory_Source_for_calculations.PMHSInventory) AS > > SumOfPMHSInventory, > > Sum(LL11_Inventory_Source_for_calculations.TotalErr00to02days) > AS > > SumOfTotalErr00to02days, > > Sum(LL11_Inventory_Source_for_calculations.TotalErr03to07days) > AS > > SumOfTotalErr03to07days, > > Sum(LL11_Inventory_Source_for_calculations.TotalErr08to14days) > AS > > SumOfTotalErr08to14days, > > Sum(LL11_Inventory_Source_for_calculations.TotalErrGT15days) > AS > > SumOfTotalErrGT15days > > FROM LL11_Inventory_Source_for_calculations > > GROUP BY LL11_Inventory_Source_for_calculations.todaysdate; > > > > In case it matters, LL11 query is as follows: > > > > SELECT [enter today's date] AS todaysdate, IIf(([Oustanding > CED-MHS > > Errors Inv]![Error Date]>=[enter last business date]) And > ([Oustanding > > CED-MHS Errors Inv]![Status]="MHS" Or [Oustanding CED-MHS > Errors > > Inv]![Status]="o" Or [Oustanding CED-MHS Errors > Inv]![Status]="EA" Or > > [Oustanding CED-MHS Errors Inv]![Status]="V" Or [Oustanding > CED-MHS > > Errors Inv]![Status]="E"),1,0) AS NewInventory, > > IIf(([Oustanding CED-MHS > > Errors Inv]![Status]="o" Or [Oustanding CED-MHS Errors > > Inv]![Status]="EA" Or [Oustanding CED-MHS Errors > Inv]![Status]="v" Or > > [Oustanding CED-MHS Errors Inv]![Status]="e"),1,0) AS > > BusinessInventory, > > IIf(([Oustanding CED-MHS Errors Inv]![Status])="mhs",1,0) AS > > PMHSInventory, IIf((DateDiff("d",[Oustanding CED-MHS Errors > > Inv]![Error > > Date],[todaysdate])>=0 And (DateDiff("d",[Oustanding CED-MHS > Errors > > Inv]![Error Date],[todaysdate])<=2)),1,0) AS > TotalErr00to02days, > > IIf((DateDiff("d",[Oustanding CED-MHS Errors Inv]![Error > > Date],[todaysdate])>=3 And (DateDiff("d",[Oustanding CED-MHS > Errors > > Inv]![Error Date],[todaysdate])<=7)),1,0) AS > TotalErr03to07days, > > IIf((DateDiff("d",[Oustanding CED-MHS Errors Inv]![Error > > Date],[todaysdate])>=8 And (DateDiff("d",[Oustanding CED-MHS > Errors > > Inv]![Error Date],[todaysdate])<=14)),1,0) AS > TotalErr08to14days, > > IIf((DateDiff("d",[Oustanding CED-MHS Errors Inv]![Error > > Date],[todaysdate])>=15),1,0) AS TotalErrGT15days > > FROM [Oustanding CED-MHS Errors Inv] > > WHERE (((IIf(([Oustanding CED-MHS Errors Inv]![Error > > Date]>=[enter last > > business date]) And ([Oustanding CED-MHS Errors > Inv]![Status]="MHS" Or > > [Oustanding CED-MHS Errors Inv]![Status]="o" Or [Oustanding > CED-MHS > > Errors Inv]![Status]="EA" Or [Oustanding CED-MHS Errors > > Inv]![Status]="V" Or [Oustanding CED-MHS Errors > > Inv]![Status]="E"),1,0))<>0)) OR (((IIf(([Oustanding CED-MHS > Errors > > Inv]![Status]="o" Or [Oustanding CED-MHS Errors > Inv]![Status]="EA" Or > > [Oustanding CED-MHS Errors Inv]![Status]="v" Or [Oustanding > CED-MHS > > Errors Inv]![Status]="e"),1,0))<>0)) OR (((IIf(([Oustanding > CED-MHS > > Errors Inv]![Status])="mhs",1,0))<>0)); > > > > The problem is that I want to record the error inventory in a > > table. (I > > purge resolved errors once a month, so I can never go back and > compare > > June to July inventory unless I save the values somewhere). I > thought > > that I could just make the LL12 query into an "append" query: > > > > INSERT INTO InventoryMasterTable ( inventorydate, > SumOfNewInventory, > > SumOfBusinessInventory, SumOfPMHSInventory, > SumOfTotalErr00to02days, > > SumOfTotalErr03to07days, SumOfTotalErr08to14days, > > SumOfTotalErrGT15days > > ) > > SELECT LL11_Inventory_Source_for_calculations.todaysdate, > > Sum(LL11_Inventory_Source_for_calculations.NewInventory) AS > > SumOfNewInventory, > > Sum(LL11_Inventory_Source_for_calculations.BusinessInventory) > AS > > SumOfBusinessInventory, > > Sum(LL11_Inventory_Source_for_calculations.PMHSInventory) AS > > SumOfPMHSInventory, > > Sum(LL11_Inventory_Source_for_calculations.TotalErr00to02days) > AS > > SumOfTotalErr00to02days, > > Sum(LL11_Inventory_Source_for_calculations.TotalErr03to07days) > AS > > SumOfTotalErr03to07days, > > Sum(LL11_Inventory_Source_for_calculations.TotalErr08to14days) > AS > > SumOfTotalErr08to14days, > > Sum(LL11_Inventory_Source_for_calculations.TotalErrGT15days) > AS > > SumOfTotalErrGT15days > > FROM LL11_Inventory_Source_for_calculations > > GROUP BY LL11_Inventory_Source_for_calculations.todaysdate; > > > > But I get an error that reads: "Couldn't append all the rows > to the > > table. MS Access set 1 field to null for type conversion > failure." > > When I go look at the InventoryMasterTable, I find that the > > [inventorydate] field is null. This is what I don't > > understand. It is > > a date field in the table. Why doesn't the date get inserted > into the > > table? > > > > Thanks, > > Pete Bradshaw > > Fulfillment Business Services Production Support > > CIGNA HealthCare > > 860.226.3019 (N:572.3019) > > mailto:[EMAIL PROTECTED] > > > > > > -------------------------------------------------------------- > > ----------------CONFIDENTIALITY NOTICE: If you have received > > this email in error, please immediately notify the sender by > > e-mail at the address shown. This email transmission may > > contain confidential information. This information is > > intended only for the use of the individual(s) or entity to > > whom it is intended even if addressed incorrectly. Please > > delete it from your files if you are not the intended > > recipient. Thank you for your compliance. Copyright 2005 > CIGNA > > ============================================================== > > ================ > > > > > > [Non-text portions of this message have been removed] > > > > > > > > > > Yahoo! Groups Links > > > > > > > > > > > > > > > SPONSORED LINKS > Microsoft access database > <http://groups.yahoo.com/gads?t=ms&k=Microsoft+access+database > &w1=Micros > oft+access+database&w2=Free+microsoft+access+database&w3=Micro soft+acces > s&w4=Microsoft+access+database+design&w5=Microsoft+access+data > base+train > ing&w6=Ms+access&c=6&s=182&.sig=hPYPsGYc-Dou8l2vK62wZA> Free > microsoft access database > <http://groups.yahoo.com/gads?t=ms&k=Free+microsoft+access+dat > abase&w1=M > icrosoft+access+database&w2=Free+microsoft+access+database&w3= > Microsoft+ > access&w4=Microsoft+access+database+design&w5=Microsoft+access > +database+ > training&w6=Ms+access&c=6&s=182&.sig=AVWbL4_C-oc-wJhYxIy1JQ> > Microsoft access > <http://groups.yahoo.com/gads?t=ms&k=Microsoft+access&w1=Micro > soft+acces > s+database&w2=Free+microsoft+access+database&w3=Microsoft+acce > ss&w4=Micr > osoft+access+database+design&w5=Microsoft+access+database+trai > ning&w6=Ms > +access&c=6&s=182&.sig=ZqXOcBXGxkTsD0a2Rz440w> > Microsoft access database design > <http://groups.yahoo.com/gads?t=ms&k=Microsoft+access+database > +design&w1 > =Microsoft+access+database&w2=Free+microsoft+access+database&w > 3=Microsof > t+access&w4=Microsoft+access+database+design&w5=Microsoft+acce > ss+databas > e+training&w6=Ms+access&c=6&s=182&.sig=7Z3OPtTFgTGRYyimH8KjRw> > Microsoft access database training > <http://groups.yahoo.com/gads?t=ms&k=Microsoft+access+database > +training& > w1=Microsoft+access+database&w2=Free+microsoft+access+database > &w3=Micros > oft+access&w4=Microsoft+access+database+design&w5=Microsoft+ac > cess+datab > ase+training&w6=Ms+access&c=6&s=182&.sig=iBJkXpIna9fO0jJIKt08mw> > Ms access > <http://groups.yahoo.com/gads?t=ms&k=Ms+access&w1=Microsoft+ac > cess+datab > ase&w2=Free+microsoft+access+database&w3=Microsoft+access&w4=M > icrosoft+a > ccess+database+design&w5=Microsoft+access+database+training&w6 > =Ms+access > &c=6&s=182&.sig=c1Aaw1_EL_gIENnELK0CbQ> > > _____ > > YAHOO! GROUPS LINKS > > > > * Visit your group "ms_access > <http://groups.yahoo.com/group/ms_access> " on the web. > > * To unsubscribe from this group, send an email to: > [EMAIL PROTECTED] > <mailto:[EMAIL PROTECTED]> > > * Your use of Yahoo! Groups is subject to the Yahoo! > Terms of Service <http://docs.yahoo.com/info/terms/> . > > > _____ > > > > -------------------------------------------------------------- > ---------------- > CONFIDENTIALITY NOTICE: If you have received this email in > error, please immediately notify the sender by e-mail at the > address shown. This email transmission may contain > confidential information. This information is intended only > for the use of the individual(s) or entity to whom it is > intended even if addressed incorrectly. Please delete it > from your files if you are not the intended recipient. Thank > you for your compliance. Copyright 2005 CIGNA > ============================================================== > ================ > > > [Non-text portions of this message have been removed] > > > > > Yahoo! Groups Links > > > > > Yahoo! Groups Links <*> To visit your group on the web, go to: http://groups.yahoo.com/group/ms_access/ <*> To unsubscribe from this group, send an email to: [EMAIL PROTECTED] <*> Your use of Yahoo! Groups is subject to: http://docs.yahoo.com/info/terms/
