Difference between DCOUNT and SQL Query attached

[robtyketto]

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

[MMcCarthy]

You have to use the correct names including capitalisation.

Try this ...

doublebooking = DCount("[reference_number]", "Booking", "[Area_ID]=" & [Forms]![Booking]![comboArea] & " AND [Facility_ID]=" & [Forms]![Booking]![comboFacility] & _
" AND [date]=#" & [Forms]![Booking]![DTPicker8] & "#")

[robtyketto]

Now I get error 2001 You have cancelled the previous operation.

Ive seen this a few times before, no ideas why it appears.
Certaintly havent pressed anything on the keyboard to cause it :(

[MMcCarthy]

Now I get error 2001 You have cancelled the previous operation.

Ive seen this a few times before, no ideas why it appears.
Certaintly havent pressed anything on the keyboard to cause it :(

Without seeing the rest of the code in this procedure I can't help.

[robtyketto]

Private Sub comboArea_Click()
Dim doublebooking As Long

MsgBox Forms!Booking!combofacility
MsgBox Forms!Booking!comboArea
MsgBox "*" & Forms!Booking!DTPicker8 & "*"
MsgBox "*" & Forms!Booking!ComboEnd & "*"


'doublebooking = DCount("reference_number", "booking", "area_ID=" & Forms!Booking!comboArea & " AND Facility_ID=" & Forms!Booking!combofacility & _
'" AND booking_date=#" & Forms!Booking!DTPicker8 & "#")

doublebooking = DCount("[reference_number]", "Booking", "[Area_ID]=" & [Forms]![Booking]![comboArea] & " AND [Facility_ID]=" & [Forms]![Booking]![combofacility] & _
" AND [date]=#" & [Forms]![Booking]![DTPicker8] & "#")

MsgBox doublebooking


If doublebooking > 0 Then
    MsgBox "Double Booked!"
End If


'Dim strSQL As String
'  strSQL = "SELECT Area.Area_ID, Booking.Date, Booking.Start_time, Booking.End_time, Booking.Facility_ID " & _
'"FROM Area INNER JOIN (Facility INNER JOIN Booking ON Facility.Facility_ID = Booking.Facility_ID) ON (Facility.Facility_ID = Area.Facility_ID) AND (Area.Area_ID = Booking.Area_ID) " & _
'"WHERE (((Area.Area_ID)=[Me].[comboarea].[Value]) AND ((Booking.Date)=[Me].[dtpicker8].[Value]) AND ((Booking.Start_time)=[Me].[comboStart].[Value]) AND ((Booking.End_time)=[Me].[ComboEnd].[Value]) AND ((Booking.Facility_ID)=[Me].[Combofacility].[Value]));"


End Sub

[MMcCarthy]

Try this ...

Private Sub comboArea_Click()
Dim doublebooking As Long
 
MsgBox Me.combofacility
MsgBox Me.comboArea
MsgBox "*" & Me.DTPicker8 & "*"
MsgBox "*" & Me.ComboEnd & "*"
 
   doublebooking = nz(DCount("[reference_number]", "Booking", "[Area_ID]=" & Me.comboArea & _
   " AND [Facility_ID]=" & Me.combofacility & " AND [date]=#" & Me.DTPicker8 & "#"),0)
 
   MsgBox doublebooking
 
 
   If doublebooking > 0 Then
	  MsgBox "Double Booked!"
   End If
 
End Sub

[NeoPa]

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!Bo oking!DTPicker8 ,'m/d/yyyy').
You also have a 'Line Continuation' character embedded in your code.

...!combofacili ty & _" 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.