Making a variable number field into a fixed digit field

[AJCB]

Hi,

I have a field that is a variable number. It can be either a single digit
or 3 digits.
Eg.. 1, 11, 111

How can I make this field so that it reads: 001, 011, 111?

Regards
AJ

 

[Allen Browne]

Set its Format property to:
000

 

[AJCB]

Thanks for this, it worked when I view that particular field.

However, when I went to join that field to another field, it reverted back
to 1 instead of 001.

Any other ideas?

 

[Allen Browne]

In an expression, use:
Format([f], "000")
substituting your field name for [f]

 

[AJCB]

Sorry, but this has not worked either. I put the following formula in:

Format([Check Digit],"000")

[Check Digit] being my field and it came up with a dialog box which said:

Undefined function 'Format' in expression

With the options "OK" and "Help".

Please help. This is stressing me out.

AJ

In an expression, use:
Format([f], "000")
substituting your field name for [f]

--
Allen Browne - Microsoft MVP. Perth, Western Australia

Reply to group, rather than allenbrowne at mvps dot org.

Thanks for this, it worked when I view that particular field.

However, when I went to join that field to another field, it reverted back
to 1 instead of 001.

Any other ideas?

 

[Allen Browne]

In what context are you doing this?

Try in the Field row in query design.

If this is Access 2007, make sure your database is in a trusted location.

Format() is a function built into Access itself.

--
Allen Browne - Microsoft MVP. Perth, Western Australia

Reply to group, rather than allenbrowne at mvps dot org.

Sorry, but this has not worked either. I put the following formula in:

Format([Check Digit],"000")

[Check Digit] being my field and it came up with a dialog box which said:

Undefined function 'Format' in expression

With the options "OK" and "Help".

Please help. This is stressing me out.

AJ

In an expression, use:
Format([f], "000")
substituting your field name for [f]

Thanks for this, it worked when I view that particular field.

However, when I went to join that field to another field, it reverted
back
to 1 instead of 001.

Any other ideas?

:

Set its Format property to:
000

Hi,

I have a field that is a variable number. It can be either a
single
digit
or 3 digits.
Eg.. 1, 11, 111

How can I make this field so that it reads: 001, 011, 111?

 

[AJCB]

I have done this in biuld and also written it in the field section of the
query.

I am running Microsoft Access 2000 with service pack 3.

When I am in build, and go to functions > built-in functions, the format
function is there, but when you double click on it to insert it, it comes up
with a string as follows:

Format («expr», «fmt», «firstweekday», «firstweek»)

Also, I have an Access 95 book, which also refers to the format functions as
a date format function.

Any more ideas?

In what context are you doing this?

Try in the Field row in query design.

If this is Access 2007, make sure your database is in a trusted location.

Format() is a function built into Access itself.

--
Allen Browne - Microsoft MVP. Perth, Western Australia

Reply to group, rather than allenbrowne at mvps dot org.

Sorry, but this has not worked either. I put the following formula in:

Format([Check Digit],"000")

[Check Digit] being my field and it came up with a dialog box which said:

Undefined function 'Format' in expression

With the options "OK" and "Help".

Please help. This is stressing me out.

AJ

In an expression, use:
Format([f], "000")
substituting your field name for [f]

Thanks for this, it worked when I view that particular field.

However, when I went to join that field to another field, it reverted
back
to 1 instead of 001.

Any other ideas?

:

Set its Format property to:
000

Hi,

I have a field that is a variable number. It can be either a
single
digit
or 3 digits.
Eg.. 1, 11, 111

How can I make this field so that it reads: 001, 011, 111?

 

[Allen Browne]

Perhaps you have a problem with library references missing:
http://allenbrowne.com/ser-38.html

--
Allen Browne - Microsoft MVP. Perth, Western Australia

Reply to group, rather than allenbrowne at mvps dot org.

I have done this in biuld and also written it in the field section of the
query.

I am running Microsoft Access 2000 with service pack 3.

When I am in build, and go to functions > built-in functions, the format
function is there, but when you double click on it to insert it, it comes
up
with a string as follows:

