[belajar-access] No seri kembali ke "0001" kalau ganti bulan

----- Original Message -----
From: "Murid Belajar Access" <murid.belajaraccess@yahoo.co.id>
To: <belajar-access@yahoogroups.com>
Sent: Monday, June 29, 2009 2:47 PM
Subject: Re: [belajar-access] No seri kembali ke "0001" kalau ganti bulan

bila menginginkan tanpa buat query terlebih dan hanya menggunakan satu field
yaitu NoURUT
bisa gunakan cara sbb:

strFormat = "SKBM-" & Format(Date, "yymm-")

'nilai max dari 4 digit paling kanan dari kolom NoUrut + 1
strMax1 = Format(CInt(Right(Nz(DMax("[NoUrut]", "tblTest", "[NoUrut]
Like 'SKBM-" & Format(Date, "yymm") & "*'"), 0), 4)) + 1, "0000")

kalo dari form current

NoUrut.DefaultValue = "'" & strFormat & strMax1 & "'"

kalo dari kontrol lain.

Nourut.value = strFormat & strMax1

mudah-mudahan bermanfaat,

salam, MBA

[belajar-access] Tgl terakhir order

----- Original Message -----
From: "Murid Belajar Access" <murid.belajaraccess@yahoo.co.id>
To: <belajar-access@yahoogroups.com>
Sent: Thursday, June 25, 2009 6:32 PM
Subject: Re: [belajar-access] Tgl terakhir order

coba gunakan sbb:

SELECT tbl_order.idcustomer, Max(tbl_order.tglorder) AS TglOrder
FROM tbl_order
GROUP BY tbl_order.idcustomer;

salam,
MBA

[belajar-access] Nama Server dan Database di Form dan Proteksi Form agar tidak bisa di import.

----- Original Message -----
From: "Murid Belajar Access" <murid.belajaraccess@yahoo.co.id>
To: <belajar-access@yahoogroups.com>
Sent: Sunday, June 21, 2009 2:38 PM
Subject: Re: [belajar-access] Nama Server dan Database di Form dan Proteksi
Form agar tidak bisa di import.

Pak Galih,
saya coba jawab ya.

1. gunakan fungsi sbb:

Function fGetServerDB() As String
With CurrentProject.AccessConnection
fGetServerDB = .Properties("Data Source").Value & " / " &
.Properties("Initial Catalog").Value
End With
End Function

2. Compile ADP anda menjadi ADE.

salam,
Murid Belajar Access

----- Original Message -----
From: GALIH PERSADHA
To: belajar-access@yahoogroups.com
Sent: Saturday, June 20, 2009 15:54
Subject: [belajar-access] cara menampilkan nama server dan nama database di
form

bagaimana cara menampilkan nama server dan nama database yang kita
gunakan pada project access di form

mohon pencerahannya

[belajar-access] Perhitungan Stock

----- Original Message -----
From: "Murid Belajar Access" <murid.belajaraccess@yahoo.co.id>
To: <belajar-access@yahoogroups.com>
Sent: Thursday, June 18, 2009 1:46 AM
Subject: Re: [belajar-access] Perhitungan Stock

Ada ide sbb, asumsinya nama tabelnya adalah datacontoh:

1. Buat query union, misalnya: query10
2. query union tsb di crosstab, misalnya: query10_Crosstab

query10:

SELECT 1 AS [No], '1-StockAwal' as
Tag,nz(Sum([datacontoh].[masuk]),0)-nz(Sum([datacontoh].[keluar]),0) AS
Jumlah FROM DataContoh WHERE (((DataContoh.[No])<1));
UNION SELECT 1 AS [No],'2-Masuk' as Tag, nz(Sum([datacontoh].[masuk]),0)
AS Jumlah FROM DataContoh WHERE (((DataContoh.[No])=1));
UNION SELECT 1 AS [No],'3-Keluar' as Tag,nz(Sum([datacontoh].[keluar]),0)
AS Jumlah FROM DataContoh WHERE (((DataContoh.[No])=1));
UNION SELECT 1 AS [No], '4-Stock' as
Tag,nz(Sum([datacontoh].[masuk]),0)-nz(Sum([datacontoh].[keluar]),0) AS
Jumlah FROM DataContoh WHERE (((DataContoh.[No])<=1));
UNION
SELECT 2 AS [No], '1-StockAwal' as
Tag,nz(Sum([datacontoh].[masuk]),0)-nz(Sum([datacontoh].[keluar]),0) AS
Jumlah FROM DataContoh WHERE (((DataContoh.[No])<2));
UNION SELECT 2 AS [No],'2-Masuk' as Tag, nz(Sum([datacontoh].[masuk]),0)
AS Jumlah FROM DataContoh WHERE (((DataContoh.[No])=2));
UNION SELECT 2 AS [No],'3-Keluar' as Tag,nz(Sum([datacontoh].[keluar]),0)
AS Jumlah FROM DataContoh WHERE (((DataContoh.[No])=2));
UNION SELECT 2 AS [No], '4-Stock' as
Tag,nz(Sum([datacontoh].[masuk]),0)-nz(Sum([datacontoh].[keluar]),0) AS
Jumlah FROM DataContoh WHERE (((DataContoh.[No])<=2));
UNION
SELECT 3 AS [No], '1-StockAwal' as
Tag,nz(Sum([datacontoh].[masuk]),0)-nz(Sum([datacontoh].[keluar]),0) AS
Jumlah FROM DataContoh WHERE (((DataContoh.[No])<3));
UNION SELECT 3 AS [No],'2-Masuk' as Tag, nz(Sum([datacontoh].[masuk]),0)
AS Jumlah FROM DataContoh WHERE (((DataContoh.[No])=3));
UNION SELECT 3 AS [No],'3-Keluar' as Tag,nz(Sum([datacontoh].[keluar]),0)
AS Jumlah FROM DataContoh WHERE (((DataContoh.[No])=3));
UNION SELECT 3 AS [No], '4-Stock' as
Tag,nz(Sum([datacontoh].[masuk]),0)-nz(Sum([datacontoh].[keluar]),0) AS
Jumlah FROM DataContoh WHERE (((DataContoh.[No])<=3));
UNION
SELECT 4 AS [No], '1-StockAwal' as
Tag,nz(Sum([datacontoh].[masuk]),0)-nz(Sum([datacontoh].[keluar]),0) AS
Jumlah FROM DataContoh WHERE (((DataContoh.[No])<4));
UNION SELECT 4 AS [No],'2-Masuk' as Tag, nz(Sum([datacontoh].[masuk]),0)
AS Jumlah FROM DataContoh WHERE (((DataContoh.[No])=4));
UNION SELECT 4 AS [No],'3-Keluar' as Tag,nz(Sum([datacontoh].[keluar]),0)
AS Jumlah FROM DataContoh WHERE (((DataContoh.[No])=4));
UNION SELECT 4 AS [No], '4-Stock' as
Tag,nz(Sum([datacontoh].[masuk]),0)-nz(Sum([datacontoh].[keluar]),0) AS
Jumlah FROM DataContoh WHERE (((DataContoh.[No])<=4));
UNION
SELECT 5 AS [No], '1-StockAwal' as
Tag,nz(Sum([datacontoh].[masuk]),0)-nz(Sum([datacontoh].[keluar]),0) AS
Jumlah FROM DataContoh WHERE (((DataContoh.[No])<5));
UNION SELECT 5 AS [No],'2-Masuk' as Tag, nz(Sum([datacontoh].[masuk]),0)
AS Jumlah FROM DataContoh WHERE (((DataContoh.[No])=5));
UNION SELECT 5 AS [No],'3-Keluar' as Tag,nz(Sum([datacontoh].[keluar]),0)
AS Jumlah FROM DataContoh WHERE (((DataContoh.[No])=5));
UNION SELECT 5 AS [No], '4-Stock' as
Tag,nz(Sum([datacontoh].[masuk]),0)-nz(Sum([datacontoh].[keluar]),0) AS
Jumlah FROM DataContoh WHERE (((DataContoh.[No])<=5));
UNION
SELECT 6 AS [No], '1-StockAwal' as
Tag,nz(Sum([datacontoh].[masuk]),0)-nz(Sum([datacontoh].[keluar]),0) AS
Jumlah FROM DataContoh WHERE (((DataContoh.[No])<6));
UNION SELECT 6 AS [No],'2-Masuk' as Tag, nz(Sum([datacontoh].[masuk]),0)
AS Jumlah FROM DataContoh WHERE (((DataContoh.[No])=6));
UNION SELECT 6 AS [No],'3-Keluar' as Tag,nz(Sum([datacontoh].[keluar]),0)
AS Jumlah FROM DataContoh WHERE (((DataContoh.[No])=6));
UNION SELECT 6 AS [No], '4-Stock' as
Tag,nz(Sum([datacontoh].[masuk]),0)-nz(Sum([datacontoh].[keluar]),0) AS
Jumlah FROM DataContoh WHERE (((DataContoh.[No])<=6));
UNION
SELECT 7 AS [No], '1-StockAwal' as
Tag,nz(Sum([datacontoh].[masuk]),0)-nz(Sum([datacontoh].[keluar]),0) AS
Jumlah FROM DataContoh WHERE (((DataContoh.[No])<7));
UNION SELECT 7 AS [No],'2-Masuk' as Tag, nz(Sum([datacontoh].[masuk]),0)
AS Jumlah FROM DataContoh WHERE (((DataContoh.[No])=7));
UNION SELECT 7 AS [No],'3-Keluar' as Tag,nz(Sum([datacontoh].[keluar]),0)
AS Jumlah FROM DataContoh WHERE (((DataContoh.[No])=7));
UNION SELECT 7 AS [No], '4-Stock' as
Tag,nz(Sum([datacontoh].[masuk]),0)-nz(Sum([datacontoh].[keluar]),0) AS
Jumlah FROM DataContoh WHERE (((DataContoh.[No])<=7));
UNION
SELECT 8 AS [No], '1-StockAwal' as
Tag,nz(Sum([datacontoh].[masuk]),0)-nz(Sum([datacontoh].[keluar]),0) AS
Jumlah FROM DataContoh WHERE (((DataContoh.[No])<8));
UNION SELECT 8 AS [No],'2-Masuk' as Tag, nz(Sum([datacontoh].[masuk]),0)
AS Jumlah FROM DataContoh WHERE (((DataContoh.[No])=8));
UNION SELECT 8 AS [No],'3-Keluar' as Tag,nz(Sum([datacontoh].[keluar]),0)
AS Jumlah FROM DataContoh WHERE (((DataContoh.[No])=8));
UNION SELECT 8 AS [No], '4-Stock' as
Tag,nz(Sum([datacontoh].[masuk]),0)-nz(Sum([datacontoh].[keluar]),0) AS
Jumlah FROM DataContoh WHERE (((DataContoh.[No])<=8));
UNION
SELECT 9 AS [No], '1-StockAwal' as
Tag,nz(Sum([datacontoh].[masuk]),0)-nz(Sum([datacontoh].[keluar]),0) AS
Jumlah FROM DataContoh WHERE (((DataContoh.[No])<9));
UNION SELECT 9 AS [No],'2-Masuk' as Tag, nz(Sum([datacontoh].[masuk]),0)
AS Jumlah FROM DataContoh WHERE (((DataContoh.[No])=9));
UNION SELECT 9 AS [No],'3-Keluar' as Tag,nz(Sum([datacontoh].[keluar]),0)
AS Jumlah FROM DataContoh WHERE (((DataContoh.[No])=9));
UNION SELECT 9 AS [No], '4-Stock' as
Tag,nz(Sum([datacontoh].[masuk]),0)-nz(Sum([datacontoh].[keluar]),0) AS
Jumlah FROM DataContoh WHERE (((DataContoh.[No])<=9));
UNION
SELECT 10 AS [No], '1-StockAwal' as
Tag,nz(Sum([datacontoh].[masuk]),0)-nz(Sum([datacontoh].[keluar]),0) AS
Jumlah FROM DataContoh WHERE (((DataContoh.[No])<10));
UNION SELECT 10 AS [No],'2-Masuk' as Tag, nz(Sum([datacontoh].[masuk]),0)
AS Jumlah FROM DataContoh WHERE (((DataContoh.[No])=10));
UNION SELECT 10 AS [No],'3-Keluar' as Tag,nz(Sum([datacontoh].[keluar]),0)
AS Jumlah FROM DataContoh WHERE (((DataContoh.[No])=10));
UNION SELECT 10 AS [No], '4-Stock' as
Tag,nz(Sum([datacontoh].[masuk]),0)-nz(Sum([datacontoh].[keluar]),0) AS
Jumlah FROM DataContoh WHERE (((DataContoh.[No])<=10));

query10_Crosstab:

TRANSFORM Sum(Query10.Jumlah) AS Jumlah
SELECT Query10.[No]
FROM Query10
GROUP BY Query10.[No]
PIVOT Query10.Tag;

salam kompak, hangat dan jabat erat,

MBA

[belajar-access] Re: form requery

----- Original Message -----
From: "Murid Belajar Access" <murid.belajaraccess@yahoo.co.id>
To: <belajar-access@yahoogroups.com>
Sent: Monday, June 15, 2009 12:43 AM
Subject: Re: [belajar-access] Re: form requery

> Mungkin seperti ini.
>
> di form transaksi
> Private Sub ProductID_KeyUp(KeyCode As Integer, Shift As Integer)
> If KeyCode = 13 Then
> DoCmd.OpenForm "Product"
> End If
> End Sub
>
> di form barang
> Private Sub ListProduct_AfterUpdate()
> Forms!Orders!OrderDetails!ProductID.value = ListProduct.value
> End Sub
>
> Salam Hangat dan Jabat Erat,
> Murid Belajar Access

[belajar-access] cari data yang isinya memo di form

----- Original Message -----
From: "Rusmanto" <rusmanto@tokatindung.com>
To: <belajar-access@yahoogroups.com>
Sent: Friday, June 12, 2009 3:39 PM
Subject: Re: [belajar-access] cari data yang isinya memo di form

>
>
> Alhamdulillah akhirnya bisa Pak, terimakasih atas bantuannya.
> Mudah2an ilmu ini berguna buat saya dan semua yang membacanya,
> dan buat yang memberinya menjadikan pahala yang sangat tak terhingga.
>
> wassalam
> Rusman
>
>
>
> Murid Belajar Access wrote:
>
> Pak Rusmanto,
>
> bila kursor masih ada di cbxTitle / fokus, gunakan sbb:
>
> Like (IIf([Forms]![frm_DocSearch]![cbxTitle].[Text] Is Null,"*","*" &
> [Forms]![frm_DocSearch]![cbxTitle].[Text])) & "*"
>
>
> jadi pake tambahan .[Text] pak.
>
> Salam Hangat dan Jabat Erat,
> Murid Belajar Access
> ----- Original Message -----
> From: Rusmanto
> To: belajar-access@yahoogroups..com
> Sent: Friday, June 12, 2009 09:25
> Subject: Re: [belajar-access] cari data yang isinya memo di form
>
>
> Betul sekali Pak kalau di query biasa bisa saya gunakan : Like
*Kontrak dengan PLN*
> Muncul datanya sesuai dengan criteria, tapi begitu saya terapkan di
Form yang menggunakan txtbox,
> datanya muncul semuanya, saya sudah coba cara yang Pak Wiyono berikan
:
>
> Like (IIf([Forms]![frm_DocSearch]![cbxTitle] Is Null,"*","*" &
> [Forms]![frm_DocSearch]![cbxTitle])) & "*"
>
> apa ada yang kurang yah ?
>
> wassalam
> Rusman
>
>
> Murid Belajar Access wrote:
> Pak Rusmanto,
>
> Kalau untuk memilih memo / teks yang 'mengandung' kata-kata
tertentu, jangan
> lupa tanda * sebelum dan sesudahnya.
> kriteria di query biasanya dituliskan misalnya : Like *Kontrak
dengan PLN*
>
> silahkan dicoba sintaks berikut.
>
> Like (IIf([Forms]![frm_DocSearch]![cbxTitle] Is Null,"*","*" &
> [Forms]![frm_DocSearch]![cbxTitle])) & "*"
>
> mudah2-an bisa membantu.
>
> Salam Hangat dan Jabat Erat,
> Murid Belajar Access
> ----- Original Message -----
> From: Rusmanto
> To: belajar-access@yahoogroups.com
> Sent: Friday, June 12, 2009 07:37
> Subject: [belajar-access] cari data yang isinya memo di form
>
> Assalamuallaikum,
>
> Temen2 saya buat query pake text box di form untuk mencari isi Memo
yang di
> query seperti berikut :
>
> Like (IIf([Forms]![frm_DocSearch]![cbxTitle] Is
> Null,"*",[Forms]![frm_DocSearch]![cbxTitle]))
>
> tapi ketika saya ketik menurut kriteria isi Memo gak bisa berfungsi,
kenapa
> yah ? ada yang salah kah?
> kalau untuk combo box sudah jalan, tapi yang isi nya memo tidak mau.
>
> contoh : saya mau cari yang Isi memonya ada kata " Kontrak dengan
PLN"
> mudah2an ada temen2 yang mau memberikan pencerahan.
>
> wassalam
> Rusman
>