ACCESS FEED: Oktober 2014

Jumat, 31 Oktober 2014

[belajar-access] WTSay salam Kenal

Selamat siang
Momod sekalian,
Rekan-rekan sekalian,

Salam kenal, salam hormat dan salam sejahtera bagi anda semua.
Perkenalken, nama saya Akbar, Domisili didepok.
Mohon bantuan dan arahan dari anda semua, saya baru minggu-minggu ini ingin mendalami Access otodidak. Semoga otak saya masih mampu nelen access.
Tengkiyu sudah berkenan menerima saya gabung disini.

Regards

--
bila bersih, mengapa risih...?

__._,_.___

Posted by: "akbar" <akbar.ismail.f@gmail.com>

Reply via web post

•

Reply to sender

•

Reply to group

•

Start a New Topic

•

Messages in this topic (1)

SPAM IS PROHIBITED

Visit Your Group

New Members 3

• Privacy • Unsubscribe • Terms of Use

__,_._,___

[MS_AccessPros] New file uploaded to MS_Access_Professionals

Hello,

This email message is a notification to let you know that
a file has been uploaded to the Files area of the MS_Access_Professionals
group.

File : /0_Utilities and Add-ins/Report View Filtering.Accdb
Uploaded by : jlviescas <JohnV@msn.com>
Description : Builds custom report shortcut menus and adds custom filtering for text and numeric fields for Report View for ALL the reports in your database.

See instructions embedded in the modReportViewFiltering module.

Code courtesy of Peter Weinwurm and John Viescas

You can access this file at the URL:
https://groups.yahoo.com/neo/groups/MS_Access_Professionals/files/0_Utilities%20and%20Add-ins/Report%20View%20Filtering.Accdb

To learn more about file sharing for your group, please visit:
https://help.yahoo.com/kb/index?page=content&y=PROD_GRPS&locale=en_US&id=SLN15398

Regards,

jlviescas <JohnV@msn.com>

__._,_.___

Reply via web post

•

Reply to sender

•

Reply to group

•

Start a New Topic

•

Messages in this topic (1067)

Visit Your Group

• Privacy • Unsubscribe • Terms of Use

__,_._,___

[MS_AccessPros] Fwd: Report View Myths

Begin forwarded message:

From: "Peter A. Weinwurm" <weinwurm@axiumsys.ca>

To: "'John Viescas'" <john@viescas.com>

Subject: Report View Myths

Date: October 31, 2014 at 4:12:57 PM GMT+1

John,

I have Access 2007 Service Pack 3 installed on my machine and I include Access 2007 Service Pack 3 with my runtime installation.

0) The Filters stick when printing, previewing or PDF the report.

1) In regards to the below. I have been filtering in Report view for 1.5 years and have never experienced / observed any errors. The only thing to watch out for is if you use DOES NOT CONTAIN X from the shortcut menu it will also filter out the records that have NULLS. For this reason none of my fields have Nulls if they appear in reports. I make Null = "Unspecified" etc…

2) I have heard people comment that Can Shrink and Can Grow don't work correctly in Report View. That is not my experience. It has always worked perfectly for me.

Peter A. Weinwurm

President

647-204-1592

www.hazwaste.ca

www.axiumsys.ca
ca.linkedin.com/in/peterweinwurm

__._,_.___

Posted by: John Viescas <johnv@msn.com>

Reply via web post

•

Reply to sender

•

Reply to group

•

Start a New Topic

•

Messages in this topic (1)

Visit Your Group

• Privacy • Unsubscribe • Terms of Use

__,_._,___

Re: [MS_AccessPros] How to enter different product in the same carton

John,

Perfectly right!

Thanks a lot John once again.

Khalid

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

Khalid-

After I sent my last response, I thought that might be the case. Add a filter on ClientCIN like this:

=DCount("CartonNo","qryGroupCartonsFrieghtInvoice","ConsignmentNo = '" & [ConsignmentNo] & "' AND ClientCIN = " & [ClientCIN])

John Viescas, Author

Microsoft Access 2010 Inside Out

Microsoft Access 2007 Inside Out

Microsoft Access 2003 Inside Out

Building Microsoft Access Applications

SQL Queries for Mere Mortals

http://www.viescas.com/

(Paris, France)

On Oct 31, 2014, at 2:53 PM, khalidtanweerburrah@... [MS_Access_Professionals] <MS_Access_Professionals@yahoogroups.com> wrote:

