UtterAccess.com
X   Site Message
(Message will auto close in 2 seconds)

Welcome to UtterAccess! Please ( Login   or   Register )

Custom Search
> Best Field Types For Calculations, SQL Server 2012    
 
   
Everettc4
post Aug 7 2019, 06:57 PM
Post#1



Posts: 212
Joined: 12-June 06
From: Oregon


Hi all hey I am in the rapids with a SQL migration been learning a ton but.... now I have come to a query in access that calculates so reports can summarize. works great in access but linking to the exported SQL tables the query crashes. I'm pretty sure it's due to the field type SQL set when I exported. The question is what type of field is best for calculations when pulling into access? Also I'm not sure why SQL put brackets [] around some fields could that be causing problems??

Thanks for the help!

Attached File(s)
Attached File  SQL_FeildTypes.JPG ( 50.39K )Number of downloads: 8
 
Go to the top of the page
 
 
Start new topic
Replies
strive4peace
post Aug 7 2019, 08:02 PM
Post#2


strive4peace
Posts: 20,532
Joined: 10-January 04



hi Everett,

>" linking to the exported SQL tables the query crashes. I'm pretty sure it's due to the field type SQL set when I exported"

Maybe (did you use Byte, Memo/Long Text, Attachment, or other known problem type?)
... so, maybe not ... I see bad names.

Some of your fieldnames use bad characters like / and space. Better not to do that, and use something else instead -- maybe underscore (_), or use ProperCase to identify different words/abbreviations ? When there are no suspect characters (stick to alphabet letters A-Z, a-z, followed perhaps with numbers 0-9, and/or include underscore. ALWAYS Start with letter), there is no need to surround names with brackets to be properly understood.

> "why SQL put brackets [] around some fields"

typically, user-defined object names are/can be surrounded with brackets. If your names don't have spaces or special characters that could be misinterpreted (/ means division, + means add, - means subtract, etc), it is obvious that what you typed is the whole name ... but names are allowed to contain characters that could cause issues, so to absolutely identify where a name begins and ends, using brackets makes it clear.


This post has been edited by strive4peace: Aug 7 2019, 08:18 PM

--------------------
have an awesome day,
crystal

Microsoft MVP
Remote Training and Programming -- let's connect and build your application together! MsAccessGurus.com
~
video: Bar Graphs in Access Query using Unicode
Go to the top of the page
 
strive4peace
post Aug 7 2019, 09:49 PM
Post#3


strive4peace
Posts: 20,532
Joined: 10-January 04



ps, Everett,

since you mentioned calculations, to specify data type, an expression could be wrapped in a conversion function such as to currency - 14 or 15 places before decimal point and 4 after, to integer <=32K, to long integer (bigger whole number), to double precision which is float, or scientific notation, and not accurate for keys and exact comparison, but has decimal places and good to represent extremely large and small numbers when all you need is a relative value, etc, so there is no doubt about data type for the result of a calculation. Other functions like round, INT and FIX, can help you get the sig figs (significant figures) you want.



This post has been edited by strive4peace: Aug 7 2019, 09:59 PM

--------------------
have an awesome day,
crystal

Microsoft MVP
Remote Training and Programming -- let's connect and build your application together! MsAccessGurus.com
~
video: Bar Graphs in Access Query using Unicode
Go to the top of the page
 

Posts in this topic



Custom Search


RSSSearch   Top   Lo-Fi    11th July 2020 - 12:05 PM