I'm using the DCount function to check for duplicates in tblBookings matching text in textboxes from frmNewBooking. I've managed to get this to work fine with text, but I then added check boxes to my form and table. I want know to see if some text equals that of in the table (like I originally had) but also, using And, to check whether the checkbox was ticked in a previous booking or not. Here's my guess at what it would be, I know that all of the criteria works up to the bit where I've added chkExtension and onwards. I have also checked that all the data types are correct in my table too.
Dim NumOfDuplicates As Integer
NumOfDuplicates = DCount("*", "tblBookings", "(tblBookings.EventDate = cboEventDate And tblBookings.Session = cboSession And tblBookings.Extension = chkExtension And tblBookings.SpecialEvent = chkSpecialEvent)")
If NumOfDuplicates > 0 Then
MsgBox "Sorry, this session is already taken.", vbInformation, "Session taken"
MsgBox "This session is available.", vbInformation, "Session Available!"
The point is your are that long string as the criteria for the dcount function. Try putting the expression in a select query and see if it works. I suspect it wont because it will read cboEventDate as exactly that - a string of 12 characters.
So instead of sending "tblBookings.EventDate = cboEventDate" to the function you really want to send:
"tblBookings.EventDate = #1/1/6#"
I suggest you create a string variable called say myCriteria and build it up bit by bit to read the criteria you need.
So a start might be:
myCriteria="tblBookings.EventDate = #" & me.cboEventDate & "#"
I've assumed that cboEvent is a field on your form (?). I'm fairly certain you have to use the # but not 100%. Anyway, the point (the point Teddy was making) is that the cboEventDate has to be used as a variable not as a string. Note how I've added strings together using &. Note also that if your criteria is a string it needs to be enclosed in single quotes. So you might have something like:
myCriteria="tblBookings.Session ='" & me.cboSession & "'"
note the single quotes which will result in:
Get the point ?
The next point made by Teddy was to use debug. I think the intermediate window depends on which version of access i.e. it's called intermediate in later versions and the debug window (ctrl-G) in earlier versions. So after the above line of code, if you put in the following line you will get the value of myCriteria output to the intermediate window and you can see if it looks like a proper criteria that you would write in SQL: