Queries/Forms not working

[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

 

[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.

 

[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

 

[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

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

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.

 

[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

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

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.

 

[David Carter]

Thanks Duane
Neat question has to be:
How do I create a "User defined function"?
Thanks
Dave

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

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

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.

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

 

[Barry-Jon]

David,

From the database window goto modules and click new.

In the module you create a public function in the format;

Public Function NameOfFunction(lngMaterial As Long, dtFitDate As Date,
etc...) As Date

If lngMaterial = 1 Then
calculations if material =1
NameOfFunction = whatever
Else
calculations if material <>1
NameOfFunction = whatever else
End If

End Function

You obviously name the function something meaningful to the
application. You also give the function a return data type (I have
given the type as date - looked like that is what you are getting from
you nested iif). Within the brackets between the function name and the
function type you declare any variables that you want to pass to the
function, again you should declare their types). Within the function
you then do your various calculations/selections and at the end you set
the value of your function to the value you have arrived at. If I was
less tired I could try to figure out your iif's and write the function
for. Alternatively explain in english what you want to return from it.
Sorry but hope this helps.

Barry-Jon