Format («expr», «fmt», «firstweekday», «firstweek»)

Also, I have an Access 95 book, which also refers to the format functions
as
a date format function.

Any more ideas?

In what context are you doing this?

Try in the Field row in query design.

If this is Access 2007, make sure your database is in a trusted location.

Format() is a function built into Access itself.

Sorry, but this has not worked either. I put the following formula in:

Format([Check Digit],"000")

[Check Digit] being my field and it came up with a dialog box which
said:

Undefined function 'Format' in expression

With the options "OK" and "Help".

Please help. This is stressing me out.

AJ

:

In an expression, use:
Format([f], "000")
substituting your field name for [f]

Thanks for this, it worked when I view that particular field.

However, when I went to join that field to another field, it
reverted
back
to 1 instead of 001.

Any other ideas?

:

Set its Format property to:
000

Hi,

I have a field that is a variable number. It can be either a
single
digit
or 3 digits.
Eg.. 1, 11, 111

How can I make this field so that it reads: 001, 011, 111?

 

[AJCB]

I did what it said in this link, but it still did not work.

I tried it on my laptop which has access 2000, and I tried it on our server
which has access 2007, and still had the same error message. I think that it
has something to do with the string i mentioned below, that the format
expression is for date formats.

Format («expr», «fmt», «firstweekday», «firstweek»)

Is there any other way this can be done, preferably without using SQL.

Regards
AJ

Perhaps you have a problem with library references missing:
http://allenbrowne.com/ser-38.html

--
Allen Browne - Microsoft MVP. Perth, Western Australia

Reply to group, rather than allenbrowne at mvps dot org.

I have done this in biuld and also written it in the field section of the
query.

I am running Microsoft Access 2000 with service pack 3.

When I am in build, and go to functions > built-in functions, the format
function is there, but when you double click on it to insert it, it comes
up
with a string as follows:

Format («expr», «fmt», «firstweekday», «firstweek»)

Also, I have an Access 95 book, which also refers to the format functions
as
a date format function.

Any more ideas?

In what context are you doing this?

Try in the Field row in query design.

If this is Access 2007, make sure your database is in a trusted location.

Format() is a function built into Access itself.

Sorry, but this has not worked either. I put the following formula in:

Format([Check Digit],"000")

[Check Digit] being my field and it came up with a dialog box which
said:

Undefined function 'Format' in expression

With the options "OK" and "Help".

Please help. This is stressing me out.

AJ

:

In an expression, use:
Format([f], "000")
substituting your field name for [f]

Thanks for this, it worked when I view that particular field.

However, when I went to join that field to another field, it
reverted
back
to 1 instead of 001.

Any other ideas?

:

Set its Format property to:
000

Hi,

I have a field that is a variable number. It can be either a
single
digit
or 3 digits.
Eg.. 1, 11, 111

How can I make this field so that it reads: 001, 011, 111?

 

[Allen Browne]

The format expression you quote is for dates.
You don't need that.

As indicated earlier, you just need:
=Format([f], "000")

This is a really basic issue. If it does not recognise the Format()
function, there is something wrong with the way Access is installed on the
computer.

--
Allen Browne - Microsoft MVP. Perth, Western Australia

Reply to group, rather than allenbrowne at mvps dot org.

I did what it said in this link, but it still did not work.

I tried it on my laptop which has access 2000, and I tried it on our
server
which has access 2007, and still had the same error message. I think that
it
has something to do with the string i mentioned below, that the format
expression is for date formats.

Format («expr», «fmt», «firstweekday», «firstweek»)

Is there any other way this can be done, preferably without using SQL.

Regards
AJ

Perhaps you have a problem with library references missing:
http://allenbrowne.com/ser-38.html

I have done this in biuld and also written it in the field section of
the
query.

I am running Microsoft Access 2000 with service pack 3.

When I am in build, and go to functions > built-in functions, the
format
function is there, but when you double click on it to insert it, it
comes
up
with a string as follows:

Format («expr», «fmt», «firstweekday», «firstweek»)

