Create table, convert from text to number 1

[Tadynn]

Hi all,

Access 2k


I've got the following code that creates, updates and appends data into a table. The problem I'm having is that in the process I'm creating 2 additional fields that I am inserting data into later on in the procedure. Unfortunately the fields are created by default as text data type fields, whereas I am inserting numeric data into the fields. So when I run the report I get a type mis-match error because of these two fields.

How can I format these two fields to numeric through the code that I run? see below:


* The two fields are AdjustedQty and MovedQty
* Fields created in step2
* Data inserted in step4 & step5


Private Sub Command15_Click()

'DoCmd.SetWarnings False

'Step1 - Get inventory transactions data and append to temp table

DoCmd.RunSQL "SELECT dbo_ITRN.Sku, dbo_ITRN.EffectiveDate AS [Date], " & _
"dbo_ITRN.Qty, dbo_ITRN.SourceKey, Trim([SourceType]) AS Source, " & _
"dbo_ITRN.FromLoc AS [From Location], dbo_ITRN.ToLoc AS [To Location], " & _
"dbo_ITRN.EditWho INTO SkuHistoryTemp " & _
"From dbo_ITRN " & _
"GROUP BY dbo_ITRN.Sku, dbo_ITRN.EffectiveDate, dbo_ITRN.Qty, dbo_ITRN.SourceKey, " & _
"Trim ([SourceType]), dbo_ITRN.FromLoc, dbo_ITRN.ToLoc, dbo_ITRN.EditWho " & _
"HAVING (((dbo_ITRN.Sku)=[Forms]![frm_Skuhistory]![Sku]) AND " & _
"((dbo_ITRN.EffectiveDate) Between [Forms]![frm_SkuHistory]![FromDate] And " & _
"[Forms]![frm_SkuHistory]![ToDate])) " & _
"ORDER BY dbo_ITRN.EffectiveDate"

'Step2 - Get data from temp table and append to actual
table with additional fields

DoCmd.RunSQL "SELECT SkuHistoryTemp.Sku, SkuHistoryTemp.Date, " & _
"SkuHistoryTemp.Qty, SkuHistoryTemp.SourceKey, SkuHistoryTemp.Source, " & _
"SkuHistoryTemp.[From Location], SkuHistoryTemp.[To Location], SkuHistoryTemp.EditWho, " & _
"'' AS MovedQty, '' AS AdjustedQty INTO SkuHistory " & _
"FROM SkuHistoryTemp"

'Step3-Get starting balance for sku history

DoCmd.RunSQL "SELECT dbo_ITRN.Sku, Sum(dbo_ITRN.Qty) AS SumOfQty INTO SkuHistoryStartBalance " & _
"From dbo_ITRN " & _
"GROUP BY dbo_ITRN.Sku, dbo_ITRN.EffectiveDate " & _
"HAVING (((dbo_ITRN.Sku)=[forms]![frm_SkuHistory]![Sku]) AND " & _
&quot;((dbo_ITRN.EffectiveDate)<[Forms]![frm_SkuHistory]![FromDate]))&quot;

'Step4 - Calculate adjusted quatities and insert into Adjusted quantity field

DoCmd.RunSQL &quot;UPDATE SkuHistory SET SkuHistory.AdjustedQty = [Qty] &quot; & _
&quot;WHERE (((SkuHistory.Source) Between 'NTR' And 'NTRz'))&quot;

'Step5 - Calculate moved quantities and insert into Moved Qty field

DoCmd.RunSQL &quot;UPDATE SkuHistory SET SkuHistory.MovedQty = [Qty] &quot; & _
&quot;WHERE (((SkuHistory.Source) Not Between 'NTR' And 'NTRZ'))&quot;

'Step6 - Insert starting balance into SkuHistoryTable

DoCmd.RunSQL &quot;INSERT INTO SkuHistory ( Sku, Source, AdjustedQty ) &quot; & _
&quot;SELECT SkuHistoryStartBalance.Sku, 'ntrStockBalance' AS Expr1, &quot; & _
&quot;Sum(SkuHistoryStartBalance.SumOfQty) AS StartBalance &quot; & _
&quot;From SkuHistoryStartBalance &quot; & _
&quot;GROUP BY SkuHistoryStartBalance.Sku, 'ntrStockBalance'&quot;

'Step7 - Perform cleanup on tables

DoCmd.DeleteObject acTable, &quot;SkuHistoryTemp&quot;
DoCmd.DeleteObject acTable, &quot;SkuHistoryStartBalance&quot;

End Sub


Thanks in advance

Tadynn

 

[Blorf]

It looks like its your make table query:

&quot;'' AS MovedQty, '' AS AdjustedQty

Change it to

0 AS MovedQty, 0 AS AdjustedQty

That will force the table to be made using numeric fields.

Use a number like 1.111 or what not to force precision if you need to.

Hope that helps.

ChaZ

Ascii dumb question, get a dumb Ansi