Type Conversion Failure with dates Judy Ward 18.08.2006 20:36:02 Every day I run commands that truncate a table (tblSAR) and import new data from an Excel worksheet. All of a sudden I am getting a Type Conversion Failure for the date fields. I can see that some of the dates in the Excel worksheet are in the format mm/dd/yyyy (with a leading zero) and some are not. Excel thinks the fields with a leading zero are text fields with a format of "General". I have tried formatting these fields as Date in Excel and then performing the import. That did not solve the problem and I can't figure out why. The data I get is coming out of another application--I don't have any control over that. I do have control over formatting the Excel worksheet I want to import. Linking to the Excel worksheet is not an option. My Primary Key field has some numeric and some alphanumeric entries and I get an error when I try linking. I would sure appreciate any suggestions on what else to try? Thank you, Judy

Re: Type Conversion Failure with dates "Ken Snell \(MVP\)" <kthsneisllis9[ at ]ncoomcastt.renaetl> 19.08.2006 00:18:05 It can be difficult to handle these situations when importing EXCEL data. But this is a method that should work all the time: Import the data to a temporary table where the fields are text. Then use an append query to copy the data from that table to the real table, using calculated fields in the query to convert the text data to the proper format for the permanent table. -- Ken Snell <MS ACCESS MVP> "Judy Ward" <JudyWard[ at ]discussions.microsoft.com> wrote in message news:EC8934D3-5550-449A-B681-57FDD92D44AC[ at ]microsoft.com... [Quoted Text] > Every day I run commands that truncate a table (tblSAR) and import new > data > from an Excel worksheet. All of a sudden I am getting a Type Conversion > Failure for the date fields. I can see that some of the dates in the > Excel > worksheet are in the format mm/dd/yyyy (with a leading zero) and some are > not. Excel thinks the fields with a leading zero are text fields with a > format of "General". I have tried formatting these fields as Date in > Excel > and then performing the import. That did not solve the problem and I > can't > figure out why. > > The data I get is coming out of another application--I don't have any > control over that. I do have control over formatting the Excel worksheet > I > want to import. Linking to the Excel worksheet is not an option. My > Primary > Key field has some numeric and some alphanumeric entries and I get an > error > when I try linking. > > I would sure appreciate any suggestions on what else to try? > Thank you, > Judy

Re: Type Conversion Failure with dates Judy Ward 19.08.2006 01:19:01 Thank you very much for responding. Since I have to do this every day and there are thousands of records, I was looking for the easiest (most automated) way possible. What I ended up doing was adding code to the Excel macro that is already performing formatting on the spreadsheet. Multiplying every field of the date columns by the number 1 (using Paste Special) solved the problem. It left the fields that were already dates alone and turned the fields that were text into date values. Importing into Access gives the desired results. "Ken Snell (MVP)" wrote: [Quoted Text] > It can be difficult to handle these situations when importing EXCEL data. > But this is a method that should work all the time: > > Import the data to a temporary table where the fields are text. Then use an > append query to copy the data from that table to the real table, using > calculated fields in the query to convert the text data to the proper format > for the permanent table. > > -- > > Ken Snell > <MS ACCESS MVP> > > "Judy Ward" <JudyWard[ at ]discussions.microsoft.com> wrote in message > news:EC8934D3-5550-449A-B681-57FDD92D44AC[ at ]microsoft.com... > > Every day I run commands that truncate a table (tblSAR) and import new > > data > > from an Excel worksheet. All of a sudden I am getting a Type Conversion > > Failure for the date fields. I can see that some of the dates in the > > Excel > > worksheet are in the format mm/dd/yyyy (with a leading zero) and some are > > not. Excel thinks the fields with a leading zero are text fields with a > > format of "General". I have tried formatting these fields as Date in > > Excel > > and then performing the import. That did not solve the problem and I > > can't > > figure out why. > > > > The data I get is coming out of another application--I don't have any > > control over that. I do have control over formatting the Excel worksheet > > I > > want to import. Linking to the Excel worksheet is not an option. My > > Primary > > Key field has some numeric and some alphanumeric entries and I get an > > error > > when I try linking. > > > > I would sure appreciate any suggestions on what else to try? > > Thank you, > > Judy > > >

