I've spend a while battling with this problem too, reading various blogs and posts. I've found a very simple way to achieve the desired outcome in VBA using just one line of code. In the VBA editor, place this code AFTER your On Load code:
[Your On Load code]
KeyCode = 16
This code achieves the equivalent of pressing F11, ie maximising the view of the currently open window.
I think I can guess why your VBA is only changing around 150 records instead of all of them, but more importantly, you need to know that the specific method you're using--changing all the fields using VBA--can lead to incorrect data in your table. And, you can accomplish what you want much more easily with a SQL query ... even one made using the query designer. But for now I'll stick with plain SQL. There are three possibilities and corresponding approaches:
1
You just have to change a limited number of different sizes from mm to European. In that case use the switch
function, e.g.:
update BS_2704
set Sizes = switch(
Sizes = 150, 24
, Sizes = 160, 25
, ...
, Sizes = 325, 50
, true, Sizes
)
2
You can use a mathematical formula to convert from your mm size to your European measure, e.g. like http://en.wikipedia.org/wiki/Shoe_size#Europe:
update BS_2704
set Sizes = Sizes * 3 / 20
3
You can't use a mathematical formula, and you have such a large number of conversion mappings that putting them all in a single switch
function is unwieldy: in this case you create a new table, e.g. named tblSizeMappings
, with the mm sizes and the corresponding European sizes:
SizeMM SizeEuropean
150 24
160 25
325 50
Then, do an update query like above but using the tblSizeMappings
table to get the European sizes:
update BS_2704
inner join tblSizeMappings
on BS_2704.Sizes = tblSizeMappings.SizeMM
set BS_2704.Sizes = tblSizeMappings.SizeEuropean
Best Answer
The answer can be found here in Allen Browne's page:
Numeric formatting options will not affect the text values which are automatically converted from the VBA Variant, even if the original value was coded as numeric.
His recommended solution is to do what you have already discovered: Use the IIF statement. The Jet engine uses datatype clues from the parameters to establish the returned column's data type.
Here's some code that will reveal the automatically-selected column type: