I have a table containing

[Henro]

23 fields. One of them is a name [MIS]![Engineer]
The other ones are all date/time fields in which they fill in hours.
I have a query based on that table. I use the criterium Is Not Null on a
certain field to find out which engineer when used that particular field I
set the criterium for. Now, what I would like to do is use VB to decide
which of the 22 other fields should have the criterium Is Not Null.

How can I, using VBA, decide which field should have the criterium Is Not
Null?

Thnx for thinking!

Henro

 

[Dirk Goldgar]

23 fields. One of them is a name [MIS]![Engineer]
The other ones are all date/time fields in which they fill in hours.
I have a query based on that table. I use the criterium Is Not Null
on a certain field to find out which engineer when used that
particular field I set the criterium for. Now, what I would like to
do is use VB to decide which of the 22 other fields should have the
criterium Is Not Null.

How can I, using VBA, decide which field should have the criterium Is
Not Null?

Thnx for thinking!

I'd be happy to think on your behalf, Henro, but I have no idea what you
mean! What, in plain words, is supposed to decide whether some field
should have the criterion "Is Not Null"? What do you want the query to
return?

 

[Henro]

Ok, it is complex, I will try to explain:

I have 11 engineers. They have 22 different tasks (task1, task2 task3...
etc) Whenever they spent time on a task they fill in the hours they spent on
that task

Engineer date Task1 Task2 Task3 etc etc

Henro 1-1 1:00 ___ 2:00
John 1-1 ___ 2:00 1:00

By putting these fields in a query and setting the criterium on Task3 as Is
Not Null I can see that Henro and John spent time on Task3 and how much.
Now I would like to be able to use VBA to decide which field should have the
criterium Is Not Null so that I can choose which Task I want to look at.
The moment I want a closer look at a certain task I do not need to see the
other tasks

So the query would look like:

Engineer date Task1 Task2 Task3
etc etc
Is Not Null

I want to decide where the 'Is Not Null' is placed

Is that a little clearer?

Thnx for reading

H.

23 fields. One of them is a name [MIS]![Engineer]
The other ones are all date/time fields in which they fill in hours.
I have a query based on that table. I use the criterium Is Not Null
on a certain field to find out which engineer when used that
particular field I set the criterium for. Now, what I would like to
do is use VB to decide which of the 22 other fields should have the
criterium Is Not Null.

How can I, using VBA, decide which field should have the criterium Is
Not Null?

Thnx for thinking!

I'd be happy to think on your behalf, Henro, but I have no idea what you
mean! What, in plain words, is supposed to decide whether some field
should have the criterion "Is Not Null"? What do you want the query to
return?

--
Dirk Goldgar, MS Access MVP
www.datagnostics.com

(please reply to the newsgroup)

 

[Dirk Goldgar]

Ok, it is complex, I will try to explain:

I have 11 engineers. They have 22 different tasks (task1, task2
task3... etc) Whenever they spent time on a task they fill in the
hours they spent on that task

Engineer date Task1 Task2 Task3 etc etc

Henro 1-1 1:00 ___ 2:00
John 1-1 ___ 2:00 1:00

By putting these fields in a query and setting the criterium on Task3
as Is Not Null I can see that Henro and John spent time on Task3 and
how much. Now I would like to be able to use VBA to decide which
field should have the criterium Is Not Null so that I can choose
which Task I want to look at. The moment I want a closer look at a
certain task I do not need to see the other tasks

So the query would look like:

Engineer date Task1 Task2 Task3
etc etc
Is
Not Null

I want to decide where the 'Is Not Null' is placed

Is that a little clearer?

Do you realise that a table with fields like Task1, Task2, Task3 and so
on is badly normalized and hard to query? A better design would be
based on a tables like these:

Engineers
----------
EngineerID (primary key)
EngineerName
(other fields)

Tasks
------
TaskID (primary key)
TaskName
(other fields)

TasksPerformed
-----------------
EngineerID (foreign key to Engineers)
TaskID (foreign key to Tasks)
DatePerformed
Hours

Then you could see who worked on Task 1, when, and how much, by using a
query like this:

SELECT * FROM TasksPerformed
WHERE TaskID = 1;

Or, as a parameter query ...

SELECT * FROM TasksPerformed
WHERE TaskID = [Enter Task ID];

That way, you don't have lots of Nulls in your data and the records are
easy to query and manipulate. You can use a crosstab query if you need
to present the data with the tasks listed horozontally, but it's not a
good way to store the data.

If you insist on using your current table structure, you need to use a
form to capture the desired choice of task, then rewrite the
recordsource query of a subform or list box to apply your criterion to
the correct field.

 

[Henro]

I agree on the normalization thing but when I build it i did know sh*t.
I know now it _is_ sh*t :so there is progress

