Calculated date control

[Tony Williams]

I have a form which has a calculated control "txtYear" on a subform where
the control source is

=Format([txtcd1retd],"yy") which works fine.

I have another calculated control txtletter2 where the control source is

=DLookUp("[txtletter]","[tblComplaintnbr]","[txtYear]=Forms!frmMain!Subform1![txtYear]")

But this doesn't work.

can anyone see where my problem may be?

Thanks
Tony

 

[John W. Vinson]

I have a form which has a calculated control "txtYear" on a subform where
the control source is

=Format([txtcd1retd],"yy") which works fine.

I have another calculated control txtletter2 where the control source is

=DLookUp("[txtletter]","[tblComplaintnbr]","[txtYear]=Forms!frmMain!Subform1![txtYear]")

But this doesn't work.

can anyone see where my problem may be?

Thanks
Tony

It appears that txtYear is a field of Text datatype... right? Or is it a
Date/Time field? How is the value of the *TABLE* field txtYear set (bear in
mind that a form control is not the same as a table field)?

John W. Vinson [MVP]

 

[Tony Williams]

Hi John, The text year from the table is a number whereas the text year on
the subform is YY from >>=Format([txtcd1retd],"yy") Is that where my
problem is?
Tony

I have a form which has a calculated control "txtYear" on a subform where
the control source is

=Format([txtcd1retd],"yy") which works fine.

I have another calculated control txtletter2 where the control source is

=DLookUp("[txtletter]","[tblComplaintnbr]","[txtYear]=Forms!frmMain!Subform1![txtYear]")

But this doesn't work.

can anyone see where my problem may be?

Thanks
Tony

It appears that txtYear is a field of Text datatype... right? Or is it a
Date/Time field? How is the value of the *TABLE* field txtYear set (bear
in
mind that a form control is not the same as a table field)?

John W. Vinson [MVP]

 

[John W. Vinson]

Hi John, The text year from the table is a number whereas the text year on
the subform is YY from >>=Format([txtcd1retd],"yy") Is that where my
problem is?

Probably. The number 2008 is not equal to the text string "08". Access does
not, of course, treat either of these fields as a year or a date - it's much
too literal-minded for that!

Your expression

=DLookUp("[txtletter]","[tblComplaintnbr]","[txtYear]=Forms!frmMain!Subform1![txtYear]")


will look up the first value of txtletter that it finds (probably the first
record in disk storage order, i.e. an unpredictable record) for which the
value of txtYear in the table is equal to 8. My guess is that is not what you
want! Will there be only one txtletter for each year? Will the year be stored
as an integer 8, or as 2008?

John W. Vinson [MVP]

 

[Tony Williams]

Hi John. Let me break down the parts of my expression to show what each part
is

=DLookUp("[txtletter]","[tblComplaintnbr]","[txtYear]=Forms!frmMain!Subform1![txtYear]")

In the table tblComplaintnbr txtletter is a text field and txtYear is a Long
Integer. There is a different letter for each year so that A is 2005, B is
2006 and so on.

The value of txtyear in the subform is a calculated control with a source of
=Format([txtcd1retd],"yy") where txtcd1retd is a date/time field.
What I'm trying to do is build up a reference number which is made up of the
last 2 digits of the year, the letter corresponding to that year and then a
sequential number (with a formula of
=Nz(DMax("[txtrefnbr]","tblcomplaints"),0)+1)

These relate to complaints received. So a complaint received on 28/02/08 and
is the tenth complaint we've received would have a reference number as 08D10
(08 being the last 2 digits from the date, D being the letter for 2008 and
10 being the tenth complaint)

Does this make it clearer?
Thanks for your help
Tony

Hi John, The text year from the table is a number whereas the text year on
the subform is YY from >>=Format([txtcd1retd],"yy") Is that where my
problem is?

Probably. The number 2008 is not equal to the text string "08". Access
does
not, of course, treat either of these fields as a year or a date - it's
much
too literal-minded for that!

Your expression

=DLookUp("[txtletter]","[tblComplaintnbr]","[txtYear]=Forms!frmMain!Subform1![txtYear]")


will look up the first value of txtletter that it finds (probably the
first
record in disk storage order, i.e. an unpredictable record) for which the
value of txtYear in the table is equal to 8. My guess is that is not what
you
want! Will there be only one txtletter for each year? Will the year be
stored
as an integer 8, or as 2008?

