Ran both with same values in the form and the SQL query retrieves a row and the VBA code doesnt (DCOUNT)
Ran them both at same time ???? very confused
Also later changed field Date to booking_date and still the same results!
- SELECT Booking.*
-
FROM Booking
-
WHERE (((Booking.Date)=[Forms]![Booking]![DTPicker8]) AND ((Booking.Facility_ID)=[Forms]![Booking]![comboFacility]) AND ((Booking.Area_ID)=[Forms]![Booking]![comboArea]));
- doublebooking = DCount("reference_number", "booking", "area_ID=" & Forms!Booking!comboArea & " AND Facility_ID=" & Forms!Booking!combofacility & _" AND date=#" & Forms!Booking!DTPicker8 & "#")
_________________
Thanks for the help & advice, greatly appreciated ~Rob
In the first one (SELECT Query) you are passing the full reference to the SQL interpreter.
In the second case (DCount) you are trying to resolve the items in your VBA code - passing literal values to the function instead.
This will work for the date (Forms!Booking!DTPicker8) if, and only if, you have American local settings, as the SQL date format is 'm/d/y' and it ignores local settings. Otherwise (I would recommend always) you need to use Format(Forms!Booking!DTPicker8,'m/d/yyyy').
You also have a 'Line Continuation' character embedded in your code.
...!combofacility & _" AND ...
which may be causing problems.
Just caught another problem, you use '(Booking.Date)=' in the SELECT but just 'date=' in your DCount. This will cause a problem as Date() is a recognised function and it will try to compare it to today's date instead of your field.