The object doesn't contain the automation object

[daisyMay1]

Hello, Would someone please be able to help me. I had to change a key field (GroupNo) in a table from Numeric to Text. This has cause all kinds of problems in code in a database I did not write but am trying to help fix. This field is referenced everywhere so expressions and functions, etc. are all now having to be reviewed and updated. Currently I am getting this error:

The expression you entered as a query parameter produced this error: 'The object doesn't contain the Automation object 'K006."

It is occurring on this line of code:

Public Function doQueryDefSQL(ByRef fromWhere As String, Optional keyNow As Variant = "") As Boolean
Dim bHasLocs As Boolean
'more code here...
 If Nz(DCount("*", "Accounts", "Location<>'0' and GroupNo = " & keyNow), 0) > 0 Then bHasLocs = True

When I click OK then this error is thrown:

Run-time error '0': Reserved Error

keyNow is returning as a VariantString of "K006"

This is urgently needed today if anyone can assist.
Much thanks!

[Rabbit]

If GroupNo is a string now, then you need to surround the value in single quotes the same way you do with location.

[daisyMay1]

Hi Rabbit,
I made several attempts changing where the quotes should be and finally was successful with this:

If Nz(DCount("*", "Accounts", "Location<>'0' and GroupNo = ' & keyNow & '"), 0) <> 0 Then bHasLocs = True

However, it is returning 0 and should be returning a count other than 0. I broke it down and it looks like DCount("*", "Accounts", "GroupNo = ' & keyNow & '") is evaluating incorrectly. keyNow currently has a value of "K006" and there are at least 2 additional locations in the table other than location 0 for K006, so what am I doing wrong?

Thanks!

[Rabbit]

The key now and the ampersands still need to be outside the double quotes.

[daisyMay1]

I apologize for my ignorance but appreciate your help. However, when I do as you state above:

DCount("*", "Accounts", "GroupNo = "' & keyNow & ')

then I get a compile error Expected list separator or ).

[zmbd]

Your issue is the additional quotes?

DCount("*", "Accounts", "GroupNo = "[icode]'[/icode] & keyNow & [icode]'[/icode])

instead:

DCount("*", "Accounts", "GroupNo = [icode]'[/icode]" & keyNow & [icode]"'"[/icode])

This is why I usually build the string first and then use in the function... you can debug.print the string for troubleshooting .

sSQL = "GroupNo = '" & keyNow & "'"[/icode]
'
'allowing you to view the string after running in the immediate pane via <ctrl><g>
'just uncomment the line
'debug.print sSQL 
'
DCount("*", "Accounts", [iCODE]sSQL[/iCODE])

[daisyMay1]

That did it! Thank you both for your patience! I really appreciate it. Learning on the fly and under the gun I guess sometimes has it's advantages.

[zmbd]

Our pleasure.
Thank you for selecting my post as best answer (warm fuzzys); however, I've reset and selected Rabbit's post given that I only piggy-backed upon his efforts. :)

[daisyMay1]

Yes, you both were helpful!