[MS_AccessPros] A little table strategy

 

I am setting up a database for a local political campaign.  The campaign gives me names from various sources and I try to match them to the Registered Voter table where I get address and other information.  Sometimes, the campaign has the addresses of people who are not on the Voter ID list (ex: People who live outside our three precincts but still donate).

I have been using the voter ID as my foreign key to the CONTACTS table. I could append the non-registered-voters to the Registered Voter Table and make up a new key number (Ex: start with X) but I may want to overwrite the table if I get a new Registered voter list from the Election Board. 

Is there a good way to set up a parallel table for these "rogue" addresses?  Perhaps a better way of handling it?

Thanks

__._,_.___

---

Posted by: jan.hertzsch@gmail.com

---

Reply via web post

•

Reply to sender

•

Reply to group

•

Start a New Topic

•

Messages in this topic (1)

Visit Your Group

• New Members 4

• Privacy • Unsubscribe • Terms of Use

.

__,_._,___

[belajar-access] Fwd: dinamic moving shape

 

Wah belum ada yg respond yah...
---------- Forwarded message ----------
From: "Heru Wibowo" <heru.wibowo4456@gmail.com>
Date: 28 Apr 2015 20:48
Subject: dinamic moving shape
To: "Belajar Access" <belajar-access@yahoogroups.com>
Cc:

> Salam sejahtera,
>
> Dear All,
> Adakah yang punya link/pernah develop untuk menggeser shape sesuai keinginan/dinamis?
> Saya pernah lihat cuman lupa linknya, mudah mudahan ada yang bisa share link/teknisnya bagi yang pernah develop.
>
> Maksud saya dengan menggeser shape (bisa berupa kotak/bundar) ditempat yang diinginkan, hal ini bertujuan untuk melakukan setting layout rak gudang.
>
> Atas bantuan dari Rekan Rekan kami ucapkan terima kasih.
>
> Salam,
> Heru Wibowo

__._,_.___

---

Posted by: Heru Wibowo <heru.wibowo4456@gmail.com>

---

Reply via web post

•

Reply to sender

•

Reply to group

•

Start a New Topic

•

Messages in this topic (2)

SPAM IS PROHIBITED

Visit Your Group

• New Members 2

• Privacy • Unsubscribe • Terms of Use

.

__,_._,___

RE: [MS_AccessPros] How to link to a table with start dates

 

Duane,

Yes. Got it! Thanlks for your help today!! 
Doyce

---In MS_Access_Professionals@yahoogroups.com, <duanehookom@...> wrote :

Doyce,
You shouldn't need to "create a week number table". Can't you create a group by/totals query based on tblWeekStartDates that would function as a week number table?

Duane Hookom MVP
MS Access
 

To: MS_Access_Professionals@yahoogroups.com
From: MS_Access_Professionals@yahoogroups.com
Date: Wed, 29 Apr 2015 11:54:14 -0700
Subject: RE: [MS_AccessPros] How to link to a table with start dates



Duane,
Just hit a snag. I'm going to use this query to populate a graph. I want include the weeks that have zero records to show since it will be a line graph. I'm grouping on WStartDate and that looks good except for the weeks where the count would be zero. I think I can create a week number table and link to it with a left join but I wonder if there is a better way other than creating and linking another table?
Doyce

---In MS_Access_Professionals@yahoogroups.com, <winberry.doyce@...> wrote :

Duane,
 
No I didn't. Just week no's and week start dates. Now I understand what you mean. Great idea and simple!
Thanks!!
Doyce

---In MS_Access_Professionals@yahoogroups.com, <duanehookom@...> wrote :

Doyce,
 
Did you add a date field to tblWeekStartDates so there is a record for every date? If so, just join the unique date field from tblWeekStartDates to PICKUPDATE.
 
Duane Hookom MVP
MS Access
 

To: MS_Access_Professionals@yahoogroups.com
From: MS_Access_Professionals@yahoogroups.com
Date: Wed, 29 Apr 2015 09:53:46 -0700
Subject: RE: [MS_AccessPros] How to link to a table with start dates



Duane,

