Find number

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

I have a form with a number field.

I need to Find 0001001 when entering 0001023 or entering 0001025 etc.

Is there a way I can do this?

I am using Windows XP and Access 2000.

Many thanks in advance.
 
0001001 is not a number, it is text. You can find all possibilities by using
the following criteria in a query:

Like [Please enter the first 5 digits] & "*"

If someone the enters: 00010

all of your combinations falling after that will be returned.
 
Thanks Arvin

I have tried the query route, but this doesn't let the users edit the record.

I have users who simply want to enter a number 0001023 NOT the first 5
digits and be taken to the record with 0001001 in the number field.

Thank you for your help.
--
Richard


Arvin Meyer said:
0001001 is not a number, it is text. You can find all possibilities by using
the following criteria in a query:

Like [Please enter the first 5 digits] & "*"

If someone the enters: 00010

all of your combinations falling after that will be returned.
--
Arvin Meyer, MCP, MVP
http://www.datastrat.com
http://www.mvps.org/access
http://www.accessmvp.com

Richard said:
I have a form with a number field.

I need to Find 0001001 when entering 0001023 or entering 0001025 etc.

Is there a way I can do this?

I am using Windows XP and Access 2000.

Many thanks in advance.
Click to expand...
Click to expand...
 
Thanks Arvin

I have tried the query route, but this doesn't let the users edit the record.

I have users who simply want to enter a number 0001023 NOT the first 5
digits and be taken to the record with 0001001 in the number field.
Click to expand...

I don't understand the logic.

In what way is 0001023 a match to 0001001?

Would 0001099 be a match? How about 0001999? How about 0084149 (they both
start with 00 after all)?

What are the rules for what constitutes a match, and what doesn't?

And - I think Arvin asked this - is this in fact a Number... Long Integer type
field in your table, formatted to show leading zeroes, or is it a Text type
field?

John W. Vinson [MVP]
 
Thank you John.

I will try and explain. The number field is in fact a text type in the
BOOKS table.

Forget whether the numbers are in fact numbers for a bit.

The numbers relate to physical health and safety books which have sheets.
Each book contains 50 sheets.

The data in the number field in the BOOKS table ascends in 50's. So the
data in the number field goes 0001001, 0001051, 0001101 etc there are no
numbers in between eg 0001023 etc.

So if I am looking at a physical safety sheet from a physical safety book
and the sheet has a number of 0001023 I want to enter this number using Find
or a search field to go to the Number field which has 0001001 in it so I can
edit this record.

I hope this makes sense.

And thank you once again.
 
Thank you John.

I will try and explain. The number field is in fact a text type in the
BOOKS table.

Forget whether the numbers are in fact numbers for a bit.

The numbers relate to physical health and safety books which have sheets.
Each book contains 50 sheets.

The data in the number field in the BOOKS table ascends in 50's. So the
data in the number field goes 0001001, 0001051, 0001101 etc there are no
numbers in between eg 0001023 etc.
Click to expand...

Thank you. My telepathy was on the blink and I was unable to perceive that. It
does indeed change the question.
So if I am looking at a physical safety sheet from a physical safety book
and the sheet has a number of 0001023 I want to enter this number using Find
or a search field to go to the Number field which has 0001001 in it so I can
edit this record.
Click to expand...

So if they enter 0001067 then it should return 0001051, or 0021583 should
return 0021551?

If so, try something like:

