Code not seeing duplicates 1

[kaiana]

I am trying to get the code to return "Duplicate" if a duplicate is found, it is not working, I have separated the criteria and found that it is the date part that is the problem. Here is my code and it is returning "0" even though there are duplicates in the table.

y = DCount("ParticipantID", "Forums", "ForumDate = #" & Me.ForumDate & "#")

I have been trying to rectify this problem for over a week and would really appreciate it if someone could advise me of where my code is incorrect.

Thanks

 

[RoyVidar]

First - I'm a believer in not using the same names on fields and controls on form. I always prefix controls I'm going to manipulate thru code with something, txt for text controls, cbo for combos...

Second - if your regional settings date format differs from US format, you'll need to format the date to a US recognizable format, for instance:

[tt]= DCount("ParticipantID", "Forums", "ForumDate = #" & format$(Me!txtForumDate,"mm/dd/yyyy") & "#")[/tt]

- after a change of the controls name;-)

Third - would any of those (either the field or the control) also contain the time? (As in populating using the Now() function in stead of Date())

#1/1/2004 5:59:01 PM# is different from #1/1/2004 5:43:25 PM#.

Then you could try something like this:

[tt]= DCount("ParticipantID", "Forums", "format(ForumDate,'mm/dd/yyyy') = #" & format$(Me!txtForumDate,"mm/dd/yyyy") & "#")[/tt]


Roy-Vidar

 

[kaiana]

Thank you Roy-Vidar,

The trick was indeed changing the format. I have spent hours pouring over tek-tips previous threads and trying and testing many different things. This is the first I have seen mention of setting the format of the date. Thank you so much, I can now sleep easy.

Regards

 

[scriverb]

kaiana: Roy-Vida has made a good point here that can be expanded a little farther to another problem seen here at TT quite often. When populating a table field with NOW() the time is also assigned to that field. When using the Between function in the WHERE clause of a query records can be lost on back end of the selection timeframe.

EXAMPLE:

Select A.* 
FROM [i]yourtablename[/i] as A
WHERE A.[[i]yourNOWdatefield[/i]] BETWEEN [Enter Beg_Date: ] and [Enter End_Date: ];

As you enter two date fields the expression is going to compare your tables datefield which includes a time value to date values both set to 12:00 am for that date. So, any records with a date equal to the End_Date value will not be selected due to them being populated with whatever time the field was updated with NOW(). They will larger than the End_date. The same formatting will clearup this problem or always use the Date() function to populate Date/Time fields if the time is of no interest.

Just another issue that I think is worthy of discussion here. Good luck with your project.

Bob Scriver
[blue]Want the best answers? See FAQ181-2886[/blue]