Validate against a label??

[andy_dyer]

Hi,

I have a form with a todays date text box (txtTodaysDate) and a series of fields with percentages in them...

The label is a date e.g. 30/06/2008 and I want to check on the form opening or something similar what the todays date is and then lock the related text boxes

If Me.End_Jun_08_Label > Me.txtTodaysDate Then
Me.End_Jun_08.Locked = True
Else
Me.End_Jun_08.Locked = False
End If

But all I get is a Run-time error '438': Object doesn't support this property or method

Is there a way of doing this??

Thanks for any help!!

 

[rainman89]

Have you tried the .caption property of a label??

 

[namliam]

And that (.caption) will return a text value, so you need to use Dateserial or some other conversion function (Cdate, datevalue) to convert it to a proper date...

Good luck!

 

[andy_dyer]

Thanks for that i think I've got it working but a but clumsy...

lngGrey = RGB(169, 169, 169)
lngWhite = RGB(255, 255, 255)
Me.End_Jun_08_Label.Caption = "28/06/2008"
If Me.End_Jun_08_Label.Caption > Me.txtTodaysDate Then
Me.End_Jun_08.Enabled = False
Me.End_Jun_08.Locked = True
Me.End_Jun_08.BackColor = lngGrey
Else
Me.End_Jun_08.Enabled = True
Me.End_Jun_08.Locked = False
Me.End_Jun_08.BackColor = lngWhite
End If

I have about 20 of these fields... do I have to replicate this for each one or is there a slicker way of achieving the same thing?

Thanks again

 

[rainman89]

you can write some code to loop through all the labels on the form and do it once, but i think a better question is why are you using labels? and why are there 20?

 

[andy_dyer]

We have the fields to capture % complete as at every month end we use this historically to capture actuals and for the future to capture planned profile:

So it may look like a current project

30/04/2009 - 25%
31/05/2009 - 50%
30/06/2009 - 75%
31/07/2009 - 100%

But we have fields that go back to June 2008 and currently go forward to December 2009 due to the size of our current projects...

So I'm not sure if there is a slicker or quicker way of capturing this info and then ensuring the historic isn't tampered with and users are led straight to the current and future fields to update...

Also I'm not very advanced on vba so have never seen any looping code if you could point me in the right direction I'd be grateful.

Thanks again for your help

 

[namliam]

End_June, is this a calculatable field? Or is it a constant?

If a constant copying the values is a one time excercise...

You can use "For each" to loop thru all the captions if you have a calculatable value.
Just lookup For Each in the access help you will find a pretty good example there.

Also in this you are COMPARING STRINGS, this is BAD
You hearing me?? BAD
BAD
BAD
BAD

28/Jun/2009 in a string is > 01/any month/any year because 2 > 0, this is NOT what you want.

If txtTodaysDate contains todays date, then simply use Date()
And use something like DATESERIAL (preferably) or Cdate to convert your Caption to a proper date!

 

[rainman89]

Why wont a continuous form work for this? You can still lock the textboxes and It will probably be easier to use without 20 labels......??

 

[namliam]

We have the fields to capture % complete as at every month end we use this historically to capture actuals and for the future to capture planned profile:

So it may look like a current project

30/04/2009 - 25%
31/05/2009 - 50%
30/06/2009 - 75%
31/07/2009 - 100%

And what happens if you add projects that last into 2012? Just keep adding fields??
NO! You need to make this much more flexible that is should display the date you need only. Not in fixed columns but in a related table!

 

[andy_dyer]

End_June, is this a calculatable field? Or is it a constant?

If a constant copying the values is a one time excercise...

You can use "For each" to loop thru all the captions if you have a calculatable value.
Just lookup For Each in the access help you will find a pretty good example there.

Also in this you are COMPARING STRINGS, this is BAD
You hearing me?? BAD
BAD
BAD
BAD

28/Jun/2009 in a string is > 01/any month/any year because 2 > 0, this is NOT what you want.

If txtTodaysDate contains todays date, then simply use Date()
And use something like DATESERIAL (preferably) or Cdate to convert your Caption to a proper date!

I'm sorry but that makes little sense to me...

The labels are dates but the fields are percentages - I'm not sure if this is constant or not??

I'm also not sure about strings...

Yes the field txtTodaysDate just has Date() as it's default value.

I wasn't sure if I needed to specify what the caption was in my code like I have done or not...

I'm you can assume I know nothing and am way out of my depth then you won't be far away... I'm very grateful for your assisatnce and don't want to come across wrong...

 

[namliam]

.Caption returns a STRING value in strings order of numbers is
1
10
100
11
110
111
12
120
121
2
3
4
40
45
460
etc...
This is because a string only evaluates the strings, not the whole number. Thus it does it Character by character... I suggest you make a dummy table add 1 field that is a text... Then add all numbers 1 to 30 into the column, as well as some 100, 200 and 300 numbers, possibly some 2000000 number as well. and sort it, to see that this is right...

Now equivalate this to dates in a year...
01-Dec
01-Feb
01-Jan
02-Dec
02-Feb
02-Jan
30-Dec
30-Jan
It evaluates characters not dates....
First you get all 0 starting days, 01,02...09
Then all 1 starting days 10,11..19, then 20, 21,...31 etc...

Also D in the alphabet comes before F and J so the order would be So the 01 Dec is before 01 Feb which is before 01 Jan.

Perhaps try this in a table as well... and you will see this "strange" string sorting. Its not strange, its just a different data type that acts differently.

This is quite bad if you are talking about dates.... that is why it is bad bad bad to compare dates this way!

If you have all the lables in your form will "End_Jun_08_Label" always contain 28-June-2008?? (or always contain the same value)
If the answer is yes, then its a constant... If the answer is No,then it is not and likely calculable...

 

[andy_dyer]

Thanks namliam,

Think this may be beyond me as although that kind of makes sense I wouldn't know where to start...

So forgetting the formatting thing I am still keen to understand how I can avoid having 20 fields and obviously this will only grow and grow...

Is there a smarter way of handling this??

I could obvious break these fields out into a seperate table and embed them as a subform and link them by a ProjectID field - is this what you would suggest?

Thanks

 

[gemma-the-husky]

why not use a text box (or a date variable), instead of a label

works the same way, but you dont need to use caption property, and it will be interpreted as the correct datatype

 

[namliam]

Yes seperate table would fix the need for more and more columns, as instead of changing your tables your simply adding rows...

Another alternative may be to have 1 fixed table with say 12 columns for 12 months.
The have one column which denotes the startdate/startmonth. Then each column represents StartMonth + 1, StartMonth +2, etc.