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

Dcount getting runtime error '2471'

P: 46
Bookings ID is number
ID is autonumber
Error says:
Run-Time error '2471':
The expression you entered as a query parameter produced this error: '[Bookings ID]'

Expand|Select|Wrap|Line Numbers
  1. If DCount("[Click ID]", "Access Click log", "[Bookings ID] = " & Forms![Roll Out CRM]![Royal Rollout AM Routes]![ID]) = 0 Then
Thank you for the help!
Vasago
Apr 18 '14 #1

✓ answered by zmbd

Look at two things:

-- Open the table "Access Click log" where[Bookings ID] is located.
Check that the field name is spelled correctly between the code and the table.

-- Check the type cast of the [Bookings ID] field, I suspect it is set to "text" not numeric as you are expecting.

Share this Question
Share on Google+
8 Replies


Rabbit
Expert Mod 10K+
P: 12,430
If booking id is a string, you will need to surround the value you're looking for in quotes.
Apr 18 '14 #2

P: 46
Booking ID's type is a number along with ID
Apr 18 '14 #3

Rabbit
Expert Mod 10K+
P: 12,430
Then you will need to post the full exact error message.
Apr 18 '14 #4

zmbd
Expert Mod 5K+
P: 5,397
- The string theory:
You've stumbled upon one of my pet peeves by building the criteria string within the command - and it's not your fault because that's how a majority of examples show how to use the command.
Instead I suggest that you build the string first and then use the string in the command. Why you might ask, because you can then check how the string is actually resolving; thus, making troubleshooting the code so much easier as most of the time the issue is with something missing or not resolving properly/as expected within your string.
So to use your code:

Expand|Select|Wrap|Line Numbers
  1. '>The following is air code; thus, not tested and may require more effort to operate properly
  2. '
  3.     DIM strSQL as string
  4.     Dim intCount As Integer
  5. '
  6. '
  7.     strSQL = "[Bookings ID] = " & _
  8.         Forms![Roll Out CRM]![Royal Rollout AM Routes]![ID]
  9. '
  10. 'now you can insert a debug print here for troubleshooting
  11. ' - press <ctrl><g> to open the immediate window
  12. ' - you can now cut and paste this information for review!
  13. '
  14. debug.print "Your criteria = " & strSQL
  15. '
  16. 'now use the string in your code:
  17.     intCount= DCount("[Click ID]", "Access Click log", strSQL)
  18. debug.print "Your Count= " & intCount
  19. '
  20. 'Note: I didn't return the count within the IF...THEN...
  21. 'once again because troubleshooting the code is not easily 
  22. 'done when the function returns within another 
  23. 'function/statement.
  24. '
  25. '
  26.     if intCount = 0 then
  27.         'the remaining code
  28.     end if
  29. '
  30. '
If you will make these little changes and post back the resolved string we can help you tweak the code.

When posting errors:
Please post the EXACT title, number, and text of the error message. Please do not alter, omit, nor abreviate the information provided in the error message. ALSO indicate on which line of the code the error occurs and provide enough code so that we have the context of the commands used.
Apr 18 '14 #5

P: 46
ZMBD,

I can't thank you enough for you time, education and knowledge. I have included the code below. My debug print for strSQL is Your criteria = Bookings ID = 2110. This is the correct Booking ID number.

Error Code on line 9:
Run-time error '2471':
The expression you entered as a query parameter produced this error: '[Bookings ID]'

Expand|Select|Wrap|Line Numbers
  1. Private Sub Form_AfterInsert()
  2. Dim db As Recordset
  3. Dim strSQL As String
  4. Dim intCount As Integer
  5.  
  6.     strSQL = "[Bookings ID] = " & _
  7.         Forms![Roll Out CRM]![Royal Roll out Field Trips]![ID]
  8. Debug.Print "Your criteria = " & strSQL
  9.     intCount = DCount("[Click ID]", "Access Click log", strSQL)
  10. Debug.Print "Your Count= " & intCount
  11. If intCount = 0 Then
  12. Set db = CurrentDb.OpenRecordset("SELECT * FROM [Access click Log]")
  13. db.AddNew
  14. db![Booking ID] = ID
  15. db.Update
  16. End If
Apr 22 '14 #6

P: 46
Zmbd,

I can't thank you enough for you time, lesson, and knowledge. My debug print for strSQL is Your criteria = [Bookings ID] = 2113. This is the correct Bookings ID.

I am getting the error below on line 9.

Error:
Run-time error '2471':
The expression you entered as a query parameter produced this error: '[Bookings ID]'

Expand|Select|Wrap|Line Numbers
  1. Private Sub Form_AfterInsert()
  2. Dim db As Recordset
  3. Dim strSQL As String
  4. Dim intCount As Integer
  5.  
  6.     strSQL = "[Bookings ID] = " & _
  7.         Forms![Roll Out CRM]![Royal Roll out Field Trips]![ID]
  8. Debug.Print "Your criteria = " & strSQL
  9.     intCount = DCount("[Click ID]", "Access Click log", strSQL)
  10. Debug.Print "Your Count= " & intCount
  11. If intCount = 0 Then
  12. Set db = CurrentDb.OpenRecordset("SELECT * FROM [Access click Log]")
  13. db.AddNew
  14. db![Booking ID] = ID
  15. db.Update
  16. End If
Apr 22 '14 #7

zmbd
Expert Mod 5K+
P: 5,397
Look at two things:

-- Open the table "Access Click log" where[Bookings ID] is located.
Check that the field name is spelled correctly between the code and the table.

-- Check the type cast of the [Bookings ID] field, I suspect it is set to "text" not numeric as you are expecting.
Apr 22 '14 #8

P: 46
ZMBD,

Thank you again for your help. Problem is solved and I feel extremely embarrassed. [Bookings ID] is suppose to be [Booking ID].

Thank you,
Vasago
Apr 23 '14 #9

Post your reply

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