Date Format problem.

[pr2-eugin]

I have to monitor attendance of employees on a week by week basis. I have an (unbound) Form to input the number of hours to be input for agent. The table (AGENT_ATTENDANCE) has fields WK_NO(a week number), WK_Start_Date (probably the date of Monday), 5 individual day columns (represents 5 days of the week), AGENT_ID_fk.

I want to INSERT a new record if the agentID and WeekStartDate is not in the table, if that is available, I need it to UPDATE the specific hours for that specific day..

The problem is if I enter 08/10/2012 (8 -Oct- 2012) it switches the format from dd/mm/yyyy to mm/dd/yyyy on the Update/Insert statement.
* I have set the format of the table field and the control on the form as Short Date (whose format is dd/mm/yyyy).
* I used a variable declared as Date and assigned the control's value into the update query.
* I tried taking off the # symbols in the update Query then it stored the value (whatever the date was) to 30/12/1899
* I converted the # symbol to ' , it stores the value in the right format, but is not able to check/compare the date on a later stage..
my simple code is as follows..

Dim dateVar As Date
dateVar = Me.WeekStDt_TxtBox
myQry = "INSERT INTO AGENT_ATTENDENCE(AGENT_ID_fk, WK_NO, WK_Start_Date) VALUES (88, 1, " & dateVar & ")"
[COLOR=Green]' Other combinations were...
' .... VALUES (88, 1, " & Me.WeekStDt_TxtBox & ")"
' .... VALUES (88, 1, " & CDate(Me.WeekStDt_TxtBox) & ")"
' .... VALUES (88, 1, #" & Me.WeekStDt_TxtBox & "#)"
' .... VALUES (88, 1, '" & Me.WeekStDt_TxtBox & "')"[/COLOR]

This piece of code..

' .... VALUES (88, 1, '" & Me.WeekStDt_TxtBox & "')"

stores the right format, but when I try to do a DMax with this as criteria, it fails..

Nz(DMax("WK_NO", "AGENT_ATTENDENCE", "((AGENT_ID_fk = " & Me.Agent_List.Column(0) & ") AND (WK_Start_Date = " & Me.WeekStDt_TxtBox & " ))"), 0)
[COLOR=Green]'I did similar variations to the above code as I did for INSERT query. But no luck.[/COLOR]

I did similar variations to the above code as I did for INSERT query. But no luck. Any pointers?? I have attached the mdb file..

WALK THROUGH for the form,
1. The Agent is selected from the LIST,
2. Day for which hours need to be added is selected from the Day comboBox,
3. Hours worked is added,
3. Update/Save is selected.

 

[bob fitz]

Perhaps this would help: http://allenbrowne.com/ser-36.html

 

[pr2-eugin]

Hello Bob, thank you for the link, I did go through my problem is described there DataType, However the solution does not seem to be working, as mentioned I have already set the format as Short date, it displays the right format dd/mm/yyyy but when using in the update statement, I have to use it between ## which converts the format as mm/dd/yyyy, If I do not use the # symbols it has 30/12/1899 as the date. If I use it as string stores the right format but makes it hard to compare.

 

[pr2-eugin]

Okay, the work around I found was to convert the Date to Double value and then perform an update, thus it preserved the Format of dd/mm/yyyy, and also was easier for comparing..

CLng(CDate("08/10/2012")) will give  41190 
CDate(41190)  would result in 08/10/2012

 

[RainLover]

Paul

Bob Fitz has given you the information you need. He gave you a link because the subject is too large to post individual answers.

MS Access SQL will require American Format of the Date. Not English or Australian. So don't expect to see English.

As I have said to you earlier, the Tables store neither American or English. It stores Numbers. But in order to run your query in SQL you need American Format.