Why doesn't this work!!!

[tfaiers]

I have been programming a query based on a form with each field having an 'active' tick box and a data entry, hopefully giving the user a fairly flexible interface to make queries from, but the following code is perplexing me:

Like IIf([criteria.notesactive]=-1,[criteria.notes],"*")

This code basically checks the form to see if the field is active (using the check box) and if so then searches for the criteria specified, otherwise it should find all records, ie. "*"

BUT

It doesn't find blank field entries. If I go back to basics and put a non-interactive criteria in, the following applies:

"*" - gives all records excluding blanks
is null - gives all blank records
"*" or is null - gives all records including blanks

I can't seem to incorporate the "*" or is null into the Like statement above, does anyone know the correct layout for the statement.

ALSO

This statement was for text fields, I also have a couple of number fields, which seem to cause an additional problem of not using the null or like entry, so I use this formula:

>=IIf([criteria.mincashactive]=-1,[criteria.mincash],0)

But I couldn't think of the correct syntax for 'all' numbers, like "*" is used on text, that's why I've tried putting a '0'.

So there it is, two combinations of Iif statements on text and numbers, but I need to find selected or ALL records.

Thankyou if you've got an answer!!!

 

[CJBIRKIN]

Hello

Could be completley wrong here (nothing new!) but should the statement read ???

IIf([criteria.notesactive]=-1,like([criteria.notes]),"*")

Chris

 

[tfaiers]

Seems not

The code:

IIf([criteria.notesactive]=-1,like([criteria.notes]),"*")

doesn't appear to work, I can't understand why the 'like statement' has to be at the beginning of the syntax, but if you put it within, the search returns nothing.

Still stuck

 

[CJBIRKIN]

Hello
Are you using the query builder or code in VBA?

 

[tfaiers]

What am I using

I am using the built in query builder to keep things (supposedly) simple, just so you know, I've got about 25 queries each with the:

Like IIf([criteria.notesactive]=-1,[criteria.notes],"*")
or
>=IIf([criteria.notesactive]=-1,[criteria.notes],0)

selection criteria's in, so VBA coding was physically looking very complicated as you'll appreciate.

 

[CJBIRKIN]

Hello

What about Like IIf([criteria.notesactive]=-1,[criteria.notes],"*" or isnull([criteria.notes]) = true)


Chris

 

[tfaiers]

Unfortunately not

The same result I'm afraid, still returns no results, not blank or filled entries, my head is spinning with confusion.

 

[CJBIRKIN]

Hello

Think i've solved it

first under the field [your field]

stick in the criteria

Like (IIf([criteria.notesactive]=-1,"*" & [criteria.notes] & "*")

this will select all records containing anydata like the entry in the textbox because of the wild cards at each end, just lose them if you want only exact matches.

secondly you need an addtional field in the query

This will create a value of 2 for all entries null or otherwise
FINDALLENTRIES: IIf(IsNull([Your field])=False,2,2)

under this in the or line stick

Like (IIf([criteria.notesactive]=0,2)


this will select all the data if the checkbox is not ticked

May be ugly but it seems to work.

Chris

 

[tfaiers]

I may be mis-interpreting you or I might just be tired and foggy minded from working on this last night, but.......

Do I need to create this new field called:

FINDALLENTRIES:

In my query form?

 

[CJBIRKIN]

Hello

No just put in an extra column in the query and write this in the top where the field usually goes

FINDALLENTRIES: IIf(IsNull([Your field])=False,2,2)

then stick this in the or section.

Like (IIf([criteria.notesactive]=0,2)

Chris

 

[tfaiers]

This formula appears to find all the records, regardless of whether the 'field active' switch on the form is on or off, hence the previous coding,

Like IIf([criteria.notesactive]=-1,[criteria.notes],"*")

in the perfect world, the following should equate to what I'm trying to do.

IIf([criteria.notesactive]=-1,[criteria.notes],Is null or like "*")

but doesn't, equating to:

If 'Notes Active' is ticked then find for 'criteria notes'
but
If 'Notes Active' is not ticked then find Null or * entries

still working on it......

 

[tfaiers]

forgot to say, the ideal world statement gives the following error:

Data type mismatch in criteria expression.

 

[CJBIRKIN]

Hello

Sorry if i'm not getting the right end of the stick here is my test example. On the form if you click the check box and enter 3 in the text box you get 1 record back. If you then uncheck it and click the button you get all records back. I'll promise to give up if this is wrong!!

 

[tfaiers]

Yes that works, I'm going to have a look more in detail in a minute, but while you were away I was still trying to keep it simple, so I tried these three lines that also work, and keeps the query fairly simple.

Check to see if 'tick' is active and if so use [criteria.notes]
[criteria.notes] And [criteria.notesactive]=-1
Check to see if 'tick' is inactive and if so, look for "*" all data
([criteria.notesactive]=0) And Like "*"
Check to see if 'tick' is inactive and if so, look for all null fields
([criteria.notesactive]=0) And Is Null

So these three lines work as well, hooray

Many, many, many, thanks for your help, I see you're just up the road from us in Bury St. Edmunds, nice to know help is close by.

Regards
Tony

 

[CJBIRKIN]

Well i'm glad something works, if you have any more questions dont ask me!! only joking.

Chris