formula "like"

[r]

under the object "table" : i designed the following table using Design View

I made a filed called "ID" and I chose Data Type "number". In the field size
I chose "long integer"

in validation Rule area i typed the formula.. Like "023?"

when I tried to enter values in the table .. an error messege came out
saying : (One or more values are prohibited by the validation rule
'Like"023?"' set for 'Table1.ID' . Enter a value that the expression for this
field can accept.)

can any one tell me how to fix this problem?

Thanks

 

[r]

I am using Access 2003

 

[KARL DEWEY]

Numbers never have leading zeros. Text can have leading zeros.

Your "Like statement is incorrect as you need a wildcard either in front,
behind, or both.

What are you trying to impose on the field?

 

[fredg]

under the object "table" : i designed the following table using Design View

I made a filed called "ID" and I chose Data Type "number". In the field size
I chose "long integer"

in validation Rule area i typed the formula.. Like "023?"

when I tried to enter values in the table .. an error messege came out
saying : (One or more values are prohibited by the validation rule
'Like"023?"' set for 'Table1.ID' . Enter a value that the expression for this
field can accept.)

can any one tell me how to fix this problem?

Thanks

It's doing exactly what you said it should.

Unfortunately, your message is not clear on what it is you want it to
do. Why not give us a few examples of what you want, and what you do
not want.

 

[r]

I am trying to insert the ID's of the students which always start with 023

 

[r]

I am trying to make a table of students ID's. every ID consists of 4 numbers
and starts with 023.

I made a filed called "ID" and I chose Data Type "number". In the field size
I chose "long integer"

I want the ID field to accept only the numbers that starts with 023 So I
put the condition in the "validation rule" as follows: Like "023?"

but the field does not accept that number. What should I do?

 

[jahoobob via AccessMonster.com]

You can't have a number with a leading 0. You will need to change your Data
Type to Text for this to work.
You are getting the error because Like compares text to text.
If you change the data type then if your ID's are 0231, 02345,1234 then Like
"023?" will return 0231. If you want it to return all the ID's that start
with 023 then you'll need Like "023*"

 

[jahoobob via AccessMonster.com]

Oops, i didn't read that this is a validation rule.
The last two sentences should read:
If you change the data type then Like "023?" will allow 0231 023H but not
02341 or . If you want it to allow only ID's that start with 023 then you'll
need Like "023*" but, again that will allow letters. You've got all kinds of
problems with this validation no matter which way you go!
I would forego the 023 part and just have them enter the part following as a
number and leave the ID data type as number. You can always add the 023 in
queries, forms, and reports

You can't have a number with a leading 0. You will need to change your Data
Type to Text for this to work.
You are getting the error because Like compares text to text.
If you change the data type then if your ID's are 0231, 02345,1234 then Like
"023?" will return 0231. If you want it to return all the ID's that start
with 023 then you'll need Like "023*"

under the object "table" : i designed the following table using Design View

[quoted text clipped - 11 lines]

Thanks

 

[fredg]

I am trying to make a table of students ID's. every ID consists of 4 numbers
and starts with 023.

I made a filed called "ID" and I chose Data Type "number". In the field size
I chose "long integer"

I want the ID field to accept only the numbers that starts with 023 So I
put the condition in the "validation rule" as follows: Like "023?"

but the field does not accept that number. What should I do?

If your student Id's always start with "023" then there is no need to
include it in the field.
Are you ever going to add one ID number to another ID number?
No. I don't think so.

Make the field a Text datatype (as long as you do not wish to use
Access AutoNumbering).

Make the ID Field text.

Enter your data using a form.
Enter just the second part of the text, i.e. 5698.
Code the ID control's AfterUpdate event:

Me![ID] = "023" & Me![ID]

The result will be an ID of "0235698"

 

[r]

Make the field a Text datatype (as long as you do not wish to use
Access AutoNumbering).

Make the ID Field text.

Enter your data using a form.
Enter just the second part of the text, i.e. 5698.
Code the ID control's AfterUpdate event:

Me![ID] = "023" & Me![ID]

The result will be an ID of "0235698"

Where should I put the code? is it in the form design area? or is it in
tables design area?
Could you please walk me through this?

Thank you

 

[fredg]

Make the field a Text datatype (as long as you do not wish to use
Access AutoNumbering).

Make the ID Field text.

Enter your data using a form.
Enter just the second part of the text, i.e. 5698.
Code the ID control's AfterUpdate event:

Me![ID] = "023" & Me![ID]

The result will be an ID of "0235698"

Where should I put the code? is it in the form design area? or is it in
tables design area?
Could you please walk me through this?

Thank you

Forms have events, not tables.
You should never enter data directly into the table.

After you change the [ID] field to a Text datatype,
open the Form used for data entry in design view.
Right-Click on the ID control.
Select Properties.
Click on the Event tab .

On the AfterUpdate line, write:
[Event Procedure]
Then click on the little button with 3 dots that appears on that line.
When the code window opens, the cursor will be flashing between 2
already existing lines of code.
Between those 2 lines, write:

Me![ID] = "023" & Me![ID]

Change [ID] to whatever the actual name of the Control is.
Close the code screen and run the form.

 

[r]

That was helpful
Thank you

Make the field a Text datatype (as long as you do not wish to use
Access AutoNumbering).

Make the ID Field text.

Enter your data using a form.
Enter just the second part of the text, i.e. 5698.
Code the ID control's AfterUpdate event:

Me![ID] = "023" & Me![ID]

The result will be an ID of "0235698"

Where should I put the code? is it in the form design area? or is it in
tables design area?
Could you please walk me through this?

Thank you

Forms have events, not tables.
You should never enter data directly into the table.

After you change the [ID] field to a Text datatype,
open the Form used for data entry in design view.
Right-Click on the ID control.
Select Properties.
Click on the Event tab .

On the AfterUpdate line, write:
[Event Procedure]
Then click on the little button with 3 dots that appears on that line.
When the code window opens, the cursor will be flashing between 2
already existing lines of code.
Between those 2 lines, write:

Me![ID] = "023" & Me![ID]

Change [ID] to whatever the actual name of the Control is.
Close the code screen and run the form.