John,

The code you gave me it counts total cartons of a particular Consignment, but i need TotalCartons for specific Consignment and specific selected ClientCIN.

I assume that ClientCIN should be in this code, i've tried myself but getting error.
ConsignmentNo is Text
ClientCIN is Numeric

Khalid

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

Khalid-

Ah! The clue is you have now told met that this in the ConsignmentNo footer. Change the Control Source to this:

=DCount("CartonNo","qryGroupCartonsFrieghtInvoice","ConsignmentNo = " & [ConsignmentNo])

I don't remember if ConsignmentNo is text or not. If it is text, you'll have to add quotes like this:

=DCount("CartonNo","qryGroupCartonsFrieghtInvoice","ConsignmentNo = '" & [ConsignmentNo] & "'")

John Viescas, Author
Microsoft Access 2010 Inside Out
Microsoft Access 2007 Inside Out
Microsoft Access 2003 Inside Out
Building Microsoft Access Applications
SQL Queries for Mere Mortals
http://www.viescas.com/
(Paris, France)

On Oct 31, 2014, at 1:13 PM, khalidtanweerburrah@... [MS_Access_Professionals] <MS_Access_Professionals@yahoogroups.com> wrote:

John,

OK, i will follow this method.

But what about getting CartonTotal on the report's ConsignmentNo Footer, which is not coming correct?

Khalid

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

Khalid-

For cases 1 and 3, use a letter suffix. For case 2, use a number. When a carton that is in case 1 or 3 has multiple products in one carton, use a letter AND a number. The default value for suffix should be blank or 0. If you use 0, start with 1 to indicate multiple products in a carton.

John Viescas, Author
Microsoft Access 2010 Inside Out
Microsoft Access 2007 Inside Out
Microsoft Access 2003 Inside Out
Building Microsoft Access Applications
SQL Queries for Mere Mortals
http://www.viescas.com/
(Paris, France)

On Oct 31, 2014, at 10:16 AM, khalidtanweerburrah@... [MS_Access_Professionals] <MS_Access_Professionals@yahoogroups.com> wrote:

John,
Yes! of course a good catch from Robert (Thanks Robert). It worked smoothly and now i can see ClientName on cmbClientCIN along with its relative ClientCIN.

Private Sub cmbConsignmentNo_AfterUpdate()
Me.cmbClientCIN.RowSource = "SELECT DISTINCT Clients.ClientCIN, Clients.ClientName " & _
"FROM CollectionVoucher INNER JOIN Clients " & _
"ON Clients.ClientCIN = CollectionVoucher.ClientCIN " & _
"WHERE CollectionVoucher.ConsignmentNo = '" & Nz(Me.cmbConsignmentNo, "") & _
"' ORDER BY Clients.ClientCIN"
End Sub

Regarding your question for the Total Cartons:
You wrote:
If you run "qryGroupCartons" (you say you have now named it "qryGroupCartonsFreightInvoice") by itself, do you see one row per logical carton? It should be lumping together all the cartons that have a numeric suffix indicating multiple products in one carton.

Yes i can see one row per logical carton.

On Reports ConsignmentNo Footer Text Box TotalCartons Control Source is:
=DCount("CartonNo","qryGroupCartonsFrieghtInvoice","ClientCIN = " & [ClientCIN] & " AND CartonNo = " & [CartonNo])

qryGroupCartonsFrieghtInvoice:
SELECT CollectionVoucher.ConsignmentNo, CollectionVoucher.ClientCIN, Sum(CollectionVoucher.WeightOfCarton) AS CartonWeight, CollectionVoucher.CartonNo, IIf(IsNumeric(Left(CollectionVoucher.CartonSuffix,1)),"",Left(CollectionVoucher.CartonSuffix,1)) AS CartonSuffix
FROM CollectionVoucher
GROUP BY CollectionVoucher.ConsignmentNo, CollectionVoucher.ClientCIN, CollectionVoucher.CartonNo, IIf(IsNumeric(Left(CollectionVoucher.CartonSuffix,1)),"",Left(CollectionVoucher.CartonSuffix,1));

Now i'm not getting correct TotalCartons

One thing which is still confusing me is how to enter CartonSuffix in Form CollectionVoucher.
Let me brief you the scenario of duplicate CartonNo

