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
> How To Convert Datatype In Access Services On Sp 2013 On-premise, Access 2013 Web App    
 
   
primaveranz
post Jun 14 2017, 05:22 PM
Post#1



Posts: 7
Joined: 25-January 17



Hi,

I want to create a query in Access 2013 ( for an Access services DB running on an on-premise Sharepoint 2013 server) which sums two fields, the problem is that the fields both use lookups and although the contents represent integers, lookup fields are automatically cast as varchars in SQL server which therefore throws up an error.
In stand-alone Access you can obviously use VBA functions like CINT to fix that, however VBA is not available in an Access services DB.
In SQL server I could use CAST but the access front end won't allow me to do this as CAST is not recognised by Access.
In stand-alone access you can go into SQL edit mode and put SQL functions like CAST directly into the SQL however you can only use the GUI in an Access services db ("SQL Specific" is greyed out when you right click on the design window).
Is there a way around this in Access 2013? The "Help" is devoid of useful information and trying to use SQL management Studio to modify "views" created by Access services seems to be fraught with issues as the two get out of sync, so I would rather use Access 2013 if possible.

Thanks in advance.
Go to the top of the page
 
GroverParkGeorge
post Jun 14 2017, 05:41 PM
Post#2


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


Please help clarify some terms here.

Is this actually an Access Web App? I ask because "Access services db" is not a term that is commonly used, so we need to be sure exactly what you mean. I assume that this is, in fact, a SQL Server database created via the Access Web App technology.

If, in fact you created an Access Web App in an on-premise SharePoint server, you have a SQL Server somewhere in your premises, and that is where this database resides. Not in "Access services" per se, but in SQL Server.

Therefore, you CAN get to the tables via desktop Access (accdb format). And you can do so via SSMS.

I also doubt that your lookups are actually being cast as varchars; that is not how they work. Please clarify that you are, in fact, getting a datatype mismatch error.

Also, in case you were not aware of the upcoming deprecation of AWAs, for your reference.

Because you are working with an on-premises SP server, you have another 5 years or so, and possibly up to 10 years, before the deprecation hits you. However, it is also my understanding that Microsoft has deployed, or will deploy, a "kill switch" for Access services that will allow your SP administrator to turn off that service at any point they choose. It would be wise, therefore, to consult with your team and be sure you have a game plan going forward.

--------------------
Go to the top of the page
 
GroverParkGeorge
post Jun 14 2017, 06:09 PM
Post#3


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


Oh, I got off track there and forgot to address part of the question. The Cast Function is available, but it may not actually give you what you need.

Cast([tblYourtableNameGoesHere].[YourLookupField],Integer)

It will still return the display portion of the lookup field, not the ID portion, but it WILL actually run that way, proving it is, in fact, an integer.

--------------------
Go to the top of the page
 
primaveranz
post Jun 14 2017, 06:11 PM
Post#4



Posts: 7
Joined: 25-January 17



Thanks for the quick reply. Yes mea culpa, it is an Access Web App with an SQL Server database and therefore I can use SQL Mangement Server to access the tables (note my comment about not wanting to do this though).

However when I use Access 2013 to look at the table structure the only options for a field which uses a value list (not another table) is the option "Lookup".

If you use that option then check in SQL Management you will see the field has been defined as nvarchar(220) - which cannot be used in a Sum calculation. So in SSMS you get the error"Operand data type nvarchar is invalid for sum operator" and in Access you get "Access can't evaluate an expression because a value's data type isn't compatible with its operator".

I tried retro-fitting the fields to use a lookup table with number values rather than a value list (which apparently forces the nvarchar(220)) however that kicked off such a chain of errors relating to existing data and validation that I backed off quickly.

I was aware of the decision to ditch AWA thanks; but I was not surprised by it, being one of those dinosaurs who lost faith in MS when they abandoned VB6 wink.gif
Go to the top of the page
 
primaveranz
post Jun 14 2017, 06:15 PM
Post#5



Posts: 7
Joined: 25-January 17



Ah, cross post! I just saw your second reply and yes, thanks it worked. It looks like I must have had some kind of typo when using the Cast command. So sorry for wasting your time, but many thanks for your solution.
Cheers.
Go to the top of the page
 
GroverParkGeorge
post Jun 14 2017, 06:42 PM
Post#6


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


I see, when you referred to "Lookup" fields, not "Value List" fields, I went off-track.

I'm glad to hear you sorted it out.

Continued success with your project. However long it lasts.... frown.gif

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


Custom Search
RSSSearch   Top   Lo-Fi    17th December 2017 - 04:43 AM