lower and upper limit fieldproperty fieldsize

[pEkvo]

I have a database and one of the fields is a destination code, now I need to be able to say that when someone enters a destination code, this code has to be at least 3 characters long and can be no more than 6 characters long.

I thought I would be able to do this by doing something in the fieldsize option but that only gives me an upper limit (i.e. a maximum number of characters), how do I do this? I've tried using fieldsize, set to 6 and then write in the validation rule "Len(DestinationCode)>=3" but this doesn't work...

Can anyone help me out? The destinationcode is a textfield.

 

[DCrake]

Depending on whether you are using a bound or unbound form you can perform validation by the following

If Len(Me.Activecontrol) < 3 Or Len(Me.Activecontrol) >6 Then
   .ooops
End If

Place this on either the BeforeUpdate event (Bound forms) or AfterUpdate event (Unbound forms)

David

 

[pEkvo]

I'm actually not in any kind of form, i'm just in the Table Design View. I'm inputting the destination codes straight into the table and that's where I want the limits to work.

I'm sorry if I was unclear.
Thomas

 

[DCrake]

At the end of the day how will this data be collected? I hope not directly into a table! Ok you can apply validation to a field but I tend to perfrom validation at point of entry.
Tables are there to hold the data.
Queries are there to display the data
Forms are used to collect the data
Reports are there to print out the data

 

[pEkvo]

Well, I have no idea, I'm just studying for an Access exam tomorrow and the teacher posted a 'test exam' and that was the one question I was having trouble with.

 

[Dennisk]

Ah so its homework

 

[pEkvo]

No, nothing get's handed in, this is just the exam the pupils from last year got and the teacher put it online for us, so we can get a look at what kind of questions we can expect, none of those questions will ever get evaluated, I just wanted to make sure I was able to do every question on that exam.

 

[Atomic Shrimp]

You can't assign event code directly to a table, so there's no way to do really complex validation on data at that level.

You can validate entries with a validation rule ( Len([fieldname])>2 And Len([fieldname])<7 ), or possibly do something clever with input masks, but I'm with DCrake on this - the place to do it is in a form - then you can manage the way the error is handled far more elegantly.

Personally, I try to keep tables as straightforward as possible - so that they are easy for the DBA/programmer to deal with - complexity can be added in the elements that use the table.

 

[pEkvo]

Okay thanks, the validation rule worked and I had thought of it but I had put (Len([fieldname])>=3 And Len([fieldname])=<6) and Access didn't accept that, I did it your way and it works perfect! Thanks a lot guys.