I've created the table with the dates but I still need help linking the SQL. Here is the SQL of the Query now:
SELECT "Doubles" AS Type, Count([New Primary].UNITID) AS CountOfUNITID, DatePart("ww",[PICKUPDATE],1) AS ProdWeek, Year([PICKUPDATE]) AS ProdYear
FROM [New Primary] LEFT JOIN TAG ON [New Primary].UNITID = TAG.[UNIT #]
WHERE (((TAG.TagException)=False)) OR (((TAG.TagException)=False Or (TAG.TagException) Is Null) AND ((TAG.[DATE TAG]) Is Null))
GROUP BY DatePart("ww",[PICKUPDATE],1), Year([PICKUPDATE])
HAVING (((Year([PICKUPDATE]))=Year(Now()))) OR (((Year([PICKUPDATE]))=Year(Now())));

The fields in tblWeekStartDates are ID, WeekNo, PYear and WStartDate. ProdWeek would link to WeekNo, ProdYear to PYear and I would like to add WStartDate to the query.

Doyce

---In MS_Access_Professionals@yahoogroups.com, <duanehookom@...> wrote :

I would keep this super simple by creating the tblWeekStartDates with all dates. You can add columns like [IsHoliday] etc.
 
If you view the SQL of a query, you can modify the join to use expressions rather than just field names.
 
Duane Hookom, MVP
MS Access
 

To: MS_Access_Professionals@yahoogroups.com
From: MS_Access_Professionals@yahoogroups.com
Date: Wed, 29 Apr 2015 09:31:41 -0700
Subject: [MS_AccessPros] How to link to a table with start dates



 
Hello friends,
I have created a table with Week Numbers, Years, and a Weekly Start Date. It is tblWeekStartDates. I want to create a query that uses datepart to extract a week number and year from a production date in order to group by week and then link it back to the week number table to get a weekly start date for that week's production. I know I can do this by creating a query and extracting the week number and year and then use that query as the record source for another query to link to tblWeekStartDates to get the start date but I wonder if that is the best way or if there is a way to link tblWeekStartDates to the original query?
Doyce

__._,_.___

---

Posted by: winberry.doyce@roadsysinc.com

---

Reply via web post

•

Reply to sender

•

Reply to group

•

Start a New Topic

•

Messages in this topic (8)

Visit Your Group

• New Members 5

• Privacy • Unsubscribe • Terms of Use

.

__,_._,___

RE: [MS_AccessPros] How to link to a table with start dates

 

Doyce,
You shouldn't need to "create a week number table". Can't you create a group by/totals query based on tblWeekStartDates that would function as a week number table?

Duane Hookom MVP
MS Access
 

---

To: MS_Access_Professionals@yahoogroups.com
From: MS_Access_Professionals@yahoogroups.com
Date: Wed, 29 Apr 2015 11:54:14 -0700
Subject: RE: [MS_AccessPros] How to link to a table with start dates



Duane,
Just hit a snag. I'm going to use this query to populate a graph. I want include the weeks that have zero records to show since it will be a line graph. I'm grouping on WStartDate and that looks good except for the weeks where the count would be zero. I think I can create a week number table and link to it with a left join but I wonder if there is a better way other than creating and linking another table?
Doyce

---In MS_Access_Professionals@yahoogroups.com, <winberry.doyce@...> wrote :

Duane,
 
No I didn't. Just week no's and week start dates. Now I understand what you mean. Great idea and simple!
Thanks!!
Doyce

---In MS_Access_Professionals@yahoogroups.com, <duanehookom@...> wrote :

Doyce,
 
Did you add a date field to tblWeekStartDates so there is a record for every date? If so, just join the unique date field from tblWeekStartDates to PICKUPDATE.
 
Duane Hookom MVP
MS Access
 

To: MS_Access_Professionals@yahoogroups.com
From: MS_Access_Professionals@yahoogroups.com
Date: Wed, 29 Apr 2015 09:53:46 -0700
Subject: RE: [MS_AccessPros] How to link to a table with start dates



Duane,

I've created the table with the dates but I still need help linking the SQL. Here is the SQL of the Query now:
SELECT "Doubles" AS Type, Count([New Primary].UNITID) AS CountOfUNITID, DatePart("ww",[PICKUPDATE],1) AS ProdWeek, Year([PICKUPDATE]) AS ProdYear
FROM [New Primary] LEFT JOIN TAG ON [New Primary].UNITID = TAG.[UNIT #]
WHERE (((TAG.TagException)=False)) OR (((TAG.TagException)=False Or (TAG.TagException) Is Null) AND ((TAG.[DATE TAG]) Is Null))
GROUP BY DatePart("ww",[PICKUPDATE],1), Year([PICKUPDATE])
HAVING (((Year([PICKUPDATE]))=Year(Now()))) OR (((Year([PICKUPDATE]))=Year(Now())));

The fields in tblWeekStartDates are ID, WeekNo, PYear and WStartDate. ProdWeek would link to WeekNo, ProdYear to PYear and I would like to add WStartDate to the query.

Doyce

---In MS_Access_Professionals@yahoogroups.com, <duanehookom@...> wrote :

I would keep this super simple by creating the tblWeekStartDates with all dates. You can add columns like [IsHoliday] etc.
 
If you view the SQL of a query, you can modify the join to use expressions rather than just field names.
 
Duane Hookom, MVP
MS Access
 

To: MS_Access_Professionals@yahoogroups.com
From: MS_Access_Professionals@yahoogroups.com
Date: Wed, 29 Apr 2015 09:31:41 -0700
Subject: [MS_AccessPros] How to link to a table with start dates



 
Hello friends,
I have created a table with Week Numbers, Years, and a Weekly Start Date. It is tblWeekStartDates. I want to create a query that uses datepart to extract a week number and year from a production date in order to group by week and then link it back to the week number table to get a weekly start date for that week's production. I know I can do this by creating a query and extracting the week number and year and then use that query as the record source for another query to link to tblWeekStartDates to get the start date but I wonder if that is the best way or if there is a way to link tblWeekStartDates to the original query?
Doyce

__._,_.___

---

Posted by: Duane Hookom <duanehookom@hotmail.com>

---

Reply via web post

•

Reply to sender

•

Reply to group

•

Start a New Topic

•

Messages in this topic (7)

Visit Your Group

• New Members 5

• Privacy • Unsubscribe • Terms of Use

.

__,_._,___

RE: [MS_AccessPros] How to link to a table with start dates

 

Duane,

Just hit a snag. I'm going to use this query to populate a graph. I want include the weeks that have zero records to show since it will be a line graph. I'm grouping on WStartDate and that looks good except for the weeks where the count would be zero. I think I can create a week number table and link to it with a left join but I wonder if there is a better way other than creating and linking another table?
Doyce

---In MS_Access_Professionals@yahoogroups.com, <winberry.doyce@...> wrote :

Duane,

 

No I didn't. Just week no's and week start dates. Now I understand what you mean. Great idea and simple!

Thanks!!

Doyce

---In MS_Access_Professionals@yahoogroups.com, <duanehookom@...> wrote :

Doyce,
 
Did you add a date field to tblWeekStartDates so there is a record for every date? If so, just join the unique date field from tblWeekStartDates to PICKUPDATE.
 
Duane Hookom MVP
MS Access
 

To: MS_Access_Professionals@yahoogroups.com
From: MS_Access_Professionals@yahoogroups.com
Date: Wed, 29 Apr 2015 09:53:46 -0700
Subject: RE: [MS_AccessPros] How to link to a table with start dates



Duane,

I've created the table with the dates but I still need help linking the SQL. Here is the SQL of the Query now:
SELECT "Doubles" AS Type, Count([New Primary].UNITID) AS CountOfUNITID, DatePart("ww",[PICKUPDATE],1) AS ProdWeek, Year([PICKUPDATE]) AS ProdYear
FROM [New Primary] LEFT JOIN TAG ON [New Primary].UNITID = TAG.[UNIT #]
WHERE (((TAG.TagException)=False)) OR (((TAG.TagException)=False Or (TAG.TagException) Is Null) AND ((TAG.[DATE TAG]) Is Null))
GROUP BY DatePart("ww",[PICKUPDATE],1), Year([PICKUPDATE])
HAVING (((Year([PICKUPDATE]))=Year(Now()))) OR (((Year([PICKUPDATE]))=Year(Now())));

The fields in tblWeekStartDates are ID, WeekNo, PYear and WStartDate. ProdWeek would link to WeekNo, ProdYear to PYear and I would like to add WStartDate to the query.

Doyce

---In MS_Access_Professionals@yahoogroups.com, <duanehookom@...> wrote :

I would keep this super simple by creating the tblWeekStartDates with all dates. You can add columns like [IsHoliday] etc.
 
If you view the SQL of a query, you can modify the join to use expressions rather than just field names.
 
Duane Hookom, MVP
MS Access
 

To: MS_Access_Professionals@yahoogroups.com
From: MS_Access_Professionals@yahoogroups.com
Date: Wed, 29 Apr 2015 09:31:41 -0700
Subject: [MS_AccessPros] How to link to a table with start dates



 
Hello friends,
I have created a table with Week Numbers, Years, and a Weekly Start Date. It is tblWeekStartDates. I want to create a query that uses datepart to extract a week number and year from a production date in order to group by week and then link it back to the week number table to get a weekly start date for that week's production. I know I can do this by creating a query and extracting the week number and year and then use that query as the record source for another query to link to tblWeekStartDates to get the start date but I wonder if that is the best way or if there is a way to link tblWeekStartDates to the original query?
Doyce

__._,_.___

---

Posted by: winberry.doyce@roadsysinc.com

---

Reply via web post

•

Reply to sender

•

Reply to group

•

Start a New Topic

•

Messages in this topic (6)

Visit Your Group

• New Members 5

• Privacy • Unsubscribe • Terms of Use

.

__,_._,___

RE: [MS_AccessPros] How to link to a table with start dates

 

Duane,

 

No I didn't. Just week no's and week start dates. Now I understand what you mean. Great idea and simple!

Thanks!!

Doyce

---In MS_Access_Professionals@yahoogroups.com, <duanehookom@...> wrote :

Doyce,
 
Did you add a date field to tblWeekStartDates so there is a record for every date? If so, just join the unique date field from tblWeekStartDates to PICKUPDATE.
 
Duane Hookom MVP
MS Access
 

To: MS_Access_Professionals@yahoogroups.com
From: MS_Access_Professionals@yahoogroups.com
Date: Wed, 29 Apr 2015 09:53:46 -0700
Subject: RE: [MS_AccessPros] How to link to a table with start dates



Duane,

I've created the table with the dates but I still need help linking the SQL. Here is the SQL of the Query now:
SELECT "Doubles" AS Type, Count([New Primary].UNITID) AS CountOfUNITID, DatePart("ww",[PICKUPDATE],1) AS ProdWeek, Year([PICKUPDATE]) AS ProdYear
FROM [New Primary] LEFT JOIN TAG ON [New Primary].UNITID = TAG.[UNIT #]
WHERE (((TAG.TagException)=False)) OR (((TAG.TagException)=False Or (TAG.TagException) Is Null) AND ((TAG.[DATE TAG]) Is Null))
GROUP BY DatePart("ww",[PICKUPDATE],1), Year([PICKUPDATE])
HAVING (((Year([PICKUPDATE]))=Year(Now()))) OR (((Year([PICKUPDATE]))=Year(Now())));

The fields in tblWeekStartDates are ID, WeekNo, PYear and WStartDate. ProdWeek would link to WeekNo, ProdYear to PYear and I would like to add WStartDate to the query.

Doyce

---In MS_Access_Professionals@yahoogroups.com, <duanehookom@...> wrote :

I would keep this super simple by creating the tblWeekStartDates with all dates. You can add columns like [IsHoliday] etc.
 
If you view the SQL of a query, you can modify the join to use expressions rather than just field names.
 
Duane Hookom, MVP
MS Access
 

To: MS_Access_Professionals@yahoogroups.com
From: MS_Access_Professionals@yahoogroups.com
Date: Wed, 29 Apr 2015 09:31:41 -0700
Subject: [MS_AccessPros] How to link to a table with start dates



 
Hello friends,
I have created a table with Week Numbers, Years, and a Weekly Start Date. It is tblWeekStartDates. I want to create a query that uses datepart to extract a week number and year from a production date in order to group by week and then link it back to the week number table to get a weekly start date for that week's production. I know I can do this by creating a query and extracting the week number and year and then use that query as the record source for another query to link to tblWeekStartDates to get the start date but I wonder if that is the best way or if there is a way to link tblWeekStartDates to the original query?
Doyce

__._,_.___

---

Posted by: winberry.doyce@roadsysinc.com

---

Reply via web post

•

Reply to sender

•

Reply to group

•

Start a New Topic

•

Messages in this topic (5)

Visit Your Group

• New Members 5

• Privacy • Unsubscribe • Terms of Use

.

__,_._,___

RE: [MS_AccessPros] How to link to a table with start dates

 

Doyce,
 
Did you add a date field to tblWeekStartDates so there is a record for every date? If so, just join the unique date field from tblWeekStartDates to PICKUPDATE.
 
Duane Hookom MVP
MS Access
 

---

To: MS_Access_Professionals@yahoogroups.com
From: MS_Access_Professionals@yahoogroups.com
Date: Wed, 29 Apr 2015 09:53:46 -0700
Subject: RE: [MS_AccessPros] How to link to a table with start dates



Duane,

I've created the table with the dates but I still need help linking the SQL. Here is the SQL of the Query now:
SELECT "Doubles" AS Type, Count([New Primary].UNITID) AS CountOfUNITID, DatePart("ww",[PICKUPDATE],1) AS ProdWeek, Year([PICKUPDATE]) AS ProdYear
FROM [New Primary] LEFT JOIN TAG ON [New Primary].UNITID = TAG.[UNIT #]
WHERE (((TAG.TagException)=False)) OR (((TAG.TagException)=False Or (TAG.TagException) Is Null) AND ((TAG.[DATE TAG]) Is Null))
GROUP BY DatePart("ww",[PICKUPDATE],1), Year([PICKUPDATE])
HAVING (((Year([PICKUPDATE]))=Year(Now()))) OR (((Year([PICKUPDATE]))=Year(Now())));

The fields in tblWeekStartDates are ID, WeekNo, PYear and WStartDate. ProdWeek would link to WeekNo, ProdYear to PYear and I would like to add WStartDate to the query.

Doyce

---In MS_Access_Professionals@yahoogroups.com, <duanehookom@...> wrote :

I would keep this super simple by creating the tblWeekStartDates with all dates. You can add columns like [IsHoliday] etc.
 
If you view the SQL of a query, you can modify the join to use expressions rather than just field names.
 
Duane Hookom, MVP
MS Access
 

To: MS_Access_Professionals@yahoogroups.com
From: MS_Access_Professionals@yahoogroups.com
Date: Wed, 29 Apr 2015 09:31:41 -0700
Subject: [MS_AccessPros] How to link to a table with start dates



 
Hello friends,
I have created a table with Week Numbers, Years, and a Weekly Start Date. It is tblWeekStartDates. I want to create a query that uses datepart to extract a week number and year from a production date in order to group by week and then link it back to the week number table to get a weekly start date for that week's production. I know I can do this by creating a query and extracting the week number and year and then use that query as the record source for another query to link to tblWeekStartDates to get the start date but I wonder if that is the best way or if there is a way to link tblWeekStartDates to the original query?
Doyce

__._,_.___

---

Posted by: Duane Hookom <duanehookom@hotmail.com>

---

Reply via web post

•

Reply to sender

•

Reply to group

•

Start a New Topic

•

Messages in this topic (4)

Visit Your Group

• New Members 5

• Privacy • Unsubscribe • Terms of Use

.

__,_._,___

RE: [MS_AccessPros] Re: Divide by Zero

 

Got it! Thanks Bill!

---In MS_Access_Professionals@yahoogroups.com, <wrmosca@...> wrote :

Doyce - an int ranges from -32,768 to 32,767;  a long is from -2,147,483,648 to 2,147,483,647. I always use Long because with the databases I support 32,767 records is not always a safe ceiling.

-Bill

---In MS_Access_Professionals@yahoogroups.com, <winberry.doyce@...> wrote :

Bill,

I used Cint. What is the difference between CLong and Cint?
Doyce

---In MS_Access_Professionals@yahoogroups.com, <wrmosca@...> wrote :

Doyce - I see what you mean. The query is returning the DCount as a string. Wrapping it in CLng() should fix that.

Mighty strange. 

-Bill

---In MS_Access_Professionals@yahoogroups.com, <winberry.doyce@...> wrote :

Bill,

Here is the SQL of the query:
SELECT "Doubles" AS Type, Count([New Primary].UNITID) AS CountOfUNITID, DCount("UnitID","New Primary","[PickupDate] Between [Forms]![frmStartDateEndDate]![Start Date] And [Forms]![frmStartDateEndDate]![End Date]") AS TotalProduced
FROM [New Primary] LEFT JOIN TAG ON [New Primary].UNITID = TAG.[UNIT #]
WHERE ((([New Primary].PICKUPDATE) Between [Forms]![frmStartDateEndDate]![Start Date] And [Forms]![frmStartDateEndDate]![End Date] And ([New Primary].PICKUPDATE)<>[date tag]) AND ((TAG.TagException)=False)) OR ((([New Primary].PICKUPDATE) Between [Forms]![frmStartDateEndDate]![Start Date] And [Forms]![frmStartDateEndDate]![End Date]) AND ((TAG.TagException)=False Or (TAG.TagException) Is Null) AND ((TAG.[DATE TAG]) Is Null));
Doyce

---In MS_Access_Professionals@yahoogroups.com, <wrmosca@...> wrote :

Doyce - Something is going on here. DCount returns a variant Long. Being a variant, it can return a Null as well, which happens if the field in the criteria argument is not in the domain (ie the table or query). It might look like a string in the Immediate window , but if you run this:

?vartype(dcount("*", "Customers"))

you will get 3 which is a Long.

Here is a full list of vartypes:

vbVartypes

Value     Variant type

0     Empty (unitialized)

1     Null (no valid data)

2     Integer

3     Long Integer

4     Single

5     Double

6     Currency

7     Date

8     String

9     Object

10     Error value

11     Boolean

12     Variant (only used with arrays of variants)

13     Data access object

14     Decimal value

17     Byte

36     User Defined Type

8192           Array

Regards,

Bill Mosca, Founder - MS_Access_Professionals

http://www.thatlldoit.com

Microsoft Office Access MVP

http://mvp.microsoft.com/en-us/mvp/Bill%20Mosca-35852

My nothing-to-do-with-Access blog

http://wrmosca.wordpress.com

---In MS_Access_Professionals@yahoogroups.com, <winberry.doyce@...> wrote :

It is left aligned.

Doyce

---In MS_Access_Professionals@yahoogroups.com, <duanehookom@...> wrote :

I would also expect it to return a number. When I enter this into the debug window:
? DCount("*","tblTest")
The result is returned with a leading space which suggests a number. When you viewe the query in datasheet, is the TotalProduced column left or right-aligned?
 
Duane Hookom, MVP
MS Access
 

To: MS_Access_Professionals@yahoogroups.com
From: MS_Access_Professionals@yahoogroups.com
Date: Wed, 29 Apr 2015 07:41:51 -0700
Subject: RE: [MS_AccessPros] Re: Divide by Zero



Duane,

TotalProduced is actually the result of a Dcount formula in a query with date parameters.

TotalProduced: DCount("UnitID","New Primary","[PickupDate] Between [Forms]![frmStartDateEndDate]![Start Date] And [Forms]![frmStartDateEndDate]![End Date]")

I wasn't expecting Dcount to return the number in text format. Does Dcount always do that or would it have something to do with the query being a totals query?

Doyce 

---In MS_Access_Professionals@yahoogroups.com, <duanehookom@...> wrote :

Doyce,
 
If [TotalProduced] is actually text, I would consider that a problem. Some database systems would have an issue with using a string in a numeric operation ([CountOfUnitID]/[TotalProduced]). Access is helpful in that: 123/"3" = 41.
 
I would make sure [TotalProduced] is numeric if at all possible. If the field will never be Null, you could use:

 Percent: IIf(Val([TotalProduced])=0, 0, Round(([CountOfUnitID]/Val([TotalProduced]))*100,1))
 
I prefer to be more explicit in my expressions.
 
Duane Hookom, MVP
MS Access
 

To: MS_Access_Professionals@yahoogroups.com
From: MS_Access_Professionals@yahoogroups.com
Date: Wed, 29 Apr 2015 06:26:23 -0700
Subject: [MS_AccessPros] Re: Divide by Zero

Hey, I just figured this out. Total Produced is returning the zero as text instead of a number. If I use this formula, I get the results I want:

Percent: IIf([TotalProduced]="0",0,Round(([CountOfUnitID]/[TotalProduced])*100,1))

 

Problem Solved.

Doyce

---In MS_Access_Professionals@yahoogroups.com, <winberry.doyce@...> wrote :

 Hello Friends,
I have a query where I'm trying to caculate a percentage based on two other fields. This formula works unless there is a zero in the Total Produced field and then it returns #Num!. Here is the formula:
Percent: IIf([TotalProduced] Is Null,0,Round(([CountOfUnitID]/[TotalProduced])*100,1))
Here is the SQL of the query:
SELECT qrySubPercentageTrailersNotTaggedWhenProduced.Type, qrySubPercentageTrailersNotTaggedWhenProduced.CountOfUNITID, qrySubPercentageTrailersNotTaggedWhenProduced.TotalProduced, IIf([TotalProduced] Is Null,0,Round(([CountOfUnitID]/[TotalProduced])*100,1)) AS [Percent]
FROM qrySubPercentageTrailersNotTaggedWhenProduced;
I appreciate someone's help with my syntax.
Doyce

__._,_.___

---

Posted by: winberry.doyce@roadsysinc.com

---

Reply via web post

•

Reply to sender

•

Reply to group

•

Start a New Topic

•

Messages in this topic (16)

Visit Your Group

• New Members 5

• Privacy • Unsubscribe • Terms of Use

.

__,_._,___

RE: [MS_AccessPros] Re: Divide by Zero

 

Doyce - an int ranges from -32,768 to 32,767;  a long is from -2,147,483,648 to 2,147,483,647. I always use Long because with the databases I support 32,767 records is not always a safe ceiling.

-Bill

---In MS_Access_Professionals@yahoogroups.com, <winberry.doyce@roadsysinc.com> wrote :

Bill,

I used Cint. What is the difference between CLong and Cint?
Doyce

---In MS_Access_Professionals@yahoogroups.com, <wrmosca@...> wrote :

Doyce - I see what you mean. The query is returning the DCount as a string. Wrapping it in CLng() should fix that.

Mighty strange. 

-Bill

---In MS_Access_Professionals@yahoogroups.com, <winberry.doyce@...> wrote :

Bill,

Here is the SQL of the query:
SELECT "Doubles" AS Type, Count([New Primary].UNITID) AS CountOfUNITID, DCount("UnitID","New Primary","[PickupDate] Between [Forms]![frmStartDateEndDate]![Start Date] And [Forms]![frmStartDateEndDate]![End Date]") AS TotalProduced
FROM [New Primary] LEFT JOIN TAG ON [New Primary].UNITID = TAG.[UNIT #]
WHERE ((([New Primary].PICKUPDATE) Between [Forms]![frmStartDateEndDate]![Start Date] And [Forms]![frmStartDateEndDate]![End Date] And ([New Primary].PICKUPDATE)<>[date tag]) AND ((TAG.TagException)=False)) OR ((([New Primary].PICKUPDATE) Between [Forms]![frmStartDateEndDate]![Start Date] And [Forms]![frmStartDateEndDate]![End Date]) AND ((TAG.TagException)=False Or (TAG.TagException) Is Null) AND ((TAG.[DATE TAG]) Is Null));
Doyce

---In MS_Access_Professionals@yahoogroups.com, <wrmosca@...> wrote :

Doyce - Something is going on here. DCount returns a variant Long. Being a variant, it can return a Null as well, which happens if the field in the criteria argument is not in the domain (ie the table or query). It might look like a string in the Immediate window , but if you run this:

?vartype(dcount("*", "Customers"))

you will get 3 which is a Long.

Here is a full list of vartypes:

vbVartypes

Value     Variant type

0     Empty (unitialized)

1     Null (no valid data)

2     Integer

3     Long Integer

4     Single

5     Double

6     Currency

7     Date

8     String

9     Object

10     Error value

11     Boolean

12     Variant (only used with arrays of variants)

13     Data access object

14     Decimal value

17     Byte

36     User Defined Type

8192           Array

Regards,

Bill Mosca, Founder - MS_Access_Professionals

http://www.thatlldoit.com

Microsoft Office Access MVP

http://mvp.microsoft.com/en-us/mvp/Bill%20Mosca-35852

My nothing-to-do-with-Access blog

http://wrmosca.wordpress.com

---In MS_Access_Professionals@yahoogroups.com, <winberry.doyce@...> wrote :

It is left aligned.

Doyce

---In MS_Access_Professionals@yahoogroups.com, <duanehookom@...> wrote :

I would also expect it to return a number. When I enter this into the debug window:
? DCount("*","tblTest")
The result is returned with a leading space which suggests a number. When you viewe the query in datasheet, is the TotalProduced column left or right-aligned?
 
Duane Hookom, MVP
MS Access
 

To: MS_Access_Professionals@yahoogroups.com
From: MS_Access_Professionals@yahoogroups.com
Date: Wed, 29 Apr 2015 07:41:51 -0700
Subject: RE: [MS_AccessPros] Re: Divide by Zero



Duane,

TotalProduced is actually the result of a Dcount formula in a query with date parameters.

TotalProduced: DCount("UnitID","New Primary","[PickupDate] Between [Forms]![frmStartDateEndDate]![Start Date] And [Forms]![frmStartDateEndDate]![End Date]")

I wasn't expecting Dcount to return the number in text format. Does Dcount always do that or would it have something to do with the query being a totals query?

Doyce 

---In MS_Access_Professionals@yahoogroups.com, <duanehookom@...> wrote :

Doyce,
 
If [TotalProduced] is actually text, I would consider that a problem. Some database systems would have an issue with using a string in a numeric operation ([CountOfUnitID]/[TotalProduced]). Access is helpful in that: 123/"3" = 41.
 
I would make sure [TotalProduced] is numeric if at all possible. If the field will never be Null, you could use:

 Percent: IIf(Val([TotalProduced])=0, 0, Round(([CountOfUnitID]/Val([TotalProduced]))*100,1))
 
I prefer to be more explicit in my expressions.
 
Duane Hookom, MVP
MS Access
 

To: MS_Access_Professionals@yahoogroups.com
From: MS_Access_Professionals@yahoogroups.com
Date: Wed, 29 Apr 2015 06:26:23 -0700
Subject: [MS_AccessPros] Re: Divide by Zero

Hey, I just figured this out. Total Produced is returning the zero as text instead of a number. If I use this formula, I get the results I want:

Percent: IIf([TotalProduced]="0",0,Round(([CountOfUnitID]/[TotalProduced])*100,1))

 

Problem Solved.

Doyce

---In MS_Access_Professionals@yahoogroups.com, <winberry.doyce@...> wrote :

 Hello Friends,
I have a query where I'm trying to caculate a percentage based on two other fields. This formula works unless there is a zero in the Total Produced field and then it returns #Num!. Here is the formula:
Percent: IIf([TotalProduced] Is Null,0,Round(([CountOfUnitID]/[TotalProduced])*100,1))
Here is the SQL of the query:
SELECT qrySubPercentageTrailersNotTaggedWhenProduced.Type, qrySubPercentageTrailersNotTaggedWhenProduced.CountOfUNITID, qrySubPercentageTrailersNotTaggedWhenProduced.TotalProduced, IIf([TotalProduced] Is Null,0,Round(([CountOfUnitID]/[TotalProduced])*100,1)) AS [Percent]
FROM qrySubPercentageTrailersNotTaggedWhenProduced;
I appreciate someone's help with my syntax.
Doyce

__._,_.___

---

Posted by: wrmosca@comcast.net

---

Reply via web post

•

Reply to sender

•

Reply to group

•

Start a New Topic

•

Messages in this topic (15)

Visit Your Group

• New Members 5

• Privacy • Unsubscribe • Terms of Use

.

__,_._,___