retrive the number of month from date field ....

Posted on 2006-03-25
Last Modified: 2008-03-06

I have a date field suppose (dateField) that I am attempting to use a query to determine the number of month....
for example, if the (dateField) is 30\4\1405 , I want to run a query that will return the  month (i.e. 4).  

thanks ....:)
Question by:dede11

    Author Comment

    note : the i am using hijri date ...
    LVL 77

    Assisted Solution

    Hi dede11,
    doesn't this work for hijri dates? (I don't know)

    LVL 18

    Expert Comment

    LVL 3

    Accepted Solution

    Does datepart("m",datefield) not work?
    LVL 48

    Expert Comment

    by:Gustav Brock
    As to this source:

    this calender is not exact:

    "Since the Islamic calendar is purely lunar, as apposed to solar or luni-solar,
    the Muslim (Hijri) year is shorter than the Gregorian year by about 11 days .."

    thus you'll have to run your own conversion routine, or convert it to the Gregorian calender where you can use the Month() function - but these months are different from yours ...

    LVL 58

    Expert Comment


    If the date is stored as text in your database, you want to extract a portion of that string using the backslash delimiter. The following function can help:

    Function HijriDatePart(pvarDate, pintPart As Integer)

    On Error Resume Next

        HijriDatePart = Null
        HijriDatePart = CInt(Val(Split(pvarDate, "\")(pintPart)))

    End Function

    Place it in a module and you can then use it in a query, noting that the day is part 0, the month part 1 and the year part 2.

        HijriMonth: HijriDatePart( strTheDateField, 1 )

    You can also do it all in a query expression, but it gets a little messy:

        HijriMonth: Val(Mid(Left([strField],InStr(InStr([strField],'\')+1,[strField],'\')),InStr([strField],'\')+1))

    Hope this helps

    Author Comment


    Hello  experts ...
          thanks for response ...,

      Month(datefield).........and ........datepart("m",datefield) worked perfect with hijri date....:)

    thanks again.. :)

    Featured Post

    IT, Stop Being Called Into Every Meeting

    Highfive is so simple that setting up every meeting room takes just minutes and every employee will be able to start or join a call from any room with ease. Never be called into a meeting just to get it started again. This is how video conferencing should work!

    Join & Write a Comment

    In the previous article, Using a Critera Form to Filter Records (, the form was basically a data container storing user input, which queries and other database objects could read. The form had to remain op…
    Introduction The Visual Basic for Applications (VBA) language is at the heart of every application that you write. It is your key to taking Access beyond the world of wizards into a world where anything is possible. This article introduces you to…
    Basics of query design. Shows you how to construct a simple query by adding tables, perform joins, defining output columns, perform sorting, and apply criteria.
    In Microsoft Access, learn the trick to repeating sub-report headings at the top of each page. The problem with sub-reports and headings: Add a dummy group to the sub report using the expression =1: Set the “Repeat Section” property of the dummy…

    729 members asked questions and received personalized solutions in the past 7 days.

    Join the community of 500,000 technology professionals and ask your questions.

    Join & Ask a Question

    Need Help in Real-Time?

    Connect with top rated Experts

    20 Experts available now in Live!

    Get 1:1 Help Now