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 databasedesignform
http://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
developingprogrammerAsked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Gustav BrockCIOCommented:
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
0
developingprogrammerAuthor Commented:
thanks gustav!! = ))

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

harfang
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
0
Gustav BrockCIOCommented:
No, it will always be a string.

/gustav
0
Big Business Goals? Which KPIs Will Help You

The most successful MSPs rely on metrics – known as key performance indicators (KPIs) – for making informed decisions that help their businesses thrive, rather than just survive. This eBook provides an overview of the most important KPIs used by top MSPs.

developingprogrammerAuthor Commented:
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!! = ))
0
Gustav BrockCIOCommented:
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
0
developingprogrammerAuthor Commented:
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!!
0
BitsqueezerCommented:
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
0
developingprogrammerAuthor Commented:
thanks Christian!! going through this now!! = ))
0
developingprogrammerAuthor Commented:
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!!
0
developingprogrammerAuthor Commented:
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
0
developingprogrammerAuthor Commented:
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
0
BitsqueezerCommented:
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
0
DatabaseMX (Joe Anderson - Microsoft Access MVP)Database Architect / Systems AnalystCommented:
"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
0
developingprogrammerAuthor Commented:
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
0
BitsqueezerCommented:
Hi,

funny effect, I would say that's a bug of the Combobox. It seems that the Combobox is not using the VBA format function, it uses an own one. I've attached a demo database where both problems are shown so you don't need to send one database for each problem...;-)

The "Not OK" sample shows that the Combobox has a problem whenever the time value has a corresponding double value with infinite result (you know from math, 1/3 is 0.3333333.................3..... infinite). If you set it to 10:30 the Double value is "0.4375". If you set it to 10:35, the value is "0.440972222222222". So 10:30 can be chosen, 10:35 not.

You see in the demo form that VBA (which uses the same format string) does not have any problem formatting the value in the right way - but only as long as it can be recognozed as a date format. The third sample (VBA Format in red) shows that if the display column is only an integer it can also not format it anymore (of course).

The first sample also does not make sense to me: Why do you use two columns which both are set to the same DateTime value? I would understand if you would have a replacement value there, maybe as formatted string. But that can also be done by using a Format function in a query.

The second sample shows the easiest way: Using a format string in the Combobox and the value column itself. That results in displaying the list in the format of the value and the chosen value will be formatted like in the Format property of the Combobox. If you enter a value manually you would need to use the format of the list. But here you are working with DateTime values only.

The third example shows the way of using a hidden value column and a Format string in the query. This is the easiest variant of having an equal formatting in the list and in the Textbox part of the Combobox. The format property is the same as in the other samples but it is not used as the display column is already formatted in a way that it can't be interpreted as a date value. If that would be the case it would also be used (the same reason why the VBA Format function doesn't work here).


@mx: Yes, I know, many people says "don't say NEVER when there is another way possible". I say in such cases: Yes, there are other ways possible, 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).
It doesn't matter how many entries a lookup table has: You can in most cases not guarantee that this will be always the case for the lifetime of the database. You maybe add 3 more values and want that they be displayed in a specific order - you can't use the PK ID anymore to sort them as the other values are already used. So you need to add i.e. an order column and use a query to sort them by this order column or maybe directly by ordering with the text column.
So we can say (maybe not complete):
 - if the lookup table has no PK the order of records could be random
 - if the column order is changed the Combobox is broken
 - if you want to have a specific order the PK is the only order of records
 - if you want to add values later in between you would need to create a query then

Additionally with a query we can say:
 - the PK has nothing to do with the Combobox display order
 - I can add any value any time and don't need to change anything else because the order is in the query and the new values will be ordered automatically in the right way
 - I have concrete column names in the query and so it doesn't matter if the order of columns in the table is changed as long as the names are the same
 - In case of a database server as backend I can even rename the table columns as long as I add aliases to the query - I can do it live in a production database without issues (if I am careful).
 - I don't need to change the frontend for all that
 - a saved query always has the best performance and the execution plan will additionally be cached as the Combobox normally is used often
 - I can reuse the same query, once created, in any other form where I want to query the same lookup table on the same way (which is most often the case)

That are only some of the reasons, but I think enough to make sure that using a table name as RecordSource or RowSource is an absolutely no-go, here I am really strict with this statement.
Another reason in my case is also because I use queries as the "man in the middle" which should be the only way of accessing the underlying tables. That gives the possibility to prohibit the users working with the tables in an uncontrolled way. A step further is to also prohibit the use of queries/views by using stored procedures only where I can add parameters to proof the identity and rights of the user, but that's a little bit more difficult in Access.

