Tracking Forums, Newsgroups, Maling Lists
Home Scripts Tutorials Tracker Forums
  HOME    TRACKER    Visual Basic

Find Closest Number

Need to find the closest number to numbers in one table to the list of numbers in another table (ms access). I was able to do this in a query for a small table but when I got to 10,000 records it runs for hours without returning any records. I am drawing a blank on how to do this in code. I believe code would do it quickly. Thanks for any ideas.

View Complete Forum Thread with Replies

See Related Forum Messages: Follow the Links Below to View Complete Thread
Closest Number
I have 1 varaible that a number of 100 digits
and a 2nd that as 3 digit

is there a way to find the number in the 100 var closest to the 3 digit and return it starting location, for exemple:

Var1 = "596745645645..."
Var2 = "595"

the closest would be right @ start so it would return position 1... Hope i was clear, not sure how to explain it

How Do I Find Closest Value? currenty working on a program that can save and load a 256 colored picture saved using a palette.

here's the problem:
the palette has got the red, green and blue values for all 256 colors, so the loading is not a problem....but when i want to save a file and a color does not match any in the palette, how do i find the most similar color?

because the 32 bit image uses 3 values to describe the color (red, green and blue) i dont know how to search for the closest one in the table...i've tried adding all 3 values but that gives me a wierd result...

please help me with this one.

Find Variable From Array With Value Closest To Zero
I need to find which variable in an integer array is closest to 0. Possible values will range from 180 to -180.

Here's what I have going (simplified):

VB Code:
dim tfIntArr() as integerredim tfIntArr(0 to 25) ' populate tfIntArr with random values between 180 and -180 Dim tfInt as integer, tmpInt as integer For tfInt = 0 to 25      tmpInt = tfIntArr(tfInt)      ' now what?Next tfInt

At the end of the For-Next I need to know which value of tfInt produced the tmpInt value closest to 0.

Thanks for any help. I hope I made this clear enough, but if you have any questions I'll do my best to clarify.

- Jake

Easy - Find The Closest Variable
i have six variables say the follwing:

a = 1.1290
b = 1.1316
c = 1.1342
d = 1.1367
e = 1.1390
f = 1.1413

another variable has the value 1.136. What code can i use to find out which one of the above variables is closest to this value ('d') !!!

How To Find The Closest Value In A Field (Access)
Hi guys,

I have a access database has a field called fldGrade,

fldGrade fldName

45 Chris
46 Kate
78 ..

How can I find the student who has the closest 'Grade' to the specified student? for example, if Chris's grade is 45, the closest Grade should be 46 (Kate). are there any SQL statement to do it? and how I can return the closest grade's student to a label control?

the interface likes this:

Please enter a student name _____Chris
Find the closest grade (click button)
the closest grade to Chris is 46 Kate (label.caption)
Thank you

Find Record With Date Closest To Date Entered
I have a table in Access with weekly prices with Monday's dates.

I have a user entering invoices in a VB form, and when the user clicks Calculate I need my application to find the previous week's price for the invoice date entered.

Also, I have this in a function for my form called Private Function Calculations(). Would that work okay or should I put it in a module? Do I need a class? I am still learning.


How To Find Hard Disk Model Number & Microprocesser Number?
How to find hard disk model number & microprocesser number.
Is there any API through which I can find out these numbers.

Find A Number..
i couldnt really explain well in a title..but i want to know if a string contains a number that i can do something..i dont think isnumeric works here..any ideas?

Find Number
Hi All, Again,

How can I find a number from a string i.e. I have a text field with "hell12o" and I want a interger to have the value 12 the text field could be any text or number.



How To Find % Of A Number
I Have 3 text boxes the first text box the user will put in the % of the number the second text box will be the number to get the % from and the third will be answeer of text 1 and text 2.

What I need to know is how to do % of a number in
Visual Basic

Thanks in Advance
Donny S.

Closest Match
When workng with palettes stored in arrays is there any good way to find the closest match for a color in one array from the other?


2 arrays

you have the color 191, 249, 121 in array2 and you want to find the closest match for that color in array1 as there is no direct match.

Any guidance would be appreciated.

Finding The Closest Value
Hi hope someone can help me,

I'm using VB 6 to write a complete shipping system for a company and I've come across a rather simple problem but I cant think of an easy solution.

I have value 'a' which i want to compare to value 'b' and value 'c' .. what I need to do is see which value ('b' or 'c') is closest to value 'a' .. it is unlikley that 'a' will ever exactly match 'b' or 'c' ..

PS - these are integer values

Any ideas?

