Welcome to SPN

Register and Join the most happening forum of Sikh community & intellectuals from around the world.

Sign Up Now!

Report Problem with Dates

Discussion in 'Information Technology' started by Paul, Jul 28, 2006.

  1. Paul

    Paul
    Expand Collapse
    Guest

    I have a report form from a big access database. It has numerous date fields
    to track useful information. Some of these fields are blank.

    The report prints fine as is but there is a request to substitute in for the
    blank fields another date field output if it is blank.

    Typically the fields have names like me.Acutal_Date and me.Proposed_Date.
    Right now the report simply addresses fields by (Actual_Date) and
    (Proposed_Date).
    If the (Actual_Date) is blank and I want to substitute in the
    (Proposed_Date) I have trouble.

    I have tried a VBA code passing as parameters the two fields to a function.
    The data alway goes out from the Proposed_Date field and ignores any
    Actual_Date field. Even when named properly. As such I don't get my
    sustitution properly for blank fields.

    I have tried using an =IIF(not
    IsNull(Actual_Date)=true,Acutal_Date,Proposed_Date) type statement as well.
    This always results in the Proposed_Date.

    Please help.

    Thanks in Advance
     
  2. Loading...

    Similar Threads Forum Date
    UK British Sikh Report-2016 Breaking News Mar 24, 2016
    1984 The Story I Did Not Report In ’84 History of Sikhism Jun 28, 2015
    Opinion Top Green Beret Officer Forced to Resign Over Affair With WaPo Reporter Breaking News Jun 26, 2014
    United Sikhs New Report Shows Increasing Hate Attacks on Sikhs Sikh Organisations May 21, 2014
    Legal ACLU reports: Judge orders Sikh man, "Remove that rag, or Go to Jail! Breaking News Sep 27, 2013

  3. Allen Browne

    Allen Browne
    Expand Collapse
    Guest

    The Nz() function lets you substitute something for null.
    You could set the ControlSource of your text box to:
    =Nz([Actual_Date], [Proposed_Date])

    Be sure to change the Name of this text box to something else (e.g.
    txtActualOrProposed.) Access gets confused if the control has the same Name
    as a field, but the ControlSource is somthing different.

    --
    Allen Browne - Microsoft MVP. Perth, Western Australia.
    Tips for Access users - http://allenbrowne.com/tips.html
    Reply to group, rather than allenbrowne at mvps dot org.

    "Paul" <Paul@discussions.microsoft.com> wrote in message
    news:1D2B5D7E-5C1D-4FE0-970D-08A2F275BB27@microsoft.com...
    >I have a report form from a big access database. It has numerous date
    >fields
    > to track useful information. Some of these fields are blank.
    >
    > The report prints fine as is but there is a request to substitute in for
    > the
    > blank fields another date field output if it is blank.
    >
    > Typically the fields have names like me.Acutal_Date and me.Proposed_Date.
    > Right now the report simply addresses fields by (Actual_Date) and
    > (Proposed_Date).
    > If the (Actual_Date) is blank and I want to substitute in the
    > (Proposed_Date) I have trouble.
    >
    > I have tried a VBA code passing as parameters the two fields to a
    > function.
    > The data alway goes out from the Proposed_Date field and ignores any
    > Actual_Date field. Even when named properly. As such I don't get my
    > sustitution properly for blank fields.
    >
    > I have tried using an =IIF(not
    > IsNull(Actual_Date)=true,Acutal_Date,Proposed_Date) type statement as
    > well.
    > This always results in the Proposed_Date.
    >
    > Please help.
    >
    > Thanks in Advance
     
  4. Paul

    Paul
    Expand Collapse
    Guest

    This problem I actually wrote what you said into the control of the text box
    and it is not named the same.

    The results
    =Nz([Actual_Date], [Proposed_Date])
    [if the Actual_Date is Null] = Proposed_Date
    [if the Actual_Date is not null] = Proposed_Date
    This is always going to the null value response
    We have some deeper problem needing you most kind suggestions.

    Thanks

    "Allen Browne" wrote:

    > The Nz() function lets you substitute something for null.
    > You could set the ControlSource of your text box to:
    > =Nz([Actual_Date], [Proposed_Date])
    >
    > Be sure to change the Name of this text box to something else (e.g.
    > txtActualOrProposed.) Access gets confused if the control has the same Name
    > as a field, but the ControlSource is somthing different.
    >
    > --
    > Allen Browne - Microsoft MVP. Perth, Western Australia.
    > Tips for Access users - http://allenbrowne.com/tips.html
    > Reply to group, rather than allenbrowne at mvps dot org.
    >
    > "Paul" <Paul@discussions.microsoft.com> wrote in message
    > news:1D2B5D7E-5C1D-4FE0-970D-08A2F275BB27@microsoft.com...
    > >I have a report form from a big access database. It has numerous date
    > >fields
    > > to track useful information. Some of these fields are blank.
    > >
    > > The report prints fine as is but there is a request to substitute in for
    > > the
    > > blank fields another date field output if it is blank.
    > >
    > > Typically the fields have names like me.Acutal_Date and me.Proposed_Date.
    > > Right now the report simply addresses fields by (Actual_Date) and
    > > (Proposed_Date).
    > > If the (Actual_Date) is blank and I want to substitute in the
    > > (Proposed_Date) I have trouble.
    > >
    > > I have tried a VBA code passing as parameters the two fields to a
    > > function.
    > > The data alway goes out from the Proposed_Date field and ignores any
    > > Actual_Date field. Even when named properly. As such I don't get my
    > > sustitution properly for blank fields.
    > >
    > > I have tried using an =IIF(not
    > > IsNull(Actual_Date)=true,Acutal_Date,Proposed_Date) type statement as
    > > well.
    > > This always results in the Proposed_Date.
    > >
    > > Please help.
    > >
    > > Thanks in Advance

    >
    >
    >
     
  5. Paul

    Paul
    Expand Collapse
    Guest

    Adding a bit more information
    If the control source reads

    Actual_Date

    The output is fine but it prints the blank or null spaces in the report.

    if the control Source says

    =[Acutal_Date] ---- or ----- = Nz([Acutal_Date],[Proposed_Date])
    ----- or ------

    =IIF(Not IsNull([Acutal_Date),[Actual_Date],[Proposed_Date])

    All I get back is the [Proposed_Date] information

    The text box is just named txtBox145

    Paul Noel

    "Paul" wrote:

    > This problem I actually wrote what you said into the control of the text box
    > and it is not named the same.
    >
    > The results
    > =Nz([Actual_Date], [Proposed_Date])
    > [if the Actual_Date is Null] = Proposed_Date
    > [if the Actual_Date is not null] = Proposed_Date
    > This is always going to the null value response
    > We have some deeper problem needing you most kind suggestions.
    >
    > Thanks
    >
    > "Allen Browne" wrote:
    >
    > > The Nz() function lets you substitute something for null.
    > > You could set the ControlSource of your text box to:
    > > =Nz([Actual_Date], [Proposed_Date])
    > >
    > > Be sure to change the Name of this text box to something else (e.g.
    > > txtActualOrProposed.) Access gets confused if the control has the same Name
    > > as a field, but the ControlSource is somthing different.
    > >
    > > --
    > > Allen Browne - Microsoft MVP. Perth, Western Australia.
    > > Tips for Access users - http://allenbrowne.com/tips.html
    > > Reply to group, rather than allenbrowne at mvps dot org.
    > >
    > > "Paul" <Paul@discussions.microsoft.com> wrote in message
    > > news:1D2B5D7E-5C1D-4FE0-970D-08A2F275BB27@microsoft.com...
    > > >I have a report form from a big access database. It has numerous date
    > > >fields
    > > > to track useful information. Some of these fields are blank.
    > > >
    > > > The report prints fine as is but there is a request to substitute in for
    > > > the
    > > > blank fields another date field output if it is blank.
    > > >
    > > > Typically the fields have names like me.Acutal_Date and me.Proposed_Date.
    > > > Right now the report simply addresses fields by (Actual_Date) and
    > > > (Proposed_Date).
    > > > If the (Actual_Date) is blank and I want to substitute in the
    > > > (Proposed_Date) I have trouble.
    > > >
    > > > I have tried a VBA code passing as parameters the two fields to a
    > > > function.
    > > > The data alway goes out from the Proposed_Date field and ignores any
    > > > Actual_Date field. Even when named properly. As such I don't get my
    > > > sustitution properly for blank fields.
    > > >
    > > > I have tried using an =IIF(not
    > > > IsNull(Actual_Date)=true,Acutal_Date,Proposed_Date) type statement as
    > > > well.
    > > > This always results in the Proposed_Date.
    > > >
    > > > Please help.
    > > >
    > > > Thanks in Advance

    > >
    > >
    > >
     
  6. Allen Browne

    Allen Browne
    Expand Collapse
    Guest

    Tell use about these fields.

    Do they come directly from a table?
    If so what data type? Date/Time? Text?

    Or are they calculated fields in a query?
    If so post the calcuation.

    If the value is in fact Null, what you have should work.
    If it does not work, the value may not be null.

    --
    Allen Browne - Microsoft MVP. Perth, Western Australia.
    Tips for Access users - http://allenbrowne.com/tips.html
    Reply to group, rather than allenbrowne at mvps dot org.

    "Paul" <Paul@discussions.microsoft.com> wrote in message
    news:15C3A005-3246-4851-9507-A84B4FF2E7C4@microsoft.com...
    > Adding a bit more information
    > If the control source reads
    >
    > Actual_Date
    >
    > The output is fine but it prints the blank or null spaces in the report.
    >
    > if the control Source says
    >
    > =[Acutal_Date] ---- or ----- = Nz([Acutal_Date],[Proposed_Date])
    > ----- or ------
    >
    > =IIF(Not IsNull([Acutal_Date),[Actual_Date],[Proposed_Date])
    >
    > All I get back is the [Proposed_Date] information
    >
    > The text box is just named txtBox145
    >
    > Paul Noel
    >
    > "Paul" wrote:
    >
    >> This problem I actually wrote what you said into the control of the text
    >> box
    >> and it is not named the same.
    >>
    >> The results
    >> =Nz([Actual_Date], [Proposed_Date])
    >> [if the Actual_Date is Null] = Proposed_Date
    >> [if the Actual_Date is not null] = Proposed_Date
    >> This is always going to the null value response
    >> We have some deeper problem needing you most kind suggestions.
    >>
    >> Thanks
    >>
    >> "Allen Browne" wrote:
    >>
    >> > The Nz() function lets you substitute something for null.
    >> > You could set the ControlSource of your text box to:
    >> > =Nz([Actual_Date], [Proposed_Date])
    >> >
    >> > Be sure to change the Name of this text box to something else (e.g.
    >> > txtActualOrProposed.) Access gets confused if the control has the same
    >> > Name
    >> > as a field, but the ControlSource is somthing different.
    >> >
    >> > "Paul" <Paul@discussions.microsoft.com> wrote in message
    >> > news:1D2B5D7E-5C1D-4FE0-970D-08A2F275BB27@microsoft.com...
    >> > >I have a report form from a big access database. It has numerous date
    >> > >fields
    >> > > to track useful information. Some of these fields are blank.
    >> > >
    >> > > The report prints fine as is but there is a request to substitute in
    >> > > for
    >> > > the
    >> > > blank fields another date field output if it is blank.
    >> > >
    >> > > Typically the fields have names like me.Acutal_Date and
    >> > > me.Proposed_Date.
    >> > > Right now the report simply addresses fields by (Actual_Date) and
    >> > > (Proposed_Date).
    >> > > If the (Actual_Date) is blank and I want to substitute in the
    >> > > (Proposed_Date) I have trouble.
    >> > >
    >> > > I have tried a VBA code passing as parameters the two fields to a
    >> > > function.
    >> > > The data alway goes out from the Proposed_Date field and ignores any
    >> > > Actual_Date field. Even when named properly. As such I don't get my
    >> > > sustitution properly for blank fields.
    >> > >
    >> > > I have tried using an =IIF(not
    >> > > IsNull(Actual_Date)=true,Acutal_Date,Proposed_Date) type statement as
    >> > > well.
    >> > > This always results in the Proposed_Date.
    >> > >
    >> > > Please help.
    >> > >
    >> > > Thanks in Advance
     
  7. Paul

    Paul
    Expand Collapse
    Guest

    The fields are derived using a wizard generated report form and they come
    directly from a table. I cannot give you the exact code for business
    limitations but I am giving you the exact formats.

    The report has a text box (txtBox131) or something like that that was
    autogenerated by the report wizard. The assigned control property is a
    standard date field in the table and its name while different from what I
    sent you is exactly the same in format. If I go to the Report design view
    and click on the properties for the text box the name of the database
    property comes up without brackets when this is working right. If you put
    brackets around the value it becomes the next value in the table. If you
    try the Nz() function or using a pass to a function same. The pointer to the
    file is getting off by one location.

    "Allen Browne" wrote:

    > Tell use about these fields.
    >
    > Do they come directly from a table?
    > If so what data type? Date/Time? Text?
    >
    > Or are they calculated fields in a query?
    > If so post the calcuation.
    >
    > If the value is in fact Null, what you have should work.
    > If it does not work, the value may not be null.
    >
    > --
    > Allen Browne - Microsoft MVP. Perth, Western Australia.
    > Tips for Access users - http://allenbrowne.com/tips.html
    > Reply to group, rather than allenbrowne at mvps dot org.
    >
    > "Paul" <Paul@discussions.microsoft.com> wrote in message
    > news:15C3A005-3246-4851-9507-A84B4FF2E7C4@microsoft.com...
    > > Adding a bit more information
    > > If the control source reads
    > >
    > > Actual_Date
    > >
    > > The output is fine but it prints the blank or null spaces in the report.
    > >
    > > if the control Source says
    > >
    > > =[Acutal_Date] ---- or ----- = Nz([Acutal_Date],[Proposed_Date])
    > > ----- or ------
    > >
    > > =IIF(Not IsNull([Acutal_Date),[Actual_Date],[Proposed_Date])
    > >
    > > All I get back is the [Proposed_Date] information
    > >
    > > The text box is just named txtBox145
    > >
    > > Paul Noel
    > >
    > > "Paul" wrote:
    > >
    > >> This problem I actually wrote what you said into the control of the text
    > >> box
    > >> and it is not named the same.
    > >>
    > >> The results
    > >> =Nz([Actual_Date], [Proposed_Date])
    > >> [if the Actual_Date is Null] = Proposed_Date
    > >> [if the Actual_Date is not null] = Proposed_Date
    > >> This is always going to the null value response
    > >> We have some deeper problem needing you most kind suggestions.
    > >>
    > >> Thanks
    > >>
    > >> "Allen Browne" wrote:
    > >>
    > >> > The Nz() function lets you substitute something for null.
    > >> > You could set the ControlSource of your text box to:
    > >> > =Nz([Actual_Date], [Proposed_Date])
    > >> >
    > >> > Be sure to change the Name of this text box to something else (e.g.
    > >> > txtActualOrProposed.) Access gets confused if the control has the same
    > >> > Name
    > >> > as a field, but the ControlSource is somthing different.
    > >> >
    > >> > "Paul" <Paul@discussions.microsoft.com> wrote in message
    > >> > news:1D2B5D7E-5C1D-4FE0-970D-08A2F275BB27@microsoft.com...
    > >> > >I have a report form from a big access database. It has numerous date
    > >> > >fields
    > >> > > to track useful information. Some of these fields are blank.
    > >> > >
    > >> > > The report prints fine as is but there is a request to substitute in
    > >> > > for
    > >> > > the
    > >> > > blank fields another date field output if it is blank.
    > >> > >
    > >> > > Typically the fields have names like me.Acutal_Date and
    > >> > > me.Proposed_Date.
    > >> > > Right now the report simply addresses fields by (Actual_Date) and
    > >> > > (Proposed_Date).
    > >> > > If the (Actual_Date) is blank and I want to substitute in the
    > >> > > (Proposed_Date) I have trouble.
    > >> > >
    > >> > > I have tried a VBA code passing as parameters the two fields to a
    > >> > > function.
    > >> > > The data alway goes out from the Proposed_Date field and ignores any
    > >> > > Actual_Date field. Even when named properly. As such I don't get my
    > >> > > sustitution properly for blank fields.
    > >> > >
    > >> > > I have tried using an =IIF(not
    > >> > > IsNull(Actual_Date)=true,Acutal_Date,Proposed_Date) type statement as
    > >> > > well.
    > >> > > This always results in the Proposed_Date.
    > >> > >
    > >> > > Please help.
    > >> > >
    > >> > > Thanks in Advance

    >
    >
    >
     
  8. Allen Browne

    Allen Browne
    Expand Collapse
    Guest

    I didn't follow the bit about "off by one location"?
    Is it returning the wrong value from a lookup table?
    Or the value from the next record in the report?
    Or ?

    --
    Allen Browne - Microsoft MVP. Perth, Western Australia.
    Tips for Access users - http://allenbrowne.com/tips.html
    Reply to group, rather than allenbrowne at mvps dot org.

    "Paul" <Paul@discussions.microsoft.com> wrote in message
    news:F046ADA6-69EA-4A59-9A1F-74C1087D2E45@microsoft.com...
    > The fields are derived using a wizard generated report form and they come
    > directly from a table. I cannot give you the exact code for business
    > limitations but I am giving you the exact formats.
    >
    > The report has a text box (txtBox131) or something like that that was
    > autogenerated by the report wizard. The assigned control property is a
    > standard date field in the table and its name while different from what I
    > sent you is exactly the same in format. If I go to the Report design view
    > and click on the properties for the text box the name of the database
    > property comes up without brackets when this is working right. If you put
    > brackets around the value it becomes the next value in the table. If you
    > try the Nz() function or using a pass to a function same. The pointer to
    > the
    > file is getting off by one location.
    >
    > "Allen Browne" wrote:
    >
    >> Tell use about these fields.
    >>
    >> Do they come directly from a table?
    >> If so what data type? Date/Time? Text?
    >>
    >> Or are they calculated fields in a query?
    >> If so post the calcuation.
    >>
    >> If the value is in fact Null, what you have should work.
    >> If it does not work, the value may not be null.
    >>
    >> "Paul" <Paul@discussions.microsoft.com> wrote in message
    >> news:15C3A005-3246-4851-9507-A84B4FF2E7C4@microsoft.com...
    >> > Adding a bit more information
    >> > If the control source reads
    >> >
    >> > Actual_Date
    >> >
    >> > The output is fine but it prints the blank or null spaces in the
    >> > report.
    >> >
    >> > if the control Source says
    >> >
    >> > =[Acutal_Date] ---- or ----- = Nz([Acutal_Date],[Proposed_Date])
    >> > ----- or ------
    >> >
    >> > =IIF(Not IsNull([Acutal_Date),[Actual_Date],[Proposed_Date])
    >> >
    >> > All I get back is the [Proposed_Date] information
    >> >
    >> > The text box is just named txtBox145
    >> >
    >> > Paul Noel
    >> >
    >> > "Paul" wrote:
    >> >
    >> >> This problem I actually wrote what you said into the control of the
    >> >> text
    >> >> box
    >> >> and it is not named the same.
    >> >>
    >> >> The results
    >> >> =Nz([Actual_Date], [Proposed_Date])
    >> >> [if the Actual_Date is Null] = Proposed_Date
    >> >> [if the Actual_Date is not null] = Proposed_Date
    >> >> This is always going to the null value response
    >> >> We have some deeper problem needing you most kind suggestions.
    >> >>
    >> >> Thanks
    >> >>
    >> >> "Allen Browne" wrote:
    >> >>
    >> >> > The Nz() function lets you substitute something for null.
    >> >> > You could set the ControlSource of your text box to:
    >> >> > =Nz([Actual_Date], [Proposed_Date])
    >> >> >
    >> >> > Be sure to change the Name of this text box to something else (e.g.
    >> >> > txtActualOrProposed.) Access gets confused if the control has the
    >> >> > same
    >> >> > Name
    >> >> > as a field, but the ControlSource is somthing different.
    >> >> >
    >> >> > "Paul" <Paul@discussions.microsoft.com> wrote in message
    >> >> > news:1D2B5D7E-5C1D-4FE0-970D-08A2F275BB27@microsoft.com...
    >> >> > >I have a report form from a big access database. It has numerous
    >> >> > >date
    >> >> > >fields
    >> >> > > to track useful information. Some of these fields are blank.
    >> >> > >
    >> >> > > The report prints fine as is but there is a request to substitute
    >> >> > > in
    >> >> > > for
    >> >> > > the
    >> >> > > blank fields another date field output if it is blank.
    >> >> > >
    >> >> > > Typically the fields have names like me.Acutal_Date and
    >> >> > > me.Proposed_Date.
    >> >> > > Right now the report simply addresses fields by (Actual_Date) and
    >> >> > > (Proposed_Date).
    >> >> > > If the (Actual_Date) is blank and I want to substitute in the
    >> >> > > (Proposed_Date) I have trouble.
    >> >> > >
    >> >> > > I have tried a VBA code passing as parameters the two fields to a
    >> >> > > function.
    >> >> > > The data alway goes out from the Proposed_Date field and ignores
    >> >> > > any
    >> >> > > Actual_Date field. Even when named properly. As such I don't get
    >> >> > > my
    >> >> > > sustitution properly for blank fields.
    >> >> > >
    >> >> > > I have tried using an =IIF(not
    >> >> > > IsNull(Actual_Date)=true,Acutal_Date,Proposed_Date) type statement
    >> >> > > as
    >> >> > > well.
    >> >> > > This always results in the Proposed_Date.
     
  9. Paul

    Paul
    Expand Collapse
    Guest

    The two fields are adjacent and in order in the table
    The Report if the fields are extracted merely by their values appears in
    order and in good condition. The report is just fine as is. The requested
    change was to remove the blank date fields that appear in the report by
    placing the next column over value as I stated above in the column

    Report
    xxx xxx xxx xxx xxx xxxx (Actual_Date) (Proposed_Date) xxx
    xxx

    if the (Actual_Date) is blank then we want to put the (Proposed_Date) in the
    report like this.

    Report
    xxx xxx xxx xxx xxx xxxx (Proposed_Date) (Proposed_Date)
    xxx xxx

    Everything else is just like I reported here in previous posts. The
    problem is that when I set up the value for (Proposed_Date) in the Control
    field it works find and comes out blank Control content --> Proposed_Date
    <----

    If I put the content to -->[Actual_Date] <-- or -->=[Actual_Date]<-- or to
    -->=Nz([Actual_Date],[Proposed_Date]) the control results in a report that
    looks like this even if the Actual_Date is not blank!
    Report
    xxx xxx xxx xxx xxx xxxx (Proposed_Date) (Proposed_Date)
    xxx xxx
    In short I lost the (Actual_Date) entirely!

    The Report is a simple report done using the report wizard. There is no
    complicated report content here.

    Paul Noel

    "Allen Browne" wrote:

    > I didn't follow the bit about "off by one location"?
    > Is it returning the wrong value from a lookup table?
    > Or the value from the next record in the report?
    > Or ?
    >
    > --
    > Allen Browne - Microsoft MVP. Perth, Western Australia.
    > Tips for Access users - http://allenbrowne.com/tips.html
    > Reply to group, rather than allenbrowne at mvps dot org.
    >
    > "Paul" <Paul@discussions.microsoft.com> wrote in message
    > news:F046ADA6-69EA-4A59-9A1F-74C1087D2E45@microsoft.com...
    > > The fields are derived using a wizard generated report form and they come
    > > directly from a table. I cannot give you the exact code for business
    > > limitations but I am giving you the exact formats.
    > >
    > > The report has a text box (txtBox131) or something like that that was
    > > autogenerated by the report wizard. The assigned control property is a
    > > standard date field in the table and its name while different from what I
    > > sent you is exactly the same in format. If I go to the Report design view
    > > and click on the properties for the text box the name of the database
    > > property comes up without brackets when this is working right. If you put
    > > brackets around the value it becomes the next value in the table. If you
    > > try the Nz() function or using a pass to a function same. The pointer to
    > > the
    > > file is getting off by one location.
    > >
    > > "Allen Browne" wrote:
    > >
    > >> Tell use about these fields.
    > >>
    > >> Do they come directly from a table?
    > >> If so what data type? Date/Time? Text?
    > >>
    > >> Or are they calculated fields in a query?
    > >> If so post the calcuation.
    > >>
    > >> If the value is in fact Null, what you have should work.
    > >> If it does not work, the value may not be null.
    > >>
    > >> "Paul" <Paul@discussions.microsoft.com> wrote in message
    > >> news:15C3A005-3246-4851-9507-A84B4FF2E7C4@microsoft.com...
    > >> > Adding a bit more information
    > >> > If the control source reads
    > >> >
    > >> > Actual_Date
    > >> >
    > >> > The output is fine but it prints the blank or null spaces in the
    > >> > report.
    > >> >
    > >> > if the control Source says
    > >> >
    > >> > =[Acutal_Date] ---- or ----- = Nz([Acutal_Date],[Proposed_Date])
    > >> > ----- or ------
    > >> >
    > >> > =IIF(Not IsNull([Acutal_Date),[Actual_Date],[Proposed_Date])
    > >> >
    > >> > All I get back is the [Proposed_Date] information
    > >> >
    > >> > The text box is just named txtBox145
    > >> >
    > >> > Paul Noel
    > >> >
    > >> > "Paul" wrote:
    > >> >
    > >> >> This problem I actually wrote what you said into the control of the
    > >> >> text
    > >> >> box
    > >> >> and it is not named the same.
    > >> >>
    > >> >> The results
    > >> >> =Nz([Actual_Date], [Proposed_Date])
    > >> >> [if the Actual_Date is Null] = Proposed_Date
    > >> >> [if the Actual_Date is not null] = Proposed_Date
    > >> >> This is always going to the null value response
    > >> >> We have some deeper problem needing you most kind suggestions.
    > >> >>
    > >> >> Thanks
    > >> >>
    > >> >> "Allen Browne" wrote:
    > >> >>
    > >> >> > The Nz() function lets you substitute something for null.
    > >> >> > You could set the ControlSource of your text box to:
    > >> >> > =Nz([Actual_Date], [Proposed_Date])
    > >> >> >
    > >> >> > Be sure to change the Name of this text box to something else (e.g.
    > >> >> > txtActualOrProposed.) Access gets confused if the control has the
    > >> >> > same
    > >> >> > Name
    > >> >> > as a field, but the ControlSource is somthing different.
    > >> >> >
    > >> >> > "Paul" <Paul@discussions.microsoft.com> wrote in message
    > >> >> > news:1D2B5D7E-5C1D-4FE0-970D-08A2F275BB27@microsoft.com...
    > >> >> > >I have a report form from a big access database. It has numerous
    > >> >> > >date
    > >> >> > >fields
    > >> >> > > to track useful information. Some of these fields are blank.
    > >> >> > >
    > >> >> > > The report prints fine as is but there is a request to substitute
    > >> >> > > in
    > >> >> > > for
    > >> >> > > the
    > >> >> > > blank fields another date field output if it is blank.
    > >> >> > >
    > >> >> > > Typically the fields have names like me.Acutal_Date and
    > >> >> > > me.Proposed_Date.
    > >> >> > > Right now the report simply addresses fields by (Actual_Date) and
    > >> >> > > (Proposed_Date).
    > >> >> > > If the (Actual_Date) is blank and I want to substitute in the
    > >> >> > > (Proposed_Date) I have trouble.
    > >> >> > >
    > >> >> > > I have tried a VBA code passing as parameters the two fields to a
    > >> >> > > function.
    > >> >> > > The data alway goes out from the Proposed_Date field and ignores
    > >> >> > > any
    > >> >> > > Actual_Date field. Even when named properly. As such I don't get
    > >> >> > > my
    > >> >> > > sustitution properly for blank fields.
    > >> >> > >
    > >> >> > > I have tried using an =IIF(not
    > >> >> > > IsNull(Actual_Date)=true,Acutal_Date,Proposed_Date) type statement
    > >> >> > > as
    > >> >> > > well.
    > >> >> > > This always results in the Proposed_Date.

    >
    >
    >
     
  10. Allen Browne

    Allen Browne
    Expand Collapse
    Guest

    Sorry, Paul, I don't know what else to suggest.

    I would have expected this to return the Actual_Date if that field has a
    value, and if Actual_Date is Null then the Proposed_Date should appear in
    the column instead:
    =Nz([Actual_Date], [Proposed_Date])

    --
    Allen Browne - Microsoft MVP. Perth, Western Australia.
    Tips for Access users - http://allenbrowne.com/tips.html
    Reply to group, rather than allenbrowne at mvps dot org.

    "Paul" <Paul@discussions.microsoft.com> wrote in message
    news:C06378C0-E5B3-4840-B6F6-0121F39DF1BC@microsoft.com...
    > The two fields are adjacent and in order in the table
    > The Report if the fields are extracted merely by their values appears in
    > order and in good condition. The report is just fine as is. The requested
    > change was to remove the blank date fields that appear in the report by
    > placing the next column over value as I stated above in the column
    >
    > Report
    > xxx xxx xxx xxx xxx xxxx (Actual_Date) (Proposed_Date)
    > xxx
    > xxx
    >
    > if the (Actual_Date) is blank then we want to put the (Proposed_Date) in
    > the
    > report like this.
    >
    > Report
    > xxx xxx xxx xxx xxx xxxx (Proposed_Date) (Proposed_Date)
    > xxx xxx
    >
    > Everything else is just like I reported here in previous posts. The
    > problem is that when I set up the value for (Proposed_Date) in the
    > Control
    > field it works find and comes out blank Control content -->
    > Proposed_Date
    > <----
    >
    > If I put the content to -->[Actual_Date] <-- or -->=[Actual_Date]<-- or
    > to
    > -->=Nz([Actual_Date],[Proposed_Date]) the control results in a report that
    > looks like this even if the Actual_Date is not blank!
    > Report
    > xxx xxx xxx xxx xxx xxxx (Proposed_Date) (Proposed_Date)
    > xxx xxx
    > In short I lost the (Actual_Date) entirely!
    >
    > The Report is a simple report done using the report wizard. There is no
    > complicated report content here.
    >
    > Paul Noel
    >
    > "Allen Browne" wrote:
    >
    >> I didn't follow the bit about "off by one location"?
    >> Is it returning the wrong value from a lookup table?
    >> Or the value from the next record in the report?
    >> Or ?
    >>
    >> "Paul" <Paul@discussions.microsoft.com> wrote in message
    >> news:F046ADA6-69EA-4A59-9A1F-74C1087D2E45@microsoft.com...
    >> > The fields are derived using a wizard generated report form and they
    >> > come
    >> > directly from a table. I cannot give you the exact code for business
    >> > limitations but I am giving you the exact formats.
    >> >
    >> > The report has a text box (txtBox131) or something like that that was
    >> > autogenerated by the report wizard. The assigned control property is
    >> > a
    >> > standard date field in the table and its name while different from what
    >> > I
    >> > sent you is exactly the same in format. If I go to the Report design
    >> > view
    >> > and click on the properties for the text box the name of the database
    >> > property comes up without brackets when this is working right. If you
    >> > put
    >> > brackets around the value it becomes the next value in the table. If
    >> > you
    >> > try the Nz() function or using a pass to a function same. The pointer
    >> > to
    >> > the
    >> > file is getting off by one location.
    >> >
    >> > "Allen Browne" wrote:
    >> >
    >> >> Tell use about these fields.
    >> >>
    >> >> Do they come directly from a table?
    >> >> If so what data type? Date/Time? Text?
    >> >>
    >> >> Or are they calculated fields in a query?
    >> >> If so post the calcuation.
    >> >>
    >> >> If the value is in fact Null, what you have should work.
    >> >> If it does not work, the value may not be null.
    >> >>
    >> >> "Paul" <Paul@discussions.microsoft.com> wrote in message
    >> >> news:15C3A005-3246-4851-9507-A84B4FF2E7C4@microsoft.com...
    >> >> > Adding a bit more information
    >> >> > If the control source reads
    >> >> >
    >> >> > Actual_Date
    >> >> >
    >> >> > The output is fine but it prints the blank or null spaces in the
    >> >> > report.
    >> >> >
    >> >> > if the control Source says
    >> >> >
    >> >> > =[Acutal_Date] ---- or ----- =
    >> >> > Nz([Acutal_Date],[Proposed_Date])
    >> >> > ----- or ------
    >> >> >
    >> >> > =IIF(Not IsNull([Acutal_Date),[Actual_Date],[Proposed_Date])
    >> >> >
    >> >> > All I get back is the [Proposed_Date] information
    >> >> >
    >> >> > The text box is just named txtBox145
    >> >> >
    >> >> > Paul Noel
    >> >> >
    >> >> > "Paul" wrote:
    >> >> >
    >> >> >> This problem I actually wrote what you said into the control of the
    >> >> >> text
    >> >> >> box
    >> >> >> and it is not named the same.
    >> >> >>
    >> >> >> The results
    >> >> >> =Nz([Actual_Date], [Proposed_Date])
    >> >> >> [if the Actual_Date is Null] = Proposed_Date
    >> >> >> [if the Actual_Date is not null] = Proposed_Date
    >> >> >> This is always going to the null value response
    >> >> >> We have some deeper problem needing you most kind suggestions.
    >> >> >>
    >> >> >> Thanks
    >> >> >>
    >> >> >> "Allen Browne" wrote:
    >> >> >>
    >> >> >> > The Nz() function lets you substitute something for null.
    >> >> >> > You could set the ControlSource of your text box to:
    >> >> >> > =Nz([Actual_Date], [Proposed_Date])
    >> >> >> >
    >> >> >> > Be sure to change the Name of this text box to something else
    >> >> >> > (e.g.
    >> >> >> > txtActualOrProposed.) Access gets confused if the control has the
    >> >> >> > same
    >> >> >> > Name
    >> >> >> > as a field, but the ControlSource is somthing different.
    >> >> >> >
    >> >> >> > "Paul" <Paul@discussions.microsoft.com> wrote in message
    >> >> >> > news:1D2B5D7E-5C1D-4FE0-970D-08A2F275BB27@microsoft.com...
    >> >> >> > >I have a report form from a big access database. It has numerous
    >> >> >> > >date
    >> >> >> > >fields
    >> >> >> > > to track useful information. Some of these fields are blank.
    >> >> >> > >
    >> >> >> > > The report prints fine as is but there is a request to
    >> >> >> > > substitute
    >> >> >> > > in
    >> >> >> > > for
    >> >> >> > > the
    >> >> >> > > blank fields another date field output if it is blank.
    >> >> >> > >
    >> >> >> > > Typically the fields have names like me.Acutal_Date and
    >> >> >> > > me.Proposed_Date.
    >> >> >> > > Right now the report simply addresses fields by (Actual_Date)
    >> >> >> > > and
    >> >> >> > > (Proposed_Date).
    >> >> >> > > If the (Actual_Date) is blank and I want to substitute in the
    >> >> >> > > (Proposed_Date) I have trouble.
    >> >> >> > >
    >> >> >> > > I have tried a VBA code passing as parameters the two fields to
    >> >> >> > > a
    >> >> >> > > function.
    >> >> >> > > The data alway goes out from the Proposed_Date field and
    >> >> >> > > ignores
    >> >> >> > > any
    >> >> >> > > Actual_Date field. Even when named properly. As such I don't
    >> >> >> > > get
    >> >> >> > > my
    >> >> >> > > sustitution properly for blank fields.
    >> >> >> > >
    >> >> >> > > I have tried using an =IIF(not
    >> >> >> > > IsNull(Actual_Date)=true,Acutal_Date,Proposed_Date) type
    >> >> >> > > statement
    >> >> >> > > as
    >> >> >> > > well.
    >> >> >> > > This always results in the Proposed_Date.
     

Share This Page