Strange situation
In query i have CDate(Format([p1ok];"00000000")) - which should convert string from p1ok column to date . It is working on 2016 Access.
When I copied to access 2010 is not working and is showing error in this column
I agree with CJ - we need to see typical contents of p1ok to understand this. Just for snorts & giggles, current dates will be floating-point numbers for which the integer parts are in the low- to mid-40K range because it has been about 43,300+ days since the Access reference date.
Note also that if your eight-digit number formats to something that looks like 20180826 (today's date), that is NOT a valid input for CDate. In fact, ANY date without delimiters that includes month, day, and year will be an invalid date that will either cause a data type mismatch or an overflow. (I've tried it multiple times using Debug.Print on various possible numbers in string and integer formats.)
Not unless p1ok is the number of days since the reference date. As stated earlier, we need to see some values. Is p1ok originally derived from a date field that got converted to a LONG or DOUBLE? Because if not, that format specification of "00000000" will never produce anything that CDate would like.
The largest integer or double you can supply to CDate() is 2958465, which if you formatted as a date would read back 31-Dec-9999, the largest date you can express in Access. An 8-digit number would NEVER convert to a date in ANY version of Access if it exceeded that integer value.
Basiclly column p1 is long text "Test End: 20.08.2018". P1ok using middle function to cut this text . After cut I have 20.08.2018. And this is imput for CDate
If you format "20.08.2018" using "00000000" in hopes of getting 20082018, that result is an integer greater than the maximum number of days that a date-oriented field can manage. Without the delimiters, that is going to look like very large number.
I can believe that this doesn't work in Ac2010. I've tried at least six or seven variant formats, but nothing much seems to work. In fact, when I isolated the format statement, it seemed to have no effect:
An interesting thought, Mahmood. Might actually work, though I think it will still depend on the Windows Regional settings for date format. But a good shot.
Hello,
For confirmation I am sending pic with query settings and output.
I am not sure why but in all query formula i need use ";" if I am using , I have fault
On my personal laptop where I have Access2016 is working . When I copy database on pendrive and after on business laptop where i have Access2010 it is casing problem.
I have tried several experiments. This makes absolutely no sense.
First, I looked for some kind of "Options" setting that would allow me to make the semi-colon my function-argument delimiter. There is no such setting.
Then I worked on the theory that since the Format function didn't change the value of the string that had the dots in it, maybe CDate would also leave it unchanged. But all it did was either tell me "Type Mismatch" or "Expression expected" depending on which style I tried for the conversions.
I am unable to trace or to duplicate your problem. I feel, however, that we lack crucial information in order to diagnose this problem and I don't even know at this time what question to ask.
If the error occurs when you use a character to separate the arguments you expect to work and Excel will not accept it then this is typically caused by either or both of the following scenarios:
The list separator in Windows - Regional Settings does not match what is being typed for the Excel formula.
The 'Use system separators' option is set in Excel Advanced Options and does not match what is being typed for the Excel formula.
MajP - Is that an Excel list separator option? That must be why I missed it. I was only looking for Access separators.
Where is that found for Access? Because even knowing what to call it, I can't seem to find it as an Access 2010 option, so I'm at a loss to see how the OP managed to get it to work in Access with an invalid list separator.
No that is the windows regional settings. It is in the additional settings tab. I do not have access on this computer to see it there is also a use system separator option. I updated the image to show where it is in control panel.
Interesting. Never tried THAT one before. Next question: Does the date/time setting allow you to specify "." as the date separator? It would go a LONG way to explaining what is going on - and might answer the original question. The OP would have to visit the regional settings for Windows on the systems with Ac2010 and Ac2016 to see if they are set the same way.