Using Live Functions in a Query

[Guest]

If I wanted to use the Time() function in an Append query to add to a Field
called [Time Entered] to represent the excat time the query appended that
record how do I do that?

When I used the value of Time() to append a field called [Time Entered] all
the records had the same value in that field.

I even created a Function to generate a random Time -rdmTime()- and yet all
the records still had the same value.

Does the query run the function once and uses that same value for the
duration of the query? If so how do I tell the query to run the function
again to get me a fresh value?

 

[Marshall Barton]

If I wanted to use the Time() function in an Append query to add to a Field
called [Time Entered] to represent the excat time the query appended that
record how do I do that?

When I used the value of Time() to append a field called [Time Entered] all
the records had the same value in that field.

I even created a Function to generate a random Time -rdmTime()- and yet all
the records still had the same value.

Does the query run the function once and uses that same value for the
duration of the query? If so how do I tell the query to run the function
again to get me a fresh value?

The query optimizer determines if the function needs to be
evaluated for each record or if one time is sufficient. It
makes this determination based on the arguments used to call
the function. If the arguments do not change from one
record to another, then the function only needs to be
evaluated once. If an argument includes a field from the
table/query, then the function must be called for every
record.

This is easy to deal with in your function:

Public Function rndTime(xxx As Variant) As Date
' your code, which can ignore the argument
End Function

Then use the function in your query this way:
SELECT ..., rndTime([your table].[any field])
FROM [your table]

Be aware that a query will process records in any order and
as many times as it feels are appropriate to a given
situation. This means that you can not readily create a
function to calculate something like a "roe number".

 

[Guest]

Excellent it works, but It takes forever, and I can't interrupt the query.
Everytime I try to break it and reset the code window, the query resumes and
I have to close the whole database program.

If I wanted to use the Time() function in an Append query to add to a Field
called [Time Entered] to represent the excat time the query appended that
record how do I do that?

When I used the value of Time() to append a field called [Time Entered] all
the records had the same value in that field.

I even created a Function to generate a random Time -rdmTime()- and yet all
the records still had the same value.

Does the query run the function once and uses that same value for the
duration of the query? If so how do I tell the query to run the function
again to get me a fresh value?

The query optimizer determines if the function needs to be
evaluated for each record or if one time is sufficient. It
makes this determination based on the arguments used to call
the function. If the arguments do not change from one
record to another, then the function only needs to be
evaluated once. If an argument includes a field from the
table/query, then the function must be called for every
record.

This is easy to deal with in your function:

Public Function rndTime(xxx As Variant) As Date
' your code, which can ignore the argument
End Function

Then use the function in your query this way:
SELECT ..., rndTime([your table].[any field])
FROM [your table]

Be aware that a query will process records in any order and
as many times as it feels are appropriate to a given
situation. This means that you can not readily create a
function to calculate something like a "roe number".

 

[Guest]

Two items, Marshall.
1. All the times being the same may be caused by the fact that the query is
working with a small number of records and it executes quickly enough that
there is no, or least very little, difference in the timestamp applied to the
records.

2. I found through experimentation that a UDF in a query will execute for
every row provided there is any arugment passed. It doesn't have to be a
field in the table. Just for grins I tried
DummyFld: SomeFunction("")
and it worked.

If I wanted to use the Time() function in an Append query to add to a Field
called [Time Entered] to represent the excat time the query appended that
record how do I do that?

When I used the value of Time() to append a field called [Time Entered] all
the records had the same value in that field.

I even created a Function to generate a random Time -rdmTime()- and yet all
the records still had the same value.

Does the query run the function once and uses that same value for the
duration of the query? If so how do I tell the query to run the function
again to get me a fresh value?

The query optimizer determines if the function needs to be
evaluated for each record or if one time is sufficient. It
makes this determination based on the arguments used to call
the function. If the arguments do not change from one
record to another, then the function only needs to be
evaluated once. If an argument includes a field from the
table/query, then the function must be called for every
record.

This is easy to deal with in your function:

Public Function rndTime(xxx As Variant) As Date
' your code, which can ignore the argument
End Function

Then use the function in your query this way:
SELECT ..., rndTime([your table].[any field])
FROM [your table]

Be aware that a query will process records in any order and
as many times as it feels are appropriate to a given
situation. This means that you can not readily create a
function to calculate something like a "roe number".

 

[Marshall Barton]

Your first item is quite likely to happen.

Your second observation is a new one on me. When I try a
query like:
SELECT tbl.*, Rnd(1) As X, Rnd(ID) As Y
FROM tbl

I get the same result for field X in every row, but
different values for field Y.
--
Marsh
MVP [MS Access]

