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

Welcome to UtterAccess! Please ( Login   or   Register )

Custom Search
 
   Reply to this topicStart new topic
> Formula For SQL Table, SQL Server 2012    
 
   
arpirnat
post Nov 29 2017, 11:35 AM
Post#1



Posts: 66
Joined: 25-June 16



I have a BE Table on an SQL server with a field that I wish to make a 'calculated' field. Is there a limiting factor to formulas? In short there are 7 fields I am trying to combine into this field (I know not ideal, but I did not design this and only have limited control).
(((((((([ProviderName]+' - Service: ')+[ProviderService])+' - Phone: ')+[PhoneNumber])+' - Fax: ')+[FaxNumber])+' - Contract ID#: ')+[ContractID]) is the current code in the Formula for the field and it does not error out, but on the Front End when I look at the table the field appears blank. If I remove the last bit to: ((((((([ProviderName]+' - Service: ')+[ProviderService])+' - Phone: ')+[PhoneNumber])+' - Fax: ')+[FaxNumber])+' - Contract ID#: ')
it displays fine. So basically I am wondering if I am doing something wrong or if there is a limit to how many things a formula can do at once? Is there a better way to do this? The total formula I was trying to do is:
((((((((((((((('Name: '+[ProviderName])+' Service: ')+[ProviderService])+' Phone: ')+[PhoneNumber])+' Fax: ')+[FaxNumber])+' Contract ID#: ')+[ContractID])+' POC: ')+[ProviderPOC])+' Location: ')+[ProviderCity])+',')+[ProviderState])
Go to the top of the page
 
GroverParkGeorge
post Nov 29 2017, 11:46 AM
Post#2


UA Admin
Posts: 31,195
Joined: 20-June 02
From: Newcastle, WA


The first thought would be if you indicated that is calculated field should be persisted:

Columns not persisted are not stored on disk.
Attached File  Persisted.png ( 1.29K )Number of downloads: 2

--------------------
Go to the top of the page
 
arpirnat
post Nov 29 2017, 11:48 AM
Post#3



Posts: 66
Joined: 25-June 16



I do not have it set to persisted but it worked when the formula was smaller. I can see if changing will help with the longer one.
Go to the top of the page
 
GroverParkGeorge
post Nov 29 2017, 11:55 AM
Post#4


UA Admin
Posts: 31,195
Joined: 20-June 02
From: Newcastle, WA


Please let us know what happens. Thanks.

--------------------
Go to the top of the page
 
arpirnat
post Nov 29 2017, 11:57 AM
Post#5



Posts: 66
Joined: 25-June 16



Unfortunately still blank when the table is opened from the front end. Still no errors though.
Go to the top of the page
 
GroverParkGeorge
post Nov 29 2017, 12:07 PM
Post#6


UA Admin
Posts: 31,195
Joined: 20-June 02
From: Newcastle, WA


So, you can run a query in SSMS and see the calculated values.

You can't see the calculated values at all in Access.

How about relinking the table in Access using the table relinker?

Does that help?

--------------------
Go to the top of the page
 


Custom Search
RSSSearch   Top   Lo-Fi    11th December 2017 - 02:06 PM