Left([Enter sheet no:], 5) & IIF(Val(Right([Enter sheet no:], 2) > 50, "51",
"01")

as your criterion.

John W. Vinson [MVP]
 
Thanks John

What am I supposed to do with this:

Left([Enter sheet no:], 5) & IIF(Val(Right([Enter sheet no:], 2) > 50, "51",
"01")
--
Richard


John W. Vinson said:
Thank you John.

I will try and explain. The number field is in fact a text type in the
BOOKS table.

Forget whether the numbers are in fact numbers for a bit.

The numbers relate to physical health and safety books which have sheets.
Each book contains 50 sheets.

The data in the number field in the BOOKS table ascends in 50's. So the
data in the number field goes 0001001, 0001051, 0001101 etc there are no
numbers in between eg 0001023 etc.
Click to expand...

Thank you. My telepathy was on the blink and I was unable to perceive that. It
does indeed change the question.
So if I am looking at a physical safety sheet from a physical safety book
and the sheet has a number of 0001023 I want to enter this number using Find
or a search field to go to the Number field which has 0001001 in it so I can
edit this record.
Click to expand...

So if they enter 0001067 then it should return 0001051, or 0021583 should
return 0021551?

If so, try something like:

Left([Enter sheet no:], 5) & IIF(Val(Right([Enter sheet no:], 2) > 50, "51",
"01")

as your criterion.

John W. Vinson [MVP]
Click to expand...
 
Thanks John

What am I supposed to do with this:

Left([Enter sheet no:], 5) & IIF(Val(Right([Enter sheet no:], 2) > 50, "51",
"01")
Click to expand...

Copy and paste it onto the Criteria line under the field in a Query, or
incorporate it into the query's WHERE clause. Sorry - I wasn't intentionally
leaving you in the dark but I clearly did!

John W. Vinson [MVP]
 
Thanks again John, your help is much appreciated.

I have copied and pasted the code into my query, but I am now getting this
error message:

Function is not available in expressions in query expression 'Left([Enter
sheet no:],5) & If(Val(Right([Enter sheet no:],2)>50),"51","01")'

Any idea what may be causing this?

--
Richard


John W. Vinson said:
Thanks John

What am I supposed to do with this:

Left([Enter sheet no:], 5) & IIF(Val(Right([Enter sheet no:], 2) > 50, "51",
"01")
Click to expand...

Copy and paste it onto the Criteria line under the field in a Query, or
incorporate it into the query's WHERE clause. Sorry - I wasn't intentionally
leaving you in the dark but I clearly did!

John W. Vinson [MVP]
Click to expand...
 
John

I have resolved the error message about the expression, and the code you
wrote for me is now working in the query.

There is just one last problem. The form opens and the query works and I am
at the correct record, but I can't amend any data, I am getting the message
"This recordset in not updateable.

Is there an easy solution to this. I need to be able to edit data in the
form once the query has run.

Thanks again.
--
Richard


John W. Vinson said:
Thanks John

What am I supposed to do with this:

Left([Enter sheet no:], 5) & IIF(Val(Right([Enter sheet no:], 2) > 50, "51",
"01")
Click to expand...

Copy and paste it onto the Criteria line under the field in a Query, or
incorporate it into the query's WHERE clause. Sorry - I wasn't intentionally
leaving you in the dark but I clearly did!

John W. Vinson [MVP]
Click to expand...
 
There is just one last problem. The form opens and the query works and I am
at the correct record, but I can't amend any data, I am getting the message
"This recordset in not updateable.

Is there an easy solution to this. I need to be able to edit data in the
form once the query has run.
Click to expand...

Many queries are not updateable: for instance no Totals query (nor any query
including a totals operation) will be updateable. Some multitable queries are
updateable, some aren't. See the online help for "Updateable", and/or open
your query in SQL view and post the SQL text here.

John W. Vinson [MVP]
 
Thanks John

I will checkout updateable and here is my sql query:

SELECT BOOKS.Number, BOOKS.[Number 2], BOOKS.Date, BOOKS.Forename,
BOOKS.Surname, BOOKS.Unit, BOOKS.Returned, BOOKS.Archived
FROM BOOKS
WHERE (((BOOKS.Number)=Left([Enter sheet no:],5) & IIf(Val(Right([Enter
sheet no:],2)>50),"51","01")))
GROUP BY BOOKS.Number, BOOKS.[Number 2], BOOKS.Date, BOOKS.Forename,
BOOKS.Surname, BOOKS.Unit, BOOKS.Returned, BOOKS.Archived
ORDER BY BOOKS.Number DESC;

Thanks.
 
John

I have removed the Group by from the query and i am now able to edit the
data using the query. So all of my questions has been answered.

Thank you very much for your help.

Have a great day.
 
Thanks John

I will checkout updateable and here is my sql query:

SELECT BOOKS.Number, BOOKS.[Number 2], BOOKS.Date, BOOKS.Forename,
BOOKS.Surname, BOOKS.Unit, BOOKS.Returned, BOOKS.Archived
FROM BOOKS
WHERE (((BOOKS.Number)=Left([Enter sheet no:],5) & IIf(Val(Right([Enter
sheet no:],2)>50),"51","01")))
GROUP BY BOOKS.Number, BOOKS.[Number 2], BOOKS.Date, BOOKS.Forename,
BOOKS.Surname, BOOKS.Unit, BOOKS.Returned, BOOKS.Archived
ORDER BY BOOKS.Number DESC;
Click to expand...

PMFJI, but there are several problems here, as I'm sure John will
also be quick to point out:

(1) "Date" is a reserved word in Access. If it is not conveniently
possible to rename this field/column, you should enclose it in square
brackets, i.e.:
BOOKS.[Date]
The best thing would be to rename it ASAP because otherwise, you will
be constantly having trouble with such names;

(2) You have a "GROUP BY" clause in your query, yet there are no
aggregations (i.e. SUM, AVG, MAX etc.) in the SELECT list. This by
itself would make your query non-updatable. Solution: just get rid of
everything from GROUP BY... up to ORDER BY (delete the keywords
"GROUP BY", but keep "ORDER BY" and everything after that);

(3) You have expressions like this:
(BOOKS.Number)=Left([Enter sheet no:],5) ...
which look like there should be a PARAMETERS clause at the very top
of your SQL statement, yet there is none. Where does "[Enter sheet
no:]" come from??
 
Thank you Bob

I will rename the Date fields.
--
Richard


Bob Hairgrove said:
Thanks John

I will checkout updateable and here is my sql query:

SELECT BOOKS.Number, BOOKS.[Number 2], BOOKS.Date, BOOKS.Forename,
BOOKS.Surname, BOOKS.Unit, BOOKS.Returned, BOOKS.Archived
FROM BOOKS
WHERE (((BOOKS.Number)=Left([Enter sheet no:],5) & IIf(Val(Right([Enter
sheet no:],2)>50),"51","01")))
GROUP BY BOOKS.Number, BOOKS.[Number 2], BOOKS.Date, BOOKS.Forename,
BOOKS.Surname, BOOKS.Unit, BOOKS.Returned, BOOKS.Archived
ORDER BY BOOKS.Number DESC;
Click to expand...

PMFJI, but there are several problems here, as I'm sure John will
also be quick to point out:

(1) "Date" is a reserved word in Access. If it is not conveniently
possible to rename this field/column, you should enclose it in square
brackets, i.e.:
BOOKS.[Date]
The best thing would be to rename it ASAP because otherwise, you will
be constantly having trouble with such names;

(2) You have a "GROUP BY" clause in your query, yet there are no
aggregations (i.e. SUM, AVG, MAX etc.) in the SELECT list. This by
itself would make your query non-updatable. Solution: just get rid of
everything from GROUP BY... up to ORDER BY (delete the keywords
"GROUP BY", but keep "ORDER BY" and everything after that);

(3) You have expressions like this:
(BOOKS.Number)=Left([Enter sheet no:],5) ...
which look like there should be a PARAMETERS clause at the very top
of your SQL statement, yet there is none. Where does "[Enter sheet
no:]" come from??
Click to expand...
 
Back
Top