Date Field (1 Viewer)

[Trevor G]

I am looking to create a table which holds exchange rates, but we only want to enter the month and year as the exchange rates are valid for a month not the day of the month.

I looked to create a field and did use Date/Time set the properties as follows:

Format mmmm yyyy
Inputmask to 00/00

When I enter date like 06/11 it converts it to this month and year rather than June 2011.

Is it possible to do this using Date/Time or do I have to use a Text Field

 

[Mr. B]

In situations where I only need to store the Month and Year, I normally use a text field. It is easier to manage the data.

 

[plog]

I vote against text. Mainly because if you ever want to order your data by month, April will be first and October will be last.

I would either use a date field and always enter your data with the 1st day of the month (i.e. June 2006 becomes 6/1/2006, October 2008 becomes 10/1/2008). Or I would break this into two numeric fields to store the month and year.

 

[Trevor G]

Interesting responses guys.

I have looked at both ideas/suggestions and will look to work with date/time field use a date picker and then format the field to show month year, I can then query on that if entries range the dates in the month.

 

[Alansidman]

Trevor;
I tried to recreate this abnormal behavior, but when I set the format and input mask as you indicate, I got the correct date in Month Year format. I wonder if this is a UK/US date convention issue. BTW, I tried it on my XP machine which has Access 2002 on it. I have not tried it on my WIN7 machine which has Access 2007 loaded.

Alan

 

[vbaInet]

For me I would use a Date/Time field and concatenate a "1" to it before saving it. So your date field will become:

CDate("1/" & [Month/Year entered])

That way you can still use the Date/Time functions for performing operations on the Date/Time field.

You will need to get rid of the Format property.

Edit: I didn't see the other responses before posting. I only saw Mr. B's.

 

[Alansidman]

I went back and re-looked at this. I was getting the correct date because I was inputting all years as 11. When I tried to input a date such as 12/05, it returned December 2011. So I guess this is not a US/UK convention issue. Very strange.

Alan

 

[Trevor G]

Hi Alan,

It Probably is Regional settings related as the date formats are different.

I think this will work for what I need though.

 

[vbaInet]

Trevor,

Did you notice my reply? It's a bit similar to plog's.

The behaviour you see isn't bizarre. What Access is trying to do is form a date out of the text you entered. Remember that the Format property performs some validation, i.e. it ensures that data entered is of the (first) data type and (second) format applied.

 

[The_Doc_Man]

I would do the following:

1. In a general module, define a public function called BegOfMonth that returns a date given an arbitrary input date.

2.a In the function, pull apart the input date's information using DatePart to get the day of the month. This will be a number from 1 to 31, of course.

2.b If the day is 1 then return the input date to step 2.d

2.c If the day is > 1, then compute the input date minus the day number using DateAdd with the "d" for the units, - (day number - 1) for the numeric add value, and the input date as the base date value. Send this result to step 2.d

2.d whatever you return, you might wish to finish the function with

BegOfMonth = CDate( CDbl( CLng( CDbl (date resulting from 2b or 2c))))

The above line with the funky conversions normalizes the date to midnight of the resultant date. The output of this function is a perfect normal (and normalized) date for the first day of the input month.

3.a To get the beginning date of this month, use BegOfMonth(Now())

3.b To get the beginning date of an arbitrary full date, use BegOfMonth(arbitrary date)

If it is a public function, it can be used in VBA code AND in SQL queries or on the query grid.

 

[Trevor G]

Trevor,

Did you notice my reply? It's a bit similar to plog's.

The behaviour you see isn't bizarre. What Access is trying to do is form a date out of the text you entered. Remember that the Format property performs some validation, i.e. it ensures that data entered is of the (first) data type and (second) format applied.

Sorry I never replied lastnight I had to do some other things.

I like this idea, just point me to where I need to place the formula.

CDate("1/" & [Month/Year entered])

The_Doc_Man, your response is also a very good idea.

 

[vbaInet]

The usual After Update of the control or the After Update of the form or the Lost Focus of the control. It all depends on when you want it fired.

 

[Trevor G]

I have tried this but it doesn't like it.

I added this to the After Update Event, it colours the code red on the CDate line.

Obviously I am doing this wrong.

It is a date/time field, no format or mask. I select a Date from the date picker and it doesn't work.

Private Sub ExchangeMonth_AfterUpdate()
CDate("1/" & ExchangeMonth)
End Sub

 

[vbaInet]

It doesn't work because you need to assign that value to the field.

So are you are using the Date Picker now? Selecting a date from the date picker requires you to use the Change event of the textbox.

 

[Trevor G]

It still shows the text in Red no matter what I have done.

A real simple database is attached. A single table and single form.

If you have the time and would look at it that would help me out.

 

[vbaInet]

A bit confused here Trevor. Did you say you are using a Date picker control?

 

[Trevor G]

I have MS Office 2007 but using MDB so the Date Picker is part of the Date/Time Field.

 

[vbaInet]

I have MS Office 2007 but using MDB so the Date Picker is part of the Date/Time Field.

Ah, that's what got me confused. I saw the Date picker pop-up but noticed it was a 2003 db.

If you are solely going to be using the Date picker you won't be needing that code. And since you want just the Month/Year entered what you should be giving your users is a Month/Year picker.

It would be best if you rethink your options and tell me exactly whether you will definitely still use the Date picker or just get your users to type it in or a combi of a Month/Year picker and manual entry.

 

[Trevor G]

I rethought as suggested, went with text field and additional table in the background to add in month year then use the lookup wizard to link them together, to keep them in the order I want I added a autonumber field to the right and hide from view, then added a form to allow users to add new month year in a controlled area.

Bless you for taking the time to look at this question.

Hope you have a lovely weekend.

 

[vbaInet]

If I get a bit of time later today I will amend your db and show you the approach I was talking about.

But if I don't do it this weekend, have a good one too