Two items, Marshall.
1. All the times being the same may be caused by the fact that the query is
working with a small number of records and it executes quickly enough that
there is no, or least very little, difference in the timestamp applied to the
records.

2. I found through experimentation that a UDF in a query will execute for
every row provided there is any arugment passed. It doesn't have to be a
field in the table. Just for grins I tried
DummyFld: SomeFunction("")
and it worked.

If I wanted to use the Time() function in an Append query to add to a Field
called [Time Entered] to represent the excat time the query appended that
record how do I do that?

When I used the value of Time() to append a field called [Time Entered] all
the records had the same value in that field.

I even created a Function to generate a random Time -rdmTime()- and yet all
the records still had the same value.

Does the query run the function once and uses that same value for the
duration of the query? If so how do I tell the query to run the function
again to get me a fresh value?

The query optimizer determines if the function needs to be
evaluated for each record or if one time is sufficient. It
makes this determination based on the arguments used to call
the function. If the arguments do not change from one
record to another, then the function only needs to be
evaluated once. If an argument includes a field from the
table/query, then the function must be called for every
record.

This is easy to deal with in your function:

Public Function rndTime(xxx As Variant) As Date
' your code, which can ignore the argument
End Function

Then use the function in your query this way:
SELECT ..., rndTime([your table].[any field])
FROM [your table]

Be aware that a query will process records in any order and
as many times as it feels are appropriate to a given
situation. This means that you can not readily create a
function to calculate something like a "roe number".

 

[Guest]

Remove the Rnd(ID) and see what happens. That will cause it to execute for
each row whether the Rnd(1) does or not.

Your first item is quite likely to happen.

Your second observation is a new one on me. When I try a
query like:
SELECT tbl.*, Rnd(1) As X, Rnd(ID) As Y
FROM tbl

I get the same result for field X in every row, but
different values for field Y.
--
Marsh
MVP [MS Access]

Two items, Marshall.
1. All the times being the same may be caused by the fact that the query is
working with a small number of records and it executes quickly enough that
there is no, or least very little, difference in the timestamp applied to the
records.

2. I found through experimentation that a UDF in a query will execute for
every row provided there is any arugment passed. It doesn't have to be a
field in the table. Just for grins I tried
DummyFld: SomeFunction("")
and it worked.

The Flash wrote:

If I wanted to use the Time() function in an Append query to add to a Field
called [Time Entered] to represent the excat time the query appended that
record how do I do that?

When I used the value of Time() to append a field called [Time Entered] all
the records had the same value in that field.

I even created a Function to generate a random Time -rdmTime()- and yet all
the records still had the same value.

Does the query run the function once and uses that same value for the
duration of the query? If so how do I tell the query to run the function
again to get me a fresh value?


The query optimizer determines if the function needs to be
evaluated for each record or if one time is sufficient. It
makes this determination based on the arguments used to call
the function. If the arguments do not change from one
record to another, then the function only needs to be
evaluated once. If an argument includes a field from the
table/query, then the function must be called for every
record.

This is easy to deal with in your function:

Public Function rndTime(xxx As Variant) As Date
' your code, which can ignore the argument
End Function

Then use the function in your query this way:
SELECT ..., rndTime([your table].[any field])
FROM [your table]

Be aware that a query will process records in any order and
as many times as it feels are appropriate to a given
situation. This means that you can not readily create a
function to calculate something like a "roe number".

 

[Guest]

When I run
SELECT [ZCT TEMPFILE].First_Name, RndTime(1) AS X
FROM [ZCT TEMPFILE]
WITH OWNERACCESS OPTION;
I also get the same values

Remove the Rnd(ID) and see what happens. That will cause it to execute for
each row whether the Rnd(1) does or not.

Your first item is quite likely to happen.

Your second observation is a new one on me. When I try a
query like:
SELECT tbl.*, Rnd(1) As X, Rnd(ID) As Y
FROM tbl

I get the same result for field X in every row, but
different values for field Y.
--
Marsh
MVP [MS Access]

Two items, Marshall.
1. All the times being the same may be caused by the fact that the query is
working with a small number of records and it executes quickly enough that
there is no, or least very little, difference in the timestamp applied to the
records.

2. I found through experimentation that a UDF in a query will execute for
every row provided there is any arugment passed. It doesn't have to be a
field in the table. Just for grins I tried
DummyFld: SomeFunction("")
and it worked.

:

The Flash wrote:

If I wanted to use the Time() function in an Append query to add to a Field
called [Time Entered] to represent the excat time the query appended that
record how do I do that?

When I used the value of Time() to append a field called [Time Entered] all
the records had the same value in that field.

I even created a Function to generate a random Time -rdmTime()- and yet all
the records still had the same value.

