Converting Number to Date Format 1

[rstitzel]

I have an table on an AS400 that I have linked to using ODBC. There's a date field that is stored as a number. For example 20101. I want to be able to convert this number to a date 02/01/01. I tried using formatdatetime but I'm getting incorrect dates. For example 20101 becomes 1/12/1955. What am I doing wrong?

Thank you.

 

[nealvenables]

Hi,

This is a little(!) convoluted, but should do the job.

Mid$(Format(20101,"000000&quot,1,2) & "/" & Mid$(Format(20101,"000000&quot,3,2) & "/" & Mid$(Format(20101,"000000&quot,5,2)

 

[rstitzel]

That did the trick. Thank you VERY much.

 

[rstitzel]

A problem I've found once the field is converted using the format suggested by NEALV the field is not a REAL date.

I'm trying to use an expression that looks for a date (from the converted field using the expression NEALV suggested) but it's not always accurate. For example I enter the expression &quot;between 02/17/03 and 02/22/03&quot; and I'll get records with the dates in the wrong year. Also when I enter &quot;<[startdate]&quot; I only get a few records and not everything that is less than the start date I've entered.

The field looks like a date field but doesn't &quot;act&quot; like one. Is there a way to take a date stored as a number i.e. 21703 and turn it into a real date?

Hope this makes sense!?

Any and all help is appreciated. Thank you.

 

[beetee]

The code looks good.

Can you post an example of an incorrectly converted date?

Also, although you mention converting the number to a date, you didn't indicate if you were storing the information in a 'date' field.

This seems to work fine:

Mid$(Format(21703 ,&quot;000000&quot,1,2) & &quot;/&quot; & Mid$(Format(21703 ,&quot;000000&quot,3,2) & &quot;/&quot; & Mid$(Format(21703 ,&quot;000000&quot,5,2)

as does this:

cdate(Mid$(Format(21703 ,&quot;000000&quot,1,2) & &quot;/&quot; & Mid$(Format(21703 ,&quot;000000&quot,3,2) & &quot;/&quot; & Mid$(Format(21703 ,&quot;000000&quot,5,2))


you arent't by any chance storing dates as D/M/Y are you?

If so, the expression needs to be changed as follows:
Mid$(Format(21703 ,&quot;000000&quot,3,2) & &quot;/&quot; & Mid$(Format(21703 ,&quot;000000&quot,1,2) & &quot;/&quot; & Mid$(Format(21703 ,&quot;000000&quot,5,2)

 

[rstitzel]

Thank you very much for the replies. The problem WAS in my start and end date formats. I had them set to number and NOT date/time. That's why I was getting spotty results.

Sorry for questioning the suggestions above.

Thank you.

 

[Bodhis3mta3]

Hi, all...

I've battled this problem for nearly a week, and hope you can give me some guidance in the right direction, but...

What if I'm not storing the data anywhere? I'm trying to pull the data straight from an AS400 logical join file (of about 7 different files) through an Access query strictly for reporting purposes (corporate likes it a lot better than greenbar (shrug)). I can't do any date calculations (ie, between ([orderdate] -7) and [orderdate] (for costs the week before, which is what the orders are priced by)) because of the AS400 numeric fields (yyyymmdd).

Is there anyway to convert the data "on the fly" or perhaps on the 400 side in my join file?

Thanks for any help at all.

 

[rstitzel]

Not sure if this will work. Go into your odbc link and click on the SERVER tab. Click ADVANCED. You can change the date format. I haven't tested it so I don't know if it will work. Worth a shot....

 

[SeanR01]

Just on these 2 problems, rstitzel's March problem can also be solved by using the DATEVALUE () function - try surrounding your MID statement with DATEVALUE (). (Without doing this, it only remains as a string.) DATEVALUE () is smart enough to recognise a number of typical date formats automatically (based on your NT regional settings). So you need to say something like:

DateValue (Mid$(Format(21703 ,"000000"),1,2) & "/" & Mid$(Format(21703 ,"000000"),3,2) & "/" & Mid$(Format(21703 ,"000000"),5,2))

To answer Bodhis3mta3, you can create a calculated field in a query which does the complete conversion of the AS400 date format much as in the example above, then you are working with a real date. If you're using Access, you can use the Query grid to construct such a formula, which of course has an equivalent syntax generated in SQL. An even better solution for the long term is to convert the formula above to a user defined function in the Modules area of Access - just create a new module called 'Functions' or similar, then create a function like:

Function ConvDate (InString as String) as Date

If IsNull (InString) Or Not IsNumeric (InString) or Len (InString) < 5 or Len (InString) > 6 then Exit Function

ConvDate = DateValue (Mid$(Format(21703 ,"000000"),1,2) & "/" & Mid$(Format(21703 ,"000000"),3,2) & "/" & Mid$(Format(21703 ,"000000"),5,2))

End Function

Then you can just use the 'ConvDate' function anywhere in that database any time you want to do such a conversion, rather than typing up all the MID statements every time. There may be a way of adding in ConvDate to other projects by making a library reference to it, I haven't got that far with Access yet, but I have a grab-bag of custom functions I've written to do stuff like this, which I isolate from more 'ad hoc' code in a module named 'Functions'.

That syntax might be a bit wonky, but you get the idea - the only issues you might face are if you try to run it over a NULL value or an empty string or whatever - so you should ideally do some error checking - the first sample line above attempts to do this, not sure what value is returned if you just exit the function on invalid data as I've done. If the function keeps complaining of a type mismatch or whatever when it tries to convert dud data, try declaring it as:

Function ConvDate (InString as String) as Variant

hth,
Sean

 

[Bodhis3mta3]

Stars for ya!

Sometimes it takes a new set of eyes to see the problem. I'm slapping myself in the head for not thinking of the obvious. I was so intent trying to stuff that code into the report's VBA that I completely missed the module option!

Kudos, Sean, you just gave me a Friday for a week's worth of Mondays...

 

[SeanR01]

of course, i shoulda said:

Function ConvDate (InString as String) as Date

If IsNull (InString) Or Not IsNumeric (InString) Or Len (InString) < 5 Or Len (InString) > 6 then Exit Function

ConvDate = DateValue (Mid$(Format(21703 ,"000000"),1,2) & "/" & Mid$(Format(21703 ,"000000"),3,2) & "/" & Mid$(Format(21703 ,"000000"),5,2))

End Function

just had to come back and do that TGML thing for the first time!!!