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  

change control source or value of text box



 
 
Thread Tools Display Modes
  #1  
Old May 21st, 2010, 09:01 PM posted to microsoft.public.access.reports
MNJoe
external usenet poster
 
Posts: 30
Default change control source or value of text box

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.

--
MNJoe
  #2  
Old May 21st, 2010, 09:50 PM posted to microsoft.public.access.reports
Steve[_77_]
external usenet poster
 
Posts: 1,017
Default change control source or value of text box

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

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



"MNJoe" wrote in message
...
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.

--
MNJoe



  #3  
Old May 22nd, 2010, 04:02 AM posted to microsoft.public.access.reports
Duane Hookom[_4_]
external usenet poster
 
Posts: 316
Default change control source or value of text box

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" wrote in message
...
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.

--
MNJoe


  #4  
Old May 24th, 2010, 05:02 PM posted to microsoft.public.access.reports
MNJoe
external usenet poster
 
Posts: 30
Default change control source or value of text box

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,SYSAD M_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" wrote:

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" wrote in message
...
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.

--
MNJoe


  #5  
Old May 24th, 2010, 07:35 PM posted to microsoft.public.access.reports
MNJoe
external usenet poster
 
Posts: 30
Default change control source or value of text box

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_P URC_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_P URC_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" wrote:

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

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



"MNJoe" wrote in message
...
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.

--
MNJoe



.

  #6  
Old May 24th, 2010, 07:50 PM posted to microsoft.public.access.reports
Duane Hookom
external usenet poster
 
Posts: 7,177
Default change control source or value of text box

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/...ddbuz/threads?


"MNJoe" wrote:

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,SYSAD M_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" wrote:

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" wrote in message
...
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.

--
MNJoe


  #7  
Old May 24th, 2010, 09:17 PM posted to microsoft.public.access.reports
MNJoe
external usenet poster
 
Posts: 30
Default change control source or value of text box

Never mind. I was missing the "E" on the very end of the last "DATE" Now it
works

Thanks

--
MNJoe


"MNJoe" wrote:

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_P URC_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_P URC_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" wrote:

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

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



"MNJoe" wrote in message
...
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.

--
MNJoe



.

  #8  
Old May 25th, 2010, 04:42 PM posted to microsoft.public.access.reports
MNJoe
external usenet poster
 
Posts: 30
Default change control source or value of text box

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" wrote:

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/...ddbuz/threads?


"MNJoe" wrote:

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,SYSAD M_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" wrote:

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" wrote in message
...
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.

--
MNJoe

  #9  
Old May 25th, 2010, 05:04 PM posted to microsoft.public.access.reports
MNJoe
external usenet poster
 
Posts: 30
Default change control source or value of text box

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" wrote:

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" wrote:

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/...ddbuz/threads?


"MNJoe" wrote:

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,SYSAD M_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" wrote:

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" wrote in message
...
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.

--
MNJoe

  #10  
Old May 25th, 2010, 08:12 PM posted to microsoft.public.access.reports
John Spencer
external usenet poster
 
Posts: 7,815
Default change control source or value of text box

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

MNJoe wrote:
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.

 




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 11:56 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.