Queries/Forms not working

D

David Carter

Hi
I have a form which calculates dates. This works perfectly.
However, when I cut and paste the expression into a query, it doesn't work.
Anyone any idea why????
Thanks
Dave
 
B

Barry Jon

What is the expression you are using - is it an inbuilt function or a custom
function? If it is an custom function where and how is it declared?. If it
is a custom function that you want to use in queries and forms it needs to
be a public function and shouldn't be declared in a single forms module.
 
D

David Carter

Barry
Thankyou for the reply.
It seems I have reached the limit of my knowledge!
Not sure what you mean regarding public functions and modules.
It is an expression that uses IIf , DateDiff and DateAdd functions all
nested together

This is the expression
=IIf([MATERIAL]=1,IIf(DateDiff("m",[CURE],[FIT
DATE])+IIf([HD]=1,48,96)>120,DateAdd("m",120,[CURE]),DateAdd("m",IIf([HD]=1,48,96),[FIT
DATE])),IIf(DateDiff("m",[CURE],[FIT
DATE])+IIf([HD]=1,48,96)>180,DateAdd("m",180,[CURE]),DateAdd("m",IIf([HD]=1,48,96),[FIT
DATE])))

As I said, it works fine in a form.
Thanks for taking the time to help
Dave
 
D

David Carter

Also, I am now getting a message which says
"Syntax error (comma) in query expression"
Am I correct in thinking that an expression for a form should work in a
Query?
Thanks
Dave


David Carter said:
Barry
Thankyou for the reply.
It seems I have reached the limit of my knowledge!
Not sure what you mean regarding public functions and modules.
It is an expression that uses IIf , DateDiff and DateAdd functions all
nested together

This is the expression
=IIf([MATERIAL]=1,IIf(DateDiff("m",[CURE],[FIT
DATE])+IIf([HD]=1,48,96)>120,DateAdd("m",120,[CURE]),DateAdd("m",IIf([HD]=1,48,96),[FIT
DATE])),IIf(DateDiff("m",[CURE],[FIT
DATE])+IIf([HD]=1,48,96)>180,DateAdd("m",180,[CURE]),DateAdd("m",IIf([HD]=1,48,96),[FIT
DATE])))

As I said, it works fine in a form.
Thanks for taking the time to help
Dave

Barry Jon said:
What is the expression you are using - is it an inbuilt function or a
custom function? If it is an custom function where and how is it
declared?. If it is a custom function that you want to use in queries
and forms it needs to be a public function and shouldn't be declared in a
single forms module.
Click to expand...
Click to expand...
 
D

Duane Hookom

Using "=IIf(..." in a query generally doesn't work. You woudl need
TheColumnName: IIf(...)
I would never use an expression this complex in an expression anywhere. You
are much better off creating a user-defined function that accepts the
Material, Cure, FitDate and HD fields and returns the value you need.

You would write the function once, maintain it in one place and use it in
forms, reports, queries, code,...

--
Duane Hookom
MS Access MVP


David Carter said:
Barry
Thankyou for the reply.
It seems I have reached the limit of my knowledge!
Not sure what you mean regarding public functions and modules.
It is an expression that uses IIf , DateDiff and DateAdd functions all
nested together

This is the expression
=IIf([MATERIAL]=1,IIf(DateDiff("m",[CURE],[FIT
DATE])+IIf([HD]=1,48,96)>120,DateAdd("m",120,[CURE]),DateAdd("m",IIf([HD]=1,48,96),[FIT
DATE])),IIf(DateDiff("m",[CURE],[FIT
DATE])+IIf([HD]=1,48,96)>180,DateAdd("m",180,[CURE]),DateAdd("m",IIf([HD]=1,48,96),[FIT
DATE])))

As I said, it works fine in a form.
Thanks for taking the time to help
Dave

Barry Jon said:
What is the expression you are using - is it an inbuilt function or a
custom function? If it is an custom function where and how is it
declared?. If it is a custom function that you want to use in queries
and forms it needs to be a public function and shouldn't be declared in a
single forms module.
Click to expand...
Click to expand...
 
Top