Combo Box Date Picker

[RexesOperator]

I must be missing something obvious.

It would be nice to have a date picker, to reduce input errors.

I'm trying to get a combo box datepicker to work from a popup form. I've looked at fontstuff's version (http://www.fontstuff.com/access/acctut13a.htm), but it has the date on the main form (as do the examples I've found using search).

I have to run two queries to get the data I need, and the best way is to have them called at the same time from a macro - hence the popup form. If I use a text box txt_Date formatted as a date box forms![GetDate]![txt_Date] works fine. How do I get the variabe txt_Date to accept the results of the three combo boxes as a single date value?

 

[RexesOperator]

I'm trying to avoid using anything that is dependent on a particular version of Access. That makes the program easier to upgrade. As it is - I'm stuck using Access 2000 rather than 2003 with Access 2007 on the horizon, so I am already 2 issues behind! The ones I have seen look like they require an ActiveX control. Fontstuff has one that emulates a popup, but it is on the main form. I need to have the input on a separate form to pass the date as a parameter to two queries. What I would like to be able to do is something like:

forms![GetDate]![txt_Day]
forms![GetDate]![txt_Month]
forms![GetDate]![txt_Year]

Then combine the results and have the final parameter behave like a date field.

 

[CraigDolphin]

Well, this doesn't answer your question per se, but Stephen Lebans has an awesome calendar that does not use ActiveX so it should be a little more future-proof. http://www.lebans.com/monthcalendar.htm You can run it from within your popup form easily enough.

As for getting the box to combine the three other control values, you might look into the format function. Something like:

Dim mydatestring as string
mydatestring = "#" & forms![GetDate]![txt_Month] & "/" & forms![GetDate]![txt_Day] & "/" & forms![GetDate]![txt_Year] & "#"

Me.txt_Date = Format(mydatestring,"Short Date")


You could also try using a global/public variable as the parameter for your queries and bypass your popup form entirely.

 

[RexesOperator]

I've looked at Steve Lebans site before. It's a bit more than I need. I'm going to try your idea since it's close to one I thought of, but now I have the code to start. What is the purpose of the wild cards "#"?

I'm not sure I understand what you mean by "You could also try using a global/public variable as the parameter for your queries and bypass your popup form entirely." These dates are input parameters for queries.

 

[CraigDolphin]

Actually, you may not need the # in this case....usually you need to put them either side of a date/time so that access knows that what is between them is a date. Exactly parallel to enclosing a string inside a pair of " ".

Queries can use a global/public variables as parameters for queries in the same way you can use a control on a form as a parameter.

As an example, in a module declare a variable like:
Public currentdate As Date

then a function to retrieve the value of the variable:
Function Getcurrentdate()
Getcurrentdate = currentdate
End Function

somewhere you would set the value of this global variable. For example, if you were using lebans calendar you might open the calendar via a command button and store the value of the date that you select using

currentdate = dtStart '(dtStart is the output from the calendar)
instead of
Me.TxtDate = dtStart

Then, in the query, instead of Forms!fmYourForm!TxtDate as your criteria, you'd put something like
Eval("Getcurrentdate()")

 

[RexesOperator]

Thanks for the detailed explanation. I'm familiar with the concept of global/local variables from a linear programming perspective (Turbo Pascal - late 80s - vintage), but not in the vba environment which is entirely foreign (language pun intended) to me.

I'm still having trouble getting the query to recognize the text string. It may be a data type issue.

This may sound like a stupid question, but when I create my combos for the day and month and text box for the year, should the wizard button on the toolbar menu be on or off (I know it matters for some things)? Or does it matter in this case?

I am trying to put

"#" & forms![frm_GetOneDate]![cboMonth] & "/" & forms![frm_GetOneDate]![cboDay] & "/" & forms![frm_GetOneDate]![txt_Year] & "#"

