Solved Changing from a linked excel spreadsheet to SQL Query Link

Number11

Member
Local time
Today, 18:55
Joined
Jan 29, 2020
Messages
619
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: 493
Double is a number data type?
So how is that being treated as text?
 
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
 
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?
 
I'm intrigued as to why Val() does not work.?
 
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
 
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?
 
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 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.
 
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
 
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
 
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

Back
Top Bottom