Timetable

[Duane Hookom]

And which text boxes do you wish to position? All of your un-normalized
"test" fields? Are all of these date fields that you want to plot within the
date range you want to display? If not, what would you want to do with dates
that don't fall between the start and end dates?

--
Duane Hookom
MS Access MVP

Duane,

Thanks for getting back to me. I really do appreciate it.

My fields are as follows:

E-Form ID (Primary Key)
Form Number
Form (This is the description of the E-Form)
Test 1
Test 2
Test 3
Test 4
Test 5
Test 6
Test 7

All test fields are date/time formats.

I can confirm that the names of the controls are exactly the same as the
fields which, incidentally are all text boxes.

I've tried and like I said I feel I cracked it in getting the months in
the
timline to run from April and to continue for 12 months. With regards to
the
actual scale I would like, if possible for this to be weekly, would that
be
ok?

Once again Duane, many thanks for helping me.

Kind regards

Chris

Can you provide your report's record source field like:

ID primary key
FieldName1 Datatype
FieldName2 Datatype (use this field to position horizontally)
FieldName3 Datatype (display this one on time line)
FieldName4 Datatype (display this one on time line)
FieldName5 Datatype (use this field to position horizontally)

Can we assume the names of the controls are the same as the fields? If
not,
provide the control names and sources.

