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. |
|
|
Thread Tools | Display Modes |
#1
|
|||
|
|||
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
|
|||
|
|||
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
|
|||
|
|||
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
|
|||
|
|||
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
|
|||
|
|||
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
|
|||
|
|||
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
|
|||
|
|||
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
|
|||
|
|||
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
|
|||
|
|||
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
|
|||
|
|||
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 | |
|
|