Formatting values to 00000 not working

[Kurt]

I have a combo box (cboRespondant) with this for a row
source:

SELECT DISTINCTROW tblSrvRspns.RspnsID FROM tblSrvRspns
UNION SELECT "(All)" From tblSrvRspns ORDER BY
tblSrvRspns.RspnsID;

The RspnsIDs are stored as 1, 2, 10, etc. (they are
generated from an autonumber elsewhere on a form), but
I'd like them to appear in the combo box as 00001, 00002,
00010, etc. I set the Format property of the combo box to
00000, but the IDs still appear as 1, 2, 10, etc. Also,
the 00000 format makes the "(All)" option stop working:
"The value you entered isn't valid for this field.".

Any idea how to fix this? Thanks. - Kurt

 

[Marshall Barton]

I have a combo box (cboRespondant) with this for a row
source:

SELECT DISTINCTROW tblSrvRspns.RspnsID FROM tblSrvRspns
UNION SELECT "(All)" From tblSrvRspns ORDER BY
tblSrvRspns.RspnsID;

The RspnsIDs are stored as 1, 2, 10, etc. (they are
generated from an autonumber elsewhere on a form), but
I'd like them to appear in the combo box as 00001, 00002,
00010, etc. I set the Format property of the combo box to
00000, but the IDs still appear as 1, 2, 10, etc. Also,
the 00000 format makes the "(All)" option stop working:
"The value you entered isn't valid for this field.".

First, don't use DISTINCTROW unless you really need it.
Most likely, the UNION will eliminate any duplicates in the
result dataset that you might be concerned about. If you
have no reason to worry about duplicate results, then you
should use UNION ALL since it can be much faster.

Your query raises the issue between using UNION and UNION
ALL because the second query is using a table with many
records causing it to return as many records containing
"(All)" as the tblSrvRspns table has records. Then you have
to use UNION to get rid af all those duplicate records. The
optimal way to do this is to change the secord SELECT to use
a table with one row (create one if you don't have a
convenient one row table to use for this purpose).

To answer your original question, you can format the id
values in the query:

SELECT Format(RspnsID, "00000") FROM tblSrvRspns
UNION ALL
SELECT "(All)" From OneRowTable
ORDER BY tblSrvRspns.RspnsID;

 

[Kurt]

Thank you. I understood and applied everything except for:

The optimal way to do this is to change the secord
SELECT to use a table with one row (create one if
you don't have a convenient one row table to use
for this purpose).

I guess I'm not sure what you mean by a "one row table."
A table with only one record? When I create the one row
table, what is the field name I use? What data should
populate it? How (or should it?) be linked to other table
(s)?

Thanks. - Kurt

P.S. I don't need to worry about duplicates in
tblSrvRspns, so UNION ALL works great.

 

[Marshall Barton]

Thank you. I understood and applied everything except for:


I guess I'm not sure what you mean by a "one row table."
A table with only one record? When I create the one row
table, what is the field name I use? What data should
populate it? How (or should it?) be linked to other table
(s)?

You can use any table that only has one record. It doesn't
matter what fields the table has (or the values in the
fields) because you are not actually retrieving any of the
fields. For the purpose of including "(All)", it would not
be linked at all (the UNION is all the linking this
particular use needs).

This is not an absolute requirement to make things work, but
it can provide a signiifcant performance improvement in some
cases (if not immediately, then as the real data table grows
over time).

 

[Kurt]

Great. Everything's working now. Thanks for all your help.

I ended up with:

SELECT "(All)" From tblOneRowTable;
UNION ALL
SELECT Format (RspnsID, "00000") From tblSrvRspns;

I moved the "(All)" SELECT statement to the first line so that "(All)" is the first option that shows.

Kurt

-----Original Message-----

Thank you. I understood and applied everything except for:


I guess I'm not sure what you mean by a "one row table."
A table with only one record? When I create the one row
table, what is the field name I use? What data should
populate it? How (or should it?) be linked to other table
(s)?

You can use any table that only has one record. It doesn't
matter what fields the table has (or the values in the
fields) because you are not actually retrieving any of the
fields. For the purpose of including "(All)", it would not
be linked at all (the UNION is all the linking this
particular use needs).

This is not an absolute requirement to make things work, but
it can provide a signiifcant performance improvement in some
cases (if not immediately, then as the real data table grows
over time).
--
Marsh
MVP [MS Access]

Thanks. - Kurt

P.S. I don't need to worry about duplicates in
tblSrvRspns, so UNION ALL works great.

.

 

[Marshall Barton]

Great. Everything's working now. Thanks for all your help.

I ended up with:

SELECT "(All)" From tblOneRowTable;
UNION ALL
SELECT Format (RspnsID, "00000") From tblSrvRspns;

I moved the "(All)" SELECT statement to the first line so that "(All)" is the first option that shows.

Your orignial Order By clause

ORDER BY RspnsID

is probably a better way to do that. Since your other
RspnsID values all start with decimal digits, the "(" will
sort before all the other values.

If you don't use an Order By clause, the values might not
appear in numerical order.
--
Marsh
MVP [MS Access]

 

[Kurt]

Your orignial Order By clause

ORDER BY RspnsID

is probably a better way to do that.

I would have preferred that method, too, but the new SQL
statement:

SELECT Format(RspnsID, "00000") FROM tblSrvRspns
UNION ALL
SELECT "(All)" From OneRowTable
ORDER BY tblSrvRspns.RspnsID;

was producing this error message:

The ORDER BY expression (tblSrvRspns.RspnsID)
includes fields that are not selected by the
query. Only those fields requested in the first
query can be included in an ORDER BY expression.

I should be okay since I don't anticipate the RspnsID
ever reaching 5 digits, so the first 0 should help keep
them in order. Either way, I'd prefer using an ORDER BY
clause if you know how to resolve the error.

Thanks. Kurt

-----Original Message-----

so that "(All)" is the first option that shows.

Your orignial Order By clause

ORDER BY RspnsID

is probably a better way to do that. Since your other
RspnsID values all start with decimal digits, the "(" will
sort before all the other values.

If you don't use an Order By clause, the values might not
appear in numerical order.
--
Marsh
MVP [MS Access]


.

 

[Van T. Dinh]

Try

.... ORDER BY 1

HTH
Van T. Dinh
MVP (Access)

 

[Marshall Barton]

I would have preferred that method, too, but the new SQL
statement:

SELECT Format(RspnsID, "00000") FROM tblSrvRspns
UNION ALL
SELECT "(All)" From OneRowTable
ORDER BY tblSrvRspns.RspnsID;

was producing this error message:

The ORDER BY expression (tblSrvRspns.RspnsID)
includes fields that are not selected by the
query. Only those fields requested in the first
query can be included in an ORDER BY expression.

I should be okay since I don't anticipate the RspnsID
ever reaching 5 digits, so the first 0 should help keep
them in order. Either way, I'd prefer using an ORDER BY
clause if you know how to resolve the error.

Sorry about that. The query should add an alias for the
calculated field and use that in the Order By clause:

SELECT Format(RspnsID, "00000") AS RspID FROM tblSrvRspns
UNION ALL
SELECT "(All)" FROM OneRowTable
ORDER BY RspID