Date fields

[kschomer]

I am downloading data from Siebel and importing it into MSaccess to generate reports. I import the date fields as text because I am receiving errors when I try to import them as date fields. Once the date has been imported, I change the text field to a date field. The problem is that the dates from Siebel are ie. 4/1/01, 4/10/01, 4/9/01 etc. instead of 04/01/01, 04/09/01, etc. Using a MIN[completed] and MAX[completed] the expected results are MAX[4/9/01] when it should be 4/30/01. But since 9 is greater than 3 I quess that is where the problem is. Do you have any suggestions to get around this situation???

 

[BlackKnight]

Hi,

Hmmm...I duplicated what you did by importing dates into a text field of an Access 2000 table. I changed the field from text to a date datatype. I used exactly the same data as you did. I ran a query to get the min and max dates. The dates returned were 04/1/01 and 04/30/01 respectively. Isn't this what you want?

Have a good one!
BK

 

[kschomer]

Thank you for your prompt reply... you are right. I quess I forgot to convert the text field to a date field. I am trying to automate this weekly report and let someone else process the weekly data. Do you know WHY I can't import the raw data as a date/time field so that I don't have to import it as text and remember to change it to date/time. Or do you have any suggestions to automate the process even if I do import the raw data as text... I want to be able to give a front end Menu application to the user without any interface from me. Thanks for your help. Karen

 

[BlackKnight]

Hi,

Depending upon how you are importing the records. Are you using the docmd.transfertext or are you using your own import routine? If you are using your own routine then you can convert the imported date value to a date/time value with cdate() function.

If you are using the transfertext method then you can define an import specification which stores the information Microsoft Access needs to import a text file

Have a good one!
BK

 

[kschomer]

I only know how to import the data using the standard MSAccess FILE; Get external Data; Import... I DO have a specification set up. However, unless I have the [Completed] field defined as TEXT I receive import errors on the field and the field is BLANK.