Gantt Chart

[Jonny45wakey]

Hi all

Hoping someone could help with a little structuring of a formula in a text box i have which uses dates in other text boxes to populate a Gantt chart (image attached)

When dates are entered in the reference date text boxes the formula in each of the squares representing each week of the year will change colour using the following formula in the text box control source = =IIf(DateValue([Finish Date1])>=[Text1159]+7*0,IIf(DateValue([Start Date1]<=[Text1159]+6+7*0,"True","False"),"False")

The conditional formatting of the text box adds the blue colour shading if true condition is met, what i would like is to get rid of the Error messages you see below

#Name?

Thanks

Jonny

 

[cheekybuddha]

Hi,

Perhaps test that you have dates for all you inputs first:
= =IIf(IsDate([Finish Date1]) AND IsDate([Start Date1]) AND IsDate([Text1159]), IIf(DateValue([Finish Date1])>=[Text1159]+7*0,IIf(DateValue([Start Date1]<=[Text1159]+6+7*0, True, False), False), False)

 

[theDBguy]

Hi Jonny. Belated welcome to AWF!

 

[Jonny45wakey]

Thanks cheekybuddha, i tried your suggestion but got the following:-

 

[theDBguy]

Thanks cheekybuddha, i tried your suggestion but got the following:-

View attachment 82645

Maybe there's a missing closing parens here (in red)?

... IIf(DateValue([Start Date1])<=[Text1159]+6+7*0, ...

 

[plog]

Cheeky is missing a right parenthesis, but I think that's only going to fix the #Type errors not the #Name errors.

Are you sure [Text1159] is a valid reference? Compare the code In Row 10 Day 19 to Row 10 Day 20. Ones a #Type and the other is #Name. The answer is there.

 

[cheekybuddha]

>> Cheeky is missing a right parenthesis <<
True, but copied from OP - apologies, didn't check my inputs!

 

[Dreamweaver]

You could try Peter hibbs Gantt Chart

https://www.utteraccess.com/forum/index.php?showtopic=2032649

 

[Dreamweaver]

Also found this topic I was involved in

UtterAccess.com

www.utteraccess.com

 

[arnelgp]

you can also use:

=Switch(DateValue(Nz([Finish Date1],0))>=[Text1159]+7*0 And DateValue(Nz([Start Date1],0)<=[Text1159]+6+7*0, "True", True, "False")

 

[Jonny45wakey]

Hi all, thanks for the help but still cant sort the #Type! error!

The Textbox control source looks as follows:

Text 1159 refers to the following calculation in textbox1159

Essentially the #Type! error occurs when there are no dates entered into [Start Date6] and [Finish Date6] text boxes, what i need is something to leave textbox2337 blank if no dates in these text boxes?

Thanks in advance for further help

Jonny

 

[arnelgp]

add a Public function to your form:

Public Function CellValue(byval StartDate As Variant, Byval EndDate As Variant) As Variant
StartDate=Trim(StartDate & "")
EndDate = Trim(EndDate & "")
If StartDate = "" Or EndDate = "" Then
    Exit Function
End If
StartDate = DateValue(CDate(StartDate))
EndDate = DateValue(CDate(EndDate))
CellValue=(EndDate >= ([Text1159]+7*0) And StartDate <= ([Text1159]+6+7*0))
End Function

on Each Unbound textbox:

=CellValue([Start Date1], [End Date1])
=CellValue([Start Date2], [End Date2])
=etc...

 

[Dreamweaver]

Hi Jonny45wakey

Correct me if I'm wrong but you are adding unbound controls a line at a time at least thats what it looks like in post 11 Your OP didn't show it.

if you are you will run out of controls at around 750

why all the mess with the Expressions you could very easly do what you need with a loop.

I've built a number of this type of object and would never use your approch.

mick

 

[Jonny45wakey]

Hi Jonny45wakey

Correct me if I'm wrong but you are adding unbound controls a line at a time at least thats what it looks like in post 11 Your OP didn't show it.

if you are you will run out of controls at around 750

why all the mess with the Expressions you could very easly do what you need with a loop.

I've built a number of this type of object and would never use your approch.

mick

Hi MickJav

Are you able to share any of these objects so i can see if i can apply these to my project?

Thanks

Jonny

 

[Dreamweaver]

Thi is only available in the project manager https://databasedreams.createaforum.com/index.php Info

Or you can download peter hibbs program as he was the developer I just made a number of changes to fit the project manager

UtterAccess.com

www.utteraccess.com

Regards mick

 

[cheekybuddha]

Essentially the #Type! error occurs when there are no dates entered into [Start Date6] and [Finish Date6] text boxes, what i need is something to leave textbox2337 blank if no dates in these text boxes?

Did you try with the correction made to the expression I suggested in post#2 ?

=IIf(IsDate([Finish Date1]) AND IsDate([Start Date1]) AND IsDate([Text1159]), IIf(DateValue([Finish Date1])>=[Text1159]+7*0,IIf(DateValue([Start Date1])<=[Text1159]+6+7*0, True, False), False), False)

 

[cheekybuddha]

Out of curiosity, what does this part of the expression mean?
DateValue([Start Date1])<=[Text1159]+6+7*0

Surely it's thsame as writing:
DateValue([Start Date1])<=[Text1159]+6

??