change control source or value of text box

  • Thread starter Thread starter MNJoe
  • Start date Start date
M

MNJoe

I have a report with GroupHeader0 and GroupHeader1. The data source of the
report is a query. In the GroupHeader0 I have the Purchase Order Header info
which includes the Purchase Order due date. In the GroupHeader1 I have the
Purchase Order line info which includes the line due date. What I need is if
the line due date is Null or blank to default to the PO header due date. How
can I assign the header due date to the line due date. Can I change the
control source of the text box in VB programming.
 
Put the following expression in the first blank field in your query:
DueDate:IIF(IsNull(LineDueDate),POHeaderDueDate,LineDueDate)

Then where you have line due date in your report, change its control source
to DueDate.

Be sure to use YOUR field names in the expression in the query.

Steve
(e-mail address removed)
 
I expect you can simply set the control source to:
=Nz([Line Due Date Field],[PO Due Date Field])
Make sure the name of the control is not the name of a field.
 
Does not work completely. These are the fields from the query

SYSADM_PURC_ORDER_LINE.DESIRED_RECV_DATE
SYSADM_PURCHASE_ORDER.DESIRED_RECV_DATE

My text control names are

PO_Due_Date
PO_Line_Due_Date

When I do this line

=nz(SYSADM_PURC_ORDER_LINE.DESIRED_RECV_DATE,SYSADM_PURCHASE_ORDER.DESIRED_RECV_DATE)

It wants me to enter in the SYSADM_PURC_ORDER_LINE

when I do this (square brackets at begining and end of the field)

=nz([SYSADM_PURC_ORDER_LINE.DESIRED_RECV_DATE],[SYSADM_PURCHASE_ORDER.DESIRED_RECV_DATE])

Where there is a date for the po line I get that date, for when the date is
null or blank I get "#error".

When I do this . (square brackets surrounding table and field of query)

=nz([SYSADM_PURC_ORDER_LINE].[DESIRED_RECV_DATE],[SYSADM_PURCHASE_ORDER].[DESIRED_RECV_DATE])

It again wants me to enter the SYSADM_PURC_ORDER_LINE

Any help is very appreciated.

Thanks
--
MNJoe


Duane Hookom said:
I expect you can simply set the control source to:
=Nz([Line Due Date Field],[PO Due Date Field])
Make sure the name of the control is not the name of a field.

--
Duane Hookom
MS Access MVP

MNJoe said:
I have a report with GroupHeader0 and GroupHeader1. The data source of the
report is a query. In the GroupHeader0 I have the Purchase Order Header
info
which includes the Purchase Order due date. In the GroupHeader1 I have the
Purchase Order line info which includes the line due date. What I need is
if
the line due date is Null or blank to default to the PO header due date.
How
can I assign the header due date to the line due date. Can I change the
control source of the text box in VB programming.
Click to expand...
Click to expand...
 
If I enter the line below. It prompts me to enter in the
"SYSADM_PURC_ORDER_LINE.DESIRED_RECV_DAT"

DueDate:
IIf(IsNull(SYSADM_PURC_ORDER_LINE.DESIRED_RECV_DAT),SYSADM_PURCHASE_ORDER.DESIRED_RECV_DATE,SYSADM_PURC_ORDER_LINE.DESIRED_RECV_DAT)


If I bracket the field for the isnull statement I get an error "Invalid
bracketing of name "[SYSADM_PURC_ORDER_LINE.DESIRED_RECV_DAT]"

DueDate:
IIf(IsNull([SYSADM_PURC_ORDER_LINE.DESIRED_RECV_DAT]),SYSADM_PURCHASE_ORDER.DESIRED_RECV_DATE,SYSADM_PURC_ORDER_LINE.DESIRED_RECV_DAT)


If I try to bracket the first part [SYSADM_PURC_ORDER_LINE] then the second
part. It automatically drops the brackets and then prompts me to enter in the
value the same as the first example.

Thanks for your help
 
Does your report record source actually return two date fields with the same
field name? This would create an issue. I would rename the Line date and PO
desired received dates to
Line_Desired_Recv_Date and PO_Desired_Recv_Date. Then you can use:

=nz([Line_Desired_Recv_Date],[PO_Desired_Recv_Date ])

IMO, you should never create queries that return the same field name more
than once without creating ALIASES.
--
Duane Hookom
Microsoft Access MVP

NOTE: These public News Groups are ending June 1st. Consider asking
questions at http://social.answers.microsoft.com/Forums/en-US/addbuz/threads?


MNJoe said:
Does not work completely. These are the fields from the query

SYSADM_PURC_ORDER_LINE.DESIRED_RECV_DATE
SYSADM_PURCHASE_ORDER.DESIRED_RECV_DATE

My text control names are

PO_Due_Date
PO_Line_Due_Date

When I do this line

=nz(SYSADM_PURC_ORDER_LINE.DESIRED_RECV_DATE,SYSADM_PURCHASE_ORDER.DESIRED_RECV_DATE)