Also, I have an Access 95 book, which also refers to the format
functions
as
a date format function.

Any more ideas?

:

In what context are you doing this?

Try in the Field row in query design.

If this is Access 2007, make sure your database is in a trusted
location.

Format() is a function built into Access itself.

Sorry, but this has not worked either. I put the following formula
in:

Format([Check Digit],"000")

[Check Digit] being my field and it came up with a dialog box which
said:

Undefined function 'Format' in expression

With the options "OK" and "Help".

Please help. This is stressing me out.

AJ

:

In an expression, use:
Format([f], "000")
substituting your field name for [f]

Thanks for this, it worked when I view that particular field.

However, when I went to join that field to another field, it
reverted
back
to 1 instead of 001.

Any other ideas?

:

Set its Format property to:
000

Hi,

I have a field that is a variable number. It can be either a
single
digit
or 3 digits.
Eg.. 1, 11, 111

How can I make this field so that it reads: 001, 011, 111?

 

[AJCB]

I have put this into the field.

What I cannot get my head around is that I have tried it on my laptop and I
have also tried it on Access 2007.

I think I will just have to accept that this cannot be done.

Thanks for your help.

Regards
AJ

The format expression you quote is for dates.
You don't need that.

As indicated earlier, you just need:
=Format([f], "000")

This is a really basic issue. If it does not recognise the Format()
function, there is something wrong with the way Access is installed on the
computer.

--
Allen Browne - Microsoft MVP. Perth, Western Australia

Reply to group, rather than allenbrowne at mvps dot org.

I did what it said in this link, but it still did not work.

I tried it on my laptop which has access 2000, and I tried it on our
server
which has access 2007, and still had the same error message. I think that
it
has something to do with the string i mentioned below, that the format
expression is for date formats.

Format («expr», «fmt», «firstweekday», «firstweek»)

Is there any other way this can be done, preferably without using SQL.

Regards
AJ

Perhaps you have a problem with library references missing:
http://allenbrowne.com/ser-38.html

I have done this in biuld and also written it in the field section of
the
query.

I am running Microsoft Access 2000 with service pack 3.

When I am in build, and go to functions > built-in functions, the
format
function is there, but when you double click on it to insert it, it
comes
up
with a string as follows:

Format («expr», «fmt», «firstweekday», «firstweek»)

Also, I have an Access 95 book, which also refers to the format
functions
as
a date format function.

Any more ideas?

:

In what context are you doing this?

Try in the Field row in query design.

If this is Access 2007, make sure your database is in a trusted
location.

Format() is a function built into Access itself.

Sorry, but this has not worked either. I put the following formula
in:

Format([Check Digit],"000")

[Check Digit] being my field and it came up with a dialog box which
said:

Undefined function 'Format' in expression

With the options "OK" and "Help".

Please help. This is stressing me out.

AJ

:

In an expression, use:
Format([f], "000")
substituting your field name for [f]

Thanks for this, it worked when I view that particular field.

However, when I went to join that field to another field, it
reverted
back
to 1 instead of 001.

Any other ideas?

:

Set its Format property to:
000

Hi,

I have a field that is a variable number. It can be either a
single
digit
or 3 digits.
Eg.. 1, 11, 111

How can I make this field so that it reads: 001, 011, 111?

 

[AJCB]

I have managed to sort the problem.

It was a problem with references.

I had to open up a code box in access, go to tools, then references.

There was a reference that had "missing" in the description.

I unchecked this box, pressed ok, then closed the code box down, and it
started working straight away.

Thanks for your help.

Incidently. My mothers maiden name is Browne (with an "e"). I don't
suppose your family originate from Granard in Ireland?...

I have put this into the field.

What I cannot get my head around is that I have tried it on my laptop and I
have also tried it on Access 2007.

I think I will just have to accept that this cannot be done.

Thanks for your help.

Regards
AJ

The format expression you quote is for dates.
You don't need that.

As indicated earlier, you just need:
=Format([f], "000")

