How to segregate-single out part of the date field (month)?

[adi2011]

How to segregate-single out part of the date field (mont)?

Hello,

I need help with following: in query I made I have one short date-type field called "CurrentDate". On my PC, default short date format is like this "MM/dd/yyyy".

In my query I have one more field which looks like this "ExtractDate: Left([CurrentDate],2)". This is how it looks like:

Task of this field is to "extract" first wo values of field "CurrentDate". So for example if value of field "CurrentDate" is "07.02.2011" then field "ExtractDate" will show value "07" and that works very good but recently I had to change short format date on my PC to this one "dd/MM/yyyy" so my field "ExtractDate" is now showing value "02".

Im not sure how to write function in query which will "extract" month from this type of date format "dd/MM/yyyy". How to single out value "02" from this type of date "07.02.2011"??

Any help is appreciated and many thanks in advance for prompt replys!

Cheers

 

[adi2011]

Hello,

I need help with following: in query I made I have one short date-type field called "CurrentDate". On my PC, default short date format is like this "MM/dd/yyyy".

In my query I have one more field which looks like this "ExtractDate: Left([CurrentDate],2)". This is how it looks like:

Task of this field is to "extract" first wo values of field "CurrentDate". So for example if value of field "CurrentDate" is "07.02.2011" then field "ExtractDate" will show value "07" and that works very good but recently I had to change short format date on my PC to this one "dd/MM/yyyy" so my field "ExtractDate" is now showing value "02".

Im not sure how to write function in query which will "extract" month from this type of date format "dd/MM/yyyy". How to single out value "02" from this type of date "07.02.2011"??

Any help is appreciated and many thanks in advance for prompt replys!

Cheers

 

[bob fitz]

Hi
You may like to try the Mid function Mid(string, start[, length]) Full details can be found in the Access VBA Help file.

The following is untested but may be of use to you.

ExtractDate: Mid([CurrentDate],4,2)

 

[raskew]

Hi -

Try the Month() function. Example:

x = date()
? x 'US date format
7/2/2011 
? month(x)
 7 

Or, if you needed to show 2 digits:

? format(month(x), "00")
07

Hope that helps - Bob

 

[MarkK]

Check out the Month() function provided by the VBA.DateTime module.
Cheers,
Mark

 

[jwhite]

To extend raskew and lagbolt's on-target responses, using Month() will only work if the date field is stored in the table as data type - Date/Time. Month() is also correct because it will pull the month no matter what the Regional Settings of the computer is. Attempting to use Mid() will be inaccurate on computers with different regional settings.

 

[boblarson]

using Month() will only work if the date field is stored in the table as data type - Date/Time.

Hmm, my test using a text data type still worked with Month([FieldName]). That was with Access 2010 and U.S. Regional Settings, so I don't know if it will work that way with any others.

 

[adi2011]

Thank you all very much for prompt replys, I really appreciate it.

@
bob fitz

your code worked like charm, I just had to modify it a little bit because I use Microsoft Access 2003

ExtractDate: Mid([CurrentDate];4;2)

Thank you!