One Result from multiple memo fields 05-03-07

I am an Access newbie, so I am not even sure this can be done...

I need a report to pull material listing from a memo field [Notes]
or show which jobs have missing materials...

My report has the following record source:
SELECT dbo_RB_JOBNOTE.JOBID, dbo_RB_JOBNOTE.ID, dbo_RB_JOBNOTE.NOTE,
dbo_RB_JOBNOTE.OWNERFULLNAME
FROM dbo_RB_JOBNOTE;

The report has a Group header on JOBID
and a [Material] text field with the following control source:
=IIf([NOTE] Like "Material*",[NOTE],"No Material listing found")

What I get is a listing of all the notes,
-----------------
Example:
Job1
  No Material listing found
  Material note2
  No Material listing found
Job2
  No Material listing found
  No Material listing found
  Material note1
Job3
  No Material listing found
  No Material listing found
  No Material listing found
Job4
  Material note1
  Material note2
  No Material listing found
-----------------


I only want the notes if materials are listed
or the text "No Material listing found" if not,
----------------
Example:
Job1
  Material note2
Job2
  Material note1
Job3
  No Material listing found
Job4
  Material note1
  Material note2
-----------------


All and any help greatly appreciated

0
Sunflower
5/3/2007 1:32:48 AM
access.reports 4434 articles. 0 followers. Follow

10 Replies
929 Views

Similar Articles

[PageSpeed] 11

since no one has replied - I will give one suggestion;  consider splitting 
your task in two....

first I would consider establishing the results you want using a query - 
without attempting to insert the default phrase.....

with the correct query then, if I understand you correctly, you will have a 
set of records and some will have info in the [Memo] field and some will have 
blank fields in the [Memo] field. (it will probably require an outer join in 
order to return blank fields or you can rely on the query design that will 
return all records somehow....and you don't have to use all fields in your 
report)

this would be the first step.

Create your report sourced on this query...... once that looks right (except 
for the blanks in some)

and then the second step would be to add to the Report's "OnFormat" event 
the VBA that IF the Memo Field is Null (or maybe [Memo]="" ) THEN [Memo]="No 
Material listing found"

and that phrase will appear where the blanks were....
probably need to experiment with correct VB as I'm not sure what a blank is 
for a memo field property in terms of it being Null or just nothing "" - but 
in any case I think this would work....

-- 
NTC


"Sunflower" wrote:

> I am an Access newbie, so I am not even sure this can be done...
> 
> I need a report to pull material listing from a memo field [Notes]
> or show which jobs have missing materials...
> 
> My report has the following record source:
> SELECT dbo_RB_JOBNOTE.JOBID, dbo_RB_JOBNOTE.ID, dbo_RB_JOBNOTE.NOTE,
> dbo_RB_JOBNOTE.OWNERFULLNAME
> FROM dbo_RB_JOBNOTE;
> 
> The report has a Group header on JOBID
> and a [Material] text field with the following control source:
> =IIf([NOTE] Like "Material*",[NOTE],"No Material listing found")
> 
> What I get is a listing of all the notes,
> -----------------
> Example:
> Job1
>   No Material listing found
>   Material note2
>   No Material listing found
> Job2
>   No Material listing found
>   No Material listing found
>   Material note1
> Job3
>   No Material listing found
>   No Material listing found
>   No Material listing found
> Job4
>   Material note1
>   Material note2
>   No Material listing found
> -----------------
> 
> 
> I only want the notes if materials are listed
> or the text "No Material listing found" if not,
> ----------------
> Example:
> Job1
>   Material note2
> Job2
>   Material note1
> Job3
>   No Material listing found
> Job4
>   Material note1
>   Material note2
> -----------------
> 
> 
> All and any help greatly appreciated
> 
> 
0
Utf
5/3/2007 3:23:00 PM
Can I assume that the following solution did not work?  This solution was 
posted on April 24th in response to your earlier posting.  Since I did not 
see any response that it failed or that you could not implement it, my guess 
was that it worked for you.

SELECT tblJob.Job
, NZ(T.Notes,"No Material Listing Found") as TheNote
FROM tblJob LEFT JOIN
    (SELECT JobID, Notes
      FROM tblNotes
      WHERE Notes Like "Material*") as T
ON tblJob.ID=T.JOBID;

SELECT dbo_RB_JOBNOTE.JOBID
, NZ(T.NOTE, "No Material Listing Found") as MaterialNote
, dbo_RB_JOBNOTE.OWNERFULLNAME
FROM dbo_RB_JOBNOTE LEFT JOIN
   (SELECT ID, NOTE
    FROM dbo_RB_JOBNOTE
    WHERE NOTE Like "Material*" ) as T


-- 
John Spencer
Access MVP 2002-2005, 2007
Center for Health Program Development and Management
University of Maryland Baltimore County
..

"Sunflower" <LuvSunflowers@gmail.com> wrote in message 
news:1178155968.329947.253750@q75g2000hsh.googlegroups.com...
>I am an Access newbie, so I am not even sure this can be done...
>
> I need a report to pull material listing from a memo field [Notes]
> or show which jobs have missing materials...
>
> My report has the following record source:
> SELECT dbo_RB_JOBNOTE.JOBID, dbo_RB_JOBNOTE.ID, dbo_RB_JOBNOTE.NOTE,
> dbo_RB_JOBNOTE.OWNERFULLNAME
> FROM dbo_RB_JOBNOTE;
>
> The report has a Group header on JOBID
> and a [Material] text field with the following control source:
> =IIf([NOTE] Like "Material*",[NOTE],"No Material listing found")
>
> What I get is a listing of all the notes,
> -----------------
> Example:
> Job1
>  No Material listing found
>  Material note2
>  No Material listing found
> Job2
>  No Material listing found
>  No Material listing found
>  Material note1
> Job3
>  No Material listing found
>  No Material listing found
>  No Material listing found
> Job4
>  Material note1
>  Material note2
>  No Material listing found
> -----------------
>
>
> I only want the notes if materials are listed
> or the text "No Material listing found" if not,
> ----------------
> Example:
> Job1
>  Material note2
> Job2
>  Material note1
> Job3
>  No Material listing found
> Job4
>  Material note1
>  Material note2
> -----------------
>
>
> All and any help greatly appreciated
> 


