Get all but last two digits of a number

[cowannbell]

This is what I currently have for text, how do I do this for a number field?  I want to extract all but the last 2 digits of the number.

Left([field1], Length([field1]) - 2)

[alexnajm]

ToNumber(Left(ToString([field1]), Length(ToString([field1])) - 2))

Basically force a number to be a string, do the same function, and change it back!

[phottovy]

Hi @cowannbell, how are you hoping to handle numbers that either have decimals or have fewer than two digits? My first thought, which will give you similar results as the formula provided by @alexnajm, is to use a formula like this:

FLOOR([field1] / 100)

After plugging in some random numbers, both solutions start to act differently when smaller numbers or decimals are involved. Here are the results of both of our formulas with a variety of number types:

[alexnajm]

Good call @phottovy ! Always good to get more clarity 😊

[cowannbell]

My numbers don't have decimals, so we are good.

I guess I should have included this.  I'm trying to inner join two tables in Visual Query builder via the SQL.

What I need to do is connect two tables on table 1 field 1 and table 2 field 1.  Table 1 field 1 matches table 2 field 1 but only by the numbers to the left.  It just means that table 1 field 1 has two extra numbers added to the right.

How would I do this in Visual Query Builder?  I tried adding this in the inner join but it didn't like it.

[cowannbell]

Also, table 1 field 1 is a vstring and table 2 field 1 is a int64