Solved Textbox "format" dropdown not working in properties in form design view

[HalloweenWeed]

Title says it exactly. I click on the dropdown arrow at the right side of the "format" properties, the down chevron to bring up the setting list context dropdown, and all I get is an empty line.
I'm reworking a split database to conform to standard database conventions (it never was designed properly over a decade ago). A subform with 15 textboxes in the detail section. I had trouble with date/time textbox trying to display date with time (too long, cut off) and when I went to select "short date" in the "format" option there is nothing in the dropdown, "short date" is not there. So I wrote it in manually: "Short Date" but that didn't help at all. I replaced the textbox with a fresh one, no change in problem. So I figured OK, the subform must be corrupted somewhere, so I started a new one from scratch. I copied all the header into the new header, then started creating all the textboxes in the detail section from scratch.

When I got halfway, after closing Access for the night and "compact and repair," then when I come in the next morning I add a (date) textbox, click on the "format" dropdown, and again the same problem: the dropdown is blank. No "short date," no "general number," or any other options. The dropdown was working properly previously while I was designing this form.
I have Googled this problem and can't find anybody complaining about this. That said, you can imagine how many hits I get with a search line where I have to search for "format" dropdown not working (in a textbox in a form).

If you don't know a solution, then I would appreciate advice on how to proceed, thank you.

 

[HalloweenWeed]

I have an idea for a workaround for this, I can use a custom vba function to strip the time from the date. But I think it may still say [DATE]+ 12:00:00 AM anyway.

 

[HalloweenWeed]

Oh yeah, it's the newest version Access Office 365.
Version 2412 Build 18324.20194 Click-to-run.

 

[GPGeorge]

The obvious question is what datatype is the field to which this control is bound?

 

[arnelgp]

if the textbox is bound to a Short Text field, there is no Item for the dropdown.

 

[HalloweenWeed]

The obvious question is what datatype is the field to which this control is bound?

Date/time.

 

[HalloweenWeed]

OK I just created a new blank database, and copied (imported) the form (subform) into it, and then the "format" dropdown worked. But I did not import the source objects, so neither the form or the textbox had valid sources.

 

[HalloweenWeed]

I think I've figured it out (thanx @GPGeorge and @arnelgp). I have 83 fields in the source query, it is a query of a query that has many other queries as it's source. Some of the queries get their data from custom vba functions that are fed other fields in the same source query or table as their parameters. This is done because the IIf functions otherwise necessary would put the SQL over the character limit and be extremely difficult to troubleshoot.

I think somewhere in there, in the fields for the textbox, access lost the field type so it's assuming a text source. I verified this by importing the source queries and tables, and seen the aforementioned problem again in the textboxes. I will work on it, thank you all.
I am relieved my database may not be corrupt.

 

[GPGeorge]

Ah, that does make sense. In fact, if one of those VBA functions includes formatting, it's highly likely that that's where the date/time is converted to a string. In other words, Access didn't "lose" the data type; a VBA function changed it. I may not be worth tracking back through it to find where it's happening, but then again, it might not hurt either.

 

[DHookom]

If you view the datasheet of the form’s recordsource, all date and numeric fields/data should be right aligned. If they are left aligned, there is some expression that is converting them to text.

 

[Pat Hartman]

To reiterate - NEVER use the Format() function to format data in a query UNLESS you need to because the query will be exported and you need to specifically control the format of the data. Format() changes the datatype from datetime or number to a string so the columns become text rather than their native data type.

 

[HalloweenWeed]

Thanx for the advice guys. But in my queries (and functions) all the evaluation is done using Null Date checks (most evals) and otherwise Date <= Date.

The sticking point was that I just learned that I have to return a type "Date" from the custom vba function in order for Access to recognize that the result field (within the query) is type Date. But in doing so, this (vba) doesn't allow for Null returns (previously it declared the return type "variant"). So now I need to screen for Nulls before I query my custom vba function. My luck has it though, that in this case, the key field is never a Null, so it works without screening. That is, if this is the only field that I run into with this problem.

And now we're stretching the limits of this subforum as it's going into SQL and vba talk. I'll make another thread in a different subforum if I can't resolve it by myself.

 

[GPGeorge]

"(previously it declared the return type "variant")"

There you go.