It wants me to enter in the SYSADM_PURC_ORDER_LINE

when I do this (square brackets at begining and end of the field)

=nz([SYSADM_PURC_ORDER_LINE.DESIRED_RECV_DATE],[SYSADM_PURCHASE_ORDER.DESIRED_RECV_DATE])

Where there is a date for the po line I get that date, for when the date is
null or blank I get "#error".

When I do this . (square brackets surrounding table and field of query)

=nz([SYSADM_PURC_ORDER_LINE].[DESIRED_RECV_DATE],[SYSADM_PURCHASE_ORDER].[DESIRED_RECV_DATE])

It again wants me to enter the SYSADM_PURC_ORDER_LINE

Any help is very appreciated.

Thanks
--
MNJoe


Duane Hookom said:
I expect you can simply set the control source to:
=Nz([Line Due Date Field],[PO Due Date Field])
Make sure the name of the control is not the name of a field.

--
Duane Hookom
MS Access MVP

MNJoe said:
I have a report with GroupHeader0 and GroupHeader1. The data source of the
report is a query. In the GroupHeader0 I have the Purchase Order Header
info
which includes the Purchase Order due date. In the GroupHeader1 I have the
Purchase Order line info which includes the line due date. What I need is
if
the line due date is Null or blank to default to the PO header due date.
How
can I assign the header due date to the line due date. Can I change the
control source of the text box in VB programming.
Click to expand...
Click to expand...
Click to expand...
 
Never mind. I was missing the "E" on the very end of the last "DATE" Now it
works

Thanks

--
MNJoe


MNJoe said:
If I enter the line below. It prompts me to enter in the
"SYSADM_PURC_ORDER_LINE.DESIRED_RECV_DAT"

DueDate:
IIf(IsNull(SYSADM_PURC_ORDER_LINE.DESIRED_RECV_DAT),SYSADM_PURCHASE_ORDER.DESIRED_RECV_DATE,SYSADM_PURC_ORDER_LINE.DESIRED_RECV_DAT)


If I bracket the field for the isnull statement I get an error "Invalid
bracketing of name "[SYSADM_PURC_ORDER_LINE.DESIRED_RECV_DAT]"

DueDate:
IIf(IsNull([SYSADM_PURC_ORDER_LINE.DESIRED_RECV_DAT]),SYSADM_PURCHASE_ORDER.DESIRED_RECV_DATE,SYSADM_PURC_ORDER_LINE.DESIRED_RECV_DAT)


If I try to bracket the first part [SYSADM_PURC_ORDER_LINE] then the second
part. It automatically drops the brackets and then prompts me to enter in the
value the same as the first example.

Thanks for your help

--
MNJoe


Steve said:
Put the following expression in the first blank field in your query:
DueDate:IIF(IsNull(LineDueDate),POHeaderDueDate,LineDueDate)

Then where you have line due date in your report, change its control source
to DueDate.

Be sure to use YOUR field names in the expression in the query.

Steve
(e-mail address removed)





.
Click to expand...
Click to expand...
 
I was able to change the query where it now has PO_Desired_Recv_Date and
Line_Desired_Recv_Date as you suggested. I put each of these dates in the
control and run the report just to verify that they work and dates are
output. when I put the equation
=nz([Line_Desired_Recv_Date],[PO_Desired_Recv_Date]) , it still puts out
"#error" on the report when ever the line date is null. It puts out the line
date correctly when it is not null. I looked up the function NZ and was
wondering. Is it for numbers only? The only examples I could find were for
when a number is null it would return 0 or some other number.

thanks for all your help
--
MNJoe


Duane Hookom said:
Does your report record source actually return two date fields with the same
field name? This would create an issue. I would rename the Line date and PO
desired received dates to
Line_Desired_Recv_Date and PO_Desired_Recv_Date. Then you can use:

=nz([Line_Desired_Recv_Date],[PO_Desired_Recv_Date ])

IMO, you should never create queries that return the same field name more
than once without creating ALIASES.
--
Duane Hookom
Microsoft Access MVP

NOTE: These public News Groups are ending June 1st. Consider asking
questions at http://social.answers.microsoft.com/Forums/en-US/addbuz/threads?


MNJoe said:
Does not work completely. These are the fields from the query

SYSADM_PURC_ORDER_LINE.DESIRED_RECV_DATE
SYSADM_PURCHASE_ORDER.DESIRED_RECV_DATE

My text control names are

PO_Due_Date
PO_Line_Due_Date

When I do this line

=nz(SYSADM_PURC_ORDER_LINE.DESIRED_RECV_DATE,SYSADM_PURCHASE_ORDER.DESIRED_RECV_DATE)

It wants me to enter in the SYSADM_PURC_ORDER_LINE

when I do this (square brackets at begining and end of the field)

=nz([SYSADM_PURC_ORDER_LINE.DESIRED_RECV_DATE],[SYSADM_PURCHASE_ORDER.DESIRED_RECV_DATE])

