IIF statement driving me mad 1

[1DMF]

Hello, back again with another IIF nightmare, logically I can't see anything wrong with this IIF, can some one tell me why it is erroring.

Between IIf([Please enter visit start date - leave blank for all] Is Null,"10/01/1971",[Please enter visit start date - leave blank for all]) And IIf([Please enter visit end date - leave blank for all] Is Null,left(now(),10),[Please enter visit end date - leave blank for all])

error......

the expression is typed incorrectly,or is too complex to be evaluated......try simplifying the expression or assigning parts of the expression to variables

Well i've done that by creating 2 expressions dte1 & dte2

so the iif is the expression for the dte1 & dte2 like so

dte1 : IIf([Please enter visit start date - leave blank for all] Is Null,"10/01/1971",[Please enter visit start date - leave blank for all])

etc.. and the between is simply

Between [dte1] and [dte2]

but for some reason it keeps asking for input for dte1 & dte2 , so assinging the iff to a variable as the message suggests does not work.

If I remove the between criterian and display the dte1 & dte2 expressions they are the data expected, either the date you enter or the default made by the iif, this is driving me mad, can someone please help restore my hairline

"In complete darkness we are all the same, only our knowledge and wisdom separates us, don't let your eyes deceive you.

 

[PHV]

You may try this:
Between Nz([Please enter visit start date - leave blank for all], #1971-10-01#) And Nz([Please enter visit end date - leave blank for all], Date())

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ181-2886

 

[Crowley16]

how's about using more sensible names for your columns?!

and when you use now, don't put a left on it.

presumably you're using this in a where clause, if the sql statement is very complicated, it could cause it to be too complicated...

--------------------
Procrastinate Now!

 

[1DMF]

You're a diamond PHV, so obvious too, using Nz, K.I.S.S.

"In complete darkness we are all the same, only our knowledge and wisdom separates us, don't let your eyes deceive you.

 

[1DMF]

p.s. left(now(),10) works fine!

"In complete darkness we are all the same, only our knowledge and wisdom separates us, don't let your eyes deceive you.

 

[1DMF]

how do you mean more sensible column names ?

"In complete darkness we are all the same, only our knowledge and wisdom separates us, don't let your eyes deceive you.

 

[Crowley16]

lol, something shorter would be nice...

your displays should be handled by the form/report so in the sql you should just have short names really.

also, if you really do need a long descriptive column name like an export from the query, you can use aliases to output the name to but retain the short system name.

--------------------
Procrastinate Now!

 

[1DMF]

I beleive in making field names that give a good indication of what is held in them, it was how I was taught.

[Membership_Number] is much better than [MNo], I always thought this was "Good Practices", just like commenting your code, anyone else have an opinion ?

"In complete darkness we are all the same, only our knowledge and wisdom separates us, don't let your eyes deceive you.

 

[Crowley16]

[Membership_Number] is fine, however [Please enter visit start date - leave blank for all] is just plain ridiculous...

usually I'll abreviate column names as well, e.g. memNum = MembershipNumber. Seems more than clear enough

there should be a nameing scheme which needs to be adhered to completely...

--------------------
Procrastinate Now!

 

[1DMF]

huh sorry I think you've miss understood the usage

[Please enter visit start date - leave blank for all]

is NOT a column name, it's an input query field to request input from the user into a query, and that is the correct syntax to use, this has nothing to do with table columns.

"In complete darkness we are all the same, only our knowledge and wisdom separates us, don't let your eyes deceive you.

 

[Crowley16]

then why not have a form to enter this, and then open up the form passing in the parameters?

you can do checking on the form and have whatever descriptions you need...

--------------------
Procrastinate Now!

 

[1DMF]

far to much like hard work when access offers the facility to do this simply with the query.

design a form, put fields on it, do loads of data checking, ensure form is open so the query can use the fields on the form.

I do this for more complex reporting , but not when all a report needs is a simple date input.

it also increases the size of the DB, which is a premium when converting to MDE, if the DB has to many controls I have found it won't convert to MDE.



"In complete darkness we are all the same, only our knowledge and wisdom separates us, don't let your eyes deceive you.