Rabu, 31 Agustus 2011

RE: [MS_AccessPros] Missing records with 'nil returns'.

 

Aha! DateSerial takes three numeric arguments - year, month and day. If a
number is outside the "acceptable" range then it is manipulated
arithmetically. For example, month=13 gives January in the following year
and month=0 gives December in the previous year.

Likewise, day=0 gives the last day of the previous month, so:
DateSerial( Year( Date() ), Month( Date() ), 0 )
gives the last day of the month prior to the current one.

You could also use:
DateSerial( Year( Date() ), Month( Date() ) - 1, 1 )
which would give you the first day last month.

Since you are then using Format( d, "mmmm yyyy" ) it doesn't really matter
about the day.

Cheers,
Graham

> -----Original Message-----
> From: Robin Chapple [mailto:robinski@mymail.net.au]
> Sent: Thursday, 1 September 2011 15:36
> To: Graham Mandeno
> Subject: RE: [MS_AccessPros] Missing records with 'nil returns'.
>
>
> Graham,
>
> I have now studied your code and I cannot understand how it finds the
> previous month. We are already in September in Australia and my report
> shows results for August as expected, but how?
>
> What is the secret?
>
> Regards,
>
> Robin
>
> At 1/09/2011 11:56 AM, you wrote:
> >Hi Robin
> >
> >How about this:
> >ReportMonth=Format(DateSerial(Year(Date()),Month(Date()),0), "mmmm
> >yyyy")
> >
> >Cheers,
> >Graham
> >
> > > -----Original Message-----
> > > From: Robin Chapple [mailto:robinski@mymail.net.au]
> > > Sent: Thursday, 1 September 2011 13:44
> > > To: Graham Mandeno
> > > Subject: RE: [MS_AccessPros] Missing records with 'nil returns'.
> > >
> > >
> > > Graham,
> > >
> > > One way to work around my ASP problem would be if I had code that
> > > produced "mmm yyyy" for the previous month every month. That would
> > > allow the report to show the July 2011 in August 2011 and August in
> > > September for instance.
> > >
> > > Robin
> > >
> > >
> > > At 1/09/2011 09:42 AM, you wrote:
> > > >Hi Robin
> > > >
> > > >Getting my head around this slowly :-)
> > > >
> > > >So, the filtered qYearStart (or qYearStartTy) has 61 records, but 3
> > > >of them have no ClubID. Why? (That's just an aside)
> > > >
> > > >The problem here is that a LEFT join will only be "frustrated" if
> > > >there are no matching records in the right table. In your case,
> > > >there ARE matching records for the recalcitrant clubs, but not ones
> > > >that fulfill the ReportMonth="july 2011" filter.
> > > >
> > > >What you need to do is filter the query BEFORE you make the join.
> > > >You can do this with a subquery.
> > > >
> > > >Try this:
> > > >
> > > >SELECT
> > > >ys.ClubID, ys.ClubName, ys.Members AS [Year Start], tm.Members,
> > > >tm!Members-ys!Members AS Change, tm.MeetingsHeld,
> > > tm.MonthPerCent,
> > > >tm.ReportMonth, ys.YearStart FROM qYearStart as ys LEFT JOIN
> > > >(select * from qAttThisMonth where ReportMonth="july 2011") as tm
> > > >ON ys.ClubID
> > > =
> > > >tm.ClubID WHERE ys.YearStart=IIf(Month(Date())>=7,
> > > > DateSerial(Year(Date()),7,1),
> > > > DateSerial(Year(Date())-1,7,1)) ORDER BY ys.ClubName;
> > > >
> > > >Cheers,
> > > >Graham
> > > >
> > > > > -----Original Message-----
> > > > > From: Robin Chapple [mailto:robinski@mymail.net.au]
> > > > > Sent: Thursday, 1 September 2011 09:36
> > > > > To: MS_Access_Professionals@yahoogroups.com
> > > > > Subject: RE: [MS_AccessPros] Missing records with 'nil returns'.
> > > > >
> > > > >
> > > > > G'day Graham,
> > > > >
> > > > > Some of this is my ignorance and misuse of terms.
> > > > >
> > > > > At 1/09/2011 07:23 AM, you wrote:
> > > > >
> > > > > >There are two things that are puzzling me:
> > > > > >
> > > > > >In your original post you said: "The query "qYearStart" has 61
> > > > > >entries." If so, why do you need to filter on
> > > > > >qYearStart.YearStart at all? The query will return all 61
> > > > > >records with or without the
> >filter.
> > > > >
> > > > > I should have said 'when filtered. I have now removed that
> > > > > problem by
> > > >using
> > > > > "qYearStartTy" which delivers records for "This Year" without a
> >filter.
> > > > >
> > > > > >Also, does qAttThisMonth include any records which are NOT for
> > > > > >the current year, and can it have multiple records per ClubID?
> > > > >
> > > > > "qAttThisMonth" includes records for many years. There are no
> > > > > multiple records.
> > > > >
> > > > > >Note that there is another unrelated problem with this
expression:
> > > > > >[qAttThisMonth!Members]-[qYearStart!Members] AS Change
> > > > > >
> > > > > >It should be:
> > > > > >[qAttThisMonth]![Members]-[qYearStart]![Members] AS Change
> > > > > >
> > > > > >Or, if you prefer, leave out the square brackets altogether.
> > > > >
> > > > > I will correct that error.
> > > > >
> > > > > Many thanks,
> > > > >
> > > > > Regards,
> > > > >
> > > > > Robin
> > > > >
> > > > >
> > > > >
> > > > > >Best regards,
> > > > > >Graham
> > > > > >
> > > > > >From: MS_Access_Professionals@yahoogroups.com
> > > > > >[mailto:MS_Access_Professionals@yahoogroups.com] On Behalf Of
> > > Robin
> > > > > >Chapple
> > > > > >Sent: Thursday, 1 September 2011 00:25
> > > > > >To: MS_Access_Professionals@yahoogroups.com
> > > > > >Subject: RE: [MS_AccessPros] Missing records with 'nil returns'.
> > > > > >Graham,
> > > > > >
> > > > > >That delivers 61 records, 3 without ClubID.
> > > > > >
> > > > > >Robin
> > > > > >
> > > > > >At 31/08/2011 08:07 PM, you wrote:
> > > > > > >Robin,
> > > > > > >
> > > > > > >As a matter of interest, how many records does this give you?
> > > > > > >
> > > > > > >SELECT * FROM qYearStart WHERE (
> > > > > > >qYearStart.YearStart=IIf(Month(Date())>=7,
> > > > > > >DateSerial(Year(Date()),7,1),
> > > > > > >DateSerial(Year(Date())-1,7,1)) );
> > > > > > >--
> > > > > > >
> > > > > > >Graham
> > > > > >
> > > > > >
> > > > > >
> > > > > >------------------------------------
> > > > > >
> > > > > >Yahoo! Groups Links
> > > > > >
> > > > > >
> > > > > >
> > > > >
> > >
> > >
>
>

__._,_.___
Recent Activity:
MARKETPLACE
A bad score is 579. A good idea is checking yours at freecreditscore.com.
.

__,_._,___

Re: [AccessDevelopers] 2007 vs XP - puzzled??? help!!!

 


Hello,
I have not been on this site for a while.  I am doing some volunteer work on an access 2007 mdb which I am getting used to.  Today I modified a couple of forms and had absolutely no problem in test in access 2007 on windows xp.  I did not know that this shop had one pc with windows 7 (used by the manager) and all the other pcs had XP.  Now here's where it got wierd. 
The volunteer mdb is on a LAN  (ok - no problem).  It works on all the pcs with XP but LOCKED UP ALL OF ACCESS on the pc with WINDOW 7.  I could not get into access at all on that computer windows 7.  This was all working before I made this change!!!  <ouch>
I think this must have something to do with security??  you would think it would be the other way around, that
a modificaton on windows 7 would not work on the younger version.  It looks like access will need to be reinstalled on the 'volunteer' pc with windows 7. 
More info, the 2007 mdb has a security prompt which I needed to click to enable me to make the changes to the mdb.    entitled SECURITY WARNING.  All who use this should know what I'm talking about.
In my test mdb, I just ignored it and all worked.  But I need to click the 'enable' button to modify the volunteer mdb  and I do not know why?
I clicked the Open Trust Center in both mdb (my simple test one and the volunteer one) and the settings are identicle.  I have windows 7 on my desktop.
 
I know, you always should have the same operating system to avoid problems but it worked before on both versions??
 
Has anyone encountered this issue?  Any clue?  Is using access 2007 on 2 diff versions of windows error prone?
When I worked I always insisted that everyone work on the same operating system at the same time.
Non profits can be a mess.
 

__._,_.___
Recent Activity:

Please zip all files prior to uploading to Files section.
MARKETPLACE
A bad score is 598. A bad idea is not checking yours, at freecreditscore.com.
.

__,_._,___

RE: [MS_AccessPros] Query driving me nuts

 

Hi Lee

Try this:

Select tblEmployee.EmployeeName, tblForms.FormName
FROM tblEmployee, tblForms
WHERE tblEmployee.EmployeeID not in
(Select tblEmployeeForms.EmployeeID from tblEmployeeForms
where tblEmployeeForms!FormID=tblForms!FormID);

Note that there is no join between the two tables!

Best regards,
Graham

From: MS_Access_Professionals@yahoogroups.com
[mailto:MS_Access_Professionals@yahoogroups.com] On Behalf Of Lee
Sent: Thursday, 1 September 2011 11:30
To: MS_Access_Professionals@yahoogroups.com
Subject: [MS_AccessPros] Query driving me nuts

 
Hi,
I don't know why I can't get this...

I have a list of forms and a list of employees. Each employee has to fill
out each form.
So we have the employee table (tblEmployee), the form info table (tblForms),
and then the "many" table (tblEmployeeForms) that tells you which employee
has filled out which form.

There are 7 forms, it's easy enough to figure out which forms the employees
HAVE filled out, but I can't figure out which ones they HAVEN'T filled out.

So if Bob filled out forms A, B, and C, but not D-G, it's easy for me to
show
Bob A
Bob B
Bob C

What do I need to do to show the missing:
Bob D
Bob E
Bob F
Bob G

And what if Bubba hasn't filled out F and G? I need it to work for multiple
employees in the same query:
Bob D
Bob E
Bob F
Bob G
Bubba F
Bubba G

I've tried subqueries, exist statements, different joins, aliasing a query
as a table, union, I can't get anything to work! I have a strange feeling
I'm over-complicating it in my head =/

Thanks
-Lee

__._,_.___
Recent Activity:
MARKETPLACE
A bad score is 579. A good idea is checking yours at freecreditscore.com.
.

__,_._,___

[MS_AccessPros] Re: Query driving me nuts

 

I figured it out.

I alias'd a cartesian table, then left joined the table with the forms the employees have filled out, and got rid of the nulls

SELECT A.EMPLID, A.FormType, C.FullName
FROM ((SELECT tblEmployee.EMPLID, tblFormTypes.FormType
FROM tblEmployee, tblFormTypes) AS A
LEFT JOIN tblEmployeeForms AS B ON (A.FormType= B.FormType) AND (A.EMPLID= B.EMPLID))
LEFT JOIN tblEmployee as C ON A.EMPLID= C.EMPLID
WHERE B.EMPLID Is Null AND B.FormType Is Null

--- In MS_Access_Professionals@yahoogroups.com, "Lee" <leevt99@...> wrote:
>
> Hi,
> I don't know why I can't get this...
>
> I have a list of forms and a list of employees. Each employee has to fill out each form.
> So we have the employee table (tblEmployee), the form info table (tblForms), and then the "many" table (tblEmployeeForms) that tells you which employee has filled out which form.
>
> There are 7 forms, it's easy enough to figure out which forms the employees HAVE filled out, but I can't figure out which ones they HAVEN'T filled out.
>
> So if Bob filled out forms A, B, and C, but not D-G, it's easy for me to show
> Bob A
> Bob B
> Bob C
>
> What do I need to do to show the missing:
> Bob D
> Bob E
> Bob F
> Bob G
>
> And what if Bubba hasn't filled out F and G? I need it to work for multiple employees in the same query:
> Bob D
> Bob E
> Bob F
> Bob G
> Bubba F
> Bubba G
>
> I've tried subqueries, exist statements, different joins, aliasing a query as a table, union, I can't get anything to work! I have a strange feeling I'm over-complicating it in my head =/
>
> Thanks
> -Lee
>

__._,_.___
Recent Activity:
MARKETPLACE
A bad score is 579. A good idea is checking yours at freecreditscore.com.
.

__,_._,___

RE: [MS_AccessPros] Query driving me nuts

 

Have you tried the query wizard for unmatched records?

Liz Ravenwood
Database Developer / Programmer
Super First Class Products
B/E Aerospace
O: 1.520.239.4808
www.beaerospace.com

-----Original Message-----
From: MS_Access_Professionals@yahoogroups.com [mailto:MS_Access_Professionals@yahoogroups.com] On Behalf Of Lee
Sent: Wednesday, August 31, 2011 4:30 PM
To: MS_Access_Professionals@yahoogroups.com
Subject: [MS_AccessPros] Query driving me nuts

Hi,
I don't know why I can't get this...

I have a list of forms and a list of employees. Each employee has to fill out each form.
So we have the employee table (tblEmployee), the form info table (tblForms), and then the "many" table (tblEmployeeForms) that tells you which employee has filled out which form.

There are 7 forms, it's easy enough to figure out which forms the employees HAVE filled out, but I can't figure out which ones they HAVEN'T filled out.

So if Bob filled out forms A, B, and C, but not D-G, it's easy for me to show
Bob A
Bob B
Bob C

What do I need to do to show the missing:
Bob D
Bob E
Bob F
Bob G

And what if Bubba hasn't filled out F and G? I need it to work for multiple employees in the same query:
Bob D
Bob E
Bob F
Bob G
Bubba F
Bubba G

I've tried subqueries, exist statements, different joins, aliasing a query as a table, union, I can't get anything to work! I have a strange feeling I'm over-complicating it in my head =/

Thanks
-Lee

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

Yahoo! Groups Links

This email (and all attachments) is for the sole use of the intended recipient(s) and may contain privileged and/or proprietary information. Any unauthorized review, use, disclosure or distribution is prohibited. If you are not the intended recipient, please contact the sender by reply e-mail and destroy all copies of the original message.

__._,_.___
Recent Activity:
MARKETPLACE
A bad score is 598. A bad idea is not checking yours, at freecreditscore.com.
.

__,_._,___

[MS_AccessPros] Query driving me nuts

 

Hi,
I don't know why I can't get this...

I have a list of forms and a list of employees. Each employee has to fill out each form.
So we have the employee table (tblEmployee), the form info table (tblForms), and then the "many" table (tblEmployeeForms) that tells you which employee has filled out which form.

There are 7 forms, it's easy enough to figure out which forms the employees HAVE filled out, but I can't figure out which ones they HAVEN'T filled out.

So if Bob filled out forms A, B, and C, but not D-G, it's easy for me to show
Bob A
Bob B
Bob C

What do I need to do to show the missing:
Bob D
Bob E
Bob F
Bob G

And what if Bubba hasn't filled out F and G? I need it to work for multiple employees in the same query:
Bob D
Bob E
Bob F
Bob G
Bubba F
Bubba G

I've tried subqueries, exist statements, different joins, aliasing a query as a table, union, I can't get anything to work! I have a strange feeling I'm over-complicating it in my head =/

Thanks
-Lee

__._,_.___
Recent Activity:
MARKETPLACE
A bad score is 598. A bad idea is not checking yours, at freecreditscore.com.
.

__,_._,___

[MS_AccessPros] Re: Resizing/positioning a popup form

 

My experience has been that I usually move my mouse to the very top border of the form, right click and then "Move", and "Save". I then do this for for the size as well.

You might want to try changing the autosize property of the form and see if changing that provides you with ability to grab either the top of form or bottom.

I am also pretty sure that if you have autoresize "ON", Access may ignore your Height and width changes in your code.

I also think I read some where that the height property applies to Form sections and not the form itself, form section being Header, footer, detail.

I am curious, is it that the Top and Bottom of the form are off your screen ?, or can't you "grab" the corner/bottom. ?

terence

--- In MS_Access_Professionals@yahoogroups.com, "David Tolson" <drtolson@...> wrote:
>
> Thank you Terrence, I have followed your instructions and am unable to grab
> the bottom or top of the form for resizing. Since I can resize the width, it
> opens stretching from the top of the page to the bottom. I placed the
> following in the On Load event and that seemed to correct it
>
>
>
> Private Sub Form_Load()
>
> InsideHeight = 4000 '@ 4.25"
>
> InsideWidth = 5000 '@ 5.25"
>
> End Sub
>
>
>
> but after I reposition the form in the center of the screen and then save
> it, it still opens closer to the top when opened in form view.
>
>
>
> Thoughts?
>
>
>
> r/David
>
>
>
> From: MS_Access_Professionals@yahoogroups.com
> [mailto:MS_Access_Professionals@yahoogroups.com] On Behalf Of Terence
> Sent: Wednesday, August 31, 2011 4:31 PM
> To: MS_Access_Professionals@yahoogroups.com
> Subject: [MS_AccessPros] Re: Resizing/positioning a popup form
>
>
>
>
>
> David, I assume you have the forms property set to POPUP, If not, then set
> it to that.
>
> POPUP is in the properties , OTHER tab, POPUP set to yes.
>
> Under the Format tab the form properties set AUTO CENTER to YES.
>
> Sizing is your choice, you can either do it via code in the On Load event,
> or
> When the popup opens in Form mode, Grab an Edge of the form and drag the
> edge to size it the way you want, you can position the form on the screen as
> well. Be sure to do a right click SAVE on the form after you have positioned
> and sized it. Access will remember the size and position for the next time
> it opens.
>
> Terence
>
> --- In MS_Access_Professionals@yahoogroups.com
> <mailto:MS_Access_Professionals%40yahoogroups.com> , "djsdaddy531"
> <drtolson@> wrote:
> >
> > Great Day! I have created a popup form that I am using that permits the
> user to enter values into a table when the NotInList event triggers. but it
> opens into the entire page. I want it to open in the center of the screen
> and only be (maybe) 3" by 5".
> >
> > Any assistance will be greatly appreciated. Thanks in advance.
> >
> > r/David
> >
>
>
>
>
>
> [Non-text portions of this message have been removed]
>

__._,_.___
Recent Activity:
MARKETPLACE
A good Credit Score is 720, find yours & what impacts it at freecreditscore.com.
.

__,_._,___

[MS_AccessPros] query

 

Hi all!

I think this is simple BUT can't get my head around it.

I have two tables--Listings and ListingsMiscCat which joins ListID with MiscCatID. So one ListID can have several MiscCatID. The code I have below also joins the data with other tables.

I'd like to create a query where if there are no MiscCatID for a ListID or one of the MiscCatID's for a ListID is 4, that ListID is not in the result.

I have created one which works for null or if there's one MiscCatID for a ListID and that MiscCat=4. But if there are two MiscCatID's for a ListID, the one with the MiscCat<>4 shows up in the results.

Thanks!
Connie

SELECT Listings.ListID, Pending.PendingID, Listings.PropertyName, ConcatRelated("FullName1","qZ1ListingContactsCurrent","ListID = " & [Listings].[ListID]) & " L# "+[Listings].[LockboxNbr] AS KeyContacts, Listings.TelCombo, IIf([Listings].[ZoningID]=5," L",Left([Occupancy_List].[Occupancy],1)) AS Oc, qAddressCity.AddressCity AS Address, qCurrentPriceAll.CurrentPrice, Listings.ExpDate, ConcatRelated("Email","qZ2ListingContactsEmailNoDupes","ListID = " & [Listings].[ListID])+" " & [Listings].[XtraInfo] AS Email, Listings.ListingAgency, Listings.CurrentListing
FROM ((((qAddressCity INNER JOIN Listings ON qAddressCity.HomeInfoID = Listings.HomeInfoID) LEFT JOIN Occupancy_List ON Listings.OccupancyID = Occupancy_List.OccupancyID) INNER JOIN qCurrentPriceAll ON Listings.ListID = qCurrentPriceAll.ListID) LEFT JOIN ListingsMiscCat ON Listings.ListID = ListingsMiscCat.ListID) LEFT JOIN Pending ON Listings.ListID = Pending.ListID
WHERE (((Listings.ListingAgency)=1) AND ((Listings.CurrentListing)=-1) AND ((ListingsMiscCat.ListID) Is Null)) OR (((Listings.ListingAgency)=1) AND ((Listings.CurrentListing)=-1) AND ((ListingsMiscCat.ListID)<>4));

__._,_.___
Recent Activity:
MARKETPLACE
A bad score is 596. A good idea is checking yours at freecreditscore.com.
.

__,_._,___

RE: [MS_AccessPros] Missing records with 'nil returns'.

 


G'day Graham,

Some of this is my ignorance and misuse of terms.

At 1/09/2011 07:23 AM, you wrote:

>There are two things that are puzzling me:
>
>In your original post you said: "The query "qYearStart" has 61 entries." If
>so, why do you need to filter on qYearStart.YearStart at all? The query
>will return all 61 records with or without the filter.

I should have said 'when filtered. I have now removed that problem by
using "qYearStartTy" which delivers records for "This Year" without a filter.

>Also, does qAttThisMonth include any records which are NOT for the current
>year, and can it have multiple records per ClubID?

"qAttThisMonth" includes records for many years. There are no multiple records.

>Note that there is another unrelated problem with this expression:
>[qAttThisMonth!Members]-[qYearStart!Members] AS Change
>
>It should be:
>[qAttThisMonth]![Members]-[qYearStart]![Members] AS Change
>
>Or, if you prefer, leave out the square brackets altogether.

I will correct that error.

Many thanks,

Regards,

Robin

>Best regards,
>Graham
>
>From: MS_Access_Professionals@yahoogroups.com
>[mailto:MS_Access_Professionals@yahoogroups.com] On Behalf Of Robin Chapple
>Sent: Thursday, 1 September 2011 00:25
>To: MS_Access_Professionals@yahoogroups.com
>Subject: RE: [MS_AccessPros] Missing records with 'nil returns'.
>Graham,
>
>That delivers 61 records, 3 without ClubID.
>
>Robin
>
>At 31/08/2011 08:07 PM, you wrote:
> >Robin,
> >
> >As a matter of interest, how many records does this give you?
> >
> >SELECT * FROM qYearStart WHERE
> >(
> >qYearStart.YearStart=IIf(Month(Date())>=7,
> >DateSerial(Year(Date()),7,1),
> >DateSerial(Year(Date())-1,7,1))
> >);
> >--
> >
> >Graham
>
>
>
>------------------------------------
>
>Yahoo! Groups Links
>
>
>

__._,_.___
Recent Activity:
MARKETPLACE
A bad score is 598. A bad idea is not checking yours, at freecreditscore.com.
.

__,_._,___

RE: [MS_AccessPros] Missing records with 'nil returns'.

 

Hi Robin

There are two things that are puzzling me:

In your original post you said: "The query "qYearStart" has 61 entries." If
so, why do you need to filter on qYearStart.YearStart at all? The query
will return all 61 records with or without the filter.

Also, does qAttThisMonth include any records which are NOT for the current
year, and can it have multiple records per ClubID?

Note that there is another unrelated problem with this expression:
[qAttThisMonth!Members]-[qYearStart!Members] AS Change

It should be:
[qAttThisMonth]![Members]-[qYearStart]![Members] AS Change

Or, if you prefer, leave out the square brackets altogether.

Best regards,
Graham

From: MS_Access_Professionals@yahoogroups.com
[mailto:MS_Access_Professionals@yahoogroups.com] On Behalf Of Robin Chapple
Sent: Thursday, 1 September 2011 00:25
To: MS_Access_Professionals@yahoogroups.com
Subject: RE: [MS_AccessPros] Missing records with 'nil returns'.
Graham,

That delivers 61 records, 3 without ClubID.

Robin

At 31/08/2011 08:07 PM, you wrote:
>Robin,
>
>As a matter of interest, how many records does this give you?
>
>SELECT * FROM qYearStart WHERE
>(
>qYearStart.YearStart=IIf(Month(Date())>=7,
>DateSerial(Year(Date()),7,1),
>DateSerial(Year(Date())-1,7,1))
>);
>--
>
>Graham

__._,_.___
Recent Activity:
MARKETPLACE
A bad score is 598. A bad idea is not checking yours, at freecreditscore.com.
.

__,_._,___