Where there is a date for the po line I get that date, for when the date is
null or blank I get "#error".

When I do this . (square brackets surrounding table and field of query)

=nz([SYSADM_PURC_ORDER_LINE].[DESIRED_RECV_DATE],[SYSADM_PURCHASE_ORDER].[DESIRED_RECV_DATE])

It again wants me to enter the SYSADM_PURC_ORDER_LINE

Any help is very appreciated.

Thanks
--
MNJoe


Duane Hookom said:
I expect you can simply set the control source to:
=Nz([Line Due Date Field],[PO Due Date Field])
Make sure the name of the control is not the name of a field.

--
Duane Hookom
MS Access MVP

I have a report with GroupHeader0 and GroupHeader1. The data source of the
report is a query. In the GroupHeader0 I have the Purchase Order Header
info
which includes the Purchase Order due date. In the GroupHeader1 I have the
Purchase Order line info which includes the line due date. What I need is
if
the line due date is Null or blank to default to the PO header due date.
How
can I assign the header due date to the line due date. Can I change the
control source of the text box in VB programming.
Click to expand...
Click to expand...
Click to expand...
 
Just as a test I tried this. =nz([Line_Desired_Recv_Date],"99/99/99") and
this worked. For some reason I put in the PO_Desired_Recv_Date and I get
#error for output. ????? Yet when I output just the PO_Desired_Recv_Date it
outputs the po date.

--
MNJoe


MNJoe said:
I was able to change the query where it now has PO_Desired_Recv_Date and
Line_Desired_Recv_Date as you suggested. I put each of these dates in the
control and run the report just to verify that they work and dates are
output. when I put the equation
=nz([Line_Desired_Recv_Date],[PO_Desired_Recv_Date]) , it still puts out
"#error" on the report when ever the line date is null. It puts out the line
date correctly when it is not null. I looked up the function NZ and was
wondering. Is it for numbers only? The only examples I could find were for
when a number is null it would return 0 or some other number.

thanks for all your help
--
MNJoe


Duane Hookom said:
Does your report record source actually return two date fields with the same
field name? This would create an issue. I would rename the Line date and PO
desired received dates to
Line_Desired_Recv_Date and PO_Desired_Recv_Date. Then you can use:

=nz([Line_Desired_Recv_Date],[PO_Desired_Recv_Date ])

IMO, you should never create queries that return the same field name more
than once without creating ALIASES.
--
Duane Hookom
Microsoft Access MVP

NOTE: These public News Groups are ending June 1st. Consider asking
questions at http://social.answers.microsoft.com/Forums/en-US/addbuz/threads?


MNJoe said:
Does not work completely. These are the fields from the query

SYSADM_PURC_ORDER_LINE.DESIRED_RECV_DATE
SYSADM_PURCHASE_ORDER.DESIRED_RECV_DATE

My text control names are

PO_Due_Date
PO_Line_Due_Date

When I do this line

=nz(SYSADM_PURC_ORDER_LINE.DESIRED_RECV_DATE,SYSADM_PURCHASE_ORDER.DESIRED_RECV_DATE)

It wants me to enter in the SYSADM_PURC_ORDER_LINE

when I do this (square brackets at begining and end of the field)

=nz([SYSADM_PURC_ORDER_LINE.DESIRED_RECV_DATE],[SYSADM_PURCHASE_ORDER.DESIRED_RECV_DATE])

Where there is a date for the po line I get that date, for when the date is
null or blank I get "#error".

When I do this . (square brackets surrounding table and field of query)

=nz([SYSADM_PURC_ORDER_LINE].[DESIRED_RECV_DATE],[SYSADM_PURCHASE_ORDER].[DESIRED_RECV_DATE])

It again wants me to enter the SYSADM_PURC_ORDER_LINE

Any help is very appreciated.

Thanks
--
MNJoe


:

I expect you can simply set the control source to:
=Nz([Line Due Date Field],[PO Due Date Field])
Make sure the name of the control is not the name of a field.

--
Duane Hookom
MS Access MVP

I have a report with GroupHeader0 and GroupHeader1. The data source of the
report is a query. In the GroupHeader0 I have the Purchase Order Header
info
which includes the Purchase Order due date. In the GroupHeader1 I have the
Purchase Order line info which includes the line due date. What I need is
if
the line due date is Null or blank to default to the PO header due date.
How
can I assign the header due date to the line due date. Can I change the
control source of the text box in VB programming.
Click to expand...
Click to expand...
Click to expand...
 
Try adding a control with [PO_Desired_Recv_Date] as its source. You can hide
the control.

Access has a habit of rebuilding the query when the report is run and if it
decides it does not need a field it will drop it from the select clause.
Since the field [PO_Desired_Recv_Date] is buried in the NZ function it may be
missed as being required. Putting it into a control as the control source and
hiding the control should take care of the problem. Be sure you name the
control something other than the name of the field.

John Spencer
Access MVP 2002-2005, 2007-2010
The Hilltop Institute
University of Maryland Baltimore County
 
Back
Top