Many thanks,

Getting Closest List Value Below Set Value?
I have a quick question.

Lets say I have a list such as the following:


If a user entered '51728', I would want to return The closest Value WITHOUT Going Over the Entered Value. So Although 52000 Is Closer, it would return 10000, as 52000 is over the entered value.


Closest But Less Than Or Equal To
Is there a function or a way to find the smallest variable in a group of variables? The variables are declared as Single.

The Closest Integer
How do i get the program to work out the closest integer to a number?

.Find For The Exponential Number
Hi All,

I am trying to work with the cell containing the minimum value from a range. I have the code working fine except for when I try to deal with transendental numbers (such as pi), which it cant register exactly (Stupid floating points!)

I've got a way around it with the code below, but just wondering if someone can astound me yet again with a better method!

Sub FindCell()
'Other Code Here

MinNum = Application.WorksheetFunction.Min(rngUse)

Set rngMin = rngUse.Find(what:=MinNum, lookat:=xlWhole)

If rngMin Is Nothing Then
Set rngMin = ClosestCell(MinNum, rngUse)
End If

'Other Code Here
End Sub

Function ClosestCell(MinNum As Double, rngTot As Range) As Range
Dim rng As Range
Dim Min As Double
Dim Diff As Double

Min = 1
For Each rng In rngTot
Diff = Abs(rng.Value - MinNum)
If Diff < Min Then
Min = Diff
Set ClosestCell = rng
End If
Next rng
End Function

Any help greatly appreciated!


How To Find Week Number?
I am trying to calculate the week number of a given date. I am using:

where k3 is the date in question
and L2 is a cell with the first day of the year (eg 01/01/2004)

This works, but what I would really like is some way of generating the value in L2 automatically.
So, the problem is this, how can I automatically return a date that represents the first of january of the current year, without using VBA?

Any ideas?

Find The Largest Number
I have to make a loop statement that will read a file full of integers such as.

etc, and A message box must pop up telling the user the largest number in the file. I thought it would be easy because all you had to do was read the file and find out what's already in there, but it must work on any file full of numbers.

Can someone help me to get started, perhaps with some pseudocode?

Im really stuck and so far, I cannot make it work.
I will post again as soon as I at least have something worth posting.

Find The Number Of Char
I have a text file that contains a LONG sentence with about 10 commas separating certain fields, (CVS). I want to get the value after lets say comma 5 and before comma 6 , is there a easy way to do this?

Trying To Find Number Of Records.....
i'm using a access database and i'm trying to find out how many records are in a table using vb. does anyone know how i would go on about doing this??

Can't FInd Reason For Err.Number
VB 6 Enterprise

See below for explanation:

This code is under a Command Button

If DeleteContract Then
SQL = "Select * from PercentTable where dbPercentContractNumber = '" & txtContractNumber.Text & "';"
Call OpenPercentTable ' Open Percent Table
Call GetPercentTable ' Get Records needed based off of SQL statement
If rstPercentName.RecordCount = 1 Then
Do Until rstPercentName.EOF
End If
Set dbgPercent.DataSource = rstPercentName
End If

Public Sub OpenPercentTable()
Set pcmdPercent.ActiveConnection = pconTracking
pcmdPercent.CommandType = adCmdTable
pcmdPercent.CommandText = "PercentTable"
Set rstPercentName = New ADODB.Recordset
rstPercentName.LockType = adLockOptimistic
rstPercentName.CursorLocation = adUseClient
rstPercentName.CursorType = adOpenKeyset
rstPercentName.Open pcmdPercent
End Sub
Get the Records needed

Public Sub GetPercentTable()
On Error Resume Next
rstPercentName.Open SQL 'See below NOTE # 1
If Err.Number = 3705 Then
Call OpenPercentTable
rstPercentName.Open SQL
Err.Number = 0
On Error GoTo 0
End If
End Sub
When I go to GetPercentTable
NOTE # 1: After this statement is executed, ERR.Number = 3705 and Err.Description says "Operation is not allowed when object is open."

So then I close the Table, call OpenPercentTable, re-issue the open. The rstPercentName.RecordCount equals ALL the records in the table (584). I should only have 3. Here's my SQL statement:
Select * from PercentTable where dbPercentPONumber = 'TY' order by dbPercentContractNumber

I don't understand why I am getting ALL THE RECORDS? When I test the SQL statement using Visual Data Manager, it works great. I get my 3 records.

Find A Digit In A Number
Hi to all:

I need to find a digit in a number like:


For example: I need to find the 5 digit (,) through a instruction

