Link to home
Start Free TrialLog in
Avatar of developingprogrammer
developingprogrammer

asked on

format of combobox not working

hey guys,

ok, SUPER dumb question. i don't know why i just can't get it to work.

i've read almost ALL harfang's articles on comboboxes etc.

i'm trying to format my combobox into hh:nn AM/PM format.

no matter what i do, it still shows the seconds component. even if i use Medium Time format.

here're the screen shots and the POC databaseUser generated imageUser generated image
https://www.experts-exchange.com/Microsoft/Development/MS_Access/A_6289-Understanding-the-ComboBox-and-ListBox.html

btw i feel like a retard asking this question. i probably am. ok i am a retard. hahaha
POC.zip
Avatar of Gustav Brock
Gustav Brock
Flag of Denmark image

Please recall that combo- and listboxes always returns strings.
Thus, if you need any formatting, create this in the query that feeds the combobox using Format() or other methods.

/gustav
Avatar of developingprogrammer
developingprogrammer

ASKER

thanks gustav!! = ))

i was trying to figure that out but in harfang's article here is what he says

User generated image
shouldn't this convert to date then convert to formatted date be also done? further more i am using a bound column too to avoid the combobox not being able to find a bound value after conversion
No, it will always be a string.

/gustav
oh ok. gustav so sorry, but could you then explain to me what harfang meant in the screenshot above? the article link is there too.

he says 1) the string (as what you said) is converted into a number. or date
2) that number or date is then formatted into the desired format.

is what he is saying wrong? so sorry i'm a bit confused. thanks gustav!! = ))
Marcus (harfang) is never wrong.
The comments are about percentages, not dates.

You can often feed a string to a non-text field as long as it can be casted to the other data type. Still, if you require a different format for display, create that as an expression in your query.

/gustav
Great thanks gustav!!

I went to re read hi sari le again and I picked out now that ALL columns are strings and

When dealing with any numeric value that needs to be formatted to become readable (this includes dates), the best solution is often to use a hidden bound column. The row source should use CStr() to transform the value to string, which will be compatible with the implicit conversion. A second column is used to display the formatted version. If values not in the list are allowed, this doesn't work. For dates, the best bet it to use “general date” as format...

I will try in a few hours the string conversion. I wonder if the date will be converted as a string number or a string date. Will find out soon ha.

I do also wonder why the date string couldn't be converted. Will test tomorrow to understand better. Now I'm not sure if my combo box bound colum is an underlying date field, is the value 43527 something like that or is it the mm/dd/yy format. All test tomorrow. Thanks gustav!!
Hi,

I thought we had discussed the question about what a combobox is returning and found out that the Combobox definitely NOT always returns a string, that's a rumour always comes up again and again.

Here's the link to your last question where I have shown that in a demo database (doesn't matter if the combobox is bound or unbound, as long as the data comes from a table and not from a value list):

Combo Box Format Property

The "Format" property of the Combobox is only used for the value which is displayed in the Textbox part of the Combobox, not in the dropdown list. If you choose a value from the list and leave the field the format is used, see the attached file.

If you also want to see that in the dropdown list you indeed need to use a Format function in the query. But your combobox has more errors:

1. ALWAYS use a query with concrete column names and NEVER use a table name as RowSource! That's really, really important! If you simply set a table name and then later you move the order of columns in the table your combobox stops working in the way it was intended. If you set the number of columns to "1" like here, the first found column in the table would be used so if you change the order of columns then suddenly your combobox shows a list of values of another column!

2. For the same reason, never use "SELECT *" - in 99% of all cases the "*" is a bad idea (there are tons of informations about that theme so I do not want to repeat that all here). The most important thing here about that is similar to using a table name: You have no control over the order of columns or which one is used as ID column.

3. Try to always use the first column in the query as bound column, Access sometimes has problems if another column is bound. If you always use this standard it makes it easier for you to identify the ID column, it is easier for others which are going on developing your database later, it is also easier to work with the Combobox in VBA as you always now that the default "Value" is the value of the ID and not some display column.

