Date range?

[Exposita]

Hi,



first of all, i hope my English is good enough to explain my problem.

I want to use a date field in my table to select a period and not just one exact date.

The data i want to import have multiple ranges such as Q1-2010 2010 and 2017/2019 i want to be able to check on the first date and the last date to get everything the same.

I hope there is someone who can help me with this.

Greetings,
Exposita

[June7]

This won't be easy. Those are not date values, they are text. And there does not seem to be a consistent structure, if those examples are all in the same field. Consistency is critical to any effort at string manipulation.

[Exposita]

the date values i have are indeed text and not usable. to make them usable i'm looking for a way to get them in one field by selecting the first day and the last day of the range in a calendar. I don't know if this is possible and if anyone has another wa i'm completely open for it.

[June7]

Don't understand.

The "2017/2019" value could easily be parsed into to two date values but what about "Q1-2010 2010"? A single procedure to handle every possible structure in this field would not be difficult if the set of possibilities is finite and known.

[Exposita]

i have a list of names with install dates, the dates vary in format and in range.

For example:

Name date
a oct-2012
b 2016
c Q4-2014
d 2015-2018

I want all the dates to be the same format and i'm looking for the most efficient way to get this in access.

I hope you now understand my problem.

[June7]

I do understand and my previous remarks apply. You do not really provide any new info to work with. Are the possible combinations finite? What are those exact structures? Are those 4 examples every possible combination?

[Exposita]

I don't understand "Are the possible combinations finite?"

What are those exact structures?"
there project dates

Are those 4 examples every possible combination?
I think these are indeed all possible combinations but i'm looking to get them all to one structure so that the are all the same format.

I wanted to get the table set as date and select the date range, for example Q1-2013 gets to be 1-1-2013 till 3-31-2013 selected in the calendar, but i don't think that's possible. In that way i can get all formats to be the same and see in what range the project is running.

[June7]

Those values are not dates. They are just strings of characters in various combinations of letters, numbers, and symbols. Converting these strings to date values will require VBA code. It would be necessary to know all possible combinations (patterns) that will be encountered. You show 4 examples of data.

oct-2012
2016
Q4-2014
2015-2018

Those examples represent 4 distinct patterns:

mon-year
year
Q#-year
year-year

A VBA procedure (sub or function) can be written to do the conversion. The procedure can either just show the results in calculated field of query or actually populate fields in table.

[Exposita]

do you have an example for me? I've never used VBA before so i don't know how this will show in my table/form and how i can use it.

[June7]

Need to understand programming, start with review of http://office.microsoft.com/en-us/ac...010341717.aspx

How many records are involved? Here is a function that will handle the sample data (assumes every record has a value of one of the 4 patterns and does not include error handling):

Function ConvertDate(strDate As String, strRangePosition) As Date
Dim aryParts As Variant
Dim dteStart As Date, dteEnd As Date
If InStr(strDate, "-") Then
    aryParts = Split(strDate, "-")
    If Val(aryParts(0)) > 0 Then
        'strDate is pattern YYYY-YYYY
        dteStart = "1/1/" & aryParts(0)
        dteEnd = "12/31/" & aryParts(1)
    ElseIf Left(aryParts(0), 1) = "Q" Then
        'strDate is pattern Q#-YYYY
        dteStart = CDate(Choose(Mid(aryParts(0), 2), 1, 4, 7, 10) & "/1/" & aryParts(1))
        dteEnd = DateAdd("m", 3, dteStart) - 1
    Else
        'strDate is pattern MMM-YYYY
        dteStart = CDate(aryParts(0) & "/1/" & aryParts(1))
        dteEnd = DateAdd("m", 1, dteStart) - 1
    End If
Else
    'strDate is pattern YYYY
    dteStart = CDate("1/1/" & strDate)
    dteEnd = CDate("12/31/" & strDate)
End If
ConvertDate = IIf(strRangePosition = "Start", dteStart, dteEnd)
End Function

Call the function in a query or textbox on form or report, like
ConvertDate([fieldname],"Start")
ConvertDate([fieldname],"End")