How can I do this?


Control For Find Tel Number
Do you know a free control for VB for :

It can find Tel number before hang off .


Find Next Consecutive Number
I have a ADO datastore which links to a field of numbers.

What I want to do is to scroll through the set of numbers and either ..
a. Find gap in the set
b. Find the next number in the set


1, 2, 3, 4, 6, 7, 8
I want 5 returned

otherwise, if

1, 2, 3, 4, 5, 6, 7
I want 8 returned

Number Of Record With Find
VB Code:
adors.Find "Staffno LIKE  '*" & Left(TxtEE.Text, 11) & "*' "

I need to know the number of records find upon adors.find and to have a MsgBox to show find number of record.

VB Code:
if adors.Find > 0 Thencondition1 Elsecondition2End If

The above code is incorrect. Does anyone know how to do it?
I can't use recordCount as RecordCount willl abtract number of record from SQL Select Statement.

THank you in at advance

How To Find System Number?
How to distinguish from one system to another? Is there any universal key for each system? if so how to find that system key for that system.. I want to use this in my code... please help me in this regard....

eagerly waiting for your favourable reply


How To Find Multiples Of A Number Using VB?
hi,i am learner.please tell me how to find multiples of a number using VB5.0.
thank u.

Find Version Number Of An .exe

I my vb6 application I call several external applications. On my About form I want to show the version number of these external applications. How can I get these applications version numbers.

Thanks for the help,

Dale Williams

How To Find A Number In A String?
How can I find the first occurence of a number starting on the right side of a string moving to the left? I know about the right function.

Any ideas?


How To Find A Number In A String
How to find a number in a string?
When i get a string :
mystring = "jkj kj j jjjk 4.235kkik"
i want
mynumber = 4.235

The number doesn't always stand on the same place
in the string.


Hot Ot Find And Give Number...
i have datagrid1 and data.dbf file...

Dim ConAkt As ADODB.Connection
Dim RecAkt As ADODB.Recordset
Set ConAkt = New ADODB.Connection
Set RecAkt = New ADODB.Recordset
Dim sq As String
sq = "SELECT * FROM data.dbf ORDER BY NR"
ConAkt1.Open "Driver={Microsoft dBASE Driver (*.dbf)};" & _
"DriverID=277;" & _
RecAkt.Open sq, ConAkt, adOpenStatic, adLockOptimistic
If RecAkt.BOF = True And RecAkt.EOF = True Then

Set DataGrid1.DataSource = RecAkt

i wanna in data.dbf file - column name "NR" give number all records but only one number to records who have the same value "NR" how to make this ??

How Do I Find The Nearest Number?
I have another problem with Access, i want to find a row based on the closest number i.e.

these are my numbers, they are set but there is no set pattern to them:


my problem is , if someone puts in 150 in the [Page Count] field, i want it to put 145 in the [Counter] field because this is the closest number

I used to use INDEX() and MATCH() on Excel but of course this does not work with Access


Selecting Closest Object
I'm building a board game that allows your to move the camera around to get a better view of the board (just the view matrix. All objects remain static). I need to be able to click on a piece no matter where the camera is positioned, and it needs to be the nearest object under the mouse. Exactly like the AutoParts example in the DX8.1 SDK. I looked at the code for that but got lost in about 2 minutes. They created a special class and user defined type and all this and I couldn't follow what they were doing.
Can anyone help me out?

Finding Closest Match
Is there a way through which a string find all possible close matches from some values


string "OPHTHALMOLOGY" is encountered where as original string in the DB is "OPTHALMOLOGY"

wrong value encountered "HEMATOLOGY ONCOLOGY"
correct value is "HEMATOLOGY/ONCOLOGY"

Is there any way through which these close match can be found?

Closest Match Algorithms?
Lets say i have 1000 items in a listbox, and 5 entries in an array (string)

anyone have an algorithm to compare the 5 entries in the array to the 1000 items in the listbox, and select the closest match between the 2? so in the end, itll return the string (in the array) and the listbox item that are the most similar..

Find The Two Lowest Number In Array
i have an array containing numbers
i want to find out how many numbers have the lowest value in the array for example imagine my array contains

1 2 6 5 1

i want to find out which columns hold the lowest numbers. so in this example i need some code to give me column 1 and 5

Find Highest Number In 2d Array
im making a program where people can input the votes for an election.
the user votes their choice for primeminister.
they can choose from 5 candidates.
they chose who to be as their 1st Choice, 2nd Choice and so on to 5th

