T
Terry Hornsby
I have a colleague at work who wants to determine the field name from the
currently selected record in a query table & dynamically use this field name
in the select query statement of the same query table.
He was wanting to use a function which would convert the records in the
field (presumably the currently selected record) from a text value into a
date value & perform a datediff on it.
I argued that he couldn't do this because he would be altering the
underlying structure of the current query which would alter the cursor
position in the table which in turn would alter the query statement & so
on - into a loop.
The reason he wants to do this is because he has to create & run an
indefinite but large number of access query tables & perform the same
datediff calculation, but he doesn't know in advance what fields will
contain the dates he needs to work with & how many fields there will be.
This is because the raw data is imported from text files supplied by the
local council's IT department (created from running a routine on proprietary
software with a limited query language), where the dates could appear at any
position in the text string.
I had suggested to him that he prefix all the date fields with a key
identifier & number them consecutively & he could then loop through the
fields in code. But this would require an amount of manual work on each
database he creates, which he is not prepared to do (he needs REALLY fast &
dirty solutions).
Converting the text into a date & performing a datediff in a function is no
problem, but although you can determine the current record number in the
function, you can't determine the field. This has to be done in his query
statement, which means a manual editing job & we're back to the problem of
it not being quick enough.
So, my questions are:
1) Can a function be written which returns the correct record from the
correct field, namely the currently selected one?
Specific to this newsgroup: -
2) Is it impossible to change the underlying query statement of the
currently active query table when the change itself relies on a change
(i.e., detection of the currently selected field)
3) Could the way forward be to wrap the way the text file is imported within
a function of some kind which automatically reformats date strings to date
values as it populates the table & if so, can it be dynamic because the
position of the date field data would not be known in advance?
I was thinking of some kind of dynamic import data / createtable routine.
Can anyone give an example?
I've not cross-posted this, but understand that only one of the possible
solutions is relevant to this newsgroup. However, any solution would be
greatly appreciated.
Terry Hornsby
currently selected record in a query table & dynamically use this field name
in the select query statement of the same query table.
He was wanting to use a function which would convert the records in the
field (presumably the currently selected record) from a text value into a
date value & perform a datediff on it.
I argued that he couldn't do this because he would be altering the
underlying structure of the current query which would alter the cursor
position in the table which in turn would alter the query statement & so
on - into a loop.
The reason he wants to do this is because he has to create & run an
indefinite but large number of access query tables & perform the same
datediff calculation, but he doesn't know in advance what fields will
contain the dates he needs to work with & how many fields there will be.
This is because the raw data is imported from text files supplied by the
local council's IT department (created from running a routine on proprietary
software with a limited query language), where the dates could appear at any
position in the text string.
I had suggested to him that he prefix all the date fields with a key
identifier & number them consecutively & he could then loop through the
fields in code. But this would require an amount of manual work on each
database he creates, which he is not prepared to do (he needs REALLY fast &
dirty solutions).
Converting the text into a date & performing a datediff in a function is no
problem, but although you can determine the current record number in the
function, you can't determine the field. This has to be done in his query
statement, which means a manual editing job & we're back to the problem of
it not being quick enough.
So, my questions are:
1) Can a function be written which returns the correct record from the
correct field, namely the currently selected one?
Specific to this newsgroup: -
2) Is it impossible to change the underlying query statement of the
currently active query table when the change itself relies on a change
(i.e., detection of the currently selected field)
3) Could the way forward be to wrap the way the text file is imported within
a function of some kind which automatically reformats date strings to date
values as it populates the table & if so, can it be dynamic because the
position of the date field data would not be known in advance?
I was thinking of some kind of dynamic import data / createtable routine.
Can anyone give an example?
I've not cross-posted this, but understand that only one of the possible
solutions is relevant to this newsgroup. However, any solution would be
greatly appreciated.
Terry Hornsby