0
John
5/3/2007 5:45:22 PM
On May 3, 10:45 am, "John Spencer" <spen...@chpdm.edu> wrote:
> Can I assume that the following solution did not work?  This solution was
> posted on April 24th in response to your earlier posting.  Since I did not
> see any response that it failed or that you could not implement it, my guess
> was that it worked for you.
>
> SELECT tblJob.Job
> , NZ(T.Notes,"No Material Listing Found") as TheNote
> FROM tblJob LEFT JOIN
>     (SELECT JobID, Notes
>       FROM tblNotes
>       WHERE Notes Like "Material*") as T
> ON tblJob.ID=T.JOBID;
>
> SELECT dbo_RB_JOBNOTE.JOBID
> , NZ(T.NOTE, "No Material Listing Found") as MaterialNote
> , dbo_RB_JOBNOTE.OWNERFULLNAME
> FROM dbo_RB_JOBNOTE LEFT JOIN
>    (SELECT ID, NOTE
>     FROM dbo_RB_JOBNOTE
>     WHERE NOTE Like "Material*" ) as T
>
> --
> John Spencer
> Access MVP 2002-2005, 2007
> Center for Health Program Development and Management
> University of Maryland Baltimore County
> .
>
> "Sunflower" <LuvSunflow...@gmail.com> wrote in message
>
> news:1178155968.329947.253750@q75g2000hsh.googlegroups.com...
>
>
>
> >I am an Access newbie, so I am not even sure this can be done...
>
> > I need a report to pull material listing from a memo field [Notes]
> > or show which jobs have missing materials...
>
> > My report has the following record source:
> > SELECT dbo_RB_JOBNOTE.JOBID, dbo_RB_JOBNOTE.ID, dbo_RB_JOBNOTE.NOTE,
> > dbo_RB_JOBNOTE.OWNERFULLNAME
> > FROM dbo_RB_JOBNOTE;
>
> > The report has a Group header on JOBID
> > and a [Material] text field with the following control source:
> > =IIf([NOTE] Like "Material*",[NOTE],"No Material listing found")
>
> > What I get is a listing of all the notes,
> > -----------------
> > Example:
> > Job1
> >  No Material listing found
> >  Material note2
> >  No Material listing found
> > Job2
> >  No Material listing found
> >  No Material listing found
> >  Material note1
> > Job3
> >  No Material listing found
> >  No Material listing found
> >  No Material listing found
> > Job4
> >  Material note1
> >  Material note2
> >  No Material listing found
> > -----------------
>
> > I only want the notes if materials are listed
> > or the text "No Material listing found" if not,
> > ----------------
> > Example:
> > Job1
> >  Material note2
> > Job2
> >  Material note1
> > Job3
> >  No Material listing found
> > Job4
> >  Material note1
> >  Material note2
> > -----------------
>
> > All and any help greatly appreciated- Hide quoted text -
>
> - Show quoted text -

I am very sorry...
I thought I had reponded to you and let you know that implemented and
failed
I just figured my question was dead, so I reposted.

I tried your new one
---------------------------------------------------
SELECT dbo_RB_JOBNOTE.JOBID
, NZ(T.NOTE, "No Material Listing Found") as MaterialNote
, dbo_RB_JOBNOTE.OWNERFULLNAME
FROM dbo_RB_JOBNOTE LEFT JOIN
   (SELECT ID, NOTE
    FROM dbo_RB_JOBNOTE
    WHERE NOTE Like "Material*" ) as T
-----------------------------------------------------
and I get a "Syntax Error in FROM clause" error
I do not know enough about SQL to understand where the problem may be.

Again I aplogize for following up with you.

Thank you for putting up with such a newbie :)

0
Sunflower
5/4/2007 2:29:14 PM
My fault I left off the join criteria

SELECT dbo_RB_JOBNOTE.JOBID
, NZ(T.NOTE, "No Material Listing Found") as MaterialNote
, dbo_RB_JOBNOTE.OWNERFULLNAME
FROM dbo_RB_JOBNOTE LEFT JOIN
   (SELECT ID, NOTE
    FROM dbo_RB_JOBNOTE
    WHERE NOTE Like "Material*" ) as T

ON dbo_RB_JobNOTE.JobID = T.ID


If that fails we can try to do this in two steps.  Step one would build a 
query that returns all the records where the note contains the word Material 
at the start of the note.  With that query as a saved query.  After that you 
would build a second query using your table and the saved query to get what 
you need.



-- 
John Spencer
Access MVP 2002-2005, 2007
Center for Health Program Development and Management
University of Maryland Baltimore County
..

"Sunflower" <LuvSunflowers@gmail.com> wrote in message 
news:1178288954.495326.23310@e65g2000hsc.googlegroups.com...
> On May 3, 10:45 am, "John Spencer" <spen...@chpdm.edu> wrote:
>> Can I assume that the following solution did not work?  This solution was
>> posted on April 24th in response to your earlier posting.  Since I did 
>> not
>> see any response that it failed or that you could not implement it, my 
>> guess
>> was that it worked for you.
>>
>> SELECT tblJob.Job
>> , NZ(T.Notes,"No Material Listing Found") as TheNote
>> FROM tblJob LEFT JOIN
>>     (SELECT JobID, Notes
>>       FROM tblNotes
>>       WHERE Notes Like "Material*") as T
>> ON tblJob.ID=T.JOBID;
>>
>> SELECT dbo_RB_JOBNOTE.JOBID
>> , NZ(T.NOTE, "No Material Listing Found") as MaterialNote
>> , dbo_RB_JOBNOTE.OWNERFULLNAME
>> FROM dbo_RB_JOBNOTE LEFT JOIN
>>    (SELECT ID, NOTE
>>     FROM dbo_RB_JOBNOTE
>>     WHERE NOTE Like "Material*" ) as T
>>
>> --
>> John Spencer
>> Access MVP 2002-2005, 2007
>> Center for Health Program Development and Management
>> University of Maryland Baltimore County
>> .
>>
>> "Sunflower" <LuvSunflow...@gmail.com> wrote in message
>>
>> news:1178155968.329947.253750@q75g2000hsh.googlegroups.com...
>>
>>
>>
>> >I am an Access newbie, so I am not even sure this can be done...
>>
>> > I need a report to pull material listing from a memo field [Notes]
>> > or show which jobs have missing materials...
>>
>> > My report has the following record source:
>> > SELECT dbo_RB_JOBNOTE.JOBID, dbo_RB_JOBNOTE.ID, dbo_RB_JOBNOTE.NOTE,
>> > dbo_RB_JOBNOTE.OWNERFULLNAME
>> > FROM dbo_RB_JOBNOTE;
>>
>> > The report has a Group header on JOBID
>> > and a [Material] text field with the following control source:
>> > =IIf([NOTE] Like "Material*",[NOTE],"No Material listing found")
>>
>> > What I get is a listing of all the notes,
>> > -----------------
>> > Example:
>> > Job1
>> >  No Material listing found
>> >  Material note2
>> >  No Material listing found
>> > Job2
>> >  No Material listing found
>> >  No Material listing found
>> >  Material note1
>> > Job3
>> >  No Material listing found
>> >  No Material listing found
>> >  No Material listing found
>> > Job4
>> >  Material note1
>> >  Material note2
>> >  No Material listing found
>> > -----------------
>>
>> > I only want the notes if materials are listed
>> > or the text "No Material listing found" if not,
>> > ----------------
>> > Example:
>> > Job1
>> >  Material note2
>> > Job2
>> >  Material note1
>> > Job3
>> >  No Material listing found
>> > Job4
>> >  Material note1
>> >  Material note2
>> > -----------------
>>
>> > All and any help greatly appreciated- Hide quoted text -
>>
>> - Show quoted text -
>
> I am very sorry...
> I thought I had reponded to you and let you know that implemented and
> failed
> I just figured my question was dead, so I reposted.
>
> I tried your new one
> ---------------------------------------------------
> SELECT dbo_RB_JOBNOTE.JOBID
> , NZ(T.NOTE, "No Material Listing Found") as MaterialNote
> , dbo_RB_JOBNOTE.OWNERFULLNAME
> FROM dbo_RB_JOBNOTE LEFT JOIN
>   (SELECT ID, NOTE
>    FROM dbo_RB_JOBNOTE
>    WHERE NOTE Like "Material*" ) as T
> -----------------------------------------------------
> and I get a "Syntax Error in FROM clause" error
> I do not know enough about SQL to understand where the problem may be.
>
> Again I aplogize for following up with you.
>
> Thank you for putting up with such a newbie :)
> 


