A Microsoft Office (Excel, Word) forum. OfficeFrustration

If this is your first visit, be sure to check out the FAQ by clicking the link above. You may have to register before you can post: click the register link above to proceed. To start viewing messages, select the forum that you want to visit from the selection below.

Go Back   Home » OfficeFrustration forum » Microsoft Access » Setting Up & Running Reports
Site Map Home Register Authors List Search Today's Posts Mark Forums Read  

Timetable



 
 
Thread Tools Display Modes
  #21  
Old October 3rd, 2006, 02:37 AM posted to microsoft.public.access.reports
Duane Hookom
external usenet poster
 
Posts: 2,251
Default Timetable

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


"hobbit2612 via AccessMonster.com" u27332@uwe wrote in message
news:672d940a75c4c@uwe...
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

Duane Hookom wrote:
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?


--
Message posted via AccessMonster.com
http://www.accessmonster.com/Uwe/For...ports/200610/1



  #22  
Old October 3rd, 2006, 05:09 PM posted to microsoft.public.access.reports
hobbit2612 via AccessMonster.com
external usenet poster
 
Posts: 107
Default Timetable

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

Duane Hookom wrote:
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?


--
Message posted via http://www.accessmonster.com

  #23  
Old October 3rd, 2006, 11:27 PM posted to microsoft.public.access.reports
Duane Hookom
external usenet poster
 
Posts: 2,251
Default Timetable

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


"hobbit2612 via AccessMonster.com" u27332@uwe wrote in message
news:673807d941913@uwe...
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

Duane Hookom wrote:
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?


--
Message posted via http://www.accessmonster.com



  #24  
Old October 4th, 2006, 07:39 PM posted to microsoft.public.access.reports
hobbit2612 via AccessMonster.com
external usenet poster
 
Posts: 107
Default Timetable

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

Duane Hookom wrote:
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]
you
mean?


--
Message posted via http://www.accessmonster.com

  #25  
Old October 11th, 2006, 06:57 PM posted to microsoft.public.access.reports
hobbit2612 via AccessMonster.com
external usenet poster
 
Posts: 107
Default Timetable

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


hobbit2612 wrote:
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?


--
Message posted via http://www.accessmonster.com

  #26  
Old October 11th, 2006, 07:54 PM posted to microsoft.public.access.reports
Duane Hookom
external usenet poster
 
Posts: 2,251
Default Timetable

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


"hobbit2612 via AccessMonster.com" u27332@uwe wrote in message
news:679d8df2914ae@uwe...
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


hobbit2612 wrote:
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?


--
Message posted via http://www.accessmonster.com



  #27  
Old October 11th, 2006, 08:03 PM posted to microsoft.public.access.reports
hobbit2612 via AccessMonster.com
external usenet poster
 
Posts: 107
Default Timetable

Duane,

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

Many thanks

Chris

Duane Hookom wrote:
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]
you
mean?


--
Message posted via AccessMonster.com
http://www.accessmonster.com/Uwe/For...ports/200610/1

  #28  
Old October 13th, 2006, 06:07 PM posted to microsoft.public.access.reports
hobbit2612 via AccessMonster.com
external usenet poster
 
Posts: 107
Default Timetable

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

hobbit2612 wrote:
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?


--
Message posted via AccessMonster.com
http://www.accessmonster.com/Uwe/For...ports/200610/1

  #29  
Old October 13th, 2006, 08:26 PM posted to microsoft.public.access.reports
Duane Hookom
external usenet poster
 
Posts: 2,251
Default Timetable

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

"hobbit2612 via AccessMonster.com" u27332@uwe wrote in message
news:67b643e1033dc@uwe...
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

hobbit2612 wrote:
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?


--
Message posted via AccessMonster.com
http://www.accessmonster.com/Uwe/For...ports/200610/1



  #30  
Old October 14th, 2006, 01:59 PM posted to microsoft.public.access.reports
hobbit2612 via AccessMonster.com
external usenet poster
 
Posts: 107
Default Timetable

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

Duane Hookom wrote:
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?


--
Message posted via http://www.accessmonster.com

 




Thread Tools
Display Modes

Posting Rules
You may not post new threads
You may not post replies
You may not post attachments
You may not edit your posts

vB code is On
Smilies are On
[IMG] code is Off
HTML code is Off
Forum Jump


All times are GMT +1. The time now is 03:51 PM.


Powered by vBulletin® Version 3.6.4
Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
Copyright ©2004-2024 OfficeFrustration.
The comments are property of their posters.