4. You used ColumnCount 1, but bound column 2 and as source the table...

Cheers,

Christian
POC2.zip
thanks Christian!! going through this now!! = ))
arghhh Christian!! enlightening enlightening enlightening!!!!

ok let me freshen up quickly and come and reread your post for the 3rd time. i'm such a slow learner darnit!!
hi Christian!! thanks for the super enlightening comment you made above!!

1) i will always define my column names (never us * again)
2) i will always use a query for my rowsource - even if it's inline SQL in the property itself (this is ok for inline sql right?
3) i will search for some articles on not using select * later on - think i found one of them http://www.sommarskog.se/dynamic_sql.html
4) i will always use the first column as my bound column moving forward - making it an invisible column too in the columnwidth setting

ok. got it.

ok Christian i tried to play with the database a bit. i made 1 change. in the query, i changed the 2nd column from "Display: Format([TimeValue],"hh:nn AM/PM")" to just "TimeValue".

that's the only change i made and then when i select a value from the dropdown list, the combobox remains empty except for 12:00:00 AM and 12:05:00 AM.

1) i don't understand why the combobox shows the dropdown list but doesn't show the value in the combobox itself. i'm suspecting a conversion mismatch or something cause the format of the combobox does not match with the underlying query column now

2) i don't understand why 12:00:00 AM and 12:05:00 AM show up

here's the database.

Edit: hrmm after C&R and closing access and opening the database again, seems like i can select a greater range of values that will actually appear in the combobox itself. very strange. i don't understand it = (
Christian-modified.mdb
hi Christian, here's the second problem.

this time i left the query alone. i only changed the combobox control's format to "hh" that's all. and it isn't showing the hh format in the combobox. still showing the query format. why is that so?

thanks for your help as always Christian!! = ))
Christian-Modified-2.mdb
Hi,

I didn't saw that either because this function is not so often used (although I wrote it to you in another solution): "TimeValue" is the name of a VBA function and must not be used as name of an object.

That's why using prefixes is always a good idea. In case of tables you should use an abbreviation of the table name itself (never the datatype like with "strText" in VBA), so if your table is called i.e. "Customers" you could use "CUST_FirstName", "CUST_LastName" and so on. On this method you can be sure that you never will use a name of a keyword or function or whatever else.

Cheers,

Christian
"1. ALWAYS use a query with concrete column names and NEVER use a table name as RowSource! That's really, really important! If you simply set a table name and then later you move the order of columns in the table your combobox stops working in the way it was intended"
I think ALWAYS is a bit of a strong word. Whereas what you state is true regarding column rearrangement in a Table, if I have say a simple lookup table with maybe 5-10 max entries that are not going to change, two columns (ID, Description) ... a query as the RowSource might be a bit of an overkill, I (or we) as the developer have total control over this.

"Try to always use the first column in the query as bound column, Access sometimes has problems if another column is bound. "

What problems does Access have with this?

mx
hi Christian,

ok i'm testing the database you sent me again.



POC_Problem1POC-Problem1.mdb
Changes I've Made:
1) i changed the field name to DD_TimeValue and DD_TimeDisplay
2) second column of the query, i removed the format formula.

Problem --> when i choose an item from the drop down now it still doesn't appear in the combobox.

POC_Problem2POC-Problem2.mdb
Changes I've Made:
1) i changed the field name to DD_TimeValue and DD_TimeDisplay
2) i changed the combobox's format to "hh"

Problem --> when i choose an item from the drop down now it doesn't appear in hh format. still shows hh:nn AM/PM which is the query column's format
SOLUTION
Avatar of Bitsqueezer
Bitsqueezer
Flag of Germany image

Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
Christian, you're a high tech wizard. i'm a stone edge Fred Flintstone. i think it's a big achievement i'm even uploading 2 databases to you instead of printing out the databases and mailing it to you. and again for the second database. your expectations of me are too high. slowly slowly... hahahahha OK will upload all in ONE database next time!! squeeze the bits!! = ))

