Using Custom Autonumber

[pathfinder225]

hi frnds, the following is my db schema

service_no:AUTONUMBER
name:Text
day:Date/Time

My Autonumber field is of the format SExxx (ie SE000,SE001,SE002...)

the following query doesn't work

select * from table_name where service_no=SE002

but this works ..

select * from table_name where service_no=2 
(or)
select * from table_name where service_no=002

user will input service_no in format SExxx to retrieve records if the above format doesn't work how can i write queries to retrieve them,i don't have any idea of VBA please help me Thank you.

 

[Galaxiom]

hi frnds, the following is my db schema

service_no:AUTONUMBER
name:Text
day:Date/Time

My Autonumber field is of the format SExxx (ie SE000,SE001,SE002...)

the following query doesn't work

select * from table_name where service_no=SE002

but this works ..

select * from table_name where service_no=2 
(or)
select * from table_name where service_no=002

Your SExxx numbers cannot be an Autonumber field. The word Autonumber has a specific meaning in databases and they are integers that increment automatically from the value in the previous record.

The query that works is testing a field against a number so the service_no field is being populated with numbers not SExxx.

You will have to put your service number in a different field. As a mixed alphnumeric value it must be a text field. Consequently it must be tested against a string that is expressed between quote marks.

select * from table_name where service_no="SE002"

 

[raskew]

Hi -

day: Date/Time

In addition to the previous suggestion, it would be wise to change this field name since Day is a reserved word in Access, i.e. it's the name of an internal function and using it as a field name may cause problems down the road.

Bob

 

[pathfinder225]

You will have to put your service number in a different field. As a mixed alphnumeric value it must be a text field. Consequently it must be tested against a string that is expressed between quote marks.

select * from table_name where service_no="SE002"

Thanks for replying, well service number must be generated automatically ,which is possible if i use it as autonumber. If i use it as text type i have to insert the service no eachtime. So, what should i do now, any solution?

 

[raskew]

Hi -

Here's an example of how you might handle it:

SELECT Reservations.ID, "SE" & Format([ID],"000") AS x
FROM Reservations;

Keep in mind that 'real' numbers do not begin with alpha characters or leading zeros.

Bob