directly into the query as a parameter. I get the #Name error on the form, so the query is not recognizing something. I have also tried to get it to read one variable at a time to see if there one particular variable that is a problem. It should be easy to concatenate text as a string and then use the # # notation to convince Access it is looking at a date - shouldn't it?

 

[CraigDolphin]

You don't need the # in this case.

forms![frm_GetOneDate]![cboMonth] & "/" & forms![frm_GetOneDate]![cboDay] & "/" & forms![frm_GetOneDate]![txt_Year] should work as a criteria in a date field provided that the values in your text boxes are integers AND that the concatenation comes up with a valid date. e.g., 2/31/04 is not a valid date (28 days in feb unless it's a leap year!) and neither is 9/31/05 etc
Also, make sure that the query will not be run while the form is closed, or before you have values in all three combo boxes.

When using the wizard to create combo boxes, make sure that the bound column (Properties>Data) matches the correct column in the row source query. Oftentimes the wizard hides and stores the value in the key column of the table, while displaying the column you are interested in. Check Properties>Format and look at the Column Count and Column Width properties. If the bound column = 1 and the column count is 2, and the first column width is 0, then the combo box is hiding the actual field/value it is selecting.

To fix this, click on the three dots next to your combo box's row source. It will open the query that generates the combo box list. If you see any field other than the one you want then delete those extraneous fields and close the query (it will prompt you to save the SQL....do so). Then change the column count and column widths to match any changes you've made to the rowsource.

I've attached a A2k file demonstrating the use of combo boxes on a form as a parameter to a query. I have not coded to prevent you entering impossible dates and there will be a recurring error box on the form if you enter one.

I still think you're better off using a calendar and a single storage field or global variable: that way you can be assured that no impossible dates will foul up your query. The other option is to do some research on cascading combo boxes and put some code in the afterupdate box for each combo box to ensure that impossible dates will not result from the change. Don't forget about leap years either.

 

[billyr]

I have a calendar that I have used with access 97 and 2000. It uses a popup form called by a function attached to a small custom command button. There is public module that comes with it so you can use the calendar with any form for any date. I always place the small command button adjacent to the textbox it will fill. If you are interested, I can make a sample db and attach it to a subsequent reply.

 

[RexesOperator]

Thanks CraigDolphin. I was using a value list (like the one at fontstuff) for the days and months, but yours works perfectly for what I want. I have been using a lot of unbound combo boxes for specific reasons, and I wasn't sure about the wizard button. One nice thing about NOT using wizards, is it forces you to check all the properties yourself to make sure it works. It makes it easier to trace things when they don't work.

And thanks for the lessons on the combo-box wizard and variables. This forum is a better learning place than any course I've taken.

To billyr - thanks for the offer, but the one CraigDolphin supplied works exactly as I want it to.

 

[CraigDolphin]

Glad to repay the favor....learned everything I know about Access here myself. Just be super careful about those impossible dates!

 

[RexesOperator]

Funny how one problem creates another. This query is giving me a "too complex expression" error. What have I done (or not done)? BTW the code worked great for single dates. I'm trying to get a range of dates here.

Between [Forms]![frm_GetStartEndDates]![cbo_StartMonth] & "/" & " [Forms]![frm_GetStartEndDates]![cbo_StartDay]" & "/" & [Forms]![frm_GetStartEndDates]![cbo_StartYear] And [Forms]![frm_GetStartEndDates]![cbo_EndMonth] & "/" & [Forms]![frm_GetStartEndDates]![cbo_EndDay] & "/" & [Forms]![frm_GetStartEndDates]![cbo_EndYear]

 

[CraigDolphin]

make sure all combo boxes have values in them, check your combo box and form names are all correct as per your criteria. I just tried the between ... and... with the example db I showed you and it works just fine.

 

[RexesOperator]

I used copy and paste and changed the names to my objects. All works now. I must've had a typo somewhere.

That's the last major(?) part of my project. Now all I have to do is document it! Ugh!