0
John
5/4/2007 3:57:07 PM
On May 4, 8:57 am, "John Spencer" <spen...@chpdm.edu> wrote:
> My fault I left off the join criteria
>
> SELECT dbo_RB_JOBNOTE.JOBID
> , NZ(T.NOTE, "No Material Listing Found") as MaterialNote
> , dbo_RB_JOBNOTE.OWNERFULLNAME
> FROM dbo_RB_JOBNOTE LEFT JOIN
>    (SELECT ID, NOTE
>     FROM dbo_RB_JOBNOTE
>     WHERE NOTE Like "Material*" ) as T
>
> ON dbo_RB_JobNOTE.JobID = T.ID
>
> If that fails we can try to do this in two steps.  Step one would build a
> query that returns all the records where the note contains the word Material
> at the start of the note.  With that query as a saved query.  After that you
> would build a second query using your table and the saved query to get what
> you need.
>
> --
> John Spencer
> Access MVP 2002-2005, 2007
> Center for Health Program Development and Management
> University of Maryland Baltimore County
> .
>
> "Sunflower" <LuvSunflow...@gmail.com> wrote in message
>
> news:1178288954.495326.23310@e65g2000hsc.googlegroups.com...
>
>
>
> > On May 3, 10:45 am, "John Spencer" <spen...@chpdm.edu> wrote:
> >> Can I assume that the following solution did not work?  This solution was
> >> posted on April 24th in response to your earlier posting.  Since I did
> >> not
> >> see any response that it failed or that you could not implement it, my
> >> guess
> >> was that it worked for you.
>
> >> SELECT tblJob.Job
> >> , NZ(T.Notes,"No Material Listing Found") as TheNote
> >> FROM tblJob LEFT JOIN
> >>     (SELECT JobID, Notes
> >>       FROM tblNotes
> >>       WHERE Notes Like "Material*") as T
> >> ON tblJob.ID=T.JOBID;
>
> >> SELECT dbo_RB_JOBNOTE.JOBID
> >> , NZ(T.NOTE, "No Material Listing Found") as MaterialNote
> >> , dbo_RB_JOBNOTE.OWNERFULLNAME
> >> FROM dbo_RB_JOBNOTE LEFT JOIN
> >>    (SELECT ID, NOTE
> >>     FROM dbo_RB_JOBNOTE
> >>     WHERE NOTE Like "Material*" ) as T
>
> >> --
> >> John Spencer
> >> Access MVP 2002-2005, 2007
> >> Center for Health Program Development and Management
> >> University of Maryland Baltimore County
> >> .
>
> >> "Sunflower" <LuvSunflow...@gmail.com> wrote in message
>
> >>news:1178155968.329947.253750@q75g2000hsh.googlegroups.com...
>
> >> >I am an Access newbie, so I am not even sure this can be done...
>
> >> > I need a report to pull material listing from a memo field [Notes]
> >> > or show which jobs have missing materials...
>
> >> > My report has the following record source:
> >> > SELECT dbo_RB_JOBNOTE.JOBID, dbo_RB_JOBNOTE.ID, dbo_RB_JOBNOTE.NOTE,
> >> > dbo_RB_JOBNOTE.OWNERFULLNAME
> >> > FROM dbo_RB_JOBNOTE;
>
> >> > The report has a Group header on JOBID
> >> > and a [Material] text field with the following control source:
> >> > =IIf([NOTE] Like "Material*",[NOTE],"No Material listing found")
>
> >> > What I get is a listing of all the notes,
> >> > -----------------
> >> > Example:
> >> > Job1
> >> >  No Material listing found
> >> >  Material note2
> >> >  No Material listing found
> >> > Job2
> >> >  No Material listing found
> >> >  No Material listing found
> >> >  Material note1
> >> > Job3
> >> >  No Material listing found
> >> >  No Material listing found
> >> >  No Material listing found
> >> > Job4
> >> >  Material note1
> >> >  Material note2
> >> >  No Material listing found
> >> > -----------------
>
> >> > I only want the notes if materials are listed
> >> > or the text "No Material listing found" if not,
> >> > ----------------
> >> > Example:
> >> > Job1
> >> >  Material note2
> >> > Job2
> >> >  Material note1
> >> > Job3
> >> >  No Material listing found
> >> > Job4
> >> >  Material note1
> >> >  Material note2
> >> > -----------------
>
> >> > All and any help greatly appreciated- Hide quoted text -
>
> >> - Show quoted text -
>
> > I am very sorry...
> > I thought I had reponded to you and let you know that implemented and
> > failed
> > I just figured my question was dead, so I reposted.
>
> > I tried your new one
> > ---------------------------------------------------
> > SELECT dbo_RB_JOBNOTE.JOBID
> > , NZ(T.NOTE, "No Material Listing Found") as MaterialNote
> > , dbo_RB_JOBNOTE.OWNERFULLNAME
> > FROM dbo_RB_JOBNOTE LEFT JOIN
> >   (SELECT ID, NOTE
> >    FROM dbo_RB_JOBNOTE
> >    WHERE NOTE Like "Material*" ) as T
> > -----------------------------------------------------
> > and I get a "Syntax Error in FROM clause" error
> > I do not know enough about SQL to understand where the problem may be.
>
> > Again I aplogize for following up with you.
>
> > Thank you for putting up with such a newbie :)- Hide quoted text -
>
> - Show quoted text -

I put in your revised SQL...
I also replaced my Materials text box with "MaterialNote"
Now I get the dialog box "Enter Parameter Value - ID"

Is this where I try the 2 queries?

Thanks again for helping me.

