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,

 

[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.

 

[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.

 

[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.