okie let me read through your post multiple times again and respond!! = )) thanks Christian!! = ))
" but if I have a better way and see problems with the other, then it is clearly a rule to not use the other way, especially if the only difference is only laziness (here of not creating a query)."

But your 'better' is subjective and doesn't necessarily make it so in the real world. And this has nothing to do with laziness on my part.

mx
Hi mx,

I thought I have proved that with facts. So where do you see any "subjective"?

Really strange argument.

Cheers,

Christian
hi Christian, thanks!!

ok Christian i tried one more thing. (btw your demo database looks great, somehow i get this feeling your demo database looks nicer than my real database haha - all the colour coding, the rectangles grouping stuff together, ah i really need to improve!!)

i created a 2nd NOT OK group where i removed the combobox's format altogether. so no more formatting. now the combobox has problems displaying the display column as well and the double conversion doesn't work.

why is this the case? so that means it takes its values from a table which has fields prefixed with DD so it won't conflict with TimeValue, the combobox is not doing any formatting, BUT it still has problems displaying the display value. so strange right - why? i can't figure out = (

so Date/Time data type is stored as a double data type. according to harfang's article (this part i've posted below), the text of the value in the list is converted to a number.

so that means 12:35:00 AM (text) is converted into a number (double). then any formatting applied to the combobox is applied to it here. but there is no formatting applied.

in the edited database example i've attached here, the value of the combobox is correct - as showing in the Value textbox of the 2nd NOT OK group. but the displaying of it has a problem. so the combobo has a problem displaying doubles that are infinite.

ok conclusion time. so what i think is that

1) it's not the combobox's value that has a problem. it's the display column. (i think this is what you said above just that for me in my head it wasn't clear if the value has a problem or just the display - i've confirmed it's just the display - even when the combobox doesn't have formatting applied.
2) like what you've pointed out it has a problem when the number is infinite.

hrmm so just to clarify in my head Christian - and please do correct me if i'm wrong; the combobox has problems accepting time values for its display column if they are infinite doubles

and that is probably the case because floating point stores an infinite number with fixed decimal places --> and this is almost the equivalent of a unknown.  1+null=null. why? cause who knows what 1 + unknown is? unknown is the answer! so when the display column takes an infinite date/time, it doesn't know what the date/time really is. so it SHOWS null. but because the bound column is another column, there is still a value.

OK WAIT! I THINK I FIGURED IT OUT!!!!!!!

ok so sorry for this long comment, will just post it still cause i think there's some trail as to how i arrived at my conclusion. thinking and writing at the same time makes me sound like a dummy. but hey, at least i'm thinking!! haha = PP

User generated imagePOC-Problem1and2.mdb
ok Christian, i'm VERY sure (i think) the problem lies with the translation of the stored value and the displayed value.

BECAUSE, in your demo, the 1st OK group uses a single column WITH formatting. what's the difference between this and the NOT OK group?

OK group uses        ---->1 column
NOT OK group uses --->2 columns

so clearly the combobox is being assigned a value when the user selects it, whether or not it is SHOWING a value. this is proven because your textbox of VBA Format is still showing a properly formatted value.

ok check this part out in harfang's article.User generated image
ok let's take a moment to take stock. i posted 2 screenshots of harfang's article. the 1st related to formatting. this one relates to display column.

so our problem here is actually NOT formatting. it's the display column.

the value is assigned to the combobox as such the VBA Format textbox is showing a value.

but somehow the "vlookup" of the combobox isn't working. why?

I THINK I FIGURED IT OUT CHRISTIAN!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!

ok it is clear that the combobox CAN find the its own value in the bound column. proof? in your single column combobox it CAN show the display. so it's proven that the combobx CAN find its assigned value in the bound column.

in harfang's screenshot here, we're under the illusion that combobox then just uses its list index once it finds the value in the bound column and pulls the same list index but just for the display column.

question - is this true? i don't know. i don't know how microsoft made combobox translate from bound column to display column.

