adding leading "0" to text field

  • Thread starter Thread starter carol
  • Start date Start date
C

carol

i am referencing a post from the 12/17 and using
Right$("00000"& fieldname,5) in update query but query
builder indicates cannot compile. here is the query code.
pls advise what i am doing wrong. thx.
UPDATE checkdigittest SET checkdigittest.[pfg#] =
Right$("000000" & "[pfg#]",6)
 
Change it to this:

UPDATE checkdigittest SET checkdigittest.[pfg#] =
Right$("000000" & [pfg#], 6)
 
took off " marks, used your expression however, still
getting compile error. help!! thx.

-----Original Message-----
Change it to this:

UPDATE checkdigittest SET checkdigittest.[pfg#] =
Right$("000000" & [pfg#], 6)


--
Ken Snell
<MS ACCESS MVP>

i am referencing a post from the 12/17 and using
Right$("00000"& fieldname,5) in update query but query
builder indicates cannot compile. here is the query code.
pls advise what i am doing wrong. thx.
UPDATE checkdigittest SET checkdigittest.[pfg#] =
Right$("000000" & "[pfg#]",6)
Click to expand...


.
Click to expand...
 
UPDATE checkdigittest SET checkdigittest.[pfg#] = Right$("000000" &
checkdigittest.[pfg#], 6);

Try the above.
--
Ken Snell
<MS ACCESS MVP>


carol said:
took off " marks, used your expression however, still
getting compile error. help!! thx.

-----Original Message-----
Change it to this:

UPDATE checkdigittest SET checkdigittest.[pfg#] =
Right$("000000" & [pfg#], 6)


--
Ken Snell
<MS ACCESS MVP>

i am referencing a post from the 12/17 and using
Right$("00000"& fieldname,5) in update query but query
builder indicates cannot compile. here is the query code.
pls advise what i am doing wrong. thx.
UPDATE checkdigittest SET checkdigittest.[pfg#] =
Right$("000000" & "[pfg#]",6)
Click to expand...


.
Click to expand...
Click to expand...
 
sorry to have to bother you again but did try your code as
indicated below. this is what i have copied from sql view:

UPDATE checkdigittest SET checkdigittest.[pfg#] =
Right$("000000" & checkdigittest.[pfg#], 6);

thx much for your help!
-----Original Message-----
UPDATE checkdigittest SET checkdigittest.[pfg#] =
Click to expand...
Right$("000000" & checkdigittest.[pfg#], 6);
Try the above.
--
Ken Snell
<MS ACCESS MVP>


took off " marks, used your expression however, still
getting compile error. help!! thx.

-----Original Message-----
Change it to this:

UPDATE checkdigittest SET checkdigittest.[pfg#] =
Right$("000000" & [pfg#], 6)


--
Ken Snell
<MS ACCESS MVP>

i am referencing a post from the 12/17 and using
Right$("00000"& fieldname,5) in update query but query
builder indicates cannot compile. here is the query code.
pls advise what i am doing wrong. thx.
UPDATE checkdigittest SET checkdigittest.[pfg#] =
Right$("000000" & "[pfg#]",6)


.
Click to expand...
Click to expand...


.
Click to expand...
 
It may be that your references are messed up, so that it's not recognizing
the Right$ function.

This can be caused by differences in either the location or file version of
certain files between the machine where the application was developed, and
where it's being run (or the file missing completely from the target
machine). Such differences are common when new software is installed.

On the machine(s) where it's not working, open any code module (or open the
Debug Window, using Ctrl-G, provided you haven't selected the "keep debug
window on top" option). Select Tools | References from the menu bar. Examine
all of the selected references.

If any of the selected references have "MISSING:" in front of them, unselect
them, and back out of the dialog. If you really need the reference(s) you
just unselected (you can tell by doing a Compile All Modules), go back in
and reselect them.

If none have "MISSING:", select an additional reference at random, back out
of the dialog, then go back in and unselect the reference you just added. If
that doesn't solve the problem, try to unselect as many of the selected
references as you can (Access may not let you unselect them all), back out
of the dialog, then go back in and reselect the references you just
unselected. (NOTE: write down what the references are before you delete
them, because they'll be in a different order when you go back in)



--
Doug Steele, Microsoft Access MVP

(No private e-mails, please)



carol said:
sorry to have to bother you again but did try your code as
indicated below. this is what i have copied from sql view:

UPDATE checkdigittest SET checkdigittest.[pfg#] =
Right$("000000" & checkdigittest.[pfg#], 6);

thx much for your help!
-----Original Message-----
UPDATE checkdigittest SET checkdigittest.[pfg#] =
Click to expand...
Right$("000000" & checkdigittest.[pfg#], 6);
Try the above.
--
Ken Snell
<MS ACCESS MVP>


took off " marks, used your expression however, still
getting compile error. help!! thx.


-----Original Message-----
Change it to this:

UPDATE checkdigittest SET checkdigittest.[pfg#] =
Right$("000000" & [pfg#], 6)


--
Ken Snell
<MS ACCESS MVP>

message
i am referencing a post from the 12/17 and using
Right$("00000"& fieldname,5) in update query but query
builder indicates cannot compile. here is the query
code.
pls advise what i am doing wrong. thx.
UPDATE checkdigittest SET checkdigittest.[pfg#] =
Right$("000000" & "[pfg#]",6)


.
Click to expand...


.
Click to expand...
Click to expand...
 
thx, i am also going to reinstall my add-in's and possibly
do a reinstall as i've noticed some other problems with
functions not being recognized. thx for bringing this to
my attention.
-----Original Message-----
It may be that your references are messed up, so that it's not recognizing
the Right$ function.

This can be caused by differences in either the location or file version of
certain files between the machine where the application was developed, and
where it's being run (or the file missing completely from the target
machine). Such differences are common when new software is installed.

On the machine(s) where it's not working, open any code module (or open the
Debug Window, using Ctrl-G, provided you haven't selected the "keep debug
window on top" option). Select Tools | References from the menu bar. Examine
all of the selected references.

If any of the selected references have "MISSING:" in front of them, unselect
them, and back out of the dialog. If you really need the reference(s) you
just unselected (you can tell by doing a Compile All Modules), go back in
and reselect them.

If none have "MISSING:", select an additional reference at random, back out
of the dialog, then go back in and unselect the reference you just added. If
that doesn't solve the problem, try to unselect as many of the selected
references as you can (Access may not let you unselect them all), back out
of the dialog, then go back in and reselect the references you just
unselected. (NOTE: write down what the references are before you delete
them, because they'll be in a different order when you go back in)



--
Doug Steele, Microsoft Access MVP

(No private e-mails, please)



sorry to have to bother you again but did try your code as
indicated below. this is what i have copied from sql view:

UPDATE checkdigittest SET checkdigittest.[pfg#] =
Right$("000000" & checkdigittest.[pfg#], 6);

thx much for your help!
-----Original Message-----
UPDATE checkdigittest SET checkdigittest.[pfg#] =
Click to expand...
Right$("000000" & checkdigittest.[pfg#], 6);
Try the above.
--
Ken Snell
<MS ACCESS MVP>


took off " marks, used your expression however, still
getting compile error. help!! thx.


-----Original Message-----
Change it to this:

UPDATE checkdigittest SET checkdigittest.[pfg#] =
Right$("000000" & [pfg#], 6)


--
Ken Snell
<MS ACCESS MVP>

message
i am referencing a post from the 12/17 and using
Right$("00000"& fieldname,5) in update query but query
builder indicates cannot compile. here is the query
code.
pls advise what i am doing wrong. thx.
UPDATE checkdigittest SET checkdigittest. [pfg#] =
Right$("000000" & "[pfg#]",6)


.



.
Click to expand...
Click to expand...


.
Click to expand...
 
Check your references first: there may be no need for a reinstall. In fact,
if there is a References problem, doing a reinstall is not likely going to
fix the problem. References are created as part of the database when it's
created. Using it on a different system, or changing the system (installing
new software, etc.) after you created the database will cause the references
to break. Unless your reinistall takes the system back to exactly how it was
when the database was first created (unlikely), it won't help.


--
Doug Steele, Microsoft Access MVP

(No private e-mails, please)



carol said:
thx, i am also going to reinstall my add-in's and possibly
do a reinstall as i've noticed some other problems with
functions not being recognized. thx for bringing this to
my attention.
-----Original Message-----
It may be that your references are messed up, so that it's not recognizing
the Right$ function.

This can be caused by differences in either the location or file version of
certain files between the machine where the application was developed, and
where it's being run (or the file missing completely from the target
machine). Such differences are common when new software is installed.

On the machine(s) where it's not working, open any code module (or open the
Debug Window, using Ctrl-G, provided you haven't selected the "keep debug
window on top" option). Select Tools | References from the menu bar. Examine
all of the selected references.

If any of the selected references have "MISSING:" in front of them, unselect
them, and back out of the dialog. If you really need the reference(s) you
just unselected (you can tell by doing a Compile All Modules), go back in
and reselect them.

If none have "MISSING:", select an additional reference at random, back out
of the dialog, then go back in and unselect the reference you just added. If
that doesn't solve the problem, try to unselect as many of the selected
references as you can (Access may not let you unselect them all), back out
of the dialog, then go back in and reselect the references you just
unselected. (NOTE: write down what the references are before you delete
them, because they'll be in a different order when you go back in)



--
Doug Steele, Microsoft Access MVP

(No private e-mails, please)



sorry to have to bother you again but did try your code as
indicated below. this is what i have copied from sql view:

UPDATE checkdigittest SET checkdigittest.[pfg#] =
Right$("000000" & checkdigittest.[pfg#], 6);

thx much for your help!

-----Original Message-----
UPDATE checkdigittest SET checkdigittest.[pfg#] =
Right$("000000" & checkdigittest.[pfg#], 6);

Try the above.
--
Ken Snell
<MS ACCESS MVP>


message
took off " marks, used your expression however, still
getting compile error. help!! thx.


-----Original Message-----
Change it to this:

UPDATE checkdigittest SET checkdigittest.[pfg#] =
Right$("000000" & [pfg#], 6)


--
Ken Snell
<MS ACCESS MVP>

message
i am referencing a post from the 12/17 and using
Right$("00000"& fieldname,5) in update query but
query
builder indicates cannot compile. here is the query
code.
pls advise what i am doing wrong. thx.
UPDATE checkdigittest SET checkdigittest. [pfg#] =
Right$("000000" & "[pfg#]",6)


.



.
Click to expand...


.
Click to expand...
Click to expand...
 
hi again douglas...

i have successfully corrected my problem following your
suggestion. this board is great support and thx for your
timeliness with your response as i needed this for a
project that i am working on at work!

it would be great if one day, all your tech info could be
compiled and organized as it looks like there are several
topics that are very common problems for folks. i have
used the search tool but still is cumbersome to find what
you need. i would think that it would also aleviate some
of the burden on you guys too! thx again and happy new
year!
 
Back
Top