Cant figure error with calendar issue

[gint32]

Hi Everyone,

I am attempting to build an Overtime database in access 2010 and I am about half way through it as I still ahve to sort out the security side of it yet (keeping a log of all changes), The thing is I've been really struggling with my limited knowledge of Access (for days now really) to figure out an issue I have with my SQL UPDATES and INSERT, Essentially my issue is the logged on user enters an/or there own HR-Id, then they are presented with a form that presents an interactive calendar, simply put ..if the users double clicks any date they can then book(eg ..show they are willing to work a shift on that date and the colour of the calendar will reflect this), the calendar is set up where each time they click a date it'll change colour representing a Day, night or willing to both night and day.

So far code wise my VBA/SQl seems to work fine accept for a just few of certain dates in the calendar (such as January 2-12th), which is the part that is driving me nuts as I can't figure out why it behaves this way, So if I choose Jan 2nd through 7th it'll give me a message saying about to update (0) rows,and I have no clue as to why as all other dates work fine..Can anyone throw some light as to why this is happening

I'd pull my hair out (but aint got any left unfortunately.)
Please see the attached cut down version for test purposes

Many thanks Gerry From Australia

[isladogs]

I haven't looked at your database but the clue is in the dates Jan 1st-12th.
You will have the same problem for the first 12 days of each month ....

For SQL, dates must be in US format mm/dd/yyyy

If you have a date in dd/mm/yyyy format such as 03/01/2018 , it will be wrongly interpreted as 3rd March etc
However, 13/01/2018 can't be anything other than 13 Jan so Access gets that right!

So you get some correct dates & other incorrect dates !!!

So change your SQL code to something like Format(MyDateField, "mm/dd/yyyy")

[gint32]

I haven't looked at your database but the clue is in the dates Jan 1st-12th.
You will have the same problem for the first 12 days of each month ....

For SQL, dates must be in US format mm/dd/yyyy

If you have a date in dd/mm/yyyy format such as 03/01/2018 , it will be wrongly interpreted as 3rd March etc
However, 13/01/2018 can't be anything other than 13 Jan so Access gets that right!

So you get some correct dates & other incorrect dates !!!

So change your SQL code to something like Format(MyDateField, "mm/dd/yyyy")

Thanks tried as you suggest but still get weird results whether I change all to Format(TDate, "mm/dd/yy") or even find and replace all with Format(TDate, "dd/mm/yy")

Still doesn't work ,but thanks anyways for taking the time to look

[Minty]

I had a quick look - you have some fixing to do. This line

Dim TDate, LstAmdDte As Date, C1 As Integer, strSql As String, TypeAttend, RecDetect, PrevVal_TypeAttend

will not do what you think.

This will declare TDate, TypeAttend, RecDetect, PrevVal_TypeAttend as Variants.

What you should have done is something like

Dim TDate as Date 
Dim LstAmdDte As Date
Dim C1 As Integer, 
Dim strSql As String, TypeAttend as String , RecDetect as String, PrevVal_TypeAttend as String

You have to explicitly declare the type.
Fix all that first then try compiling the database and fix those issues.
Then repost the fixed version and I suspect some of your issues will be solved.

[isladogs]

Removed post - forgot you had already posted the db

[gint32]

I had a quick look - you have some fixing to do. This line

Dim TDate, LstAmdDte As Date, C1 As Integer, strSql As String, TypeAttend, RecDetect, PrevVal_TypeAttend

will not do what you think.

This will declare TDate, TypeAttend, RecDetect, PrevVal_TypeAttend as Variants.

What you should have done is something like

Dim TDate as Date 
Dim LstAmdDte As Date
Dim C1 As Integer, 
Dim strSql As String, TypeAttend as String , RecDetect as String, PrevVal_TypeAttend as String

You have to explicitly declare the type.
Fix all that first then try compiling the database and fix those issues.
Then repost the fixed version and I suspect some of your issues will be solved.

Thanks. I Finally found the SQL issue and its was as you said the needed USA Date format...I wasn't aware that sql will only insert.update if we use USA format(only) dates are a really common issue and I should have known(you'd think MSwould sort this out so as they are not an issue in the future wouldn't you ,considering how much of a pain it is and the amount of revenue they get each month) , So all credit to you for pointing me in the right area.
I added amended

TDate = Format(TDate, "mm/dd/yy")

above the sql insert and it worked a treat...I so happy and relieved many thanks for your suggestions

TDate = Format(TDate, "mm/dd/yy")

MsgBox " this will run an Update because a records already exists in AttendType " & vbNewLine & " so it'll increment"
 strSql = "UPDATE Attend SET"
 strSql = strSql & " Attend.AttType = " & TypeAttend & ","
 strSql = strSql & " Attend.LoggedOnUser = '" & Me![txtUserId] & "',"
  strSql = strSql & " Attend.LastAmendedDate = #" & Format(Now, "mm/dd/yy") & "#,"
  strSql = strSql & " PrevVal_TypeAttend =  " & PrevVal_TypeAttend
  strSql = strSql & " WHERE Attend.AttStudent = " & Me![scrStudent] & " AND"
  strSql = strSql & " Attend.AttDate = #" & TDate & "#;"

[Missinglinq]

The grandfather of all Access developers, Allen Browne, also from 'down under,' (Perth, I believe) has an excellent article on this subject:

International Dates in Access

Linq ;0)>