1- Same ConsignmentNo, same ClientCIN but having different DeliveryVr. Some CartonNo may be duplicate some may not.

2- Same ConsignmentNo, same ClientCIN but having multiple Products in certain Cartons.

3- Some Cartons are shifted from another Consignment some CartonNo may be duplicate, some may not be duplicate.

Khalid

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

Good catch, Robert!

John Viescas, Author
Microsoft Access 2010 Inside Out
Microsoft Access 2007 Inside Out
Microsoft Access 2003 Inside Out
Building Microsoft Access Applications
SQL Queries for Mere Mortals
http://www.viescas.com/
(Paris, France)

On Oct 30, 2014, at 6:23 PM, 'Robert Peterson' bob@... [MS_Access_Professionals] <MS_Access_Professionals@yahoogroups.com> wrote:

Should the last ClientCIN in the order by clause also have which table it is looking at.
Says ORDER BY ClientCIN
Should it be ORDER BY Clients.ClientCIN"

Bob Peterson
Alternate Finishing, Inc.
P: 978-567-9205 ext. 22
F: 978-567-8742
M: 978-333-0060
Our full range of services can be found at http://www.alternatefinishing.com/services.html .

From: MS_Access_Professionals@yahoogroups.com [mailto:MS_Access_Professionals@yahoogroups.com]
Sent: Thursday, October 30, 2014 5:58 AM
To: MS_Access_Professionals@yahoogroups.com
Subject: Re: [MS_AccessPros] How to enter different product in the same carton

Khalid-

Sorry about that - forgot that ClientCIN is in both tables. Do this:

Private Sub cmbConsignmentNo_AfterUpdate()
Me.cmbClientCIN.RowSource = "SELECT DISTINCT Clients.ClientCIN, Clients.ClientName " & _
"FROM CollectionVoucher INNER JOIN Clients " & _
"ON Clients.ClientCIN = Col lectionVoucher.ClientCIN " & _
"WHERE CollectionVoucher.ConsignmentNo = '" & Nz(Me.cmbConsignmentNo, "") & _
"' ORDER BY ClientCIN"
End Sub

__._,_.___

Posted by: khalidtanweerburrah@yahoo.com

Reply via web post

•

Reply to sender

•

Reply to group

•

Start a New Topic

•

Messages in this topic (56)

Visit Your Group

• Privacy • Unsubscribe • Terms of Use

__,_._,___

Re: [MS_AccessPros] How to enter different product in the same carton

Khalid-

After I sent my last response, I thought that might be the case. Add a filter on ClientCIN like this:

=DCount("CartonNo","qryGroupCartonsFrieghtInvoice","ConsignmentNo = '" & [ConsignmentNo] & "' AND ClientCIN = " & [ClientCIN])

John Viescas, Author

Microsoft Access 2010 Inside Out

Microsoft Access 2007 Inside Out

Microsoft Access 2003 Inside Out

Building Microsoft Access Applications

SQL Queries for Mere Mortals

http://www.viescas.com/

(Paris, France)

On Oct 31, 2014, at 2:53 PM, khalidtanweerburrah@yahoo.com [MS_Access_Professionals] <MS_Access_Professionals@yahoogroups.com> wrote:

John,

The code you gave me it counts total cartons of a particular Consignment, but i need TotalCartons for specific Consignment and specific selected ClientCIN.

I assume that ClientCIN should be in this code, i've tried myself but getting error.

ConsignmentNo is Text

ClientCIN is Numeric

Khalid

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

Khalid-

Ah! The clue is you have now told met that this in the ConsignmentNo footer. Change the Control Source to this:

=DCount("CartonNo","qryGroupCartonsFrieghtInvoice","ConsignmentNo = " & [ConsignmentNo])

I don't remember if ConsignmentNo is text or not. If it is text, you'll have to add quotes like this:

=DCount("CartonNo","qryGroupCartonsFrieghtInvoice","ConsignmentNo = '" & [ConsignmentNo] & "'")

John Viescas, Author

Microsoft Access 2010 Inside Out

Microsoft Access 2007 Inside Out

Microsoft Access 2003 Inside Out

Building Microsoft Access Applications

SQL Queries for Mere Mortals

http://www.viescas.com/

(Paris, France)

On Oct 31, 2014, at 1:13 PM, khalidtanweerburrah@... [MS_Access_Professionals] <MS_Access_Professionals@yahoogroups.com> wrote:

