Solved Changing from a linked excel spreadsheet to SQL Query Link (1 Viewer)

Number11

Member
Local time
Today, 05:43
Joined
Jan 29, 2020
Messages
367
Hi,

So i was using a linked excel spreadsheet and now wish to use a direct SQL table, however i have an issue the SQL Table has a field formatted as Text the spreadsheet I was linked to had that field set as a "Number", so now having issued with all queries I have trend to convert the text file back to number using

Account: Val([AccountNo])

but this didnt work, so i imported the excel spreadsheet as then looked up the formatting within the table and the formatting is set as follows... so maybe i need to do something else in regards to formating?
 

Attachments

  • Capture.GIF
    Capture.GIF
    12.4 KB · Views: 101

Minty

AWF VIP
Local time
Today, 05:43
Joined
Jul 26, 2013
Messages
8,829
Double is a number data type?
So how is that being treated as text?
 

Number11

Member
Local time
Today, 05:43
Joined
Jan 29, 2020
Messages
367
Double is a number data type?
So how is that being treated as text?
that screen shot is after i have imported the excel spreadsheet into access new table, so this is how access is seeing the external field, but the SQL table field is set as Short Text
 

Minty

AWF VIP
Local time
Today, 05:43
Joined
Jul 26, 2013
Messages
8,829
If your SQL table data only contains the numbers in that field, simply change it in design mode to Number.
Unless I'm missing something?
 

Gasman

Enthusiastic Amateur
Local time
Today, 05:43
Joined
Sep 21, 2011
Messages
9,068
I'm intrigued as to why Val() does not work.?
 

Number11

Member
Local time
Today, 05:43
Joined
Jan 29, 2020
Messages
367
If your SQL table data only contains the numbers in that field, simply change it in design mode to Number.
Unless I'm missing something?
no SQL is stored as short text not a number, and i cant change the SQL field as this will cause other issues for other databases, so i need to have the database i am working on convert it, so i relinked the spreadsheet and run a query it works, if i run the query from the SQL linked table i get error as attached.
1627653396129.png
 

theDBguy

I’m here to help
Staff member
Local time
Yesterday, 21:43
Joined
Oct 29, 2018
Messages
16,584
no SQL is stored as short text not a number, and i cant change the SQL field as this will cause other issues for other databases, so i need to have the database i am working on convert it, so i relinked the spreadsheet and run a query it works, if i run the query from the SQL linked table i get error as attached. View attachment 93298
Just a thought... How about creating a View? Do you have to edit the data?
 

Minty

AWF VIP
Local time
Today, 05:43
Joined
Jul 26, 2013
Messages
8,829
You should have mentioned that other things rely on this being text. I'm guessing this is an external programs SQL table.

The simplest solution is to create a Query in your database and try using CLng([Account]) to see if it will work.
If it does you use that query in place of your table.
 

theDBguy

I’m here to help
Staff member
Local time
Yesterday, 21:43
Joined
Oct 29, 2018
Messages
16,584
no i dont need to edit the data what do you mean when you say a view?
A View is sort of like a query in SQL. You can use a calculated column to CAST or CONVERT the Text column into Number. You can then link to that View in Access.
 

Number11

Member
Local time
Today, 05:43
Joined
Jan 29, 2020
Messages
367
You should have mentioned that other things rely on this being text. I'm guessing this is an external programs SQL table.

The simplest solution is to create a Query in your database and try using CLng([Account]) to see if it will work.
If it does you use that query in place of your table.
no thats didnt work shows results as using Expr1:CLng([Account])
1627654269906.png
 

Number11

Member
Local time
Today, 05:43
Joined
Jan 29, 2020
Messages
367
So changed it to Account: CDbl(AccountNo]) and the field now is looking like a number field as set to right hand side but now now getting error Invalid Use of Null
 

Minty

AWF VIP
Local time
Today, 05:43
Joined
Jul 26, 2013
Messages
8,829
There is something weird going on here, as @Gasman said earlier Val() should have worked. From the immediate window;
Code:
? CLng("32545")
32545
? Val("32545")
32545

So something else is afoot.
Edit : try this
CDbl(Nz([AccountNo],0))
 

Users who are viewing this thread

Top Bottom