Error on query criteria

[timon10z]

Hello!

I am more or less new in Access, and I am creating a Database using different tables. I have made a Union Query to put together some common fields for all tables and I am trying to define criteria. I have a field called [Proj Type], which can either contain "A" or "B". This is the problematic part of my code:

WHERE [Proj Type]=IIf(([Forms]![Main Menu]![Frame73])=1,"A",IIf(([Forms]![Main Menu]![Frame73])=2,"B",[Proj Type] Is Not Null))

[Frame73] is an option box in a form which can either take values for 1 (select only "A"), 2 (select only "B") or 3(select both). For 1 and 2 I am fine and the query works well, but for 3 it just comes up blank. Any clue why?

Thank you for your help, I am really stuck on this.
R.

 

[spikepl]

because, in effect, you have written

[Proj Type]="A"
[Proj Type]="B"
[Proj Type]= IS NOT Null which is syntactical nonsense

You need:

WHERE Frame73=1 AND [Proj Type]="A" OR Frame73=2 AND [Proj Type]="B" OR Frame73=3 AND [Proj Type] Not Is Null

Update: read http://www.access-programmers.co.uk/forums/showthread.php?t=225837 concerning naming. Blanks in field/object names will cost you PITA from now until doomsday

 

[timon10z]

Ok so "A" and "B" are already the two values that the field contains, I don't know if I understand well what you try to say.

My Iif statement is to take only all records which have "A", all records which have "B" or all records in general. If on my [Frame73] I click either 1 (only "A") or 2 (only "B") and then run the query it works fine, just taking either all "A" or all "B", but if I click 3 it is not pulling anything. I just dont know how to end the Iif...

Thanks!
R.

 

[spikepl]

I have 0 idea what you are asking now. You posed a problem, you were offered a solution, and if you don't like it then wait for someone else to offer something.

 

[timon10z]

I am asking the same thing. And I was not offered a solution, I was offered a reply. No need to be rude.

WHERE [Proj Type]=IIf(([Forms]![Main Menu]![Frame73])=1,"CAP",IIf(([Forms]![Main Menu]![Frame73])=2,"OPER",[Proj Type] Not Is Null))

gives me a syntax error.

Thanks!
R.

 

[spikepl]

I gave you a solution. You have taken bits and pieces, mangled them and tried to press them into your own thing (surprise, it did not work!). And on top of all this you bitch about what I gave you.
FO ! (Drop dead , in polite speak )

 

[timon10z]

Ok great help thank you! (Btw your code didnt work either, the bits and pieces was a second try).

Please if someone has a solution to this it would be great.

Thanks!
R.

 

[JHB]

Try the below:

WHERE [Proj Type]=IIf(([Forms]![Main Menu]![Frame73])=1,"CAP",IIf(([Forms]![Main Menu]![Frame73])=2,"OPER",Not Null))

 

[timon10z]

Still doesnt work, but I dont understand why...something like that should work. I have even tried ending it like Iif(......,<>"U")) , which is not very professional but doesnt work either...

 

[namliam]

You shouldnt use IIFs in where clauses ...
instead of

WHERE [Proj Type]=IIf(([Forms]![Main Menu]![Frame73])=1,"A",IIf(([Forms]![Main Menu]![Frame73])=2,"B",[Proj Type] Is Not Null))

Try something like

WHERE      ([Proj Type]="A"         and [Forms]![Main Menu]![Frame73])=1)
        or ([Proj Type]= "B"        and [Forms]![Main Menu]![Frame73]=2)
        or ([Proj Type] Is Not Null and [Forms]![Main Menu]![Frame73] not in (1,2) )

these kind of where clauses shouldnt be build in the first place, but be handled in VBA

Dim mySQL as string
mySQL = " Select ... where " 
if me.Frame = 1 then
 mysql = mysql & "[Proj Type]=""A"" "
elseif me.frame... then ..
etc...

Also Frame73, really? Give controls proper names please to prevent maintenance nightmare.
endif
curentdb.querydefs("YourQuery").sql = mysql

 

[timon10z]

Ok thanks, I'll try this when I have time and let you know the result

So just a little background, I am a Finance major who is doing a Finance Internship and hadnt seen Access in my life, and suddenly I was asked to create this database. So I know I am not using and doing things probably as I ideally should, but it is already being hard enough. I try to learn something everyday and update my database so that the build is more professional...but my main goal now is that it works. Once I accomplish this I will definitely get more into VBA and optimizing the build, but for now it's a little overwhelming if you know what I mean.

Thanks!
R.

 

[timon10z]

Ueeeeeeeeeeeeee it works!!!!!!!! Thank you really! A little bit closer to the end now!

R.

 

[namliam]

Do start by NEVER keeping default controle names!!!

Databases are a little overwhelming, at start, once you get the swing of things you Will never want anything else

What solution did you end up using?

 

[timon10z]

Ok I can change the names of the boxes and objects, thats easy.

For now I have used the where clause in my sql, I don't want to get into VBA yet. Once I get it working I'll start to look at VBA and improving the build.

R.