John,

OK, i will follow this method.

But what about getting CartonTotal on the report's ConsignmentNo Footer, which is not coming correct?

Khalid

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

Khalid-

For cases 1 and 3, use a letter suffix. For case 2, use a number. When a carton that is in case 1 or 3 has multiple products in one carton, use a letter AND a number. The default value for suffix should be blank or 0. If you use 0, start with 1 to indicate multiple products in a carton.

John Viescas, Author
Microsoft Access 2010 Inside Out
Microsoft Access 2007 Inside Out
Microsoft Access 2003 Inside Out
Building Microsoft Access Applications
SQL Queries for Mere Mortals
http://www.viescas.com/
(Paris, France)

On Oct 31, 2014, at 10:16 AM, khalidtanweerburrah@... [MS_Access_Professionals] <MS_Access_Professionals@yahoogroups.com> wrote:

John,
Yes! of course a good catch from Robert (Thanks Robert). It worked smoothly and now i can see ClientName on cmbClientCIN along with its relative ClientCIN.

Private Sub cmbConsignmentNo_AfterUpdate()
Me.cmbClientCIN.RowSource = "SELECT DISTINCT Clients.ClientCIN, Clients.ClientName " & _
"FROM CollectionVoucher INNER JOIN Clients " & _
"ON Clients.ClientCIN = CollectionVoucher.ClientCIN " & _
"WHERE CollectionVoucher.ConsignmentNo = '" & Nz(Me.cmbConsignmentNo, "") & _
"' ORDER BY Clients.ClientCIN"
End Sub

Regarding your question for the Total Cartons:
You wrote:
If you run "qryGroupCartons" (you say you have now named it "qryGroupCartonsFreightInvoice") by itself, do you see one row per logical carton? It should be lumping together all the cartons that have a numeric suffix indicating multiple products in one carton.

Yes i can see one row per logical carton.

On Reports ConsignmentNo Footer Text Box TotalCartons Control Source is:
=DCount("CartonNo","qryGroupCartonsFrieghtInvoice","ClientCIN = " & [ClientCIN] & " AND CartonNo = " & [CartonNo])

qryGroupCartonsFrieghtInvoice:
SELECT CollectionVoucher.ConsignmentNo, CollectionVoucher.ClientCIN, Sum(CollectionVoucher.WeightOfCarton) AS CartonWeight, CollectionVoucher.CartonNo, IIf(IsNumeric(Left(CollectionVoucher.CartonSuffix,1)),"",Left(CollectionVoucher.CartonSuffix,1)) AS CartonSuffix
FROM CollectionVoucher
GROUP BY CollectionVoucher.ConsignmentNo, CollectionVoucher.ClientCIN, CollectionVoucher.CartonNo, IIf(IsNumeric(Left(CollectionVoucher.CartonSuffix,1)),"",Left(CollectionVoucher.CartonSuffix,1));

Now i'm not getting correct TotalCartons

One thing which is still confusing me is how to enter CartonSuffix in Form CollectionVoucher.
Let me brief you the scenario of duplicate CartonNo

1- Same ConsignmentNo, same ClientCIN but having different DeliveryVr. Some CartonNo may be duplicate some may not.

2- Same ConsignmentNo, same ClientCIN but having multiple Products in certain Cartons.

3- Some Cartons are shifted from another Consignment some CartonNo may be duplicate, some may not be duplicate.

Khalid

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

Good catch, Robert!

John Viescas, Author
Microsoft Access 2010 Inside Out
Microsoft Access 2007 Inside Out
Microsoft Access 2003 Inside Out
Building Microsoft Access Applications
SQL Queries for Mere Mortals
http://www.viescas.com/
(Paris, France)

On Oct 30, 2014, at 6:23 PM, 'Robert Peterson' bob@... [MS_Access_Professionals] <MS_Access_Professionals@yahoogroups.com> wrote:

Should the last ClientCIN in the order by clause also have which table it is looking at.
Says ORDER BY ClientCIN
Should it be ORDER BY Clients.ClientCIN"

Bob Peterson
Alternate Finishing, Inc.
P: 978-567-9205 ext. 22
F: 978-567-8742
M: 978-333-0060
Our full range of services can be found at http://www.alternatefinishing.com/services.html .

