Conditional Change to Date Query

[danmack]

Hi all,

I need to clean up a date field (I hate dates in access!). The field in the table (imported from a spreadsheet) has records where users have simply entered a date in the correct format and then others where users feel obliged to add comments after the date rather in the 'Comments' section. Therefore Cdate alone won't work.

So far i have tried:

IIf(Len([TabDL].[PROMISE]<>10),#1/1/1900#,CDate([TabDL].[PROMISE])) AS PromiseDt1

The intention being to insert a holding date 01/01/1900 if the field contains too many characters to be a date alone. I've tried multiple variations of this code but keep getting errors across the board. Is my logic or Syntax flawed?

Any help appreciated.

Thanks

 

[pr2-eugin]

Could you please give us some example data.

 

[danmack]

Hi Paul,
The entrys are usually a date followed by a comment eg: 23/02/2014 - ALLOCATED (REQ DEL DATE NOT REACHED)
or just the date '23/02/2014' or something like 'TBA'

Thanks

 

[namliam]

IIf(Len([TabDL].[PROMISE][B][COLOR="Red"]<>10)[/COLOR][/B],#1/1/1900#,CDate([TabDL].[PROMISE])) AS PromiseDt1

Your logic isnt flawed, your typing is
the <> belongs outside the ) of the Len

Typical *duh* moment, we all done it multiple times.

Keep in mind you may want to catch dates like 1/2/14 as well which also is <>10

 

[danmack]

Thanks that has fixed it although i'm still getting a #error if the field [PROMISE] is blank....i'm guessing this is a Null issue? I'll try a NZ type solution unless anyone can suggest a better way?

 

[pr2-eugin]

This is a simple function that will get you the date from the String, if it exists. Copy the function into a standard module, make sure that the Name of the module is not the same as the function. Compile the code, then use it in a Query or wherever you wish to use it..

Public Function getDate(inputStr As String) As Date
[COLOR=Green]'**********************
'Code Courtesy of
'  Paul Eugin
'**********************[/COLOR]
    Dim tmpArr() As String
    If InStr(inputStr, "/") <> 0 Then
        tmpArr = Split(inputStr, "/")
        getDate = DateSerial(Left(tmpArr(2), 4), tmpArr(1), tmpArr(0))
    Else
        getDate = 0
    End If
End Function

For the data sample you gave me.

? getDate("23/02/2014 - ALLOCATED (REQ DEL DATE NOT REACHED)")
23/02/2014 
? getDate("23/02/2014")
23/02/2014 
? getDate("TBA")
00:00:00

 

[Galaxiom]

It would be better to test with the IsDate function

Edit: Well it was until Paul posted the getDate function.

 

[danmack]

Thanks all, really appreciate the response. I will try the function provided by Paul and let you know how i get on.

Dan

 

[danmack]

Paul could you give me an example of how you would call the funtion within the query please? I've googled it but am struggling as i've never done this before.

Thanks

 

[pr2-eugin]

Something like.

SELECT yourTable.yourStringFieldName, getDate(yourTable.yourStringFieldName) As newDate
FROM yourTable;

 

[danmack]

Paul, this works perfectly and has taught me something new! Thanks very much.

Dan

 

[pr2-eugin]

Glad it has helped ! Good Luck !