Column not bound to column 1: Unfortunately I have no example for that one but I remember that was an issue in some cases, and since I only use column 1 as bound column there is none so I forgot the issues with that. In my eyes it's also a feature which makes no sense for me. You need a bound column in any case so it can always be the first one, I know no example where I ever saw that using another bound column would bring any advantage, normally it is the opposite: You must always look into the property which column is the bound one. So use always the first one means: You have a clear standard which you can trust and which anyone else who works with the program as developer can also use.

Cheers,

Christian
POC-Problem1and2.zip
0
developingprogrammerAuthor Commented:
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!! = ))
0
DatabaseMX (Joe Anderson - Microsoft Access MVP)Database Architect / Systems AnalystCommented:
" 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
0
BitsqueezerCommented:
Hi mx,

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

Really strange argument.

Cheers,

Christian
0
DatabaseMX (Joe Anderson - Microsoft Access MVP)Database Architect / Systems AnalystCommented:
TMI  bro.
0
developingprogrammerAuthor Commented:
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

harfangPOC-Problem1and2.mdb
0
developingprogrammerAuthor Commented:
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.harfang
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!!
0
BitsqueezerCommented:
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
0
developingprogrammerAuthor Commented:
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 =  ))
0
Gustav BrockCIOCommented:
> .. 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
0
BitsqueezerCommented:
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
0
developingprogrammerAuthor Commented:
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
0
BitsqueezerCommented:
Yes, I would underline that. If the datatype of a Combobox would ALWAYS be a string the alignment must always be the same...
0
Gustav BrockCIOCommented:
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
0
BitsqueezerCommented:
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).
0
Gustav BrockCIOCommented:
I wonder where you have seen or learned this. It is even very simple to check.
If you do so, using a table with field data types of Date, Text and various Numerics, and no formatting applied, you'll see that:

    VarType(cboTest.Column(n))

returns 8 for n = 0 to ColumnCount - 1, and 1 for n = ColumnCount+.
If not selection is made, the box holds Null and the returned VarType is 1.

Now, apply some formatting which matches the bound column, run the same test, and you'll se no change.

Next, change the rowsource to a value list of, say, "1","2","3" with or without an applied format of Number. The returned VarType is 8.
Finally, change the rowsource to a value list of, say, 1,2,3 with or without an applied format of Number. The returned VarType is still 8.

Thus, as you normally wouldn't use a column index exceeding the column count, the returned data type in any case is 8 when a selection is done and 1 if not.

/gustav
0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
BitsqueezerCommented:
Hi gustav,

I did again some tests with the Combobox and came to the conclusion that you are right. Maybe we talked about different things, I meant the datatype the Value property of a CB returns, you meant the columns which are filled with the data rows (independent of it came from a table or value list). The result is, that ALL columns have a datatype of String, if they are directly accessed by their column index.

Indeed it seems to be that the CB always tries to cast the String data of the bound column to the datatype it "should" return, in the logic:

 - if it is a bound control, it casts to the datatype of the underlying column.
 - if it is an unbound control and the RowSource is a table/query it casts to the datatype of the bound column, no matter what format string is used in the format property
 - if it is an unbound control and the RowSource is a value list it casts to the datatype of the format string in the format property, if defined
 - if it is an unbound control and the RowSource is a value list and no format string is defined, it casts to a String
(I  didn't test the "Field List" setting as it is rarely used, I guess it will always be string)

It also makes sense in the logic of the AutoComplete function: This always needs a string to be able to find the first matching string in the data list, otherwise it could not compare the entered characters with the bound column's values.

Cheers,

Christian
0
developingprogrammerAuthor Commented:
Superb reasoning with backing and superb acknowledgement!! Classy at it's best!! = ))
0
developingprogrammerAuthor Commented:
Y'all are THE BEST guys!! = )))
0
developingprogrammerAuthor Commented:
gustav, any comments on the date infinity double bug Christian found above? = ))

Would harfang be able to share his thoughts on this? = ))
0
Gustav BrockCIOCommented:
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
0
developingprogrammerAuthor Commented:
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!! = ))
0
Gustav BrockCIOCommented:
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
0
developingprogrammerAuthor Commented:
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?
0
developingprogrammerAuthor Commented:
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! = ))
0
Gustav BrockCIOCommented:
I don't know. I've never seen that.

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

/gustav
0
developingprogrammerAuthor Commented:
You're fantastic gustav!! = ))
0
developingprogrammerAuthor Commented:
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
0
BitsqueezerCommented:
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
0
developingprogrammerAuthor Commented:
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
0
developingprogrammerAuthor Commented:
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! = ))
0
Gustav BrockCIOCommented:
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
0
developingprogrammerAuthor Commented:
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!! = ))
0
Gustav BrockCIOCommented:
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
0
developingprogrammerAuthor Commented:
Thanks so much gustav, I really appreciate it!! = ))
0
Gustav BrockCIOCommented:
You are welcome!

/gustav
0
developingprogrammerAuthor Commented:
= ))
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft Access

From novice to tech pro — start learning today.