From: MS_Access_Professionals@yahoogroups.com [mailto:MS_Access_Professionals@yahoogroups.com]
Sent: Thursday, October 30, 2014 5:58 AM
To: MS_Access_Professionals@yahoogroups.com
Subject: Re: [MS_AccessPros] How to enter different product in the same carton

Khalid-

Sorry about that - forgot that ClientCIN is in both tables. Do this:

Private Sub cmbConsignmentNo_AfterUpdate()
Me.cmbClientCIN.RowSource = "SELECT DISTINCT Clients.ClientCIN, Clients.ClientName " & _
"FROM CollectionVoucher INNER JOIN Clients " & _
"ON Clients.ClientCIN = Col lectionVoucher.ClientCIN " & _
"WHERE CollectionVoucher.ConsignmentNo = '" & Nz(Me.cmbConsignmentNo, "") & _
"' ORDER BY ClientCIN"
End Sub

John Viescas, Author
Microsoft Access 2010 Inside Out
Microsoft Access 2007 Inside Out
Microsoft Access 2003 Inside Out
Building Microsoft Access Applications
SQL Queries for Mere Mortals
http://www.viescas.com/
(Paris, France)

On Oct 30, 2014, at 10:30 AM, khalidtanweerburrah@... [MS_Access_Professionals] <MS_Access_Professionals@yahoogroups.com> wrote:

John,

I fixed the code as you gave:
Private Sub cmbConsignmentNo_AfterUpdate()
Me.cmbClientCIN.RowSource = "SELECT DISTINCT ClientCIN, ClientName " & _
"FROM CollectionVoucher INNER JOIN Clients " & _
"ON Clients.ClientCIN = CollectionVoucher.ClientCIN " & _
"WHERE CollectionV oucher.ConsignmentNo = '" & Nz(Me.cmbConsignmentNo, "") & _
"' ORDER BY ClientCIN"
End Sub

I can't see WHERE clause in the original Row Source of cmbConsignmentNo, which as below:

SELECT DISTINCT CollectionVoucher.ConsignmentNo FROM CollectionVoucher ORDER BY CollectionVoucher.ConsignmentNo DESC;

Is it correct?

Now when i open the report, on the Dialog after selecting any ConsignmentNo when i click on the cmbClientCIN the following error displays:

The specified fiedl 'ClientCIN' could refer to more then one table listed in the FROM clause of your SQL statement

Therefore cannot open the report now.

We will discuss second part of your reply after fixing above issue.

Khalid

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

You need to fix your code:

Private Sub cmbConsignmentNo_AfterUpdate()
Me.cmbClientCIN.RowSource = "SELECT DISTINCT ClientCIN, ClientName " & _
"FROM CollectionVoucher INNER JOIN Clients " & _
"ON Clients.ClientCIN = CollectionVoucher.ClientCIN " & _
"WHERE CollectionVoucher.ConsignmentNo = '" & Nz(Me.cmbConsignmentNo, "") & _
&nbs p; "' ORDER BY ClientCIN"
End Sub

You also need to fix the original Row Source of cmbConsignmentNo to match the code above without the WHERE clause.

If you run "qryGroupCartons" (you say you have now named it "qryGroupCartonsFreightInvoice") by itself, do you see one row per logical carton? It should be lumping together all the cartons that have a numeric suffix indicating multiple products in one carton.

John Viescas, Author
Microsoft Access 2010 Inside Out
Microsoft Access 2007 Inside Out
Microsoft Access 2003 Inside Out
Building Microsoft Access Applications
SQL Queries for Mere Mortals
http://www.viescas.com/
(Paris, France)

On Oct 30, 2014, at 6:00 AM, khalidtanweerburrah@... [MS_Access_Professionals] <MS_Access_Professionals@yahoogro ups.com> wrote:

John,

All suggestions you gave did'nt worked....
Well John don't tap your head with your hands. You were perfectly right giving all suggestions, but you were not aware of the mistake/error which was hidden and sorry to say that i could not have trapped it out before and told you.

Last night having exploring the Dialog "Dialog Freight Invoice Selective Consignment-Client" I checked the After Update event of cmbConsignmentNo for the previous settings it was:

(Message over 64 KB, truncated)

__._,_.___

Posted by: John Viescas <johnv@msn.com>

Reply via web post

•

Reply to sender

•

Reply to group

•

Start a New Topic

•

Messages in this topic (55)

Visit Your Group

• Privacy • Unsubscribe • Terms of Use

__,_._,___