CDate

[Magnus1982]

Hello,


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





Anybody know why ??

 

[CJ_London]

my guess would be that cdate cannot convert an 8 digit number to a date. Perhaps this is something that works in 2016 but not in 2010

Suggest provide some example values of p1ok which does not work

 

[The_Doc_Man]

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

It is working on 2016 Access.

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.

 

[Magnus1982]

Input in p1ok is exqmple : 20.06.2018

 

[Magnus1982]

Hello.

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

 

[Galaxiom]

DateSerial(Right([p1ok],4), Mid([p1ok],4,2), Left([p1ok],2))

 

[Magnus1982]

sry not working

 

[Galaxiom]

sry not working

That is not a helpful description.
I have corrected the missing closing parenthesis.

Of course you could also do it directly from the Test End string.
However, text is a terrible way to store a date in the first place.

DateSerial is a much more reliable function than going through a Format().

 

[The_Doc_Man]

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.

Using my copy of Access 2010, I tried this:

debug.Print CDate( Format("20.08.2018","00000000"))

Since [p1ok] is the string in question, using the actual string should be the same thing. It doesn't work.

Note also that you supplied a string with a semi-colon as a function argument delimiter, which I believe is not right.

In query i have CDate(Format([p1ok];"00000000"))

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:

debug.Print format("20.08.2018","00000000")
20.08.2018

I suspect that SQL likes this no more than VBA did, because using the semi-colon gave me a syntax error.

Changing the date string to an actual date also gave me an error.

debug.Print cdate( format( #20.08.2018#,"00000000"))

In essence, if this is working in Ac2016, I'm damned if I know how it would.

 

[almahmood]

I agree with The_Doc_Man.

Try this work around:
CDate(REPLACE([p1ok],".","/"))

 

[The_Doc_Man]

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.

 

[Frothingslosh]

In essence, if this is working in Ac2016, I'm damned if I know how it would.

It doesn't.

 

[Magnus1982]

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.

 

[CJ_London]

I am not sure why but in all query formula i need use ";" if I am using , I have fault

it depends in where you are in the world - same with date - some countries use a '/', some use a '.'

 

[The_Doc_Man]

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.

 

[MajP]

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

The setting is the list separator.

For excel

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.

https://support.microsoft.com/en-us...xcel-when-list-separater-is-not-set-correctly

 

[The_Doc_Man]

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.

 

[MajP]

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.

 

[Galaxiom]

Countries that use a comma decimal separator often use a semicolon list separator.

 

[The_Doc_Man]

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.