after ive added the data into a 2d array i need it to calculate whether its an ABSOLUTE majority, a TIE or a RECOUNT is needed.

absolute majority is when more than half of the number of voters have chosen one candidate, and a recount is needed if a clear winner is not present.

i cant really explain what i need to be done but would really appreciate anybody who can help me and has msn messenger so i can further explain what i need and currently have

Find Out If Text Box Has String Or Number.
Is there an easy way to check if a text box is a number or a string. My program has a search feature to search for a number.
If they entered a String and do the search, bad things happen.
I just want to: msgBox("You need to enter a number."), but want to find an easy solution to finding out if the user entered a string or a number. Any ideas?
FYI: The number is a long.

Ah... Sorry BUT I Cant Find It ... How Open Line Number ?
i want to debug it but i cant find line number

How Can I Find Out If A Randomly Generated Number Is Even Or Not?
How can i find out if a randomly generated number is even or not?

How To Find String/Number In Given List?

How to find whether a given string or number, in a set of strings/numbers.

For ex:

dim aa = "abc"

'--inlist function not available in VB, but I just used for example.

if inlist(aa, "acc","abb","acc",...) then
msgbox aa & "is in list"
1. Given list may contains many strings/numbers
2. I expect, return value of boolean.
3. What is the equivalent function for 'inlist' here.

Find Out The Ascii Number Without A Form
hey, ive got a little problem...
i want to find the ascii number of a key pressed, in order to determin the next course of action. i done this with a Form_Keypress sub and it automatically gave me the integer called keyascii which worked great. i then had a select case for the different keys. but now.. cos my form has evloved into a non-form form, meanin its just a systray icon now, i cant use the Form_Keypress sub anymore
i avent got a clue how to work this now. ive got the right mouse button bringin up a menu on the icon and the left mouse button is to set the timer goin (which counts to 3) and in that time, they can press a key to make something happen (in this case IE with a certain website come up). but how do i get it to c what key has been pressed without the Form_Keypress sub? anyone know how?


Function To Find Number Of Subdirectories
this may or may not be an api question, but i was wondering if there is a function that takes a directory as input (such as "C:My Music") and calculates the number of subdirectories. I was thinking it may be an API call, because i know windows does it all the time, but i wasn't sure. I've been fooling around with the idea of using a directory box to manually go through all of the subdirectories and count them, but it takes a lot of processor time to do something like that for the big directories. Thanks, Adam

Find Highest Number / Access
Hi there. I have a little problem I'm working on.
It seems that the data I'm working with has a list of customers and customer numbers (which are unique) and then theres a CustID primary key.
Since Access wont let me convert the customer number to autonumber (and then make it a primary key) I need to figure out through some kind of query what the highest customer number is.

I am using VB6 and Access97 with ADO and using SQL queries to get my info from the database.

The table is Customer and the column I'm working with is CompanyNum

Something like

Option Explicit

Private Sub SomeCommand_CLick()

Dim rs As New ADODB.Recodset, SQL As String
Dim intMyNumber As Integer
SQL = "SELECT Customer.CompanyNum FROM Customer ORDER BY

and then

rs.Open SQL, etc.. etc..

If not rs.BOF or rs.EOF then
End if

intMyNumber = rs!CompanyNum

End Sub

Is that anywhere close?

Find A Left Over Number Range

I have number range from 1 to 10000

i have programe to take out some number say 101 to 200

then 401 to 450 and 801 to 900

How i will find left over number range

1 to 100
201 to 400
451 to 800
901 to 10000

please help me to find this number range from a to b


Find The Two Highest Number From The List
Hello all,

I just joined the forum and I am new to VB programming. I am trying to solve this problem, I hope you guys can help me.

Is there a function or a formula that can be used to display two highest numbers that has been typed in a text box.

1. Reads the number from the text box each time a button is clicked and then displays the two highest number whenever the second button is clicked.
Can anyone help me on this?

Thanks a lot in advance.

How To Find The Number Of Elements In An Array?

I have a function which returns a string array. The number of elements vary each time it is called.

Can any one tell how i can get the number of index from the calling procedure?


Flexgrid - Find Column Number
i am trying to find the column position of a column where the heading is the selected value in a combo box

my code at the moment is this...

VB Code:
For i = 1 To number_of_columns                If Results.MSFlexGrid1.TextMatrix(0, i) = Combo1.Text Then            selected_column = Results.MSFlexGrid1.Col        End If    Next

i'm probably not using the right property to get the column number

can someone help me out


Find The Number Of Running Processes
Hello People

How do I find the number of running processes?


Copyright 2005-08, All rights reserved