By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
446,224 Members | 1,143 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 446,224 IT Pros & Developers. It's quick & easy.

Difference between DCOUNT and SQL Query attached

100+
P: 108
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!

Expand|Select|Wrap|Line Numbers
  1. SELECT Booking.* 
  2. FROM Booking 
  3. WHERE (((Booking.Date)=[Forms]![Booking]![DTPicker8]) AND ((Booking.Facility_ID)=[Forms]![Booking]![comboFacility]) AND ((Booking.Area_ID)=[Forms]![Booking]![comboArea])); 

Expand|Select|Wrap|Line Numbers
  1. 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
Dec 1 '06 #1
Share this Question
Share on Google+
6 Replies


MMcCarthy
Expert Mod 10K+
P: 14,534
You have to use the correct names including capitalisation.

Try this ...

Expand|Select|Wrap|Line Numbers
  1. doublebooking = DCount("[reference_number]", "Booking", "[Area_ID]=" & [Forms]![Booking]![comboArea] & " AND [Facility_ID]=" & [Forms]![Booking]![comboFacility] & _
  2. " AND [date]=#" & [Forms]![Booking]![DTPicker8] & "#") 
  3.  
Dec 1 '06 #2

100+
P: 108
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 :(
Dec 1 '06 #3

MMcCarthy
Expert Mod 10K+
P: 14,534
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.
Dec 1 '06 #4

100+
P: 108
Expand|Select|Wrap|Line Numbers
  1. Private Sub comboArea_Click()
  2. Dim doublebooking As Long
  3.  
  4. MsgBox Forms!Booking!combofacility
  5. MsgBox Forms!Booking!comboArea
  6. MsgBox "*" & Forms!Booking!DTPicker8 & "*"
  7. MsgBox "*" & Forms!Booking!ComboEnd & "*"
  8.  
  9.  
  10. 'doublebooking = DCount("reference_number", "booking", "area_ID=" & Forms!Booking!comboArea & " AND Facility_ID=" & Forms!Booking!combofacility & _
  11. '" AND booking_date=#" & Forms!Booking!DTPicker8 & "#")
  12.  
  13. doublebooking = DCount("[reference_number]", "Booking", "[Area_ID]=" & [Forms]![Booking]![comboArea] & " AND [Facility_ID]=" & [Forms]![Booking]![combofacility] & _
  14. " AND [date]=#" & [Forms]![Booking]![DTPicker8] & "#")
  15.  
  16. MsgBox doublebooking
  17.  
  18.  
  19. If doublebooking > 0 Then
  20.     MsgBox "Double Booked!"
  21. End If
  22.  
  23.  
  24. 'Dim strSQL As String
  25. '  strSQL = "SELECT Area.Area_ID, Booking.Date, Booking.Start_time, Booking.End_time, Booking.Facility_ID " & _
  26. '"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) " & _
  27. '"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]));"
  28.  
  29.  
  30. End Sub
Dec 1 '06 #5

MMcCarthy
Expert Mod 10K+
P: 14,534
Try this ...

Expand|Select|Wrap|Line Numbers
  1. Private Sub comboArea_Click()
  2. Dim doublebooking As Long
  3.  
  4. MsgBox Me.combofacility
  5. MsgBox Me.comboArea
  6. MsgBox "*" & Me.DTPicker8 & "*"
  7. MsgBox "*" & Me.ComboEnd & "*"
  8.  
  9.    doublebooking = nz(DCount("[reference_number]", "Booking", "[Area_ID]=" & Me.comboArea & _
  10.    " AND [Facility_ID]=" & Me.combofacility & " AND [date]=#" & Me.DTPicker8 & "#"),0)
  11.  
  12.    MsgBox doublebooking
  13.  
  14.  
  15.    If doublebooking > 0 Then
  16.       MsgBox "Double Booked!"
  17.    End If
  18.  
  19. End Sub
  20.  
Dec 1 '06 #6

NeoPa
Expert Mod 15k+
P: 31,660
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!

Expand|Select|Wrap|Line Numbers
  1. SELECT Booking.* 
  2. FROM Booking 
  3. WHERE (((Booking.Date)=[Forms]![Booking]![DTPicker8]) AND ((Booking.Facility_ID)=[Forms]![Booking]![comboFacility]) AND ((Booking.Area_ID)=[Forms]![Booking]![comboArea])); 

Expand|Select|Wrap|Line Numbers
  1. 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.
Dec 1 '06 #7

Post your reply

Sign in to post your reply or Sign up for a free account.