How to insert split MONTH/DAY/YEAR combobox values into date field in new record?

[arashii]

Hi again everyone. I have a table with a Date field, format xx/xx/xxxx .

I'm trying to create a form that will let the user choose the month/day/year from 3 dropdown comboboxes to make it easier for them to choose the date, then pass this formatted date off to a query and a new record datasheet.

I was able to do it for the query, see form below:

Basically my two textboxes concatenate the 3 values in the comboboxes, then pass that to the query.

My criteria value in the query is:
Between [Forms]![Date]![TextFieldDate] And [Forms]![Date]![TextFieldDate2]

and it is able to get the formatted xx/xx/xxxx dates from the 2 textfields where I concatenated the dates
TextFieldDate = (=ComboMonth.Value & "/" & ComboDay.Value & "/" & ComboYear.Value)

My problem is doing the same thing for a FORM that creates a new record .
If I do the same thing on the Date textbox (=ComboMonth.Value & "/" & ComboDay.Value & "/" & ComboYear.Value), then the box gets the value, but then how do I pass that value on to the new record datasheet . On the other hand, if I put the Date field where the textbox value should go in the Control Source property, then the textbox just shows some date values already in existing records, and not the date the user specified.

I hope my question isn't so confusing. Appreciate any help on this.

Thank you!

 

[Bodisathva]

Date criteria in query expressions that are not enclosed by '#' are interpreted as strings, not dates.

02/12/2007 is a string
#02/12/2007# is a date

 

[neileg]

Why not use the calendar control?

 

[arashii]

@Bodisathva
Hi, thanks for that info! I think that Access automatically recognizes my input as date because it does get accepted as criteria for a date column? My query doesn't return errors on that

@neileg
Regarding your question, it was because I never knew there was a built-in calendar control till you mentioned it! (I only use the controls that are instantly visible in the control toolbox) Thanks! Sure makes life a lot easier ~_~; . I also found some ready-to-use calendar forms/codes on the net, featuring cuter-looking calendars. Will it be advisable to use them or just stick to the default Access calendar for more stability?

 

[RuralGuy]

MVP Jeff Conrad has collected links to tons of calendars here. Almost all of them have versioning advantage over the built in calendar.

 

[arashii]

Hi, thank you! The calendar I used before was one of the samples on that website , so I guess it's a good and stable one.

 

[RuralGuy]

You are welcome.