0
Sunflower
5/4/2007 6:24:24 PM
On May 3, 8:23 am, NetworkTrade
<NetworkTr...@discussions.microsoft.com> wrote:
> since no one has replied - I will give one suggestion;  consider splitting
> your task in two....
>
> first I would consider establishing the results you want using a query -
> without attempting to insert the default phrase.....
>
> with the correct query then, if I understand you correctly, you will have a
> set of records and some will have info in the [Memo] field and some will have
> blank fields in the [Memo] field. (it will probably require an outer join in
> order to return blank fields or you can rely on the query design that will
> return all records somehow....and you don't have to use all fields in your
> report)
>
> this would be the first step.
>
> Create your report sourced on this query...... once that looks right (except
> for the blanks in some)
>
> and then the second step would be to add to the Report's "OnFormat" event
> the VBA that IF the Memo Field is Null (or maybe [Memo]="" ) THEN [Memo]="No
> Material listing found"
>
> and that phrase will appear where the blanks were....
> probably need to experiment with correct VB as I'm not sure what a blank is
> for a memo field property in terms of it being Null or just nothing "" - but
> in any case I think this would work....
>
> --
> NTC
>
>
>
> "Sunflower" wrote:
> > I am an Access newbie, so I am not even sure this can be done...
>
> > I need a report to pull material listing from a memo field [Notes]
> > or show which jobs have missing materials...
>
> > My report has the following record source:
> > SELECT dbo_RB_JOBNOTE.JOBID, dbo_RB_JOBNOTE.ID, dbo_RB_JOBNOTE.NOTE,
> > dbo_RB_JOBNOTE.OWNERFULLNAME
> > FROM dbo_RB_JOBNOTE;
>
> > The report has a Group header on JOBID
> > and a [Material] text field with the following control source:
> > =IIf([NOTE] Like "Material*",[NOTE],"No Material listing found")
>
> > What I get is a listing of all the notes,
> > -----------------
> > Example:
> > Job1
> >   No Material listing found
> >   Material note2
> >   No Material listing found
> > Job2
> >   No Material listing found
> >   No Material listing found
> >   Material note1
> > Job3
> >   No Material listing found
> >   No Material listing found
> >   No Material listing found
> > Job4
> >   Material note1
> >   Material note2
> >   No Material listing found
> > -----------------
>
> > I only want the notes if materials are listed
> > or the text "No Material listing found" if not,
> > ----------------
> > Example:
> > Job1
> >   Material note2
> > Job2
> >   Material note1
> > Job3
> >   No Material listing found
> > Job4
> >   Material note1
> >   Material note2
> > -----------------
>
> > All and any help greatly appreciated- Hide quoted text -
>
> - Show quoted text -

I tried to figure out your suggestion with the VBA,
unfortunately I am very new to Access, so trying to code with VBA is a
little (maybe alot) over my head.

Also, I do not want all the memos linked to a job listed in my
report,
I only want the memos that are prefaced with "Material".
If all the memos linked to a job do not have a memo prefaced with
"Material"...
then I want the text "No Material listing found" returned.
However, I do not want  "No Material listing found"  repeated for
every memo linked to a job.

