mmyyyy field on form

[Guest]

I have a process_month_year field that I set up in a table as Text-mmyyyy.
However, now I'm finding out now that it was a bad design because I can't
sort on it. I do need to display it as a month name on the input form later
down the road.
Would you recommend setting it up as a date field (but I don't want it
mm/01/yyyy-even when I define it with input mask 99/9999) or Number-integer?

Thank you!

 

[Guest]

Any date field can be formatted to display the date however you want. The
simplest way is in the properties of the text field where the date is going
to be displayed. Under the Format tab, Format, you can select Med Date from
the drop down list provided. If you want a very specific display look in help
under the Format function or even the DatePart function. This will allow you
to only display that part of the date field you want.

 

[Guest]

Thanks! Ok, I'll change my format to Date/Time and select format=Short Date.
But, when the user enters mm/yyyy, do I need to rebuild the date in order to
store it since now it is entered as only mm/yyyy and not mm/dd/yyyy which is
how it is stored in the table? Please just clarify. Thank you!

 

[Guest]

You can enter just a month and year in a date field without a day and it will
store it with the default of the 1st day. If you never show the day or care
about it then no need to change anything.

 

[Guest]

Thank you, Alan!
One more question. If I was using NZ(process_month_year,"") function
previously when the field was a text field, and now it's a date formatted
field, what is the equivalent function instead of the NZ (i.e. if the date is
a null value)?

 

[Guest]

Have you ever used an Inline If function. This might work, put it as the
control source of the Date field you are concerned with.

=IIf(IsNull([DateField]),"SomeValue",[DateField])

If the date field is null you get what ever is after the first comma,
otherwise you get what is after the second comma.

 

[Guest]

Ok. Will try this. Thanks!!

Have you ever used an Inline If function. This might work, put it as the
control source of the Date field you are concerned with.

=IIf(IsNull([DateField]),"SomeValue",[DateField])

If the date field is null you get what ever is after the first comma,
otherwise you get what is after the second comma.

Thank you, Alan!
One more question. If I was using NZ(process_month_year,"") function
previously when the field was a text field, and now it's a date formatted
field, what is the equivalent function instead of the NZ (i.e. if the date is
a null value)?