Why doesn't this work ?

[SBM]

i am trying to run an append query in SQL which appends a calculated value into another table 50 times incrementing the day by one day each time. When I run it it asks me for the parameter [NewDay] each time. It is obviuosly not picking up the variable.

Can anyone tell me why ?



Dim NewDate As Date
Dim n As Integer


For n = 0 To 50

NewDate = Date + n

DoCmd.RunSQL "INSERT INTO InventoryEvolution ( SAP, Stock, [Date] ) SELECT UK_Product_Estimate_Live.[RE SAP Code], ((Sum([Estimate01])+Sum([Estimate02]))/50)*-1 AS Stock, NewDate From UK_Product_Estimate_Live GROUP BY UK_Product_Estimate_Live.[RE SAP Code] HAVING (((UK_Product_Estimate_Live.[RE SAP Code])=513450))"

Next n

 

[neileg]

NewDate is not a field in UK_Product_Estimate_Live. Not sure how to correct your SQL, though.

 

[KeithG]

DoCmd.RunSQL "INSERT INTO InventoryEvolution ( SAP, Stock, [Date] ) SELECT UK_Product_Estimate_Live.[RE SAP Code], ((Sum([Estimate01])+Sum([Estimate02]))/50)*-1 AS Stock," & #NewDate# & " From UK_Product_Estimate_Live GROUP BY UK_Product_Estimate_Live.[RE SAP Code] HAVING (((UK_Product_Estimate_Live.[RE SAP Code])=513450))"

try the above sql and let me know if it works. The problem is that you have the whole sql statement inside of quotes. Therefore it doesn't recognize that there is a variable in the statement.

 

[WayneRyan]

SBM,

KeithG was close, the # HAVE to be within your quotes.

DoCmd.RunSQL "INSERT INTO InventoryEvolution ( SAP, Stock, [Date] ) " & _
             "SELECT UK_Product_Estimate_Live.[RE SAP Code], " & _
             "       ((Sum([Estimate01])+Sum([Estimate02]))/50)*-1 AS Stock, #" & _
                     NewDate & "# " & _
             "From UK_Product_Estimate_Live " & _
             "GROUP BY UK_Product_Estimate_Live.[RE SAP Code] " & _
             "HAVING (((UK_Product_Estimate_Live.[RE SAP Code])=513450))"

Wayne

 

[SBM]

Thanks !!

Thanks Guys. That works just fine.

One small question. The date is in US format (mm/dd/yy) but I need it in UK format (dd/mm/yy). How do I do this ?


Thanks again


Steve

 

[SBM]

More

It's not as simple as the date being in US format. See the dates below...

When
10/06/06
10/07/06
10/08/06
10/09/06
10/10/06
10/11/06
10/12/06
06/10/13
06/10/14
06/10/15

As you can see the first few look like US style but the when it gets to the 13the the date flips to some thing else. It seems like there's a format / data type issue.


Thanks


Steve

 

[SBM]

Success

OK, fixed it ...

For n = 0 To 9

DoCmd.RunSQL "INSERT INTO InventoryEvolution ( SAP, Stock, When ) SELECT UK_Product_Estimate_Live.[RE SAP Code], ((Sum([Estimate01])+Sum([Estimate02]))/50)*-1 AS Stock, date()+" & n & " From UK_Product_Estimate_Live GROUP BY UK_Product_Estimate_Live.[RE SAP Code] HAVING (((UK_Product_Estimate_Live.[RE SAP Code])=513450))"

Next n


Thanks for your help you guys. Couldn't have done it without you.


Steve