I hope that made since :(

0
Sunflower
5/6/2007 3:00:01 AM
AUUUGHH! as Charlie Brown says.

The message meant that the query did not recognize a field named ID and 
wanted you to tell it what value to use for ID.  Obviously I should have 
replace ID with JOBID when I was putting together the sample SQL 
statement.

Please accept my apology and try the following query.

SELECT dbo_RB_JOBNOTE.JOBID
  , NZ(T.NOTE, "No Material Listing Found") as MaterialNote
  , dbo_RB_JOBNOTE.OWNERFULLNAME
  FROM dbo_RB_JOBNOTE LEFT JOIN
    (SELECT JobID, NOTE
     FROM dbo_RB_JOBNOTE
     WHERE NOTE Like "Material*" ) as T
  ON dbo_RB_JobNOTE.JobID = T.JobID


If I've messed up any other field names, please fix them also.


'====================================================
  John Spencer
  Access MVP 2002-2005, 2007
  Center for Health Program Development and Management
  University of Maryland Baltimore County
'====================================================


Sunflower wrote:
> On May 4, 8:57 am, "John Spencer" <spen...@chpdm.edu> wrote:
>> My fault I left off the join criteria
>>
>> SELECT dbo_RB_JOBNOTE.JOBID
>> , NZ(T.NOTE, "No Material Listing Found") as MaterialNote
>> , dbo_RB_JOBNOTE.OWNERFULLNAME
>> FROM dbo_RB_JOBNOTE LEFT JOIN
>>    (SELECT ID, NOTE
>>     FROM dbo_RB_JOBNOTE
>>     WHERE NOTE Like "Material*" ) as T
>>
>> ON dbo_RB_JobNOTE.JobID = T.ID
>>
>> If that fails we can try to do this in two steps.  Step one would build a
>> query that returns all the records where the note contains the word Material
>> at the start of the note.  With that query as a saved query.  After that you
>> would build a second query using your table and the saved query to get what
>> you need.
>>
>> --
>> John Spencer
>> Access MVP 2002-2005, 2007
>> Center for Health Program Development and Management
>> University of Maryland Baltimore County
>> .
>>
>> "Sunflower" <LuvSunflow...@gmail.com> wrote in message
>>
>> news:1178288954.495326.23310@e65g2000hsc.googlegroups.com...
>>
>>
>>
>>> On May 3, 10:45 am, "John Spencer" <spen...@chpdm.edu> wrote:
>>>> Can I assume that the following solution did not work?  This solution was
>>>> posted on April 24th in response to your earlier posting.  Since I did
>>>> not
>>>> see any response that it failed or that you could not implement it, my
>>>> guess
>>>> was that it worked for you.
>>>> SELECT tblJob.Job
>>>> , NZ(T.Notes,"No Material Listing Found") as TheNote
>>>> FROM tblJob LEFT JOIN
>>>>     (SELECT JobID, Notes
>>>>       FROM tblNotes
>>>>       WHERE Notes Like "Material*") as T
>>>> ON tblJob.ID=T.JOBID;
>>>> SELECT dbo_RB_JOBNOTE.JOBID
>>>> , NZ(T.NOTE, "No Material Listing Found") as MaterialNote
>>>> , dbo_RB_JOBNOTE.OWNERFULLNAME
>>>> FROM dbo_RB_JOBNOTE LEFT JOIN
>>>>    (SELECT ID, NOTE
>>>>     FROM dbo_RB_JOBNOTE
>>>>     WHERE NOTE Like "Material*" ) as T
>>>> --
>>>> John Spencer
>>>> Access MVP 2002-2005, 2007
>>>> Center for Health Program Development and Management
>>>> University of Maryland Baltimore County
>>>> .
>>>> "Sunflower" <LuvSunflow...@gmail.com> wrote in message
>>>> news:1178155968.329947.253750@q75g2000hsh.googlegroups.com...
>>>>> I am an Access newbie, so I am not even sure this can be done...
>>>>> I need a report to pull material listing from a memo field [Notes]
>>>>> or show which jobs have missing materials...
>>>>> My report has the following record source:
>>>>> SELECT dbo_RB_JOBNOTE.JOBID, dbo_RB_JOBNOTE.ID, dbo_RB_JOBNOTE.NOTE,
>>>>> dbo_RB_JOBNOTE.OWNERFULLNAME
>>>>> FROM dbo_RB_JOBNOTE;
>>>>> The report has a Group header on JOBID
>>>>> and a [Material] text field with the following control source:
>>>>> =IIf([NOTE] Like "Material*",[NOTE],"No Material listing found")
>>>>> What I get is a listing of all the notes,
>>>>> -----------------
>>>>> Example:
>>>>> Job1
>>>>>  No Material listing found
>>>>>  Material note2
>>>>>  No Material listing found
>>>>> Job2
>>>>>  No Material listing found
>>>>>  No Material listing found
>>>>>  Material note1
>>>>> Job3
>>>>>  No Material listing found
>>>>>  No Material listing found
>>>>>  No Material listing found
>>>>> Job4
>>>>>  Material note1
>>>>>  Material note2
>>>>>  No Material listing found
>>>>> -----------------
>>>>> I only want the notes if materials are listed
>>>>> or the text "No Material listing found" if not,
>>>>> ----------------
>>>>> Example:
>>>>> Job1
>>>>>  Material note2
>>>>> Job2
>>>>>  Material note1
>>>>> Job3
>>>>>  No Material listing found
>>>>> Job4
>>>>>  Material note1
>>>>>  Material note2
>>>>> -----------------
>>>>> All and any help greatly appreciated- Hide quoted text -
>>>> - Show quoted text -
>>> I am very sorry...
>>> I thought I had reponded to you and let you know that implemented and
>>> failed
>>> I just figured my question was dead, so I reposted.
>>> I tried your new one
>>> ---------------------------------------------------
>>> SELECT dbo_RB_JOBNOTE.JOBID
>>> , NZ(T.NOTE, "No Material Listing Found") as MaterialNote
>>> , dbo_RB_JOBNOTE.OWNERFULLNAME
>>> FROM dbo_RB_JOBNOTE LEFT JOIN
>>>   (SELECT ID, NOTE
>>>    FROM dbo_RB_JOBNOTE
>>>    WHERE NOTE Like "Material*" ) as T
>>> -----------------------------------------------------
>>> and I get a "Syntax Error in FROM clause" error
>>> I do not know enough about SQL to understand where the problem may be.
>>> Again I aplogize for following up with you.
>>> Thank you for putting up with such a newbie :)- Hide quoted text -
>> - Show quoted text -
> 
> I put in your revised SQL...
> I also replaced my Materials text box with "MaterialNote"
> Now I get the dialog box "Enter Parameter Value - ID"
> 
> Is this where I try the 2 queries?
> 
> Thanks again for helping me.
> 
0
John
5/6/2007 2:24:19 PM
On May 6, 7:24 am, John Spencer <spen...@chpdm.umbc> wrote:
> AUUUGHH! as Charlie Brown says.
>
> The message meant that the query did not recognize a field named ID and
> wanted you to tell it what value to use for ID.  Obviously I should have
> replace ID with JOBID when I was putting together the sample SQL
> statement.
>
> Please accept my apology and try the following query.
>
> SELECT dbo_RB_JOBNOTE.JOBID
>   , NZ(T.NOTE, "No Material Listing Found") as MaterialNote
>   , dbo_RB_JOBNOTE.OWNERFULLNAME
>   FROM dbo_RB_JOBNOTE LEFT JOIN
>     (SELECT JobID, NOTE
>      FROM dbo_RB_JOBNOTE
>      WHERE NOTE Like "Material*" ) as T
>   ON dbo_RB_JobNOTE.JobID = T.JobID
>
> If I've messed up any other field names, please fix them also.
>
> '====================================================
>   John Spencer
>   Access MVP 2002-2005, 2007
>   Center for Health Program Development and Management
>   University of Maryland Baltimore County
> '====================================================
>
>
>
> Sunflower wrote:
> > On May 4, 8:57 am, "John Spencer" <spen...@chpdm.edu> wrote:
> >> My fault I left off the join criteria
>
> >> SELECT dbo_RB_JOBNOTE.JOBID
> >> , NZ(T.NOTE, "No Material Listing Found") as MaterialNote
> >> , dbo_RB_JOBNOTE.OWNERFULLNAME
> >> FROM dbo_RB_JOBNOTE LEFT JOIN
> >>    (SELECT ID, NOTE
> >>     FROM dbo_RB_JOBNOTE
> >>     WHERE NOTE Like "Material*" ) as T
>
> >> ON dbo_RB_JobNOTE.JobID = T.ID
>
> >> If that fails we can try to do this in two steps.  Step one would build a
> >> query that returns all the records where the note contains the word Material
> >> at the start of the note.  With that query as a saved query.  After that you
> >> would build a second query using your table and the saved query to get what
> >> you need.
>
> >> --
> >> John Spencer
> >> Access MVP 2002-2005, 2007
> >> Center for Health Program Development and Management
> >> University of Maryland Baltimore County
> >> .
>
> >> "Sunflower" <LuvSunflow...@gmail.com> wrote in message
>
> >>news:1178288954.495326.23310@e65g2000hsc.googlegroups.com...
>
> >>> On May 3, 10:45 am, "John Spencer" <spen...@chpdm.edu> wrote:
> >>>> Can I assume that the following solution did not work?  This solution was
> >>>> posted on April 24th in response to your earlier posting.  Since I did
> >>>> not
> >>>> see any response that it failed or that you could not implement it, my
> >>>> guess
> >>>> was that it worked for you.
> >>>> SELECT tblJob.Job
> >>>> , NZ(T.Notes,"No Material Listing Found") as TheNote
> >>>> FROM tblJob LEFT JOIN
> >>>>     (SELECT JobID, Notes
> >>>>       FROM tblNotes
> >>>>       WHERE Notes Like "Material*") as T
> >>>> ON tblJob.ID=T.JOBID;
> >>>> SELECT dbo_RB_JOBNOTE.JOBID
> >>>> , NZ(T.NOTE, "No Material Listing Found") as MaterialNote
> >>>> , dbo_RB_JOBNOTE.OWNERFULLNAME
> >>>> FROM dbo_RB_JOBNOTE LEFT JOIN
> >>>>    (SELECT ID, NOTE
> >>>>     FROM dbo_RB_JOBNOTE
> >>>>     WHERE NOTE Like "Material*" ) as T
> >>>> --
> >>>> John Spencer
> >>>> Access MVP 2002-2005, 2007
> >>>> Center for Health Program Development and Management
> >>>> University of Maryland Baltimore County
> >>>> .
> >>>> "Sunflower" <LuvSunflow...@gmail.com> wrote in message
> >>>>news:1178155968.329947.253750@q75g2000hsh.googlegroups.com...
> >>>>> I am an Access newbie, so I am not even sure this can be done...
> >>>>> I need a report to pull material listing from a memo field [Notes]
> >>>>> or show which jobs have missing materials...
> >>>>> My report has the following record source:
> >>>>> SELECT dbo_RB_JOBNOTE.JOBID, dbo_RB_JOBNOTE.ID, dbo_RB_JOBNOTE.NOTE,
> >>>>> dbo_RB_JOBNOTE.OWNERFULLNAME
> >>>>> FROM dbo_RB_JOBNOTE;
> >>>>> The report has a Group header on JOBID
> >>>>> and a [Material] text field with the following control source:
> >>>>> =IIf([NOTE] Like "Material*",[NOTE],"No Material listing found")
> >>>>> What I get is a listing of all the notes,
> >>>>> -----------------
> >>>>> Example:
> >>>>> Job1
> >>>>>  No Material listing found
> >>>>>  Material note2
> >>>>>  No Material listing found
> >>>>> Job2
> >>>>>  No Material listing found
> >>>>>  No Material listing found
> >>>>>  Material note1
> >>>>> Job3
> >>>>>  No Material listing found
> >>>>>  No Material listing found
> >>>>>  No Material listing found
> >>>>> Job4
> >>>>>  Material note1
> >>>>>  Material note2
> >>>>>  No Material listing found
> >>>>> -----------------
> >>>>> I only want the notes if materials are listed
> >>>>> or the text "No Material listing found" if not,
> >>>>> ----------------
> >>>>> Example:
> >>>>> Job1
> >>>>>  Material note2
> >>>>> Job2
> >>>>>  Material note1
> >>>>> Job3
> >>>>>  No Material listing found
> >>>>> Job4
> >>>>>  Material note1
> >>>>>  Material note2
> >>>>> -----------------
> >>>>> All and any help greatly appreciated- Hide quoted text -
> >>>> - Show quoted text -
> >>> I am very sorry...
> >>> I thought I had reponded to you and let you know that implemented and
> >>> failed
> >>> I just figured my question was dead, so I reposted.
> >>> I tried your new one
> >>> ---------------------------------------------------
> >>> SELECT dbo_RB_JOBNOTE.JOBID
> >>> , NZ(T.NOTE, "No Material Listing Found") as MaterialNote
> >>> , dbo_RB_JOBNOTE.OWNERFULLNAME
> >>> FROM dbo_RB_JOBNOTE LEFT JOIN
> >>>   (SELECT ID, NOTE
> >>>    FROM dbo_RB_JOBNOTE
> >>>    WHERE NOTE Like "Material*" ) as T
> >>> -----------------------------------------------------
> >>> and I get a "Syntax Error in FROM clause" error
> >>> I do not know enough about SQL to understand where the problem may be.
> >>> Again I aplogize for following up with you.
> >>> Thank you for putting up with such a newbie :)- Hide quoted text -
> >> - Show quoted text -
>
> > I put in your revised SQL...
> > I also replaced my Materials text box with "MaterialNote"
> > Now I get the dialog box "Enter Parameter Value - ID"
>
> > Is this where I try the 2 queries?
>
> > Thanks again for helping me.- Hide quoted text -
>
> - Show quoted text -