Does the query run the function once and uses that same value for the
duration of the query? If so how do I tell the query to run the function
again to get me a fresh value?


The query optimizer determines if the function needs to be
evaluated for each record or if one time is sufficient. It
makes this determination based on the arguments used to call
the function. If the arguments do not change from one
record to another, then the function only needs to be
evaluated once. If an argument includes a field from the
table/query, then the function must be called for every
record.

This is easy to deal with in your function:

Public Function rndTime(xxx As Variant) As Date
' your code, which can ignore the argument
End Function

Then use the function in your query this way:
SELECT ..., rndTime([your table].[any field])
FROM [your table]

Be aware that a query will process records in any order and
as many times as it feels are appropriate to a given
situation. This means that you can not readily create a
function to calculate something like a "roe number".

 

[Guest]

When I run
SELECT [ZCT TEMPFILE].First_Name, RndNum(1) AS X
FROM [ZCT TEMPFILE]
WITH OWNERACCESS OPTION;
I also get the same values

Remove the Rnd(ID) and see what happens. That will cause it to execute for
each row whether the Rnd(1) does or not.

Your first item is quite likely to happen.

Your second observation is a new one on me. When I try a
query like:
SELECT tbl.*, Rnd(1) As X, Rnd(ID) As Y
FROM tbl

I get the same result for field X in every row, but
different values for field Y.
--
Marsh
MVP [MS Access]

Two items, Marshall.
1. All the times being the same may be caused by the fact that the query is
working with a small number of records and it executes quickly enough that
there is no, or least very little, difference in the timestamp applied to the
records.

2. I found through experimentation that a UDF in a query will execute for
every row provided there is any arugment passed. It doesn't have to be a
field in the table. Just for grins I tried
DummyFld: SomeFunction("")
and it worked.

:

The Flash wrote:

If I wanted to use the Time() function in an Append query to add to a Field
called [Time Entered] to represent the excat time the query appended that
record how do I do that?

When I used the value of Time() to append a field called [Time Entered] all
the records had the same value in that field.

I even created a Function to generate a random Time -rdmTime()- and yet all
the records still had the same value.

Does the query run the function once and uses that same value for the
duration of the query? If so how do I tell the query to run the function
again to get me a fresh value?


The query optimizer determines if the function needs to be
evaluated for each record or if one time is sufficient. It
makes this determination based on the arguments used to call
the function. If the arguments do not change from one
record to another, then the function only needs to be
evaluated once. If an argument includes a field from the
table/query, then the function must be called for every
record.

This is easy to deal with in your function:

Public Function rndTime(xxx As Variant) As Date
' your code, which can ignore the argument
End Function

Then use the function in your query this way:
SELECT ..., rndTime([your table].[any field])
FROM [your table]

Be aware that a query will process records in any order and
as many times as it feels are appropriate to a given
situation. This means that you can not readily create a
function to calculate something like a "roe number".

 

[Guest]

When I run
SELECT [ZCT TEMPFILE].First_Name, RndNumber(1) AS X
FROM [ZCT TEMPFILE]
WITH OWNERACCESS OPTION;
I also get the same values.

Remove the Rnd(ID) and see what happens. That will cause it to execute for
each row whether the Rnd(1) does or not.

Your first item is quite likely to happen.

Your second observation is a new one on me. When I try a
query like:
SELECT tbl.*, Rnd(1) As X, Rnd(ID) As Y
FROM tbl

I get the same result for field X in every row, but
different values for field Y.
--
Marsh
MVP [MS Access]

Two items, Marshall.
1. All the times being the same may be caused by the fact that the query is
working with a small number of records and it executes quickly enough that
there is no, or least very little, difference in the timestamp applied to the
records.

2. I found through experimentation that a UDF in a query will execute for
every row provided there is any arugment passed. It doesn't have to be a
field in the table. Just for grins I tried
DummyFld: SomeFunction("")
and it worked.

:

The Flash wrote:

If I wanted to use the Time() function in an Append query to add to a Field
called [Time Entered] to represent the excat time the query appended that
record how do I do that?

When I used the value of Time() to append a field called [Time Entered] all
the records had the same value in that field.

I even created a Function to generate a random Time -rdmTime()- and yet all
the records still had the same value.

Does the query run the function once and uses that same value for the
duration of the query? If so how do I tell the query to run the function
again to get me a fresh value?


The query optimizer determines if the function needs to be
evaluated for each record or if one time is sufficient. It
makes this determination based on the arguments used to call
the function. If the arguments do not change from one
record to another, then the function only needs to be
evaluated once. If an argument includes a field from the
table/query, then the function must be called for every
record.

This is easy to deal with in your function:

Public Function rndTime(xxx As Variant) As Date
' your code, which can ignore the argument
End Function

Then use the function in your query this way:
SELECT ..., rndTime([your table].[any field])
FROM [your table]

Be aware that a query will process records in any order and
as many times as it feels are appropriate to a given
situation. This means that you can not readily create a
function to calculate something like a "roe number".

 

[Marshall Barton]

Maybe the problem is in your function. Post a Copy/Paste of
the function's code.
--
Marsh
MVP [MS Access]

When I run
SELECT [ZCT TEMPFILE].First_Name, RndNumber(1) AS X
FROM [ZCT TEMPFILE]
WITH OWNERACCESS OPTION;
I also get the same values.

Remove the Rnd(ID) and see what happens. That will cause it to execute for
each row whether the Rnd(1) does or not.

Your first item is quite likely to happen.

Your second observation is a new one on me. When I try a
query like:
SELECT tbl.*, Rnd(1) As X, Rnd(ID) As Y
FROM tbl

I get the same result for field X in every row, but
different values for field Y.


Klatuu wrote:
Two items, Marshall.
1. All the times being the same may be caused by the fact that the query is
working with a small number of records and it executes quickly enough that
there is no, or least very little, difference in the timestamp applied to the
records.

2. I found through experimentation that a UDF in a query will execute for
every row provided there is any arugment passed. It doesn't have to be a
field in the table. Just for grins I tried
DummyFld: SomeFunction("")
and it worked.

:

The Flash wrote:

If I wanted to use the Time() function in an Append query to add to a Field
called [Time Entered] to represent the excat time the query appended that
record how do I do that?

When I used the value of Time() to append a field called [Time Entered] all
the records had the same value in that field.

I even created a Function to generate a random Time -rdmTime()- and yet all
the records still had the same value.

Does the query run the function once and uses that same value for the
duration of the query? If so how do I tell the query to run the function
again to get me a fresh value?


The query optimizer determines if the function needs to be
evaluated for each record or if one time is sufficient. It
makes this determination based on the arguments used to call
the function. If the arguments do not change from one
record to another, then the function only needs to be
evaluated once. If an argument includes a field from the
table/query, then the function must be called for every
record.

This is easy to deal with in your function:

Public Function rndTime(xxx As Variant) As Date
' your code, which can ignore the argument
End Function

Then use the function in your query this way:
SELECT ..., rndTime([your table].[any field])
FROM [your table]

Be aware that a query will process records in any order and
as many times as it feels are appropriate to a given
situation. This means that you can not readily create a
function to calculate something like a "roe number".

 

[Marshall Barton]

I don't understand your point, Klatuu. When I run:

SELECT tbl.*, Rnd(1) As X
FROM tbl

every record gets the same value for X, same as before.

It appears to me that I must use Rnd(ID) to call the
function for each record.
--
Marsh
MVP [MS Access]

Remove the Rnd(ID) and see what happens. That will cause it to execute for
each row whether the Rnd(1) does or not.

Your first item is quite likely to happen.

Your second observation is a new one on me. When I try a
query like:
SELECT tbl.*, Rnd(1) As X, Rnd(ID) As Y
FROM tbl

I get the same result for field X in every row, but
different values for field Y.

Two items, Marshall.
1. All the times being the same may be caused by the fact that the query is
working with a small number of records and it executes quickly enough that
there is no, or least very little, difference in the timestamp applied to the
records.

2. I found through experimentation that a UDF in a query will execute for
every row provided there is any arugment passed. It doesn't have to be a
field in the table. Just for grins I tried
DummyFld: SomeFunction("")
and it worked.

:

The Flash wrote:

If I wanted to use the Time() function in an Append query to add to a Field
called [Time Entered] to represent the excat time the query appended that
record how do I do that?

When I used the value of Time() to append a field called [Time Entered] all
the records had the same value in that field.

I even created a Function to generate a random Time -rdmTime()- and yet all
the records still had the same value.

Does the query run the function once and uses that same value for the
duration of the query? If so how do I tell the query to run the function
again to get me a fresh value?


The query optimizer determines if the function needs to be
evaluated for each record or if one time is sufficient. It
makes this determination based on the arguments used to call
the function. If the arguments do not change from one
record to another, then the function only needs to be
evaluated once. If an argument includes a field from the
table/query, then the function must be called for every
record.

This is easy to deal with in your function:

Public Function rndTime(xxx As Variant) As Date
' your code, which can ignore the argument
End Function

Then use the function in your query this way:
SELECT ..., rndTime([your table].[any field])
FROM [your table]

Be aware that a query will process records in any order and
as many times as it feels are appropriate to a given
situation. This means that you can not readily create a
function to calculate something like a "roe number".