But seriously, my boss won't allow me the time to rewrite that so I have to
work with it :-(
But I understand onecannot easily use VBA to change the criteria of an
existing query?

H.

Ok, it is complex, I will try to explain:

I have 11 engineers. They have 22 different tasks (task1, task2
task3... etc) Whenever they spent time on a task they fill in the
hours they spent on that task

Engineer date Task1 Task2 Task3 etc etc

Henro 1-1 1:00 ___ 2:00
John 1-1 ___ 2:00 1:00

By putting these fields in a query and setting the criterium on Task3
as Is Not Null I can see that Henro and John spent time on Task3 and
how much. Now I would like to be able to use VBA to decide which
field should have the criterium Is Not Null so that I can choose
which Task I want to look at. The moment I want a closer look at a
certain task I do not need to see the other tasks

So the query would look like:

Engineer date Task1 Task2 Task3
etc etc
Is
Not Null

I want to decide where the 'Is Not Null' is placed

Is that a little clearer?

Do you realise that a table with fields like Task1, Task2, Task3 and so
on is badly normalized and hard to query? A better design would be
based on a tables like these:

Engineers
----------
EngineerID (primary key)
EngineerName
(other fields)

Tasks
------
TaskID (primary key)
TaskName
(other fields)

TasksPerformed
-----------------
EngineerID (foreign key to Engineers)
TaskID (foreign key to Tasks)
DatePerformed
Hours

Then you could see who worked on Task 1, when, and how much, by using a
query like this:

SELECT * FROM TasksPerformed
WHERE TaskID = 1;

Or, as a parameter query ...

SELECT * FROM TasksPerformed
WHERE TaskID = [Enter Task ID];

That way, you don't have lots of Nulls in your data and the records are
easy to query and manipulate. You can use a crosstab query if you need
to present the data with the tasks listed horozontally, but it's not a
good way to store the data.

If you insist on using your current table structure, you need to use a
form to capture the desired choice of task, then rewrite the
recordsource query of a subform or list box to apply your criterion to
the correct field.

--
Dirk Goldgar, MS Access MVP
www.datagnostics.com

(please reply to the newsgroup)

 

[Dirk Goldgar]

I agree on the normalization thing but when I build it i did know
sh*t.
I know now it _is_ sh*t :so there is progress

But seriously, my boss won't allow me the time to rewrite that so I
have to work with it :-(

I'm afraid you'll spend more time working around the faulty design than
you'd spend fixing it, but what can you do?

But I understand onecannot easily use VBA to change the criteria of an
existing query?

You can't change the criteria of a stored query very readily -- though
you can do it by getting the .SQL property of the QueryDef object,
parsing it out, changing it, and storing it back again -- but you can
easily just set the RowSource or RecordSource to a SQL statement that
you build dynamically using VBA. Then you don't need the stored query
at all.

 

[Henro]

Could you give a piece of code to illustrate that or point me to a place
where I can view such code?

H.

 

[Dirk Goldgar]

Could you give a piece of code to illustrate that or point me to a
place where I can view such code?

H.

Well, I don't know how you have this set up, but let's suppose your
table is named "TasksPerformed", and you have a form with a combo box
named "cboTask" that lists the tasks according to the names of the
fields in TasksPerformed; e.g., Task1, Task2, and so on. Suppose this
form also has a list box control named "lstTasksPerformed" that has
three columns which will be Engineer, DatePerformed, and hours. At
design time you set the RowSource of this list box to this SQL
statement:

SELECT Engineer, DatePerformed, Task1 As Hours
FROM TasksPerformed
WHERE False;

That will give you an initial state in which no records are returned.

Now, in the AfterUpdate event of cboTask you put this code:

'----- start of example code -----
Private Sub cboTask_AfterUdate()

Dim strTaskField As String
Dim strSQL As String

' Get selected task, or null string if none selected.
strTaskField = Me.cboTask & vbNullString

If Len(strTaskField) = 0 Then
strSQL = _
"SELECT Engineer, DatePerformed, Task1 As Hours " & _
"FROM TasksPerformed WHERE False;"
Else
strSQL = _
"SELECT Engineer, DatePerformed, " & _
strTaskField & "As Hours " & _
"FROM TasksPerformed WHERE " & _
strTaskField & " Is Not Null;"
End If

Me.lstTasksPerformed.RowSource = strSQL

End Sub
'----- end of example code -----

I think that ought to do it.

 

[Henro]

Well, I don't know how you have this set up, but let's suppose your
table is named "TasksPerformed", and you have a form with a combo box
named "cboTask" that lists the tasks according to the names of the
fields in TasksPerformed; e.g., Task1, Task2, and so on. Suppose this
form also has a list box control named "lstTasksPerformed" that has
three columns which will be Engineer, DatePerformed, and hours. At
design time you set the RowSource of this list box to this SQL
statement:

SELECT Engineer, DatePerformed, Task1 As Hours
FROM TasksPerformed
WHERE False;

That will give you an initial state in which no records are returned.

Now, in the AfterUpdate event of cboTask you put this code:

'----- start of example code -----
Private Sub cboTask_AfterUdate()

Dim strTaskField As String
Dim strSQL As String

' Get selected task, or null string if none selected.
strTaskField = Me.cboTask & vbNullString

If Len(strTaskField) = 0 Then
strSQL = _
"SELECT Engineer, DatePerformed, Task1 As Hours " & _
"FROM TasksPerformed WHERE False;"
Else
strSQL = _
"SELECT Engineer, DatePerformed, " & _
strTaskField & "As Hours " & _
"FROM TasksPerformed WHERE " & _
strTaskField & " Is Not Null;"
End If

Me.lstTasksPerformed.RowSource = strSQL

End Sub
'----- end of example code -----

I think that ought to do it.

--
Dirk Goldgar, MS Access MVP
www.datagnostics.com

(please reply to the newsgroup)

I think I understand that and I will try it.
Looks exactly like what I wanted. Thnx and i'll let you know if it worked!