I put in your SQL
Here is a sample of what I am getting:
-------------------------------------------------------
Notes
Jesi
Materials:3 pieces Letter paper
Janice
Materials:3 pieces Letter paper

Notes
George
Materials Used
16 feet of 24 inch satin photo base
16 feet of 40 inch 5 mil laminate
George
Materials Used
16 feet of 24 inch satin photo base
16 feet of 40 inch 5 mil laminate

Notes
George
No Material Listing Found
George
No Material Listing Found
Janice
No Material Listing Found

Notes
Travis
No Material Listing Found
Janice
No Material Listing Found
Janice
No Material Listing Found

Notes
Janice
No Material Listing Found
Janice
No Material Listing Found
-------------------------------------
I am not sure why it is doing this, but I do know it is repeating.

0
Sunflower
5/7/2007 3:45:33 PM
Looks as if we need to add at least one more field to the subquery and join 
on that field also

SELECT dbo_RB_JOBNOTE.JOBID
  , NZ(T.NOTE, "No Material Listing Found") as MaterialNote
  , dbo_RB_JOBNOTE.OWNERFULLNAME
  FROM dbo_RB_JOBNOTE LEFT JOIN
    (SELECT JobID, NOTE, OWNERFULLNAME
     FROM dbo_RB_JOBNOTE
     WHERE NOTE Like "Material*" ) as T
  ON dbo_RB_JobNOTE.JobID = T.JobID
  AND dbo_RB_JobNOTE.OWNERFULLNAME = T.OWNERFULLNAME

Somehow I missed the OwnerFullName in your earlier postings.  Hopefully the 
combination of JobID and OwnerFullName is sufficient to give you the results 
you want.

If not, construct a query that gives you what you want if Note is Like 
"Material*".  Save that query and then use it to build a query with 
dbo_RB_Jobnote where you join on the relevant fields.  Double-click on the 
join lines and set them to show all records in dbo_RB_Jobnote and only 
matching in the saved query.   If there are no "material" notes then you 
will get a record with fields from dbo_RB_JobNote and blank fields for the 
fields from the saved query.
-- 
John Spencer
Access MVP 2002-2005, 2007
Center for Health Program Development and Management
University of Maryland Baltimore County
..

>
> I put in your SQL
> Here is a sample of what I am getting:
> -------------------------------------------------------
> Notes
> Jesi
> Materials:3 pieces Letter paper
> Janice
> Materials:3 pieces Letter paper
>
> Notes
> George
> Materials Used
> 16 feet of 24 inch satin photo base
> 16 feet of 40 inch 5 mil laminate
> George
> Materials Used
> 16 feet of 24 inch satin photo base
> 16 feet of 40 inch 5 mil laminate
>
> Notes
> George
> No Material Listing Found
> George
> No Material Listing Found
> Janice
> No Material Listing Found
>
> Notes
> Travis
> No Material Listing Found
> Janice
> No Material Listing Found
> Janice
> No Material Listing Found
>
> Notes
> Janice
> No Material Listing Found
> Janice
> No Material Listing Found
> -------------------------------------
> I am not sure why it is doing this, but I do know it is repeating.
> 


0
John
5/7/2007 7:30:38 PM
On May 7, 12:30 pm, "John Spencer" <spen...@chpdm.edu> wrote:
> Looks as if we need to add at least one more field to the subquery and join
> on that field also
>
> SELECT dbo_RB_JOBNOTE.JOBID
>   , NZ(T.NOTE, "No Material Listing Found") as MaterialNote
>   , dbo_RB_JOBNOTE.OWNERFULLNAME
>   FROM dbo_RB_JOBNOTE LEFT JOIN
>     (SELECT JobID, NOTE, OWNERFULLNAME
>      FROM dbo_RB_JOBNOTE
>      WHERE NOTE Like "Material*" ) as T
>   ON dbo_RB_JobNOTE.JobID = T.JobID
>   AND dbo_RB_JobNOTE.OWNERFULLNAME = T.OWNERFULLNAME
>
> Somehow I missed the OwnerFullName in your earlier postings.  Hopefully the
> combination of JobID and OwnerFullName is sufficient to give you the results
> you want.
>
> If not, construct a query that gives you what you want if Note is Like
> "Material*".  Save that query and then use it to build a query with
> dbo_RB_Jobnote where you join on the relevant fields.  Double-click on the
> join lines and set them to show all records in dbo_RB_Jobnote and only
> matching in the saved query.   If there are no "material" notes then you
> will get a record with fields from dbo_RB_JobNote and blank fields for the
> fields from the saved query.
> --
> John Spencer
> Access MVP 2002-2005, 2007
> Center for Health Program Development and Management
> University of Maryland Baltimore County
> .
>
>
>
>
>
> > I put in your SQL
> > Here is a sample of what I am getting:
> > -------------------------------------------------------
> > Notes
> > Jesi
> > Materials:3 pieces Letter paper
> > Janice
> > Materials:3 pieces Letter paper
>
> > Notes
> > George
> > Materials Used
> > 16 feet of 24 inch satin photo base
> > 16 feet of 40 inch 5 mil laminate
> > George
> > Materials Used
> > 16 feet of 24 inch satin photo base
> > 16 feet of 40 inch 5 mil laminate
>
> > Notes
> > George
> > No Material Listing Found
> > George
> > No Material Listing Found
> > Janice
> > No Material Listing Found
>
> > Notes
> > Travis
> > No Material Listing Found
> > Janice
> > No Material Listing Found
> > Janice
> > No Material Listing Found
>
> > Notes
> > Janice
> > No Material Listing Found
> > Janice
> > No Material Listing Found
> > -------------------------------------
> > I am not sure why it is doing this, but I do know it is repeating.- Hide quoted text -
>
> - Show quoted text -

