Solved Date conversion problem

[PaquettePaul]

I am converting data from an Excel table into access, and then going through the non normalized data and inserting data into proper tables. My problem is that a date is not converting properly.

The date is stored in the conversion table as a date - 05-15-21.
i include as a record set field “rs!InvoiceDate” in a comma separated VALUES list in my INSERT INTO statement.
The msg box of the text sql statement shows it as ”…, 5/15/2021, …
When I look at the inserted record in the Event table, the date is shown as 1899-12-30, and when clicked on, it shows the time as “12:00:14 AM”.

please, what do I have to change to fix this? I tried transferring the converted date to another date field before preparing the SQL text, but still the same.

 

[PaquettePaul]

Also, what do you do when a field data is null when it goes in the SQL statement. The SQL statement just shows “…, ,…”

 

[plog]

Why VBA? Why not use the import tool in Access?

That's gonna throw an error table where you can check for values that didn't make it. Most often, and I bet in your case, to get around data type errors I import that specific field as text, then I run a query to find the values in that field that fail IsDate(). I then either remove them or correct them. Once done I can then convert that field to the correct type.

 

[MarkK]

Somewhere--probably in the SQL--you are evaluating 5 / 15 / 2021 as two division problems, which equals 0.00016493485073396. Then you are converting it to a date, for instance, if you do this in the immediate pane....

? format(0.00016493485073396, "mm/dd/yyyy hh:nn:ss")

.... you get ....

12/30/1899 00:00:14

This is what is going wrong, and why you get that result. It's just a matter now of where this is happening.
Cheers,

 

[Pat Hartman]

Are you importing the spreadsheet into an existing table or are you making a new table with the import? If you put the imported file into design view, what is the data type of the date field?

 

[arnelgp]

you need to enclosed your "date" inside "#".

"insert into table ([dateField]) values (" & Format$(rs!InvoiceDate, "\#mm\/dd\/yyyy\#") & ")"

 

[PaquettePaul]

Thank you. The last one should work.
my Latest problem is that I have a number such as 1.5 in excel, and when loaded into access, it turns it into 1.499999999
my data contains many 1 and 2 decimal place number that are getting screwed up. I am tempted tto multiply the source by 100, transfer it over and reverse the process but I am not sure even that would work.

aargh, the conversion errors are going to be the death of me.

 

[The_Doc_Man]

OK, you got a solution for the DATE problem, just the syntax of dates when in nn-mm-yyyy format needing the # sign.

Now there is a problem with your expectation here for the number conversion. It is possible that Excel stored that number in a character format, the digits "1", ".", and "5" - and we generally don't get to know what it actually does because Microsoft doesn't publish internal code. But converting from an Excel number string to an Access internal number, either SINGLE or DOUBLE, runs into the fact that on a binary machine, a decimal fraction doesn't always come out even. I'll admit that 1.5 SHOULD be one of the rare cases that actually DOES come out even both in decimal and binary, but number conversion is a bug-a-boo in any language. In any case, look to the ROUND function.

Round function (Visual Basic for Applications)

Office VBA reference topic

docs.microsoft.com

Read the description carefully because the specific type of rounding doesn't work for everyone. It's called "banker's rounding" and it tends to actually round away from 0.5 when it finds it.

Doing the rounding by 100 first might not help because when you go back and divide by 100 to finish the conversion, that division step might just reintroduce your error.

 

[PaquettePaul]

Well duh!
my problem was that I needed to put the date in quotes.

 

[PaquettePaul]

You guys are great. Thanks

 

[The_Doc_Man]

There is a moral to this story... some problems are simpler than others.

 

[PaquettePaul]

For Plog, I did revery to massaging the data in excel for most of the data. However, there were a few tables that needed programming decisions.

 

[Pat Hartman]

my problem was that I needed to put the date in quotes.

Dates are not delimited by quotes. Dates are delimited by #'s because dates are NOT strings no matter what they look like in excel or your table. They are double precision numbers. Access uses an origin date of 12/30/1899. I think Excel's is different but the concept is the same. The zero date in Access = 12/30/1899. The -1 date is 12/29/1899 the +1 date is 1/1/1900, etc. I think you ran into the same problem I ran into. Access and Excel not seeing eye-to-eye on a date format. Make certain that you date fields are not defined as short text now that they are in Access.