We also need to know what value starts your scale on the left and how the
scale is determined (6" = 2 weeks or whatever)

Duane,

[quoted text clipped - 328 lines]

you
mean?

 

[hobbit2612 via AccessMonster.com]

Hi Duane,

I would like to position all of the test dates under the timeline range.

So for example:

For 'E-form 1' I perfomed 'Test 1' on 10/05/06 and this would be plotted
under the relevant week as one colour then I carried out 'Test 2' on the
17/09/06, and again this would be plotted under the relevant date in the
timeline as a different colour.

So in essence the report will show blocks of colour against each form for the
different tests that are carried our.

When you say what would I do with the dates that don't fall within the start
and end dates, do you mean between the April 06 and March 07 I set as my
start and end months on the timeline. If i've got this correct, then there
will never be a test date that will not fit within this time period. They are
year on year test.

Hope this helps.

Kind regards

Chris

And which text boxes do you wish to position? All of your un-normalized
"test" fields? Are all of these date fields that you want to plot within the
date range you want to display? If not, what would you want to do with dates
that don't fall between the start and end dates?

Duane,

[quoted text clipped - 52 lines]

 

[Duane Hookom]

I would rename the text boxes to something like "txtTest1" to get rid of
spaces and ambiguity. Change their widths and colors to match what you want.
Note, there is a rectangle (boxTimeLine) in the page header that is the
length of your date range. This should not go all the way to your right
margin if you have a date that might get position off the page to the right.

You should be able to position each of your date text boxes with code like:

Private Sub Detail_Format(Cancel As Integer, FormatCount As Integer)
Dim lngStart As Long 'start date of tour
Dim lngLMarg As Long
Dim dblFactor As Double
Dim datStart As Date
Dim datEnd as Date
datStart = #4/1/2006#
datEnd = #3/31/2007#
lngLMarg = Me.boxTimeLine.Left
'get the length of a single date on the timeline
dblFactor = Me.boxTimeLine.Width / DateDiff("d",datStart , datEnd)
For intTextBox = 1 to 7
lngStart = DateDiff("d", datStart, Me("txtTest" & intTextBox)
Me("txtText" & intTextBox).Left = (lngStart * dblFactor) + lngLMarg
Next
End Sub


--
Duane Hookom
MS Access MVP

Hi Duane,

I would like to position all of the test dates under the timeline range.

So for example:

For 'E-form 1' I perfomed 'Test 1' on 10/05/06 and this would be plotted
under the relevant week as one colour then I carried out 'Test 2' on the
17/09/06, and again this would be plotted under the relevant date in the
timeline as a different colour.

So in essence the report will show blocks of colour against each form for
the
different tests that are carried our.

When you say what would I do with the dates that don't fall within the
start
and end dates, do you mean between the April 06 and March 07 I set as my
start and end months on the timeline. If i've got this correct, then there
will never be a test date that will not fit within this time period. They
are
year on year test.

Hope this helps.

Kind regards

Chris

And which text boxes do you wish to position? All of your un-normalized
"test" fields? Are all of these date fields that you want to plot within
the
date range you want to display? If not, what would you want to do with
dates
that don't fall between the start and end dates?

Duane,

[quoted text clipped - 52 lines]

you
mean?

 

[hobbit2612 via AccessMonster.com]

Duane,

Many thanks for taking the time to write this for me. I really do appreciate
it.

It may take me a while to get through it, to see what I need to put in my
reports and queries to conicide with the code.

But I'll let you know how I get on.

Many thanks once again

Regards

Chris

I would rename the text boxes to something like "txtTest1" to get rid of
spaces and ambiguity. Change their widths and colors to match what you want.
Note, there is a rectangle (boxTimeLine) in the page header that is the
length of your date range. This should not go all the way to your right
margin if you have a date that might get position off the page to the right.

You should be able to position each of your date text boxes with code like:

Private Sub Detail_Format(Cancel As Integer, FormatCount As Integer)
Dim lngStart As Long 'start date of tour
Dim lngLMarg As Long
Dim dblFactor As Double
Dim datStart As Date
Dim datEnd as Date
datStart = #4/1/2006#
datEnd = #3/31/2007#
lngLMarg = Me.boxTimeLine.Left
'get the length of a single date on the timeline
dblFactor = Me.boxTimeLine.Width / DateDiff("d",datStart , datEnd)
For intTextBox = 1 to 7
lngStart = DateDiff("d", datStart, Me("txtTest" & intTextBox)
Me("txtText" & intTextBox).Left = (lngStart * dblFactor) + lngLMarg
Next
End Sub

Hi Duane,

[quoted text clipped - 37 lines]

 

[hobbit2612 via AccessMonster.com]

Duane,

Hi, I've looked at the coding and there's just a couple of things that I'm
not quite sure about (like I said i'm a real beginner at this!), so I
wondered if i could run them past you please.

I understand what's going on until this line:

'dblFactor = Me.boxTimeLine.Width / DateDiff("d",datStart , datEnd)'

Can you tell me please does this code still assume that there is a start and
end date for each test? and if so do the datStart and dat End replace what
was the Start and End dates txt boxes in the original report?

Many thanks, once again for your help!

Kind regards

Chris

Duane,

Many thanks for taking the time to write this for me. I really do appreciate
it.

It may take me a while to get through it, to see what I need to put in my
reports and queries to conicide with the code.

But I'll let you know how I get on.

Many thanks once again

Regards

Chris

I would rename the text boxes to something like "txtTest1" to get rid of
spaces and ambiguity. Change their widths and colors to match what you want.

[quoted text clipped - 26 lines]

 

[Duane Hookom]

dblFactor creates a value that describes how wide a single day would be. It
doesn't have anything to do with an "end date" for each record. datEnd is
the rightmost date on your timeline. datStart is the leftmost date on your
timeline. You need to determine how wide a single day is so that you can
position your

--
Duane Hookom
MS Access MVP

Duane,

Hi, I've looked at the coding and there's just a couple of things that I'm
not quite sure about (like I said i'm a real beginner at this!), so I
wondered if i could run them past you please.

I understand what's going on until this line:

'dblFactor = Me.boxTimeLine.Width / DateDiff("d",datStart , datEnd)'

Can you tell me please does this code still assume that there is a start
and
end date for each test? and if so do the datStart and dat End replace what
was the Start and End dates txt boxes in the original report?

Many thanks, once again for your help!

Kind regards

Chris

Duane,

Many thanks for taking the time to write this for me. I really do
appreciate
it.

It may take me a while to get through it, to see what I need to put in my
reports and queries to conicide with the code.

But I'll let you know how I get on.

Many thanks once again

Regards

Chris

I would rename the text boxes to something like "txtTest1" to get rid of
spaces and ambiguity. Change their widths and colors to match what you
want.

[quoted text clipped - 26 lines]

you
mean?

 

[hobbit2612 via AccessMonster.com]

Duane,

Thanks for replying so quickly. I'll take a look at it and let you know how I
get on.

Many thanks

Chris

dblFactor creates a value that describes how wide a single day would be. It
doesn't have anything to do with an "end date" for each record. datEnd is
the rightmost date on your timeline. datStart is the leftmost date on your
timeline. You need to determine how wide a single day is so that you can
position your

Duane,

[quoted text clipped - 40 lines]

 

[hobbit2612 via AccessMonster.com]

Duane,

Just thought I'd let you know that I've manged to get this to work.

There is just one thing that I can't seem to get around. I wondered whether
you might be able to give me a little guidance on it please.

Although there are 7 tests, sometimes all the tests aren't carried out for
each form i.e. the date fields are null for some forms.

As it stands at the moment the code doesn't work for any of the test fields
that are blank or (null). I've tried this code 'intTextBox.IgnoreNulls =
True' so it will accept the data field even if there are null dates. This,
from my limited knowledge seemed to be the most logical approach, but it
doesn't work. I have the intTextBox declared as a 'Byte' variable, could this
be the problem?

Can you tell me please Duane, is there a way to get around this?

Many thanks

Chris

Duane,

Thanks for replying so quickly. I'll take a look at it and let you know how I
get on.

Many thanks

Chris

dblFactor creates a value that describes how wide a single day would be. It
doesn't have anything to do with an "end date" for each record. datEnd is

[quoted text clipped - 7 lines]

 

[Duane Hookom]

You can test for Null with the function IsNull().

For intTextBox = 1 to 7
If IsNull(Me("txtTest" & intTextBox)) Then
Me("txtTest" & intTextBox).Visible = False
Else
Me("txtTest" & intTextBox).Visible = True
lngStart = DateDiff("d", datStart, Me("txtTest" & intTextBox)
Me("txtText" & intTextBox).Left = (lngStart * dblFactor) + lngLMarg
End If
Next


--
Duane Hookom
MS Access MVP

Duane,

Just thought I'd let you know that I've manged to get this to work.

There is just one thing that I can't seem to get around. I wondered
whether
you might be able to give me a little guidance on it please.

Although there are 7 tests, sometimes all the tests aren't carried out for
each form i.e. the date fields are null for some forms.

As it stands at the moment the code doesn't work for any of the test
fields
that are blank or (null). I've tried this code 'intTextBox.IgnoreNulls =
True' so it will accept the data field even if there are null dates. This,
from my limited knowledge seemed to be the most logical approach, but it
doesn't work. I have the intTextBox declared as a 'Byte' variable, could
this
be the problem?

Can you tell me please Duane, is there a way to get around this?

Many thanks

Chris

Duane,

Thanks for replying so quickly. I'll take a look at it and let you know
how I
get on.

Many thanks

Chris

dblFactor creates a value that describes how wide a single day would be.
It
doesn't have anything to do with an "end date" for each record. datEnd is

[quoted text clipped - 7 lines]

you
mean?

 

[hobbit2612 via AccessMonster.com]

Hi Duane,

Thanks for getting back to me.

Unfortunately the code doesn't seem to work, it still won't work if the test
dates are blank.

The error message that it shows is Run-Time error '13': Type mismatch.

I know the code you gave me was to test whethe the field are blank. Due to
the query that i've set up I can see whether the fields are blank. is there
anyway to actually say to ignore any blank test date fields?

Many thanks

Chris

You can test for Null with the function IsNull().

For intTextBox = 1 to 7
If IsNull(Me("txtTest" & intTextBox)) Then
Me("txtTest" & intTextBox).Visible = False
Else
Me("txtTest" & intTextBox).Visible = True
lngStart = DateDiff("d", datStart, Me("txtTest" & intTextBox)
Me("txtText" & intTextBox).Left = (lngStart * dblFactor) + lngLMarg
End If
Next

Duane,

[quoted text clipped - 38 lines]

 

[Duane Hookom]

Come back with the exact code that you are using and the line(s) causing the
error.

--
Duane Hookom
MS Access MVP

Hi Duane,

Thanks for getting back to me.

Unfortunately the code doesn't seem to work, it still won't work if the
test
dates are blank.

The error message that it shows is Run-Time error '13': Type mismatch.

I know the code you gave me was to test whethe the field are blank. Due to
the query that i've set up I can see whether the fields are blank. is
there
anyway to actually say to ignore any blank test date fields?

Many thanks

Chris

You can test for Null with the function IsNull().

For intTextBox = 1 to 7
If IsNull(Me("txtTest" & intTextBox)) Then
Me("txtTest" & intTextBox).Visible = False
Else
Me("txtTest" & intTextBox).Visible = True
lngStart = DateDiff("d", datStart, Me("txtTest" & intTextBox)
Me("txtText" & intTextBox).Left = (lngStart * dblFactor) +
lngLMarg
End If
Next

Duane,

[quoted text clipped - 38 lines]

you
mean?

 

[hobbit2612 via AccessMonster.com]

Duane,

No need to worry. After I sent my post to the forum I continued to try
different ways of writing the coding from the limited knowledge I had, as I
said before I am keen to learn.

I had a bit of a brain wave and I managed to change the coding very slightly
and it works a treat.

I really can't thank you enough for all the help that you've given me over
these last few weeks. It would be great to buy you a drink, but that's
obviously not practical with you living in the States and me in the UK.

But I am really so grateful for helping me out with this troublesome project
that I had.

Kind regards and once again sincere thanks.

Chris

Come back with the exact code that you are using and the line(s) causing the
error.

Hi Duane,

[quoted text clipped - 33 lines]

 

[Duane Hookom]

Glad to be of assistance. Just find the most attractive lady in a pub and
buy her a drink. Tell her it is compliments of Duane from Wisconsin in the
States!


--
Duane Hookom
MS Access MVP

Duane,

No need to worry. After I sent my post to the forum I continued to try
different ways of writing the coding from the limited knowledge I had, as
I
said before I am keen to learn.

I had a bit of a brain wave and I managed to change the coding very
slightly
and it works a treat.

I really can't thank you enough for all the help that you've given me over
these last few weeks. It would be great to buy you a drink, but that's
obviously not practical with you living in the States and me in the UK.

But I am really so grateful for helping me out with this troublesome
project
that I had.

Kind regards and once again sincere thanks.

Chris

Come back with the exact code that you are using and the line(s) causing
the
error.

Hi Duane,

[quoted text clipped - 33 lines]

you
mean?

 

[hobbit2612 via AccessMonster.com]

Duane,

No problem at all!

Regards

Chris

Glad to be of assistance. Just find the most attractive lady in a pub and
buy her a drink. Tell her it is compliments of Duane from Wisconsin in the
States!

Duane,

[quoted text clipped - 28 lines]

 

[Iman]

my time line are in years, my project activities may span 20 years.

I tried the code below and it works, when I modify it to use my data I get "Method or data member not found (Error 461)"

and the debugger is at the boxtimeline

what should I do?

Thank you


EggHeadCafe.com - .NET Developer Portal of Choice
http://www.eggheadcafe.com