I constructed a query that gave me what I wanted if Note is Like
"Material*".
Saved that query as qryMATERIALNOTES

SELECT dbo_RB_JOBNOTE.JOBID, dbo_RB_JOBNOTE.NOTE
FROM dbo_RB_JOBNOTE
WHERE (((dbo_RB_JOBNOTE.NOTE) Like "Materials*"))
ORDER BY dbo_RB_JOBNOTE.JOBID;

--------------------------------
Built a query with dbo_RB_Jobnote where I joined on the JOBID fields.
Saved that query as qryNULL_MATERIALS

SELECT qryMATERIALNOTES.JOBID, qryMATERIALNOTES.NOTE
FROM qryMATERIALNOTES RIGHT JOIN dbo_RB_JOBNOTE ON
qryMATERIALNOTES.JOBID = dbo_RB_JOBNOTE.JOBID;

----------------------------------
I Double-clicked on the join lines of qryNULL_MATERIALS and set them
to show all records only matching in the saved query.

------------------------------------
In my subreport --> subrptMATERIALNOTES
with control source of --> qryNULL_MATERIALS
I created a text box --> txtMaterialNote
with the following control source:
=IIf(IsNull([NOTE]),"No Material listing found",[NOTE])

-------------------------------------

I am getting the "Enter Parameter Value ID" dialog box,
when I click OK... I get repeated values like before...

I am clueless on how to even know where to start fixing this

AUUUGHH! as Charlie Brown says.

0
Sunflower
5/8/2007 5:54:16 PM
Reply:

Similar Artilces:

TempVars unusable in field default value
Hello, I'm trying to use a temporary variable to keep track of which CSR is inputting data. I have a macro which prompts user for ID code, which is stored in the temp variable TempUser. On a form control default value property, I can use the expression [TempVars]![TempUser], which will populate that user's ID code into the control. However, I cannot use that same expression in the tables field default value property. If I try, when I save the changes to the table, I get the error message "Could not find the field 'TempVars]![TempUser'. " Any ideas why I ca...

send the same e-mail with one or two fields changed.......
I would like to send the same e-mail to many differnet people with one or two fields changed (for example the name of recipient and the date).How canthis be done?? I would also like to be able to save the e-mail and use it again and again. can anyone help cheers john If you have Word installed and it's the same version as Outlook (both 2003, for example), you can do a mail merge between the two. This would allow you to set up the text the way you want it to, and you can save the document for future use. Look at the following page for further information: http://www.slipstick.com/con...

if cell is text move left one column
ColB is a long list with sections names followed by category codes I need to move the text into colA leaving colB with codes only (all numbers) ColB. Doors 940590 555998 447006 447008 810697 810705 810706 810707 Windows 619435 525691 525692 Try Sub Macro1() Dim lngRow As Long For lngRow = 1 To Cells(Rows.Count, "B").End(xlUp).Row If Not IsNumeric(Range("B" & lngRow)) Then Range("A" & lngRow).Value = Range("B" & lngRow).Text Range("B" & lngRow).Value = "" End If Next End Sub -- Jacob ...

multiple email identities
HELP! My husband and I share the same computer and we have our email come to our inbox through Microsoft Outlook. However it is merging our accounts- how do we seperate into 2 identities so we each get our own mail! It is so frustrating! Please help! Thanks Kat Outlook does not use Identities - it uses profiles. See this page for more information: http://www.slipstick.com/outlook/share.htm --� Milly Staples [MVP - Outlook] Post all replies to the group to keep the discussion intact. Due to the Swen virus, all e-mails sent to my actual account will be deleted w/out reading. After s...

Multiple domains on one Exchange server / All but one domain shows up as spam #2
I am sorry if this is on the board somewhere but I have been searching forever and havn't found a hint yet. I have multiple domains setup on my exchange server. Lets say abc.com, def.com, and hij.com. We only send email with @abc.com but can receive from any domain name. Recently we merged with another company and we brought back their email to our server, lets say xyz.com. Now I configured a new recipient on Exchange 2003, so I now have two policies. The first policy has the three original domains in it with @abc.com as the primary domain. In the second policy I have configured xyz.com t...

Estimated Revenue of an Opportunity 03-09-07
Hi folks, It would be very useful to be able to simply quote an amount for an Opportunity without having to break the amount down into individual products. This can be done through the Estimated Revenue field of an Opportunity. When it is set to be user provided, the estimated revenue is not carried over automatically when a related quote is created. Write-in products can be added to a Quote, Order or Invoice to achieve that functionality but as far as I can see there is no facility to achieve the same thing with an Opportunity. Basically I would like to set an Estimated Revenue valu...

Field Service Calls and Invoices
We use the Service Call modules for repairs in our Stores and have a situation that I'm trying to find a workaround. We have situations where our technicians will pick-up items without prior notification to our dispatchers. All of a sudden we get an invoice and currently we have to track down the charge and then we are forcing a PO into the system from the Service Call in order to properly receive and enter the invoice. Does anyone have other ways to do this and track the information/cost on the Service Call without needing to create a PO after the fact. This is not an applicat...

Microsoft Outlook 03-11-10
I have just got a message saying not installed for the current user and it will not load. But I've been using it for years. George George;131443 Wrote: > I have just got a message saying not installed for the current user and > it > will not load. But I've been using it for years. > > GeorgeGeorge, welcome to the forum. When you post (here or in any forum), you will get faster and less frustrating replies if you do a few standard things: - try to define the problem in the thread title. Since this is an Outlook forum, simply stating "Mic...

How do I copy the result of a check box into another check box?
I'm trying to copy the result of a "check box form field" into another check box. For example: if I check (or uncheck) one box in a form, another box later in the protected form will also be checked (or unchecked) , much like the ref + F9 command for the text form field. Is this possible? Thanks. This cannot be done without macros. If your project will allow macros - see http://word.mvps.org/faqs/tblsfldsfms/ExclusiveFmFldChbxs.htm -- <>>< ><<> ><<> <>>< ><<> <>>< <>><<> G...

How to track ActiveControl.Name when switching records in form with multiple subforms
I need to have a global variable always contain the name of the current form field. This bit of code is attached to the GotFocus event of all fields and the Enter event of all subforms: gxCurrentField = Me.ActiveControl.Name However it doesn't work properly when changing records in a subform. My parent form contains two subforms in a many-to-many relationship. The above variable usually ends up containing the name of the first field in the second subform when switching records in the first subform. How to correctly code this? Or is there some native variable I'm not aware of? I...

