Tek-Tips is the largest IT community on the Internet today!

Members share and learn making Tek-Tips Forums the best source of peer-reviewed technical information on the Internet!

  • Congratulations derfloh on being selected by the Tek-Tips community for having the most helpful posts in the forums last week. Way to Go!

Create table, convert from text to number 1

Status
Not open for further replies.

Tadynn

Technical User
Joined
Oct 8, 2001
Messages
72
Location
AU
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
 
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
 
Status
Not open for further replies.

Part and Inventory Search

Sponsor

Back
Top