Buggy use of dates in Temp tables

[Antti-L]

On my new Windows 7 64-bit laptop, I dared to test using the Finnish date format dd.mm.yyyy., while still using the the VEDA compatible numeric formats (dot as the decimal symbol and comma as the digit grouping and list separator symbol).

But it does not work with VEDA-BE: When trying to use the ExRes feature, VEDA displays errors and the resulting ExRes tables are always empty. This seems to be due to VEDA-BE stuffing the date separators into the temporary table name!

I must say that I am surprised that VEDA-BE cannot even cope with different date formats. Is it really necessary to stuff the system date separators into the temporary table names?  I think there must be many built-in routines to get the system date in numeric format (e.g. three integers representing day, month and year), instead of a string that VEDA will not be able to interpret. Therefore, I don't really understand why VEDA insists on using the string representation of dates and thereby includes the date separator characters in the temporary table names, because that will lead to errors.

[AKanudia]

We use the following function to construct temp database names:

"View_Temp_" & Format(FormatDateTime(Now, vbGeneralDate), "DDMMYY_HHmmSS") & ".MDB"

Please tell me what do you see as the database name so that we can explore alternatives.

[Antti-L]

Amit,

If I make a new Table, VEDA-BE offers a table name that contains the date separators, and if I continue, it complains that the Table name is invalid.  This is annoying (because it happens every time with temp tables), but maybe one could live with that. After removing the dots from the name it works. But I would be grateful if you could automatically remove the date separators from the date.



But when invoking ExRes (e.g. for the commodity ELCC) VEDA shows the following error message:



As you can see, it seems that VEDA-BE has used the date string in the Query, and it fails.
I cannot see any *.mdb files created in the database folder for the ExRes tables, and so I cannot answer your question about the database name.

After this error, I also get the follwing subsequent error messages:





Finally, the resulting ExRes table shows up and it is empty.

What might be the root problem here? Possibly it is not related to the date separators, even though at first it very much seemed like that?

Thanks,
Antti

[AKanudia]

Sorry, I misunderstood the issue.

See if this beta exe resolves this problem:

Veda_BE487421.zip

[Antti-L]

No, I am still getting the same error:

Version: 4.8.7421
9.3.2011 12:59:56
Error in procedure codeSmart//
Source = Microsoft JET Database Engine
Number = -2147217913
Description = Syntax error in date in query expression '#9.3.2011 12:58:50#'.
Error Stack  : ProcessQuery_ExRES_1() -> Stage 6
Veda_BE.modViewCube.ProcessQuery_ExRes_1 [548]

As you can see, there is a Syntax error in the Date in the Query expression. Is that caused by the dots, or what? Is there any way to make the syntax correct?

[Antti-L]

Amit: 

I stumbled across the following interesting page:

http://classicasp.aspfaq.com/date-time-routines-manipulation/how-do-i-delimit/format-dates-for-database-entry.html

According to the information given there, the only safe format to be used with Access queries is #2004-07-20#. This is also the ISO standard. So, would it be possible to use this format internally in VBE, for example in the ExRes Query expression that failed?

[AKanudia]

Thanks for "stumbling"

Will fix soon.

[AKanudia]

Veda_BE487422.zip

does this help?

[Antti-L]

Yes it does!

Many thanks Amit!  I look forward to getting the new production version, but will stick to the Beta until that if it seems stable.

Antti

[AKanudia]

Great! Thanks a lot for discovering the bug and leading us to a robust solution.

This version should work at least as well as the current production one. I have not released it simply because I have not felt the urgency to document the new features that I have added on my own. I have been using it quite intensively. There have been a few bug fixes as well but they come into play only when using non-TIMES/MARKAL flavors.

[Kannan]

Amit

I had the same problem, but with BE487422, now it is solved

thanks

kannan