Does any one know of a template for purchases, business card siz
I would like to be able to have a business card size card that I can punch the amount of purchase for my customers. After they make a certain amount of purchases I would collect them and they would receive a discount on their next purchase or a free item. On Sun, 7 Mar 2010 15:54:01 -0800, captured memories <captured memories@discussions.microsoft.com> wrote: >I would like to be able to have a business card size card that I can punch >the amount of purchase for my customers. After they make a certain amount of >purchases I would collect them and they would rece...

Exchange server crashed, please help....! Need to restore two priv.edb and pub.edb files into one....!
Hi Guys, I was wondering if I could get some help with the following problem we are having on our company. Here is the scenario; Our Windows NT 4.0 SP4a server running Exchange 5.5 SP4 crashed (Server 1) due to the exchange database reaching its 16 Gig's max limit. I went ahead and moved some mailboxes' e-mails to a few .pst files in order to make some space. This worked ok. Then, I decided to build another exchange server (Server 2) to moved some mailboxes and alleviate the load. Once the server was ready and configured as part of the current exchange site, I went ahead and move...

Opening Multiple Web Links in a Column
Hi, I am very new to using web links in excel. A task I do very often is open a list of different websites that are in various columns in an excel spreadsheet. I am quite sure I am doing it the slowest way possible, so I need some help please. Basically I have been clicking on one link at a time. When I do this, the first website opens and excel automatically minimizes, then I have to go re maximize excel and then click the next web link and the same thing happens, etc... very time consuming. I am wondering if there is a way, either through Excel or whatever means necessary, to open all...

Root Business Unit Disabled 10-07-05
I inadvertently disabled the root business unit. Now I cannot log in as the administrator was member of the same unit. How do I re-enable this? go into the database and find the table called businessunitbase - then look at the field called isdisabled. You will probably find this is set to 1. try set it to 0 then see if you can logon. Make sure you backup the database before doing this "ril" <ril@discussions.microsoft.com> wrote in message news:CCC43C2F-F109-49BE-A4AC-287AFE0F4D93@microsoft.com... >I inadvertently disabled the root business unit. Now I cannot log in a...

Money 99 "Memo"
I was given Money 99. Everything was working fine till I printed out the check register and found it was omitting the "Memo" section which was vital info I needed. What do I need to do inorder to get it to print? Please e-mail wolfmagic53@hotmail.com Thank you. Ruby In microsoft.public.money, wolfmagic53@hotmail.com wrote: >I was given Money 99. Everything was working fine till I >printed out the check register and found it was omitting >the "Memo" section which was vital info I needed. >What do I need to do inorder to get it to print? What you want to do...

multiple iterations of same query
Running Access 2003 on Vista. I have a query that chooses 15 records at random. This works fine. What I want to do is run the same query for approx 110 different users, each with their own randomly selected 15 records. Obviously, I want to avoid manually running the same query that many times. How can I get around this? Post your query SQL. -- Build a little, test a little. "Nathan" wrote: > Running Access 2003 on Vista. > > I have a query that chooses 15 records at random. This works fine. > > What I want to do is run the same query fo...

Security Role Issue 03-16-05
Anyone know why a User assigned to only 1 custom Security Role that has restricted access for all Core records set to the "User" level?, e.g. the user is assigned the "User Read Lead" privilege, they should only be able to read Leads that they own or share. However, it doesn't seem to be working, as if this user selects the "All Leads" View, the not only can read other users' Leads, but can also modify them! Again this User is only assigned to this 1 Role. Thank you, BryanK Bryan, This is a common problem. How long ago was this role created? Someti...

Duplicating one Field from One table to Another
Hi - I have two tables - one position, one personnel - which has a 1-to-many relationship (1 position record to many personnel records). The department had a new requirement which made it necessary to change some coding (I inherited this). I'm using tab forms so that when a position is pulled up, you can click on the tab that has the personnel information (if there is any). There is a button on the Personnel form that allows the user to add a new Personnel record. Since I am using an Auto-number field in the Position table (which doubles as the PK) the functionality is fine. Wh...

when opening an excel file, 2 files open (one is book1)?
When I click on an excel file, 2 excel documents open up. One document is called Book1 and the other document is the actual document that I want to open. How do I stop this from happening? Brian To prevent Book1 from opening you can append /e to your shortcut for opening Excel. "C:\PROGRAM FILES\MICROSOFT OFFICE\OFFICE\EXCEL.EXE" /e Alternative to the above............ Go to Start>Settings>Folder>Options>File Types. Scroll down to MS Excel Worksheet. Then if running Win98 OS Edit> select "Open" and Edit. If using WinXP OS you would scroll down to...

creating template for multiple emails
How or can one create an email template (same subject line and message)for multiple emails (approx 100's)? Needing to shorten the time it takes sending 100's of individual emails (with the same subject line and same message) to sales staff on a weekly (some times daily) basis. Company does not want sales force using other sales persons email addresses (they are contractors). Try using a Distriburtion List, send one message, it goes to all of them, or get the message ready, put your address in the TO: field, and put everyone else in the BCC: field?? That way no one can see wher...

calendar for multiple time zone users: all day events
Users in different time zones post absences and meetings to a shared company calendar. When an all day event is scheduled in one time zone, it shows as spanning two days for other time zone users. How do we make this work properly? For the others it does intrude on a second day. BossLady wrote: > Users in different time zones post absences and meetings to a shared company > calendar. When an all day event is scheduled in one time zone, it shows as > spanning two days for other time zone users. How do we make this work > properly? Until you upgrade to Outlook...

Seperate jobs for multiple worksheets
Separate jobs are sent to printers when I select multiple worksheets... If I print to PDF, multiple files are created. How can I avoid this? ...

Bank One Download is not working
We are trying to download transaction data directly from Bank One and the file is not being recognized by Money - i.e. the Bank One website states that the download is complete, but Money does not recognize it. Note that a file is being saved on our hard drive but we can't figure out how to read it. It is entitled mnyimprt.exe-(numerous numbers and letters follow). In microsoft.public.money, Miki wrote: >We are trying to download transaction data directly from >Bank One and the file is not being recognized by Money - >i.e. the Bank One website states that the downloa...

Combining Several Worksheet into one
I have over 30 excel worksheets that are: 1. Password protected 2. The sheet is also password protected 3. Each worksheet contains only one tab call "All" and this tab is in the same format and contains the same column in every sheet. 4. It's located in the same folder I need to write a macro that will open all these workbook in this folder and combine the data into one new sheet with only one tab called "All". I am able to write the code to open all the workbook but am having a difficult time figuring out how to copy only the cells with data into the new workbook...

Memo Field Not pasting correctly in XL
I tried a search and couldn't find an answer. If there is an answer to this already please point me to it. I'm simply copying the record in Access and trying to paste it into XL. The problem that I'm having is one of the fields does not fully paste into XL. It is a memo field and has 539 characters. I'm only getting 255 when I try to paste the record in XL. I've tried copying just the contents of the cell and that worked fine. It seems as if I'm running into some sort of limitation. Does anyone know anything about this and is there a way to get around it? I checke...