update query..not updating % field..

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

hi,
I'm running an update query against a joined table I imported from Excel.
The % fields are not updating.

In target table, I have percent fields datatyped as numeric/percent.

When I changed the datatype on a test version to text, the update
worked..but I don't want these as text fields, for calc purposes of course..

any suggestions?..
thx
Don
 
That tells me that your expression is returning a text value rather than a
numeric value. If you are using Format(...) to format your result as a
percent, remove the Format() function. Post your SQL if you need more help.
 
Thanks Pat -
No, not using 'Format'. I import spreadsheet into empty table of all text
fields, then run the Update query. I tried making the receiving fields in
this 'import' table numeric/percent, but then the values showed as zero
before I even get to run the update query.

Here is Update query sql...f17/18 are the problems..
--------
UPDATE Buildings INNER JOIN ImportTbl ON Buildings.BuildingID = ImportTbl.F1
SET Buildings.VoiceRoyaltyPC = [importtbl].[f17], Buildings.DataRoyaltyPC =
[importtbl].[f18], Buildings.MinimumRent = [importtbl].[f20],
Buildings.Utilities = [importtbl].[f21], Buildings.VoiceMRR =
[importtbl].[f25], Buildings.VoiceRoyalty = [importtbl].[f36],
Buildings.DataRoyalty = [importtbl].[f37], Buildings.GrossProfit =
[importtbl].[f45];
 
Thanks Pat - I worked it out, changed the % fields on updated table to Double
numeric, and it works.

nycdon said:
Thanks Pat -
No, not using 'Format'. I import spreadsheet into empty table of all text
fields, then run the Update query. I tried making the receiving fields in
this 'import' table numeric/percent, but then the values showed as zero
before I even get to run the update query.

Here is Update query sql...f17/18 are the problems..
--------
UPDATE Buildings INNER JOIN ImportTbl ON Buildings.BuildingID = ImportTbl.F1
SET Buildings.VoiceRoyaltyPC = [importtbl].[f17], Buildings.DataRoyaltyPC =
[importtbl].[f18], Buildings.MinimumRent = [importtbl].[f20],
Buildings.Utilities = [importtbl].[f21], Buildings.VoiceMRR =
[importtbl].[f25], Buildings.VoiceRoyalty = [importtbl].[f36],
Buildings.DataRoyalty = [importtbl].[f37], Buildings.GrossProfit =
[importtbl].[f45];


Pat Hartman said:
That tells me that your expression is returning a text value rather than a
numeric value. If you are using Format(...) to format your result as a
percent, remove the Format() function. Post your SQL if you need more help.
Click to expand...
Click to expand...
 
Back
Top