As to the 1.49999 problem, go back to the spreadsheet and change the number of decimal places for the number fields where you are seeing the problem. Increase to 4 or 5 decimal places. If you are seeing significant digits past 2, they are probably the result of rounding and you want to remove them by rounding to two decimal places when you import the data. OR, you can try importing the numbers as strings the same way you imported the dates and convert them to their proper data type after import.

 

[PaquettePaul]

Yes, I did change from single quote to octothorps (#] and dates are working fine. With regard to extended decimal places for some values, it appears that it only occurs where the original value was calculated in Excel (e.g., 854.7 - 832.4] rather than entered as a value with a single decimal place. While a little annoying, it was easier to spend the 5 minutes to change the values in the uploaded access records. I checked the source for those fields in Excel and they did not have the extended decimals.

Setting the date using Date() works well although I noticed that if i formatted the date when setting to yyyy-mm-did (Canadian format). I had to use that format statement on the entered date as well to check the entered date against the today date. Whether that is right and proper, no big deal as it is just one line of code.

With the exception of the financial entries which require some cross entries, I arrange and massage all my data in Excel and just upload the data as is to a new table replacing the old table.

Again, thanks for the counter thoughts and ideas.

 

[Pat Hartman]

it was easier to spend the 5 minutes to change the values in the uploaded access records

If your table datatypes are single or double, you will have exactly the same problem in Access. The best solution unless you need more than four decimal places is to use the currency data type (not to be confused with the currency format). This data type is a scaled integer. So the decimal point is always assumed to be four from the right. For example
12.34 is stored as
123400

See the "When Access math doesn't add up" article at www.fmsinc.com for an explanation of floating point errors.

I checked the source for those fields in Excel and they did not have the extended decimals.

Did you change the format to show extended decimals? If the data is being imported into Access, it is there in the spreadsheet and you have some formatting defined which is hiding it from you. Access didn't make up the values of its own accord.

NEVER format your date data Except in form/report controls (and only if you have to) OR by using the Format() function if you are exporting data to excel or word where you want to control the format to remove time OR by using the Format() function if you are creating SQL strings in VBA AND your default system date format is not mm/dd/yyyy. The European dd/mm/yyyy format and US mm/dd/yyyy are ambiguous when the day value is 1-12 and SQL Server assumes US date format so 12/1/2022 = Dec 1st, Not Jan 12th.

And especially NEVER format data at the table level. The formatting does NOT change the value. It merely hides it from you causing many hours of unnecessary debugging. Formatting is a human thing. People like their data formatted. Computers like their data naked. Do not apply formats at the table level except for (percent) That one doesn't hid anything but it does help with the decimal shift.

Most people end up using short date format in controls because they have made the mistake of using Now() rather than Date() and ended up with time in their date field and rather than fixing the problem, they hide it. Changing the format to short date to hide the time doesn't make the time go away and it will bite you if you are not careful so always use Date() unless you specifically want to include TOD in your date data.

 

[PaquettePaul]

with regard to importing data, I import the data as is into a new table definition with the desired field names. After the load, I went and modified the field settings to good values such as single decimal places. I guess I could go back and change the Excel format to currency but that would still give me more decimal place than I want. As stated, the problem is annoying but not so much that I cannot fix it manually. I do have the fields defined with one decimal place but I still get the extended decimals on one column where have the data is calcultd versus entered.

With regard to dates, I use the Canadian format of yyyy-mm-dd which is different from both the US and European standard. I could go back and check if I needed to do that formatting in the first place although i intend to do only one more conversion. I did follow the group’s instructions of using Date() to set current date when I add new records through the app - that suggestion was very helpful.

I have finished the data entry forms for the first subject area and and now working on detail and statistical reports.

 

[Pat Hartman]

As I said, FORMATTING does not change the actual value of a field which is why it is a bad idea to use formats on your table. If you use a format on the table itself, you can never be sure you are seeing the actual value. Using currency instead of single or double precision will eliminate the silly floating point issues that occur when you add 1+ 1 and the answer is not 2 but 2.0000000000007. You would only ever discover this problem if you had a need to compare one calculated numerical value to another. The only way to actually have ONE decimal place is to round the result when you do the calculation. Otherwise you get what you get and you should not just hide it. If you want to show only a single decimal place on a form, that's fine. It is the table that is the master when it comes to what data is actually being stored. THAT is what I am talking about. Debugging is impossible when you can't see the actual value stored in a table.

And again, dates are not strings. They are not stored as strings. They are stored as double precision numbers. Formatting a date is for human consumption. Access automatically formats dates based on your Windows defaults. In the table, leave it at that. You want to see any time values that are there. For a form, do whatever formatting you want.