Using Year

[kirkm]

Just wondering what the story is with this. I've a date variable which is #11/21/2009#. If I hover over it, the popup shows 21/11/2009. I expect MM and DD can be reversed but would have expected to see it formatted properly. For example without the # Access prints 21 / 11 / 2009 which is something else, yes?

That aside the variable name is inDate.
If I print Day(inDate) it's 21, Month(inDate) is 11.



Why does Year (inDate) give "type Mismatch' error?

I want an IF clause to kick in where the year is > 2009. I could change to strings, but is not using Date variables or methods more 'correct' ?

 

[June7]

MM/DD/YYYY is U.S. date structure and Access standard. If you see DD/MM/YYYY then possibly you have computer regional settings set for non-U.S. structure. If not, then something else is going on. Review http://allenbrowne.com/ser-36.html

11/21/2009 and 21/11/2009 refer to the same date and Access can understand this because there is no month 21. However, 4/7/2009 and 7/4/2009 would cause confusion.

 

[kirkm]

Hi June, I don't think this is so much a US-Non US date issue as the Year is the same for both. (and it's Year I'm asking about).

 

[JHB]

..
11/12/2009 and 21/11/2009 refer to the same date..

Sorry - I do not agree.

 

[theDBguy]

Hi kirkm. Just to be sure we’re all on the same page, can you please post your code where you declared the variable as a date and the one where you assigned a value to it? Thanks.

 

[kirkm]

Hi again DBGuy

Dim InDate As Date, wk As Integer
Dim item As Variant, wc

With Form_BBsubform
For Each item In Split(.Controls("DSpan"), ",")
wc = wc + 1
InDate = DateAdd("ww", wc, CDate(Form_BBsubform.Controls("Date Entered")))
Next
End With

The Form is bound to a query-table and the [Date Entered] field in the table is a Date/Time data type.

 

[June7]

@JHB, why would you say those two are not interpreted as the same date?
?CDbl(#21/11/2009#)
40138
?CDbl(#11/21/2009#)
40138

kirkm, where are you extracting the year?

 

[JHB]

@JHB, why would you say those two are not interpreted as the same date?
?CDbl(#21/11/2009#)
40138
?CDbl(#11/21/2009#)
40138

Yes these two are the same, but it was not what you wrote, go back and read your post #2.
You wrote that 11/12/2009 and 21/11/2009 refer to the same date!

 

[June7]

Ooops, typo. Seeing what expected. Corrected. Hope readers understood what I meant regardless.

 

[kirkm]

Yes, June I did although I think Windows knows 4/7/2009 and 7/4/2009 aren't the same.
Doesn't the date *have* to be inside # chars, and anything in #s must be US Format?
But the pop up balloon displays without # and shows dd and mm correctly as per system settings. Which over here is day first.

DBGuy my problem has gone away, Year(DateVariable) now shows the Year and not the error. But it DID play up before. I could get the day and month only. I know it doesn't make sense.

 

[June7]

The popup tip won't show the # characters.

Sounds like issue resolved anyway.

 

[gemma-the-husky]

did you have another function in your programme called year()? If you did, that would override the vba function year.


you need to be sure on how your dates are handled

If you are in the Uk, then a date will be treated as a US date if at all possible.

So if you enter #21/11/2019#, vba knows you mean 21st Nov
However if you enter #12/11/2019# it will treat it as Dec 11th 2019, unless you tell it different - ie that you WANT 12th Nov 2019. This only applies to SQL/VBA.

Once the date is stored it's actually stored as a number (of days since a reference point) - so in the UK, it now knows that you meant 12/11/2019, and in the US it knows you meant 11/12/2019. It's formatting and presenting the stored date in accordance with your local custom.

I hope that makes sense.

 

[theDBguy]

DBGuy my problem has gone away, Year(DateVariable) now shows the Year and not the error. But it DID play up before. I could get the day and month only. I know it doesn't make sense.

Hi. Glad to hear the problem is gone. What did you do to fix it?

 

[kirkm]

Gemma said
.
However if you enter #12/11/2019# it will treat it as Dec 11th 2019, unless you tell it different - ie that you WANT 12th Nov 2019. This only applies to SQL/VBA.

How do you tell it different" ? Do you mean in regional settings or some other way. I like Format "dd mmm yyyy" and all ambiguity is gone.
I hadn't named a variable "year" and it's definition shows it a member of Date Time. I didn't do anything to fix the error, it suddenly worked properly. It was hard to believe, I'd spent a long time testing and looking for a reason before my message here. If it happens again I'll try to catch it. Access can do weird things though. My whole project crashed once and wouldn't do anything without errors. This was Access 2010. I copied the mdb file (I only use mdb for continuity with older stuff) to the Access 2019 machine. It was okay there so i copied it back, and it was okay again on 2010.

 

[gemma-the-husky]

no, not the regional settings. You need it if you use a date directly in a SQL statement, and the best way is to use an unambiguous format statement as you are doing.

because you are using mmm, rather then mm, you will get the month as text, and all ambiguity is removed. If you had mm it would show the month as a number, and it could be ambiguous.