Date Duplication

[sysop470]

I have made this command to prevent duplicate dates, but sometime it does not work. Can someone help me why? Thanks

If DCount("*", "LEAVE", "[Date Taken Leave]=#" & Me![Date Taken Leave] & "#") Then etc etc

Thanks a lot

 

[RuralGuy]

Are you making sure there is no time component to your stored dates?
http://support.microsoft.com/default.aspx/kb/q130514/

 

[missinglinq]

As Allan has alluded to, people sometimes use Now() to automatically generate dates and this includes times as well as dates and won't cause a match if compared to a date only field.

 

[sysop470]

As Allan has alluded to, people sometimes use Now() to automatically generate dates and this includes times as well as dates and won't cause a match if compared to a date only field.

Date is entered manually not using now()

 

[MStef]

If Dcount...................> 0 then .....
I think you have to ask if Dcount > 0.

 

[RuralGuy]

MStef, DCount returns a numerical value and any non 0 value will be taken as a True.
Sysop470, Have you examined your field in the table for invalid values?

 

[sysop470]

MStef, DCount returns a numerical value and any non 0 value will be taken as a True.
Sysop470, Have you examined your field in the table for invalid values?

There are no invalid values, all dates are correct. Do you know other alternative to check duplicates?

Thanks a lot

 

[RuralGuy]

I take it you are working from a form. Which event? Why not just do a FindFirst on the RecordsetClone of the forms' RecordSource?

 

[sysop470]

I take it you are working from a form. Which event? Why not just do a FindFirst on the RecordsetClone of the forms' RecordSource?

Yes, i am working in a form, BEFORE UPDATE event. As soon as i entered the date, it will check for duplicates.....

I don't know how to do it Findfirst etc etc as you mentioned.

Thanks a lot

 

[gemma-the-husky]

why do you think it doesnt work?

is it giving false when you expect true, or vice versa?

can you see anything untoward with the data?

does it happen often? - if so while debugging, you could get it to display the query result, not just do the dcount.

also, doesnt the leave depend on the employee as well - not just the date - that would imply only one person can be absent on that date.

 

[sysop470]

why do you think it doesnt work?

is it giving false when you expect true, or vice versa?

can you see anything untoward with the data?

does it happen often? - if so while debugging, you could get it to display the query result, not just do the dcount.

also, doesnt the leave depend on the employee as well - not just the date - that would imply only one person can be absent on that date.

The strange think is that there are dates that are picked as duplicates and others no, even if the date exists in the table.

The date depends on each employee of course yes...
Example: John Hings date of leave: 01/10/2009
if by mistake i enter same date on only John Hings, a duplicate message will appear.

thanks a lot

 

[RuralGuy]

Yes, i am working in a form, BEFORE UPDATE event. As soon as i entered the date, it will check for duplicates.....

I don't know how to do it Findfirst etc etc as you mentioned.

Thanks a lot

Assuming you have a reference to the DAO library then all it takes is:

Me.RecordsetClone.FindFirst ""[Date Taken Leave]=" & Me![Date Taken Leave] 
If Not Me.RecordsetClone.NoMatch Then
    '--- There is already a match
    Cancel = True
    MsgBox "Already have this date"
End If

Is your code in the BeforeUpdate event of the FORM or a CONTROL?

 

[sysop470]

Assuming you have a reference to the DAO library then all it takes is:

Me.RecordsetClone.FindFirst ""[Date Taken Leave]=" & Me![Date Taken Leave] 
If Not Me.RecordsetClone.NoMatch Then
    '--- There is already a match
    Cancel = True
    MsgBox "Already have this date"
End If

Is your code in the BeforeUpdate event of the FORM or a CONTROL?

BeforeUpdate event of the FORM ..

 

[RuralGuy]

The [Date Taken Leave] field *IS* a DateTime field, right?

 

[sysop470]

The [Date Taken Leave] field *IS* a DateTime field, right?

Yes of Course.. i don't know why its not working, something strange.

 

[RuralGuy]

Have you decreased the possibility of corruption being the culprit by importing your db into a new db and see if the problem follows the import?

 

[gemma-the-husky]

just to repeat

If DCount("*", "LEAVE", "[Date Taken Leave]=#" & Me![Date Taken Leave] & "#") Then etc etc

this will not work correctly on its own, since it does not include an emloyee/meember number AS WELL as the date being tested. So you probably need to be a lot clearer about why you thing this isnt working.

Is LEAVE a query or a table? what fields are in the table? - or can we see the query?

 

[sysop470]

Have you decreased the possibility of corruption being the culprit by importing your db into a new db and see if the problem follows the import?

I have tried it also, no corruption

I have noticed that from 02/01/2010 to 12/01/2010 accepts duplicate and
from 13/01/2010 - 31-01-2010, duplicates are picked.
Also noticed if you make a reverse order from 02-01-2010 till 12/01/2010 they are picked as duplicate even they are not in the table.

Example: if i have this date 02/01/2010 and enter 01/02/2010 it says duplicate, it happens all till 12-01-2010. May be this helps because i can't find a solution for it.

Thanks a lot

 

[gemma-the-husky]

I have tried it also, no corruption

I have noticed that from 02/01/2010 to 12/01/2010 accepts duplicate and
from 13/01/2010 - 31-01-2010, duplicates are picked.
Also noticed if you make a reverse order from 02-01-2010 till 12/01/2010 they are picked as duplicate even they are not in the table.

Example: if i have this date 02/01/2010 and enter 01/02/2010 it says duplicate, it happens all till 12-01-2010. May be this helps because i can't find a solution for it.

Thanks a lot

in that case, its to do with regional date formats

In a lot of cases, if there is ambiguity, Access tries to use US dates

so a date 13/01/2010 to 31/01/2010 is an unambiguous UK date

a date of 12/01/2010 can be interpreted by access as a US date - in which the month comes first - so its Dec 1st 2010

----------
to avoid this you have to force access to treat the date in the format you want

eg format(somedate,"dd/mm/yyyy")

 

[RuralGuy]

Dave is right on the money here. Here's a link by MVP Allen Browne and it will still run faster as the FindFirst code I supplied.