A date combo box in a table 1

[MichaelintheUK]

I was surprised today by a problem I had not come across before. A colleague was trying to use a look up/combo box to select a date. When the lookup tab is accessed the options are not visible.

I have tried to work around it in 2 ways

1) by using an autonumber and a date in the lookup table and hiding the column 0 and showing column 1 (the date) in the table.

2) Creating a form and then changing the date text box to a combo box, setting the properties e.g. dataentry='yes' and viewing this in datasheet view.

I feel both of these are a bit of a fudge and would like a more elegant solution if there one.

thanks

Michael

 

[fneily]

I'll ask the obvious - Why not use a calendar? Access has an ActiveX calendar control or you can make one or you can download one from the 'net, etc.
Maybe you can better explain what you're doing, where the data is stored, etc.

 

[ajetrumpet]

Are you trying to elimate the errors that are involved with data entry? Or trying to uniform the date formats for entries in new records? If this is what you are trying to do, I would just use drop down boxes in a table. I don't know much about coding, so I can't help there. But I did this for looking up dates and years....

I autofilled dates for 5 years in 5 columns with excel. Imported that to a new Access table, indexed the columns then specified them as lookup references for my records table.

-J

 

[MichaelintheUK]

I am familiar with calendar controls and combo boxes in forms. My problem was based on direct work with tables in response to a request from someone working their way through a project.

I should explain further. The colleague is trying to use a table for data entry with the use of several combo boxes as lookups to other tables. Therefore the idea was based on using tables rather than forms, queries etc.

The problem is you cannot easily use a date field with a combo box at the table level.

thanks

Michael

 

[ajetrumpet]

You're probably right about that, but someone can certainly create a lookup table and autofill columns with standard lists (like dates, hours in a day, etc...)

I'm pretty sure you don't have to make another table to reference a list if you already have a list started in a record table. But I don't have coding expertise to figure that one out. If you're simply looking for a way to select sequencial dates, try the autofill import from Excel.

-J

 

[dhookom]

IMHO, lookup fields defined in table designs should be avoided as per

http://www.mvps.org/access/lookupfields.htm.

Always create forms for editing and adding records. This allows you much greater control and provides a legitimate user experience.

Duane MS Access MVP
[green]Ask a great question, get a great answer.[/green] [red]Ask a vague question, get a vague answer.[/red]
[green]Find out how to get great answers faq219-2884.[/green]

 

[MichaelintheUK]

Thanks Duane

I agree with the comments in the FAQ.

It is just that I was given a problem I had never encountered before where someone presented me with a problem based on an attempt to use lookup on a date field.

thanks

Michael

 

[dhookom]

If you want to abandon all sensibilities ;-) you can change the date/time field to a numeric (long or double). Set the Format to Short Date and the Row Source to something like:
[tt][blue]SELECT theDate FROM tblDates order by theDate;[/blue][/tt]

Duane MS Access MVP
[green]Ask a great question, get a great answer.[/green] [red]Ask a vague question, get a vague answer.[/red]
[green]Find out how to get great answers faq219-2884.[/green]