Re: Type Conversion Failure with dates "Ken Snell \(MVP\)" <kthsneisllis9[ at ]ncoomcastt.renaetl> 19.08.2006 17:02:10 "Judy Ward" <JudyWard[ at ]discussions.microsoft.com> wrote in message news:414ADA21-C5CB-404B-8AF9-35E8D9A81E1A[ at ]microsoft.com... [Quoted Text] > Thank you very much for responding. > > Since I have to do this every day and there are thousands of records, I > was > looking for the easiest (most automated) way possible. Doing what I suggested can be done by macro or VBA in ACCESS automatically, but glad you found a solution that works. -- Ken Snell <MS ACCESS MVP>

Re: Type Conversion Failure with dates Judy Ward 19.08.2006 17:58:02 If you happen to check back, I still have a question about the method you suggested. The data that I am importing has 4000+ rows. What is very odd to me is that a block of these rows (approx 300) has the dates stored as text. The rest of the rows have the date stored as date. So for each of the date columns, I have a mixture of dates stored as dates and dates stored as text. If I do the conversion from text to date in Access, what will happen to the values that are already date values? I just realized that I can try this out for myself and see what happens, but if you happen to know I would appreciate your response. Thank you, Judy "Ken Snell (MVP)" wrote: [Quoted Text] > "Judy Ward" <JudyWard[ at ]discussions.microsoft.com> wrote in message > news:414ADA21-C5CB-404B-8AF9-35E8D9A81E1A[ at ]microsoft.com... > > Thank you very much for responding. > > > > Since I have to do this every day and there are thousands of records, I > > was > > looking for the easiest (most automated) way possible. > > Doing what I suggested can be done by macro or VBA in ACCESS automatically, > but glad you found a solution that works. > -- > > Ken Snell > <MS ACCESS MVP> > > >

Re: Type Conversion Failure with dates "Ken Snell \(MVP\)" <kthsneisllis9[ at ]ncoomcastt.renaetl> 19.08.2006 18:40:29 My experience is that ACCESS will import the "date-formatted" dates in the string format corresponding to your Regional Settings for date display. Then, the append query will need to do a proper conversion from the date string to a "date" value. If you're in US, no conversion will be needed. But if you're in a country that uses dd/mm/yyyy format then you'll need to parse the string to get the correct value for the date in the append query -- and this is best done using the DateSerial function with the arguments containing use of Left, Mid, Right, etc. string parsing functions. Not difficult once you set it up. -- Ken Snell <MS ACCESS MVP> "Judy Ward" <JudyWard[ at ]discussions.microsoft.com> wrote in message news:02BE2903-3FC2-49BB-A0AF-442D64AA1569[ at ]microsoft.com... [Quoted Text] > If you happen to check back, I still have a question about the method you > suggested. The data that I am importing has 4000+ rows. What is very odd > to > me is that a block of these rows (approx 300) has the dates stored as > text. > The rest of the rows have the date stored as date. So for each of the > date > columns, I have a mixture of dates stored as dates and dates stored as > text. > > If I do the conversion from text to date in Access, what will happen to > the > values that are already date values? I just realized that I can try this > out > for myself and see what happens, but if you happen to know I would > appreciate > your response. > > Thank you, > Judy > > "Ken Snell (MVP)" wrote: > >> "Judy Ward" <JudyWard[ at ]discussions.microsoft.com> wrote in message >> news:414ADA21-C5CB-404B-8AF9-35E8D9A81E1A[ at ]microsoft.com... >> > Thank you very much for responding. >> > >> > Since I have to do this every day and there are thousands of records, I >> > was >> > looking for the easiest (most automated) way possible. >> >> Doing what I suggested can be done by macro or VBA in ACCESS >> automatically, >> but glad you found a solution that works. >> -- >> >> Ken Snell >> <MS ACCESS MVP> >> >> >>