John W. Vinson [MVP]

 

[Tony Williams]

John I solved my problem with what appears to be an obvious solution. In
tblcomplaintnbr I stored the dates as 05,06,07,08 etc instead of
2005,2006,2007 etc and everything works fine now.
Thanks for your help and sorry to have been so dim, (at 63 I can claim it's
an age thing!!!)
Thanks again
Tony

Hi John. Let me break down the parts of my expression to show what each
part is

=DLookUp("[txtletter]","[tblComplaintnbr]","[txtYear]=Forms!frmMain!Subform1![txtYear]")

In the table tblComplaintnbr txtletter is a text field and txtYear is a
Long Integer. There is a different letter for each year so that A is 2005,
B is 2006 and so on.

The value of txtyear in the subform is a calculated control with a source
of =Format([txtcd1retd],"yy") where txtcd1retd is a date/time field.
What I'm trying to do is build up a reference number which is made up of
the last 2 digits of the year, the letter corresponding to that year and
then a sequential number (with a formula of
=Nz(DMax("[txtrefnbr]","tblcomplaints"),0)+1)

These relate to complaints received. So a complaint received on 28/02/08
and is the tenth complaint we've received would have a reference number as
08D10 (08 being the last 2 digits from the date, D being the letter for
2008 and 10 being the tenth complaint)

Does this make it clearer?
Thanks for your help
Tony

Hi John, The text year from the table is a number whereas the text year
on
the subform is YY from >>=Format([txtcd1retd],"yy") Is that where my
problem is?

Probably. The number 2008 is not equal to the text string "08". Access
does
not, of course, treat either of these fields as a year or a date - it's
much
too literal-minded for that!

Your expression

=DLookUp("[txtletter]","[tblComplaintnbr]","[txtYear]=Forms!frmMain!Subform1![txtYear]")


will look up the first value of txtletter that it finds (probably the
first
record in disk storage order, i.e. an unpredictable record) for which the
value of txtYear in the table is equal to 8. My guess is that is not what
you
want! Will there be only one txtletter for each year? Will the year be
stored
as an integer 8, or as 2008?

John W. Vinson [MVP]

 

[Tony Williams]

Whoops spoke to soon I can't get the years 05,06,07,08,09 to hold the 0 in
the table, is there anyway I can do that? I'm now getting #name errors
Tony

Hi John, The text year from the table is a number whereas the text year on
the subform is YY from >>=Format([txtcd1retd],"yy") Is that where my
problem is?

Probably. The number 2008 is not equal to the text string "08". Access
does
not, of course, treat either of these fields as a year or a date - it's
much
too literal-minded for that!

Your expression

=DLookUp("[txtletter]","[tblComplaintnbr]","[txtYear]=Forms!frmMain!Subform1![txtYear]")


will look up the first value of txtletter that it finds (probably the
first
record in disk storage order, i.e. an unpredictable record) for which the
value of txtYear in the table is equal to 8. My guess is that is not what
you
want! Will there be only one txtletter for each year? Will the year be
stored
as an integer 8, or as 2008?

John W. Vinson [MVP]

 

[John W. Vinson]

John I solved my problem with what appears to be an obvious solution. In
tblcomplaintnbr I stored the dates as 05,06,07,08 etc instead of
2005,2006,2007 etc and everything works fine now.

Just so your consistant it should work - compare four digit integers to four
digit integers, or two character text strings to two character text strings.
Just not crosswise!

Thanks for your help and sorry to have been so dim, (at 63 I can claim it's
an age thing!!!)

hey... I remember President Eisenhower too. <g>

John W. Vinson [MVP]

 

[John W. Vinson]

Whoops spoke to soon I can't get the years 05,06,07,08,09 to hold the 0 in
the table, is there anyway I can do that? I'm now getting #name errors

The numbers 5 and 05 and 00000000005 are all just ways of depicting THE SAME
NUMBER.

Use a Text datatype (if you insist, unwisely IMO, on using the two digit
representation). The text string "05" is different from the text string "5"
and will retain its leading zero.

I'd go with the 2005 in a Long Integer field, frankly.

John W. Vinson [MVP]

 

[Tony Williams]

Thanks John I'll take your advice. Remember President Eisenhower ?
Sometimes, particularly at about 7 in the morning when I crawl out of bed I
feel old enough to remember our Queen Victoria. Anyway age doesn't matter,
unless you're a cheese!
Thanks again
Tony