Query criteria not to be taken into account 1

[AlexandreAmant]

Hi !

I am building an accounting DB and the debit/credit field is giving me a problem in a query: I have a combo and the user can choose between debit, credit and both. When the user selects debit or credit, the query filters accordingly, but when the user chooses both, the query should return both results.

I try to put this the query criteria for debitcredit:

Switch([Forms]![F_AccountBalance]![ComboDebitCredit] = "Debit", "Debit", [Forms]![F_AccountBalance]![ComboDebitCredit] = "Credit", "Credit", [Forms]![F_AccountBalance]![ComboDebitCredit] = "Both", "Debit" & """ Or """ & "Credit&quot

But it does not work when "Both" is selected and I can't see what's wrong.

I've tried also to put the null value or a zero-lenght string but everytime the query returns nothing when "Both" is selected.

Any idea ?

Thanks !

@lex

 

[grnzbra]

What are you trying to concatinate in the "both" situation? Why not just "Debit or Credit"?

 

[AlexandreAmant]

That does not work because DebitCredit in the underlying table is a text field, so I need to have something like:

"Debit" Or "Credit"

When I put this alone in the cirteria, it works, but when I try in a more complicated formula like the one above, it does not work although it gives the right value when I test it in VBA.

The whole problem is coming from the syntax I think, but I can't figure out why.

Alternatively, if you know a way of having a criteria that cannot be taken into account in some cases it will be welcome.

I also tried to incorporate the ' Like "*" ' in the switch expression, but it does not work either.

 

[lameid]

Your ending up testing in your debit/Credit case to find a string that matches the value...

Debit" Or " Credit

Your where clause for the sql statement is then equivalent to

Where Field = "Debit"" Or "" Credit"

What you are attempting to get at is

Where Field = "Debit" Or Field = "Credit"

You could do something like that if you were building an SQL string in code.

However I'm going to assume that Field MUST be equal to either "Debit" or "Credit".

Now you could probably get away with a criteria cell that looks like...

Like IIF([Forms]![F_AccountBalance]![ComboDebitCredit] = "Both", "*", [Forms]![F_AccountBalance]![ComboDebitCredit])

If it doesn't work, it will be because it is looking for the literal asterisk instead of the wildcard. I don't think this will happen. If it does, you will be stuck with making your SQL statement in code.

I take the part about code back, if you are willing to take the performance hit, you can pass the criteria as a filter using docmd.openquery or for the appropriate object.

 

[AlexandreAmant]

Thanks lameid, it works fine !!!

I was stuck on the syntax !

This will help me in other DB as well !

The Like in front of the iif is the key !

Thanks again !

@lex