This is a really basic issue. If it does not recognise the Format()
function, there is something wrong with the way Access is installed on the
computer.

--
Allen Browne - Microsoft MVP. Perth, Western Australia

Reply to group, rather than allenbrowne at mvps dot org.

I did what it said in this link, but it still did not work.

I tried it on my laptop which has access 2000, and I tried it on our
server
which has access 2007, and still had the same error message. I think that
it
has something to do with the string i mentioned below, that the format
expression is for date formats.

Format («expr», «fmt», «firstweekday», «firstweek»)

Is there any other way this can be done, preferably without using SQL.

Regards
AJ

:

Perhaps you have a problem with library references missing:
http://allenbrowne.com/ser-38.html

I have done this in biuld and also written it in the field section of
the
query.

I am running Microsoft Access 2000 with service pack 3.

When I am in build, and go to functions > built-in functions, the
format
function is there, but when you double click on it to insert it, it
comes
up
with a string as follows:

Format («expr», «fmt», «firstweekday», «firstweek»)

Also, I have an Access 95 book, which also refers to the format
functions
as
a date format function.

Any more ideas?

:

In what context are you doing this?

Try in the Field row in query design.

If this is Access 2007, make sure your database is in a trusted
location.

Format() is a function built into Access itself.

Sorry, but this has not worked either. I put the following formula
in:

Format([Check Digit],"000")

[Check Digit] being my field and it came up with a dialog box which
said:

Undefined function 'Format' in expression

With the options "OK" and "Help".

Please help. This is stressing me out.

AJ

:

In an expression, use:
Format([f], "000")
substituting your field name for [f]

Thanks for this, it worked when I view that particular field.

However, when I went to join that field to another field, it
reverted
back
to 1 instead of 001.

Any other ideas?

:

Set its Format property to:
000

Hi,

I have a field that is a variable number. It can be either a
single
digit
or 3 digits.
Eg.. 1, 11, 111

How can I make this field so that it reads: 001, 011, 111?

 

[Guest]

The format expression you quote is for dates.
You don't need that.

As indicated earlier, you just need:
=Format([f], "000")

This is a really basic issue. If it does not recognise the Format()
function, there is something wrong with the way Access is installed on the
computer.

--
Allen Browne - Microsoft MVP. Perth, Western Australia

Reply to group, rather than allenbrowne at mvps dot org.

I did what it said in this link, but it still did not work.

I tried it on my laptop which has access 2000, and I tried it on our
server
which has access 2007, and still had the same error message. I think
that it
has something to do with the string i mentioned below, that the format
expression is for date formats.

Format («expr», «fmt», «firstweekday», «firstweek»)

Is there any other way this can be done, preferably without using SQL.

Regards
AJ

Perhaps you have a problem with library references missing:
http://allenbrowne.com/ser-38.html

I have done this in biuld and also written it in the field section of
the
query.

I am running Microsoft Access 2000 with service pack 3.

When I am in build, and go to functions > built-in functions, the
format
function is there, but when you double click on it to insert it, it
comes
up
with a string as follows:

Format («expr», «fmt», «firstweekday», «firstweek»)

Also, I have an Access 95 book, which also refers to the format
functions
as
a date format function.

Any more ideas?

:

In what context are you doing this?

Try in the Field row in query design.

If this is Access 2007, make sure your database is in a trusted
location.

Format() is a function built into Access itself.

Sorry, but this has not worked either. I put the following formula
in:

Format([Check Digit],"000")

[Check Digit] being my field and it came up with a dialog box which
said:

Undefined function 'Format' in expression

With the options "OK" and "Help".

Please help. This is stressing me out.

AJ

:

In an expression, use:
Format([f], "000")
substituting your field name for [f]

Thanks for this, it worked when I view that particular field.

However, when I went to join that field to another field, it
reverted
back
to 1 instead of 001.

Any other ideas?

:

Set its Format property to:
000

Hi,

I have a field that is a variable number. It can be either
a
single
digit
or 3 digits.
Eg.. 1, 11, 111

How can I make this field so that it reads: 001, 011, 111?