combo box and RWOP queries

[Guest]

I have a small db which I have implemented user-level security. I have
restricted access to my tables and access all information through RWOP
queries. I have two forms that use a combo box to provide selections for
accural rates. these rates are formated in my table as double, fixed, 6
decimal. (0.048536). Prior to converting my combo boxes to utilize the RWOP
query to draw there information my combo box displayed the number as shown.
Now the combo bos displaysthe accural rate as 0.05. There are multiple
accural rates that only vary at 3 or 4 decimal. Therefore all my options
display as 0.05. Unless I have overlooked something obvious I have checkedall
my property settings. Is there a means to display the full number in the
combo box without having to make my record source for this combo box the
originating table. The RWOP queries have worked justy great for text based
combo boxes but not for this numeric record source.

 

[Joan Wild]

Can you set the format property for the column in the query to fixed, 6
places? Select the column in the grid, and go to View...Properties.

 

[Guest]

Ms Joan thanks much for your responses to my questions that I have posted as
I have worked through this project. Also thanks to you and your fellow MVP's
for the responses to other users. This site has become an excellent means to
self teach through next steps in Access. Each of you have been a tremendous
help.

The record source query (tblqryAccuralRates) does have its properties set as
describedand does indeed display the data in the 6 decimal format. The
combobox control on the form (frmEditEmpData) has its properties set to fixed
and 6 decimal. Could it be that there is a need to include a formatting
statement within the SQL statement for the combo box record source. Just in
case it matters the frmEditEmpData uses tblqryEmployeeData as its record
source.

 

[Joan Wild]

When you open the rowsource of the combobox and run it, does it show all the
6 decimal places? If it does, then it may be that you just need to adjust
the column widths on your combobox control. The column that displays the 6
decimal value may be too narrow.

 

[Arvin Meyer [MVP]]

Try formatting as 3 (or 4) decimal:

SELECT Format([FieldName],"#.000") AS Expr1
FROM TableName
WITH OWNERACCESS OPTION;

 

[Guest]

Both queries (the query that the form is based upon and the query that builds
the record source for the combo box) when data is viewed display accural rate
as a 6 decimal. When the individual records are viewed in the form the
display in the combo box (individual employee data) and the data in my
combobox control drop down list are both displayed as 2 decimal. Width of
control columns are set at 1 inch each. It is a two column control.

 

[Guest]

This may be it but I am home now and I did not bring a copy of db home. Will
attempt in AM.

Try formatting as 3 (or 4) decimal:

SELECT Format([FieldName],"#.000") AS Expr1
FROM TableName
WITH OWNERACCESS OPTION;
--
Arvin Meyer, MCP, MVP
http://www.datastrat.com
http://www.mvps.org/access
http://www.accessmvp.com

I have a small db which I have implemented user-level security. I have
restricted access to my tables and access all information through RWOP
queries. I have two forms that use a combo box to provide selections for
accural rates. these rates are formated in my table as double, fixed, 6
decimal. (0.048536). Prior to converting my combo boxes to utilize the
RWOP
query to draw there information my combo box displayed the number as
shown.
Now the combo bos displaysthe accural rate as 0.05. There are multiple
accural rates that only vary at 3 or 4 decimal. Therefore all my options
display as 0.05. Unless I have overlooked something obvious I have
checkedall
my property settings. Is there a means to display the full number in the
combo box without having to make my record source for this combo box the
originating table. The RWOP queries have worked justy great for text based
combo boxes but not for this numeric record source.

 

[Guest]

Arvin

I did find an older copy of the db not fully functional but I did try the
format function in the SQL statement and that seems to provide desired
results. It affected how both items were displayed. The actual employee
record display 6 decimal and the combobox control dropdown list also is
displayed as 6 decimal. In AM I will verify results in full production
product. Thanks much looks like the cure.

This may be it but I am home now and I did not bring a copy of db home. Will
attempt in AM.

Try formatting as 3 (or 4) decimal:

SELECT Format([FieldName],"#.000") AS Expr1
FROM TableName
WITH OWNERACCESS OPTION;
--
Arvin Meyer, MCP, MVP
http://www.datastrat.com
http://www.mvps.org/access
http://www.accessmvp.com

I have a small db which I have implemented user-level security. I have
restricted access to my tables and access all information through RWOP
queries. I have two forms that use a combo box to provide selections for
accural rates. these rates are formated in my table as double, fixed, 6
decimal. (0.048536). Prior to converting my combo boxes to utilize the
RWOP
query to draw there information my combo box displayed the number as
shown.
Now the combo bos displaysthe accural rate as 0.05. There are multiple
accural rates that only vary at 3 or 4 decimal. Therefore all my options
display as 0.05. Unless I have overlooked something obvious I have
checkedall
my property settings. Is there a means to display the full number in the
combo box without having to make my record source for this combo box the
originating table. The RWOP queries have worked justy great for text based
combo boxes but not for this numeric record source.

 

[Jamie Collins]

Both queries (the query that the form is based upon and the query that builds
the record source for the combo box) when data is viewed display accural rate
as a 6decimal.

I should point out that Double (floating point) is an approximate
numeric type and is not suitable for values that are required to be
exact. Consider changing the column's data type to DECIMAL (fixed
point), Jet's native decimal type and the only exact numeric type that
can support six decimal places.

Jamie.

--

 

[Guest]

Thanks,

That may help did not get a chance to follow up since Arvin's post did
correct the problem. Used a formatting statement in the SQL statement for the
row source attached to the combobox control.

 

[Guest]

that worked thanks much

Arvin

I did find an older copy of the db not fully functional but I did try the
format function in the SQL statement and that seems to provide desired
results. It affected how both items were displayed. The actual employee
record display 6 decimal and the combobox control dropdown list also is
displayed as 6 decimal. In AM I will verify results in full production
product. Thanks much looks like the cure.

This may be it but I am home now and I did not bring a copy of db home. Will
attempt in AM.

Try formatting as 3 (or 4) decimal:

SELECT Format([FieldName],"#.000") AS Expr1
FROM TableName
WITH OWNERACCESS OPTION;
--
Arvin Meyer, MCP, MVP
http://www.datastrat.com
http://www.mvps.org/access
http://www.accessmvp.com


I have a small db which I have implemented user-level security. I have
restricted access to my tables and access all information through RWOP
queries. I have two forms that use a combo box to provide selections for
accural rates. these rates are formated in my table as double, fixed, 6
decimal. (0.048536). Prior to converting my combo boxes to utilize the
RWOP
query to draw there information my combo box displayed the number as
shown.
Now the combo bos displaysthe accural rate as 0.05. There are multiple
accural rates that only vary at 3 or 4 decimal. Therefore all my options
display as 0.05. Unless I have overlooked something obvious I have
checkedall
my property settings. Is there a means to display the full number in the
combo box without having to make my record source for this combo box the
originating table. The RWOP queries have worked justy great for text based
combo boxes but not for this numeric record source.