hrmm wait, ok i think it does. yes i think microsoft made combox translate from bound column to display column using list index. well can make a combobox testing this but i realised that harfang's example of Germany (ironically he chose Germany - preempting we would have this discussion!! haha = ))   ) proves that it shows the first display entry with the first matching combobox value in the bound column.

how come combobox can DISPLAY the chosen value if it is a 1 column bound=display column table, but when it is a 2 column bound and display column table, it has problems, EVEN THOUGH the values are all the same?

ok at the moment i'm not too sure already ha. and no time to spend further on this (at the moment!!) BUT i really really want to solve this cause it's my pet peeve!!!!

but here are some relevant points

1) numerator-denominator (stored in JET). fixed length double (stored in VBA). just a theory, but i don't think so cause the documentation already said it stores as a fixed space double. but if it didn't then this would affect the conversion

2) display issue only - the value of the combox is ok

3) double to date. even if we don't put any formatting in the format property, there is still a conversion of double to date. that's why the combobox shows a date instead of a number with decimal points.

4) hrmm Christian i think that if we set the data type in a table as date / time, what it is doing is it is setting the field to double ADDING THE FORMATTING OF "mm/dd/yy hh:nn:ss AM/PM" or whatever is in the system date time format. is this correct?

5) if in the query we have a second column as something:format(blah,"hh:nn AM/PM") it works because it is already a string. so the display column is a string. but if there are 2 columns in the table and both are just dates, then the display column in combobox is a datetime.

========================
ok so let's at least define our problem properly so we know what to solve.

1) combobox fails to DISPLAY a LOOKUP DATE/TIME data type if the double of it is an infinite value.

2) if the combobx does NOT have to lookup this display value because it is a single column table, it displays it correctly.

ok something like that for now. sorry for the long posts!!
Hi,

it is an often misunderstanding that people think that a double value is stored in the same way as you see it. You see "1.45234", but if you would look into the stored value you would not find that. There is an algorithm which calculates this value into (as long as I remember) 5 bytes and in the other direction from these 5 bytes back into the double value. This means, any single/double value is not a secure value. Depending on the calculation algorithm and other things the recalculated value can be different (slightly, but enough to make a comparison difficult so that different systems calculates different results). You see the effect often if you have double values from a SQL Server table which is used as linked table in Access. There is a conversion between both systems, all the datatypes must be converted into the corresponding JET/ACE datatypes to handle them. Sometimes it works - and sometimes not. Access tries to find out if a record has changed and compares each value of each field of the current record (it's old values) to the one on the server to see if they have been changed by another user. If they are different, you got an error message. And if the comparison fails because of exactly such conversion calculation differences Access says that the record has changed although it didn't change.

For example, if you look at the infinite values: Some systems calculates them with i.e. 8 or others with 16 or whatever number of decimal places after the dot. If it is "0.6666666..." that can result in "0.6666667" or in another system "0.666666666667" or whatever. So it depends on the quality of the algorithms if they are able to find out that these two values are equivalent numbers so that they can be called "equal". Not easy. And I think that's the problem the Combobox has with the conversion of numbers with infinite end, to compare that the value chosen is a value of the list, especially with "List Items Only" property.

Cheers,

Christian
whao great Christian!

Christian can you put out an article based on our discussion here (rather your advice haha) so that others can benefit from this as well? right now the main authority on comboboxes is harfang's articles - but it doesn't cover what you just said on this problem.

also it does not as clearly state the misunderstand the comboboxes are all text.

so perhaps your article can cover
1) text misconception
2) infinity problem

sorry can't type too much now!! = P =  ))
> .. it does not as clearly state the misunderstand the comboboxes are all text.

But they are. That's also why you always see numbers in a combobox/listbox coloumn left aligned.

/gustav
Hi gustav,

they are not. Please look into the demo database above or in the other thread which shows clearly that the type of the comboboxes bound column changes depending on the type of the data it contains.

Cheers,

Christian
Guys I didn't notice the left right align, but if type name says it's something other than text, then perhaps left right align isn't a reliable indication of data type.

