Find nearest number

[Guest]

Hi, I am trying to create a query which will prompt for a number to be
entered as criteria and then find the closest number going downwards eg if I
entered 0000962 as criteria it would find 0000951.

I am using Windows XP and Access 2000.

Many thanks in advance.

 

[Ken Snell \(MVP\)]

SELECT DMax(YourNumberField) AS TheNumber
FROM YourTableName
WHERE YourNumberField <= [Enter the number:];

 

[Guest]

Thanks Ken

I am getting an error message:

Wrong number of arguments used with function in query expression
'DMax(Number)'.

Any ideas.
--
Richard

SELECT DMax(YourNumberField) AS TheNumber
FROM YourTableName
WHERE YourNumberField <= [Enter the number:];

--

Ken Snell
<MS ACCESS MVP>

Hi, I am trying to create a query which will prompt for a number to be
entered as criteria and then find the closest number going downwards eg if
I
entered 0000962 as criteria it would find 0000951.

I am using Windows XP and Access 2000.

Many thanks in advance.

 

[Guest]

I have gone with another solution, which suits my needs.

I have created a form that is based this query:

SELECT BOOKS.Number
FROM BOOKS
WHERE (((BOOKS.Number)<=[Enter the number:]))
GROUP BY BOOKS.Number
ORDER BY BOOKS.Number DESC;

I have a hyperlink on the main menu to this form. So the hyperlink opens
the box to enter the criteria and then opens the form based on the query and
takes me to the correct record.

--
Richard

SELECT DMax(YourNumberField) AS TheNumber
FROM YourTableName
WHERE YourNumberField <= [Enter the number:];

--

Ken Snell
<MS ACCESS MVP>

Hi, I am trying to create a query which will prompt for a number to be
entered as criteria and then find the closest number going downwards eg if
I
entered 0000962 as criteria it would find 0000951.

I am using Windows XP and Access 2000.

Many thanks in advance.

 

[John Spencer]

Ken has a small error in his SQL statement. He obviously meant

SELECT MAX([YourNumberField])
FROM YourTableName
WHERE [YourNumberField] <=[Enter the Number]

--
John Spencer
Access MVP 2002-2005, 2007
Center for Health Program Development and Management
University of Maryland Baltimore County
..

SELECT DMax(YourNumberField) AS TheNumber
FROM YourTableName
WHERE YourNumberField <= [Enter the number:];

--

Ken Snell
<MS ACCESS MVP>

Hi, I am trying to create a query which will prompt for a number to be
entered as criteria and then find the closest number going downwards eg
if I
entered 0000962 as criteria it would find 0000951.

I am using Windows XP and Access 2000.

Many thanks in advance.

 

[Ken Snell \(MVP\)]

Thanks, John.... slip of the fingers.

Sorry, Richard, for the typo.

--

Ken Snell
<MS ACCESS MVP>

Ken has a small error in his SQL statement. He obviously meant

SELECT MAX([YourNumberField])
FROM YourTableName
WHERE [YourNumberField] <=[Enter the Number]

--
John Spencer
Access MVP 2002-2005, 2007
Center for Health Program Development and Management
University of Maryland Baltimore County
.

SELECT DMax(YourNumberField) AS TheNumber
FROM YourTableName
WHERE YourNumberField <= [Enter the number:];

--

Ken Snell
<MS ACCESS MVP>

Hi, I am trying to create a query which will prompt for a number to be
entered as criteria and then find the closest number going downwards eg
if I
entered 0000962 as criteria it would find 0000951.

I am using Windows XP and Access 2000.

Many thanks in advance.

 

[Guest]

Thanks Ken and John

The amended version of Ken's query works as well. Thank you both for your
time.
--
Richard

Thanks, John.... slip of the fingers.

Sorry, Richard, for the typo.

--

Ken Snell
<MS ACCESS MVP>

Ken has a small error in his SQL statement. He obviously meant

SELECT MAX([YourNumberField])
FROM YourTableName
WHERE [YourNumberField] <=[Enter the Number]

--
John Spencer
Access MVP 2002-2005, 2007
Center for Health Program Development and Management
University of Maryland Baltimore County
.

SELECT DMax(YourNumberField) AS TheNumber
FROM YourTableName
WHERE YourNumberField <= [Enter the number:];

--

Ken Snell
<MS ACCESS MVP>


Hi, I am trying to create a query which will prompt for a number to be
entered as criteria and then find the closest number going downwards eg
if I
entered 0000962 as criteria it would find 0000951.

I am using Windows XP and Access 2000.

Many thanks in advance.