Date Dilemma

[dgriffis1231]

Hello Everyone!,

I have two issues:

1. I have a month field with month values and a year field with year values .... I would like to combine them into a date field ex: 8 2012 8/2012

2.The second is a little more complex: There are two fields- the first field is a mixture of positive, negative integers and text dates. The second will be the field above we created in question 1. The goal is to apply the first field or the delay field to second if it is an integer but if it is a text date then the text date has priority. Examples:

Scenario 1
Field 1 : -1 (MONTH INCREMENTS)
Field 2 : 05/2013
Field 3 : 04/2013

Scenario2
Field 1 : 01/2012 <---I want the query to return the text date field if present.
Field 2 : 03/2013
Field 3 : 01/2012

Any takers? Thanks guys.

 

[plog]

You lost me with your examples. Post some sample data showing what the data looks like going in (along with table and field names) and then what the results should be based on that sample data. Use this format:

Starting data:
TableNameHere
Field1Name, Field2Name, Field3Name
1, 4/1/2010, David
3, 7/7/2011, Tom
7, 8/18/2009, Larry


Resulting data:

 

[dgriffis1231]

Sure, my apologies!

Problem 1
Month Field...Year Field...Datefield
01...............2013..........01/2013

Datefield is the desired result


Problem 2
Delay Field...Start_Date...Capital_Date
-1..............01/2013.......12/2012
2..............01/2013.......02/2013
01/2013......01/2011.......01/2013

Capital Date is the desired result.

 

[plog]

Problem 1 is simple concatenation (http://www.techonthenet.com/access/functions/string/concat.php):

Datefield: [Month Field] & "/" & [Year Field]

Problem 2 requires a little more logic and manipulation. But first, I think your 2nd example is in error:

Delay Field...Start_Date...Capital_Date
2..............01/2013.......02/2013

Should Capital_Date for that one be instead 03/2013? If not, tell me what these should yield for Capital_Date:

Delay Field...Start_Date...Capital_Date
-1..............01/2013.......12/2012
0..............01/2013....... ?
1..............01/2013....... ?
2..............01/2013.......02/2013

 

[dgriffis1231]

Wow, you are absolutely correct! My examples are not doing so hot today.

Adding 2 would add 2 months to the date example: 3/2013
Adding 0 would result in no change.
Adding 1 would add a month to the date.

 

[plog]

No problem. Because that issue requires a lot of logic and you have 2 text fields and not numeric nor date fields, I created a function to determine Capital_Date. Below is the function--you should paste this into a module:

Function getCapitalDate(Delay, Start)
' determines Capital Date based on Delay value (number or month) and Start value
 
Dim ret As String
ret = Delay
' return value, by default will be Delay
 
If (InStr(ret, "/") = 0) Then
' if Delay is not a Month/Date value, adds it to Start to determine CapitalDate
 
    Dim date_tmp As Date
    ret = ""
 
    date_tmp = CDate(Mid(Start, 1, 2) & "/1/" & Mid(Start, 4))
    date_tmp = DateAdd("m", Delay, date_tmp)
' converts Start value to actual date and adds Delay to it
 
    If (Month(date_tmp) < 9) Then ret = "0"
    ret = ret & Month(date_tmp) & "/" & Year(date_tmp)
' converts date_tmp from date to month/year value
 
End If
 
getCapitalDate = ret
 
End Function

Then to use it in a query, you would call it like this:

Capital_Date: getCapitalDate([Delay Field],[Start_Date])


The real key is using the DateAdd function (http://www.techonthenet.com/access/functions/date/dateadd.php). But, because you don't have actual dates (i.e. 12/2011), I had to convert them to dates by breaking them apart into their Month/Year values and building a date out of them using Cdate (http://www.techonthenet.com/access/functions/datatype/cdate.php). Then I had to reverse that process to give you just the month/year instead of an actual date. Another lesson in why you should store values as the correct data type.

 

[dgriffis1231]

Killer that works for sure! I created:

IIf([FirstOfDelay]=Format([FirstOfDelay],"mm/yyyy"),Format([FirstOfDelay],"mm/yyyy"),Format(DateAdd("m",[FirstOfDelay],[Earliest Production Start Date]),"mm/yyyy"))

Which also works but yours is more concise in query editor.

Thank you very much

DG