An occasional phenomenon but not the definitive authority on a data type of a combobox
Yes, I would underline that. If the datatype of a Combobox would ALWAYS be a string the alignment must always be the same...
It is text, but if bound to another data type it will be casted to that if possible.
An unbound will always be text.

/gustav
No, an unbound is also not text, in this case the datatype is the same as in the bound column of the used RowSource (if it is a table/query).

If it is a value list it depends on the formatting: If the format says this should be a date then the datatype is date (if the value list does contain mixed values of date and other strings it will not allow any other than date string).
ASKER CERTIFIED SOLUTION
Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
SOLUTION
Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
Superb reasoning with backing and superb acknowledgement!! Classy at it's best!! = ))
Y'all are THE BEST guys!! = )))
gustav, any comments on the date infinity double bug Christian found above? = ))

Would harfang be able to share his thoughts on this? = ))
Ah. That explains. Yes, the Value property is different.

However, for the Value property of the combobox this is in effect:

 - if it is an unbound control and the RowSource is a table/query and no format string is used, it casts to String except for date/time that is casted to Date.

 - if it is an unbound control and the RowSource is a table/query, a numeric coloumn may be casted to a number by using a format string for numbers.


> .. any comments on the date infinity double bug Christian found above?

It's not a bug. Refer to my very first comment. If you obey that rule you loose control of the happenings leaving Access to do what it finds best to present the data (as strings).

/gustav
great! thanks gustav!!

however when the timevalue is something for example 0.3333333333333 to infinity, why doesn't the combobox display it as 0.33333 but rather doesn't display anything at all in the combobox?

so in this case it's not a matter of format but rather there is.....!!! nothing displayed in the combobox itself!!

why is that the case? thanks gustav!! = ))
But you wouldn't have your 8 hours displayed as 0.3andsomething anyway.
As said, add a column with the formatted value of your time and display that.

DisplayTime: Format([SomeTimeField], "hh:nn")

/gustav
Understand and fully agree gustav, I have followed your advice and Christian's advice too to do that. However I just want to understand why the combobox isn't showing anything at all. Why is this the case?
Thanks for all your help so far on this gustav, really appreciate it and am getting the question reopened by admin cause we discovered a clarification here! = ))
I don't know. I've never seen that.

/gustav
I see no problem gustav, thanks for all your help and for helping us discover something important here in this question!! = ))
You are welcome!

/gustav
You're fantastic gustav!! = ))
hi Christian!

ah sigh, i think i have to rebuild my database from scratch another 3 more cycles to get it perfect. perfect in the sense to the code distributionable level using mx's .mda files.

BUT! as i'm collating coding best practices, i thought about the table field prefix you were talking about. you mentioned using an abbreviation of the table name is always a good idea because it alloweds us to avoid using a function name as a table field name by accident.

hrmm, sorry Christian could you give me an example when this is a problem? i just tried TimeValue again in this demo database and the query runs fine.

my suggestion for the abbreviation is that - since this does not give any information value, instead of torturing ourselves to come out with an abbreviation and hoping it doesn't clash if there another table with the same abbreviations, why don't we use "TF_MachineID", "TF_ProjectName". i think this way it saves us the bandwidth of thinking of an abbreciation and also avoid the name clash (though i'm still not sure what effects are caused by using a function name as a table field name ha = )
functionname.mdb
Hi,

I can tell you what your query (and also the one in the previous version) creates in my German version of Access:

Ausdr1: Table1.ZeitSeriellStr

Open in new window


"ZeitSeriellStr" is the translation of the English "TimeValue" function (although a strange translation). You see that Access automatically tries to use the function and not the field. If I start that Access asks me to enter a parameter value for that column...:-)

So you should keep in mind that it is always unpredictable how Access or VBA reacts if you use any keyword or function name for any of your own object names. Sometimes it works, sometimes it doesn't. So the simple solution is: Don't do that - never. That's a problem in many programming languages including SQL (which is of course not a real programming language).

