Operator missing

[mveijndh]

Operator missing error
I've set up a query to filter the results of the second Combobox. this works fine it I write the code by hand. When I use the query with a variable, it does not work anymore. the problem is probably the where clause, so I focus on that one:
"WHERE tblCompany.CompName Like '" & tempCompName & "' And tblContact.ContAccounting = True " & _

I do see the part of the company name I'm looking for

the full SQL is below.

CompInvoiContID.RowSource = "SELECT tblContact.ContactID, IIf(Trim([ContFirstName])="",[ContLastName],[ContFirstName] & "" "" & [ContLastName]) AS ContactDetails, tblCompany.CompStreet, " & _
"tblCompany.CompCity, tblCountry_1.CountryName, tblContact.CompID, tblContact.ContCountryID, tblContact.ContMobPhone, tblContact.ContPhone, tblContact.ContEmail, tblContact.ContFunction, " & _
"tblContact.ContDepartment, tblCountry.CountryName, tblCompany.CompName, tblCompany.CompName, tblContact.ContAccounting " & _
"FROM tblCountry INNER JOIN ((tblContact INNER JOIN tblCompany ON tblContact.CompID = tblCompany.CompID) INNER JOIN tblCountry AS tblCountry_1 ON tblCompany.CompCountryID = tblCountry_1.CountryID) " & _
"ON tblCountry.CountryID = tblContact.ContCountryID " & _
"WHERE tblCompany.CompName Like '" & tempCompName & "' And tblContact.ContAccounting = True " & _
"ORDER BY IIf(Trim([ContFirstName])="",[ContLastName],[ContFirstName] & "" "" & [ContLastName]);"

 

[plog]

When a factory builds a car and the car doesn't run straight off the assembly line, they don't go to the assembly line to find out what's wrong with the car. They look at the car.

Get the SQL it is actually producing, pop it in a query and start yanking parts until you get it to run. Once it runs, the last part you yanked is the culprit and you can focus on that.

Once you are able to fix that one specific SQL statement then you can go back to the assembly line to make sure that mistake doesn't happen again.

 

[plog]

Also,

it does not work anymore

Be more specific. VBA throws an error? Query runs but no results? Query runs but unexpected results?

Your post title is 'operator missing'. Is that the error you get and does it highlight a specific row of VBA?

 

[mveijndh]

good advise!!
I do get the error "Operator missing"
problem is in the IIF clause. When I took those out it worked. I do need the IIF to make the system readable!

 

[plog]

I do need the IIF to make the system readable!

I don't understand. Did you solve it? Do you know why the IIf is screwing you up?

Hint: its not an SQL issue, its a VBA issue caused by the SQL.

 

[arnelgp]

CompInvoiContID.RowSource = "SELECT tblContact.ContactID, IIf(Trim([ContFirstName])="",[ContLastName],[ContFirstName] & "" "" & [ContLastName]) AS ContactDetails, tblCompany.CompStreet, " & _
"tblCompany.CompCity, tblCountry_1.CountryName, tblContact.CompID, tblContact.ContCountryID, tblContact.ContMobPhone, tblContact.ContPhone, tblContact.ContEmail, tblContact.ContFunction, " & _
"tblContact.ContDepartment, tblCountry.CountryName, tblCompany.CompName, tblCompany.CompName, tblContact.ContAccounting " & _
"FROM tblCountry INNER JOIN ((tblContact INNER JOIN tblCompany ON tblContact.CompID = tblCompany.CompID) INNER JOIN tblCountry AS tblCountry_1 ON tblCompany.CompCountryID = tblCountry_1.CountryID) " & _
"ON tblCountry.CountryID = tblContact.ContCountryID " & _
"WHERE tblCompany.CompName Like '" & tempCompName & "' And tblContact.ContAccounting = True " & _
"ORDER BY 2;"

 

[plog]

Arn, every thinks your great at code. We get it.

Please stop crashing my threads where people are close to learning something. Posting just code without explanation really isn't helpful in the long run. Let's teach people to fish.

 

[arnelgp]

Please stop crashing my threads where people are close to learning something.

i thought this is public forum, anyone can share their ideas. im not in your pm.

 

[mveijndh]

It's clear the problem is the IIF, but i don't see the solution??

 

[plog]

Hey, I didn't even look at Arn's code. I take back what I say about being great at it. Everything else stands.

mv, your problem is like the movie Inception. You've got strings inside strings inside strings and then you break free of one string but your still in a string which was a level higher.

CompInvoiContID.RowSource = "SELECT tblContact.ContactID, IIf(Trim([ContFirstName])="",[ContLastName],[ContFirstName] & "" "" & [ContLastName]) AS ContactDetails, tblCompany.CompStreet, "

That first double quote says 'start a string', let's call this L1String. Then it hits the IIF which has a double quote as well, but that double quote has a double quote right after it so that means 'put 1 double quote inside this string'. It doesn't put 2 double quotes into L1String, it puts one. Double quotes start a string so now you have started L2String.

You wanted this to show:

[ContFirstName])="",

but you made this

[ContFirstName])=",

L2String doesn't end until it hits the next double quotes, which appear after the ampersand. So L1String looks like this:


...[ContFirstName])=",[ContLastName],[ContFirstName] & " "...

L2String which was the criteria of the Iif and should essentially be blank, looks like this:

,[ContLastName],[ContFirstName] &

You missed a double quote. So you need to put 2 more double quotes before the first comma of your Iif. I believe you made the same mistake in your ORDER BY clause as well.

That will fix your issue, but the best practice is to try and not use double quotes inside double quotes. I would use single quotes somewhere. Either to start and end L1String, or to start and end L2String. It just gets tricky when you use double quotes in both levels.

 

[mveijndh]

Thanks for your extensive answer, it makes it more clear. What is the difference between single and double quotes???

 

[plog]

In VBA, working with strings--nothing. So when working on a string that will have a string inside it, use one in the outer string and one in the inner string.

 

[mveijndh]

This worked. Thnks very much for the effort!