Comparison with wrong results

[khurram7x]

Ok, this is how I made it work. Pasting, but also attaching screenshot with an error message.
link to previous thread on this: http://www.access-programmers.co.uk/forums/showthread.php?t=287026

I'm now able to put date/time in database in correct format. Now when I try to confirm that program does not enter duplicate values, it comes up with strange results!! Either, it doesn't add duplicate values because criteria is matched, OR, it gives me an error message telling 'Data Type Dismatch...', OR, it enters all duplicate values in the table. 'Data Type Dismatch...' error sometimes disappears for few minutes once i restart the database.

I'm scratching my head over this now and spent my full day to solve this, but can't. Could someone please help by looking at the code and tell what am I doing wrong!!

Thanks,
K

Dim i As Integer
Dim strDate As String '**This variable is used to convert date into proper string format, against the required date format we need for the database
Dim tmp As Date

Debug.Print DLast("format(Assignment_Date, 'long date')", "tblEmployee_WorkPack", "EmployeeID = " & Me.EmployeeID)
Debug.Print DateValue(Me.txtFromDate)

tmp = Format(Me.txtFromDate, "yyyy-mm-dd")
strDate = tmp & " 06:30:00 AM"

If IsNull(DLookup("EmployeeID", "tblEmployee_WorkPack", "DateValue(Assignment_Date) = DateValue(#" & Me.txtFromDate & "#) And EmployeeID = " & Me.EmployeeID)) Then
Me.Assignment_Date = Me.txtFromDate

'**This saves values in SignIn time/date as soon as Supervisor is assigned to a job for a specific day
CurrentDb.Execute "INSERT INTO tblEmp_Clocked_Time (EmployeeID, SignedIN) " & _
"VALUES(" & Me.EmployeeID & ", '" & strDate & "') "

DoCmd.RunCommand acCmdSaveRecord

End If

For i = 1 To DateDiff("d", Me.txtFromDate, Me.txtToDate)

d = DateValue(DateAdd("d", i, Me.txtFromDate))
'Debug.Print d
'Debug.Print DLookup("assignment_Date", "tblEmployee_WorkPack", "EmployeeID = " & Me.EmployeeID)
strDate = d & " 06:30:00 AM"

'**This compares if record is already present for the specified Supervisor on specific Date. If not, it continues by adding data
If IsNull(DLookup("EmployeeID", "tblEmployee_WorkPack", "DateValue(Assignment_Date) = DateValue(DateAdd('d', " & i & ", #" & Me.txtFromDate & "#)) And EmployeeID = " & Me.EmployeeID)) Then
CurrentDb.Execute "INSERT INTO tblemployee_Workpack ( EmployeeID, Role_Type, Assignment_Date, Working_Hours, Lost_Hours, Rest_Hours, Notes) " & _
"VALUES(" & Me.EmployeeID & ", " & Me.Role_Type & ", '" & strDate & "', " & _
"" & Me.Working_Hours & ", " & Me.Lost_Hours & ", " & Me.Rest_Hours & ", Nz('" & Me.Notes & "', '" & vbNullString & "'))"

'**This saves values in SignIn time/date as soon as Supervisor is assigned to a job for a specific day
CurrentDb.Execute "INSERT INTO tblEmp_Clocked_Time (EmployeeID, SignedIN) " & _
"VALUES(" & Me.EmployeeID & ", '" & strDate & "')"
End If

Next i

 

[CJ_London]

don't get hung up on what a date looks like (i.e. its format). Date is a number - today (31st May 2016) is 42521, tomorrow is 42522. What it looks like is just a special format

anything that looks like 31/05/2016 or 05/31/2016 or 31 May 16 are just different views of the same number. If the format property of the field or control is set, the underlying value is still a number. However if you use the format function (as you have) you convert the number into a string

I don't have time to interpret your code - please represent using the code tags (the # button in the advanced editor) to preserve the indenting.

 

[khurram7x]

Thank CJ, I’ve understood and changed. Now I can’t prevent duplicate values sneaking into the database table tblEmployee_WorkPack and tblEmp_Clocked_Time. I’ve applied the correct logic but DLookUp doesn’t seem to work fine and returning NULL, and failing the condition everytime. Everytime which is basically a condition to be met if similar record is already present in the table.


If IsNull(DLookup("EmployeeID", "tblEmployee_WorkPack", "DateValue(Assignment_Date) = DateValue(#" & Me.Assignment_Date & "#) And EmployeeID = " & [Forms]![frmEmployee_WorkPack_Supervisors]![EmployeeID])) Then
…
I’ve both Me.(object name) and [Forms]!... full form path to get the job done but it is not.


Apparently, DateValue(Assignment_Date) is a problem but it seems like DLookUp is not reading ‘Assignment_Date’ value from the table at all, thus causing to fail the condition every time and enter duplicated records.
Then I need dates to be saved in European format in the table, or if not, shall I use ‘Format’ function every time when I need to display dates in specific format. Access is not good in picking up regional setting well enough?
I’m in desperate need for this solution, especially the first part of the problem, because I couldn’t proceed further without it.


I’m uploading stripped version of the database for better understanding. ‘frmSwitchBoard’ should be opened at first, and then ‘Assign Supervisor’ button. Select Supervisor name and required dates to assign him to the work for selected days. This is basically a part of my requirement. Pressing the ‘Save’ button will save the record and this is where the problematic code is!!


After assigning Supervisor(s) to the job for today, or if he’s already signed, ‘Daily Reporting & Assignments’ form could be opened. But currently it is not opening as well because again there is ‘DLookUp’ with almost same issue as above.


Any help will be highly appreciated.


Thanks

 

[CJ_London]

datevalue converts a string into a date, so if assignmentdate is already a date you don't need to convert it and will get a datatype mismatch error. me.assignmentdate on the other hand is most likely a string (assuming the control is unbound) so you either use datevalue to convert it to a date OR you use the #'s, but ensure date is in US format (mm/dd/yyyy)

 

[khurram7x]

datevalue converts a string into a date, so if assignmentdate is already a date you don't need to convert it and will get a datatype mismatch error. me.assignmentdate on the other hand is most likely a string (assuming the control is unbound) so you either use datevalue to convert it to a date OR you use the #'s, but ensure date is in US format (mm/dd/yyyy)

Really thankful CJ, at least able to work in US format. Access dates are really a trouble to work with. How am I gonna deal with other date formats please? I am gonna use the same program to other regions as well.

However, I still have to use DateValue otherwise time value will not let the date comparison pass the condition.

Thanks,
K

 

[CJ_London]

How am I gonna deal with other date formats please?

not sure what you mean - a date is a date, a numeric value per my original post - makes no difference whether you are in US, UK, EU or anywhere else in the world the number for any given day is the same - it is just formatted differently. Formatting is just what it looks like to the user.

Just remember, if you use the format property (for a field or control) this does not affect the underlying value, if you use the format function, you change the date to a string (which is then not a date), but you can use the datevalue function to change it back again.

Suggest you google 'access vba date functions' to find out about all the different functions used with dates