I didn't understand what you meant with the question about the abbreviation of the table name. This is a way which I used permanently and found it useful - you don't need to do that if you have a better idea. But I didn't got what you meant here.

Cheers,

Christian
ah i see, cool thanks Christian on the functions as names bit! = )

sorry i was like dozing off when i was writing that last night ha - about the prefixes.

what i mean was, you're using abbreviated table names for prefixes. however i'm using a fixed prefix called TF_. that stands for Table Field.

the benefit of using a fixed prefix vs an abbreviated table name prefix is that:
1) we don't need to put in the mental energy to try and make our abbreviations unique (sorry i'm bothered bout such silly things cause i don't know semantically does it make a difference if they're unique or repeated when i use it ha = P    )
2) they achieve the same objective of never letting a function name be used as a field name = )

so i think perhaps that is a better way? not sure but from what i understand of your objective for prefixes, think it does achieve the objective and takes away any consideration of the prefix being unique or not = ) cause when we name something as a programmer, we're always so frisky about naming conventions haha = PP

this prefix is something i have to incorporate into my database - database wide. it'll probably take me 3 more complete new iterations to get this done properly - but just like the other question about .mdas, i wanna use .mdas to get this rolled out when it's done properly = ))) so exciting Christian!! hope i last in my job to achieve that!! = PP
also one minor thing Christian, i used to love to use RowID or SomethingID for my meaningless primary key. but i came across a situation where the field name was called MachineID and my primary field name was called MachineID as well and that was a huge conflict for me.

huge because it made me think that my entire database was fragile and going to break anytime due to the non-100% perfect naming convention.

so now i use RowPK - i was thinking of RowMPK, meaningless primary key, but i think that PK is probably unique enough - no one's business logic is going to require the field to be called SomethingPK (unless they're developing a tool for developers - say a source code control app or something).

hrmm yes i definitely could have changed my MachineID field name to say MachineName but i kinda really didn't want to cause i wanted to be aligned to the business side of things = )

just a minor point! feel free to correct me if you think otherwise! = ))
There must some misunderstanding here and I believe you are hunting ghosts.

I always name the primary key Id, which is an AutoNumber. That's it. Never caused any kind of trouble, only relief.

A foreign key is named by the table it relates to; thus, in table OrderDetail, Id is the PK, OrderId is the FK, and in table Order, Id is the PK, CustomerId is the FK. ShipperId may another FK. You get the picture. It cannot fail and is so simple that it hurts.

Be careful not to overcomplicate matters.

/gustav
gustav, my goodness. You cleared up SUCH a BIG misconception I had.

Ok can't really type out the mistake I made here cause I'm quite deep in thought now and also gotta do one sleepless night to meet a deadline tomorrow, but yes I get it now.

Here's what I'll do from now on.

All PKs in the table will be called "PK"
When another table references this I will call it "TableNamePK_Significance"

Here's an example.

I've got a table called Staff. I've got another table called Ticket.

In a ticket, one staff could create it, it could be assigned to another staff and close by another different staff.

So in both tables, the primary key will both be called "PK". In the Ticket table, I will have 3 fields:
"StaffPK_WhoCreated"
"StaffPK_AssignedTo"
"StaffPK_WhoClosed"

My goodness gustav, you have corrected an absolute core concept of my entire understanding foundation of databases.

Ok let me let this sink in for awhile. This is very, very major for me gustav. Thanks so so much for pointing this out. What I've done now is not fatal, just slightly confusing and not the best naming convention. Ok it's total crap naming convention haha. gustav, just like how Christian will always stick in my head whenever I use meaningless primary keys, you'll always stick in my head when I name the primary keys and call upon foreign keys. This is very, very major. Thanks so so much gustav!! = ))
Great! Keep in mind please, that no single naming convention is "the right" - choose whatever fits the purpose.
The one rule however, that "is right", is to stay true to the naming convention for a project even if you come in late and don't like it. You will adopt.

/gustav
Thanks so much gustav, I really appreciate it!! = ))
You are welcome!

/gustav