DCount not working perfectly

[Heatshiver]

I have a button that checks two fields in a table to see if a user has used a date in the past. The fields are UserID and Days.

The code is:

DCount("[UserID]", "tblGenSum", "[UserID]= '" & Me.UserID & "' And [Days]= #" & Me.txtNewDate & "#") > 0

The purpose is so that the user never repeats the same day twice. The problem is that it seems to find other user ID and check that as well. The main form does pull the user ID from another form... I have tried specifying the field from that form in the DCount, but the same results occur.

I have one other user ID. I feel like it's either checking all user IDs, or it still thinks it is using the other user ID. Everything else seems to work, including a new (unused) date with either user ID, and displays as such in the table data.

Any help would be much appreciated!

 

[KenHigg]

is userid a text or numeric data type?

 

[gemma-the-husky]

I presume you are in the UK - in which case, your problem will be

Me.txtNewDate, I think.

Access always tries to use a US date format - you need it force it to use a UK format, if there is ambiguity.

ie if you look for 08/04/13 (UK), SQL will search for 04/08/13.

you need to coerce the date to a UK date.

I use format(mydate, "long date") but there are more formal ways.


(change the query to a select query and see which rows get returned, to test this)

 

[Heatshiver]

Thanks for the replies to both. To answer your questions:

1) UserID is of the datatype text, so it can have text or numerical data.

2) I am actually overseas and was instructed that using US date system was fine, so I've always used the default US system Access uses.

Any suggestions. I can post a minimized version of the DB if anyone is willing to take a look as that page has a lot of code going through it for certain events, although, I don't think most it should have any affect on this issue...