query error (1 Viewer)

Benderook

New member
Local time
Today, 06:06
Joined
Sep 18, 2018
Messages
7
Hello, here is somthing I am stuck on.

I get a syntax error, operator is missing

SELECT qrySubOpmerkingenMijlpalen.MO_Opmerking, qrySubOpmerkingenMijlpalen.MO_Inkoopnummer, tblBedrijfsonderdeelhlp.Afkorting, tblInkoopprocessen.* FROM (tblInkoopprocessen LEFT JOIN qrySubOpmerkingenMijlpalen ON tblInkoopprocessen.Inkoopactiviteitnr = qrySubOpmerkingenMijlpalen.MO_Inkoopnummer) LEFT JOIN tblBedrijfsonderdeelhlp ON tblInkoopprocessen.Bedrijfsonderdeel = tblBedrijfsonderdeelhlp.Bedrijfsonderdeel where vervallen_onhold <> 2 and vervallen_onhold <> 1 and tblInkoopprocessen.status <> 'Gereed' AND Inkoper.value ='' AND ((tblInkoopprocessen.Status)<> 'Nog te plannen') AND IIF(DCount('contractnummer','tblKoppelContracten', 'inkoopnummer = '2018(O)000199') > 0,'J','') ORDER BY tblInkoopprocessen.Inkoopactiviteitnr DESC

This part gives the error:
The inkoopnummer='2018(O)000199' is created by the sql part below

" AND IIF(DCount('contractnummer','tblKoppelContracten', 'inkoopnummer = '" & [Inkoopactiviteitnr] & "') > 0,'J','') "

Can anyone help me. It must be somthing simple but I cant find it.
 

isladogs

MVP / VIP
Local time
Today, 13:06
Joined
Jan 14, 2017
Messages
18,186
In the IIf code, if DCount>0 the output is 'J', otherwise ''. But nothing has been set to that output. So it fails.
 

JHB

Have been here a while
Local time
Today, 14:06
Joined
Jun 17, 2012
Messages
7,732
Replace the single ' with a double ":
Code:
DCount([B][COLOR=Red]"[/COLOR][/B]contractnummer[B][COLOR=red]"[/COLOR][/B],[B][COLOR=red]"[/COLOR][/B]tblKoppelContracten[B][COLOR=red]"[/COLOR][/B], [B][COLOR=red]"[/COLOR][/B]inkoopnummer = '2018(O)000199'[B][COLOR=red]"[/COLOR][/B])
 

arnelgp

..forever waiting... waiting for jellybean!
Local time
Today, 21:06
Joined
May 7, 2009
Messages
19,169
also remove .value from inkoper.

and:

IIF(DCount('contractnummer','tblKoppelContracten', 'inkoopnummer = '2018(O)000199') > 0,'J','') ='J'
 

Benderook

New member
Local time
Today, 06:06
Joined
Sep 18, 2018
Messages
7
Hello,

Thank you for your quick reply.

What I am doing is making a dynamic query with vba code. So I make a string which I use as a recordsource for a form. I need to be able to filter data on that form. Without the iif(dcount part it is working.
The problem is that to build the string (" that is between those quotes ") I cant use the double quotes. Or am I missing something?. Hope you untherstand what I am trying to accomplish.

Regards,
Ben
 

JHB

Have been here a while
Local time
Today, 14:06
Joined
Jun 17, 2012
Messages
7,732
You are missing a ' after 'inkoopnummer = '2018(O)000199', you've 3 single ' you need 4: 'inkoopnummer = '2018(O)000199'':
 

Benderook

New member
Local time
Today, 06:06
Joined
Sep 18, 2018
Messages
7
ok here is what i am doing. The part that gives the error 3075 operator missing

sSQLFilterGekoppeldLeeg = " AND IIF(DCount('contractnummer','tblKoppelContracten', 'inkoopnummer = '" & [Inkoopactiviteitnr] & "') > 0,'J','') "

sSQLFilterGekoppeldLeeg is used below to make a recordsource.

gsqlStringVoortgangsrapport = "SELECT qrySubOpmerkingenMijlpalen.MO_Opmerking, qrySubOpmerkingenMijlpalen.MO_Inkoopnummer, " _
& "tblBedrijfsonderdeelhlp.Afkorting, tblInkoopprocessen.* " _
& "FROM (tblInkoopprocessen LEFT JOIN qrySubOpmerkingenMijlpalen ON tblInkoopprocessen.Inkoopactiviteitnr = " _
& "qrySubOpmerkingenMijlpalen.MO_Inkoopnummer) LEFT JOIN tblBedrijfsonderdeelhlp ON " _
& "tblInkoopprocessen.Bedrijfsonderdeel = tblBedrijfsonderdeelhlp.Bedrijfsonderdeel " _
& sSQLWhereAofO & sSQLWhereFilter & sSQLInkoopmethodeFilter & sSQLVervallenOnhold & sSQLGereed & sSQLBedrijfsonderdeel & sSQLInkopers & sSQLSTartFase & sSQLFilterGekoppeldLeeg _
& "ORDER BY tblInkoopprocessen.Inkoopactiviteitnr DESC"

after debug.print I get:
SELECT qrySubOpmerkingenMijlpalen.MO_Opmerking, qrySubOpmerkingenMijlpalen.MO_Inkoopnummer, tblBedrijfsonderdeelhlp.Afkorting, tblInkoopprocessen.* FROM (tblInkoopprocessen LEFT JOIN qrySubOpmerkingenMijlpalen ON tblInkoopprocessen.Inkoopactiviteitnr = qrySubOpmerkingenMijlpalen.MO_Inkoopnummer) LEFT JOIN tblBedrijfsonderdeelhlp ON tblInkoopprocessen.Bedrijfsonderdeel = tblBedrijfsonderdeelhlp.Bedrijfsonderdeel where vervallen_onhold <> 2 and vervallen_onhold <> 1 and tblInkoopprocessen.status <> 'Gereed' AND ((tblInkoopprocessen.Status)<> 'Nog te plannen') AND IIF(DCount('contractnummer','tblKoppelContracten', 'inkoopnummer = '2018(O)000199') > 0,'J','') ORDER BY tblInkoopprocessen.Inkoopactiviteitnr DESC

also error 3163
The bevore sugested solutions in this tread did not work :-(
 

isladogs

MVP / VIP
Local time
Today, 13:06
Joined
Jan 14, 2017
Messages
18,186
You still haven't set the iif statement equal to anything.
 

JHB

Have been here a while
Local time
Today, 14:06
Joined
Jun 17, 2012
Messages
7,732
..
The bevore sugested solutions in this tread did not work :-(
Sorry but you're still missing the last ', (I pointed it out in post #6).
So add it to your code:
Code:
sSQLFilterGekoppeldLeeg = " AND IIF(DCount('contractnummer','tblKoppelContracten', 'inkoopnummer = '" & [Inkoopactiviteitnr] & "'[B][COLOR=Red]'[/COLOR][/B]) > 0,'J','') "
 

Benderook

New member
Local time
Today, 06:06
Joined
Sep 18, 2018
Messages
7
yes i think the iif function is correct. Its J or empty ->'' putting in something there doesnt help
 

JHB

Have been here a while
Local time
Today, 14:06
Joined
Jun 17, 2012
Messages
7,732
Have you read post #9.
Else if you can't get it work post the database with some sample data, zip it.
 

arnelgp

..forever waiting... waiting for jellybean!
Local time
Today, 21:06
Joined
May 7, 2009
Messages
19,169
see post #4. yiu are returning a string and not boolean. to which value are you comparing it?
 

Benderook

New member
Local time
Today, 06:06
Joined
Sep 18, 2018
Messages
7
Thanks all,

That (#9) is also not helping. The problem is the conversion from string to sql string I guess. I can replicate the iif part in query design and there it is working.
 

isladogs

MVP / VIP
Local time
Today, 13:06
Joined
Jan 14, 2017
Messages
18,186
yes i think the iif function is correct. Its J or empty ->'' putting in something there doesnt help

Yes it does.
At the moment you have IIF(DCount(....)>0, 'J','')
It's serving no purpose whatsoever.
You need to set the whole statement equal to something as both arnelgp and I have said more than once
 

Benderook

New member
Local time
Today, 06:06
Joined
Sep 18, 2018
Messages
7
Hi ridder,

as I pointed out it should work because I tested it in a query.

IIF(DCount(....)>0, 'J','')

means: if my count > 0 then show J ('J') else nothing ('') so there is nothing wrong here.

I use this in a field on a continues form:

=IIf(DCount("contractnummer";"tblKoppelContracten";"inkoopnummer =Inkoopactiviteitnr")>0;"J";"")

But I also want to use it in a dynamic query made with vba code for the recordsource of that form. I want to let show all records that have no 'J'
 

isladogs

MVP / VIP
Local time
Today, 13:06
Joined
Jan 14, 2017
Messages
18,186
Yes I agree that if this is used in a query field or an inbound textbox it will work as you described.

However as I've said repeatedly it won't work as part of a WHERE filter clause as written in your SQL.

Try pasting the whole SQL statement into a blank query and try to convert to design view. If I'm correct, it won't allow you to do so unless you remove the IIF section.

After reading the last sentence in the previous post, try adding either <>'J' or ='' at the end of your IIF statement
 

arnelgp

..forever waiting... waiting for jellybean!
Local time
Today, 21:06
Joined
May 7, 2009
Messages
19,169
ridders even tbat is correct, then the where clause result would be:


...And "J"

it must be boolean, not string:

...And "J" = "J" (see post 4)
 

isladogs

MVP / VIP
Local time
Today, 13:06
Joined
Jan 14, 2017
Messages
18,186
ridders even tbat is correct, then the where clause result would be:


...And "J"

it must be boolean, not string:

...And "J" = "J" (see post 4)

Hi arnel
I think you mean it is still incorrect ...and you may well be right
I think the OP was asking for the opposite of what you wrote in post #4

Perhaps this
Code:
AND IIF(DCount('contractnummer','tblKoppelContracten', 'inkoopnummer = '2018(O)000199'[COLOR="Red"][B]'[/B][/COLOR]) > 0,'J','') <>'J'

should be
Code:
AND DCount('contractnummer','tblKoppelContracten', 'inkoopnummer = '2018(O)000199'[COLOR="Red"][B]'[/B][/COLOR]) = 0

However, I'm sorry to say that I'm losing the will to live with this thread.
Unless the OP supplies a stripped down database with this code & the relevant tables, I don't think we can do any more
 

Users who are viewing this thread

Top Bottom