Minggu, 31 Juli 2016

RE: [belajar-access] Query Union Error ketika tabel terkoneksi ke MySql

 

Begini...,

1. Buat modul koneksi dan nama komputer, karena akan di pakai di hampir semua form. Nama komputer digunakan untuk nama tabel biar unik antar masing-masing user

Ini scriptnya:

Option Explicit
Public conn As New ADODB.Connection 'deklarasi koneksi

Private Const MAX_COMPUTERNAME As Long = 15
Private Declare Function GetComputerName Lib "kernel32" _
Alias "GetComputerNameA" _
(ByVal lpBuffer As String, _
nSize As Long) As Long

Private Function TrimNull(item As String)

'return string before the terminating null
Dim pos As Integer

pos = InStr(item, Chr$(0))

If pos Then
TrimNull = Left$(item, pos - 1)
Else
TrimNull = item
End If

End Function

Function KOM()
'untuk membuat tabel berdasarkan nama komputer

Dim tas As String

'pre-load the text boxes with
'the local computer name for testing
tas = Space$(MAX_COMPUTERNAME + 1)
Call GetComputerName(tas, Len(tas))

KOM = TrimNull(tas)
If KOM Like "*-*" Then
KOM = Replace(KOM, "-", "_")
End If
End Function

Public Function connToDB(ServerName As String, _
UserName As String, userPass As Variant, _
dbPath As String, dbName As String)

Dim strCon As String

On Error GoTo errHandle
'sesuaikan driver mysqlnya, ada yang pakai 3.51
strCon = "DRIVER={MySQL ODBC 5.1 Driver};SERVER=" _
& ServerName & ";DATABASE=" & dbName & ";" & _
"UID=" & UserName & ";PWD=" & userPass & ";OPTION=16426"

Set conn = New ADODB.Connection
conn.Open strCon

Exit Function

errHandle:
MsgBox "SERVER SEDANG TIDAK AKTIF", , "NON AKTIF"
conn.Close
Set conn = Nothing
End Function

Public Function EscapeQuotes(s) As String

If s = "" Then
EscapeQuotes = ""
ElseIf Left(s, 1) = "'" Then
EscapeQuotes = "''" & EscapeQuotes(Mid(s, 2))
Else

EscapeQuotes = Left(s, 1) & EscapeQuotes(Mid(s, 2))
End If

End Function

Function hp_tb(n_tb)
Dim rbs As Recordset
Dim db As DAO.Database
'untuk menghapus tabel Access temporer
Set rbs = CurrentDb.OpenRecordset("SELECT MSysObjects.Name" _
& " FROM MSysObjects WHERE MSysObjects.Type= 1 And MSysObjects.Flags=0" _
& " and MSysObjects.Name='" & n_tb & "'") 'melihat nama tabel n_tb
If Not rbs.EOF Then 'bila ada record
Set db = CurrentDb 'menetapkan bahwa db adalah database ini
db.TableDefs.Delete n_tb 'hapus tabel
'menghilangkan dari memory komputer
db.Close
Set db = Nothing
End If
'menghilangkan dari memory komputer
rbs.Close
Set rbs = Nothing

End Function

Function KONEKSI()
'connToDB txtServer, txtUID, txtPWD, 3306, txtDb
connToDB "localhost", "root", "admin", 3306, "Nama_database"
End Function

2. Kita mulai bermain di form. Misal nama comboBoxnya contoh.Propertinya:
Control Source ==> kosongkan
Row Source Type ==> Table/query
Row Source ==> kosongkan
Coloum Count ==> 1
Coloum Head ==> No

3. Misal, event yang kita manfaatkan adalah saat, pertama kali loading.

Private Sub Form_Load()
Dim tb as Variant
Dim db As Database
Dim rsp As ADODB.Recordset 'deklarasi rsp sebagai ADODB recordset

KONEKSI 'melakukan koneksi
If conn.State <> 0 Then 'bila sudah konek
contoh.RowSource = "" 'menghilangkan recordsource dulu biar tidak error
contoh.Visible = False 'tidak ditampilkan dulu
tb = "temp_" & KOM 'nama tabel temporer yang akan kita buat
hp_tb (tb) 'hapus tabel temporer dulu bila sudah ada
'buat tabel temporer asumsi nama field=field1
'size 255
DoCmd.RunSQL "CREATE TABLE " _
& tb & " (field1 Text(255));"
'melihat data di tabel mysql
Set rsp = conn.Execute("SELECT left([Week_Subc],5)" _
& " From TblCultureProduction UNION" _
& " SELECT left([Week_Subc],5) From" _
& " TblCultureIncoming")
If Not rsp.EOF Then
DoCmd.Hourglass True
Set db = CurrentDb 'deklarasi db adalah currentdb
'mengisi record dengan --All--
db.Execute ("Insert into " _
& tb & " Values ('--All---')")
Do While Not rsp.EOF 'jika ada data, lakukan berulang sampai akhir
If rsp.Fields(0) <> "" Then 'jika record berisi
'isi tabel temporer dengan data Mysql
db.Execute ("Insert into " _
& tb & " Values ('" _
& EscapeQuotes(rsp.Fields(0)) _
& "')")
End If
rsp.MoveNext
Loop
db.Close
Set db = Nothing
DoCmd.Hourglass False
End If
rsp.Close
Set rsp = Nothing
contoh.RowSource = tb 'letakkan recordsource combo contoh
contoh.DefaultValue = """--All--""" 'menjadikan --All-- sebagai devault value
contoh.Visible = Trye 'Tampilkan
contoh.Requery 'refresh
Else
MsgBox "gagal koneksi"
End If
conn.Close 'menutup koneksi
Set conn = Nothing 'menghilangkan koneksi dari memori

End Sub

Semoga bisa membantu dan memberi semangat.

Hariyanto (Surabaya)

__._,_.___

Posted by: hari yanto <har_i20002000@yahoo.com>
Reply via web post Reply to sender Reply to group Start a New Topic Messages in this topic (8)

Save time and get your email on the go with the Yahoo Mail app
Get the beautifully designed, lighting fast, and easy-to-use Yahoo Mail today. Now you can access all your inboxes (Gmail, Outlook, AOL and more) in one place. Never delete an email again with 1000GB of free cloud storage.

SPAM IS PROHIBITED

.

__,_._,___

RE: [belajar-access] Query Union Error ketika tabel terkoneksi ke MySql

 

Terimakasih mas Haryanto atas responsya,

Cara pertaman sudah saya lakukan dan bisa tetapi masih ada masalah ketika kita sudah memilih week maka – All – tidak muncul lagi dan ketika saya ketik manual di comboboxnya terjadi arror karena dianggap pilihan – All – tidak ada di list, jadi harus saya tutup dulu baru muncul lagi.

 

Cara kedua belum saya lakukan karena memang saya belum bisa, saya masih dalam tahap belajar soalnya mas.

Sudi kiranya mas haryanto mengajari saya cara yg kedua (koneksi tertutup), termasuk cara pembuatan tabel temporernya mas.

 

Best Regards

CW

 

From: belajar-access@yahoogroups.com [mailto:belajar-access@yahoogroups.com]
Sent: 29 Juli 2016 20:13
To: belajar-access@yahoogroups.com
Subject: RE: [belajar-access] Query Union Error ketika tabel terkoneksi ke MySql

 

 

Sampeyan benar. Bila menggunakan link table, union lebih dari 2, akan muncul error.

Lebih baik di buat 2 union:
SELECT left([Week_Subc],5) From TblCultureProduction;
UNION
SELECT left([Week_Subc],5) From TblCultureIncoming

Terus, --All-- sebagai default value.

Atau, bisa juga model koneksi tutup. Bukan link tabel. Kalau yang ini, bisa digunakan walau lebih 2 Union.
Hasilnya, diletakkan di tabel temporer. Lalu, tabel temporer itu dijadikan recordsource combobox. (Dulu, saya sering menggunakan cara seperti ini).

Semoga bisa membantu dan memberi semangat.

Hariyanto (Surabaya)

--------------------------------------------
On Fri, 29/7/16, 'Cecep' cepwahyu@gmail.com [belajar-access] <belajar-access@yahoogroups.com> wrote:

Subject: RE: [belajar-access] Query Union Error ketika tabel terkoneksi ke MySql [1 Attachment]
To: belajar-access@yahoogroups.com
Date: Friday, 29 July, 2016, 2:24 PM


 



 


   
     

             
        [Attachment(s) from 'Cecep'
cepwahyu@gmail.com [belajar-access] included below]
       
     
      Setelah saya utak atik masih
mentok mas,Telampir contoh databasenya mas.
Barangkali ada alternatif lain agar bisa tetap jalan dengan
BE MySql.  RegardsCW    From:
belajar-access@yahoogroups.com
[mailto:belajar-access@yahoogroups.com]
Sent: 29 Juli 2016 11:08
To: belajar-access@yahoogroups.com
Subject: RE: [belajar-access] Query
Union Error ketika tabel terkoneksi ke
MySql    Prinsip UNION, menggabungkan
beberapa tabel menjadi satu query. Dia akan berjalan, bila
jumlah field yang dipilih sama antar semua tabel.

Select field1 from tabel1
union
select field21 from
tabel2
union
select field31
from tabel3

Maka, bila
field tabel1 akan diambil data lebih banyak, bisa diakali
begini

Select
field1,field2,field3,field4 from tabel1
union
select
field21,field21,field21,field21 from tabel2
union
select
field31,field31,field31,field31 from tabel3

Atau, bisa juga dibuat SQL per
satuan.
Select * from tabel1
select field21 from tabel2
select field31 from tabel3

hasilnya lalu dioleh sesuai kebutuhan (misal
disisir dengan nama tertentu).

Semoga bisa membantu dan memberi semangat.

Hariyanto (Surabaya)

--------------------------------------------
On Fri, 29/7/16, 'Cecep' cepwahyu@gmail.com
[belajar-access] <belajar-access@yahoogroups.com>
wrote:

Subject: RE:
[belajar-access] Query Union Error ketika tabel terkoneksi
ke MySql
To: belajar-access@yahoogroups.com
Date: Friday, 29 July, 2016, 10:40 AM


 









Mas Hariyanto terimakasih atas
responsnya,Combobox ini sy gunakan untuk
memfilter report dimana di comboboxnya akan
muncul pilihan
– All – supaya report
tampil semuanya dan pilihan
week_Subc supaya
report yg tampil sesuai week yg diinginkan,
nah kata – All – ini bukan dari sebuah
field tapi
langsung dibuat di
Querynya.Setelah saya utak atik masalah
muncul jika ada tambahan UNION SELECT yg kedua
(UNION
SELECT left([Week_Subc],5) From
TblCultureIncoming), jadi kalo bagian ini saya
hilangkan tidak terjadi error, padahal sy juga
memerlukan
data Week_Subc dari tabel ini.
 Apakah ini memang tidak
memungkinkan atau
bagaimana mas?  RegardCW  From:
belajar-access@yahoogroups.com
[mailto:belajar-access@yahoogroups.com]

Sent: 28 Juli 2016 20:13
To: belajar-access@yahoogroups.com
Subject: Re: [belajar-access] Query
Union Error ketika tabel terkoneksi ke
MySql    cepwahyu...,

Sepertinya ada masalah di
SQL-nya. Karena syarat penggunaan SQL, jumlah
fieldnya harus
sama.

Coba ganti seperti
ini:

SELECT
left(nama_field,5) From TblCultureProduction

UNION
SELECT
left([Week_Subc],5) From TblCultureProduction

UNION SELECT left([Week_Subc],5) From
TblCultureIncoming;

Semoga
bisa membantu dan
memberi semangat.

Hariyanto
(Surabaya)

--------------------------------------------
On Thu, 28/7/16, 'Cecep' cepwahyu@gmail.com
[belajar-access] <belajar-access@yahoogroups.com>
wrote:

Subject:
[belajar-access] Query
Union Error ketika tabel terkoneksi
ke
MySql

To: belajar-access@yahoogroups.com
Date: Thursday, 28 July, 2016, 4:00 PM


 









Dear access mania,  Saat ini sy sedang
melakukan
migrasi BE access ke
MySql dan
sudah berhasil tapi ada satu
masalah ketika
saya mencari
data pada combobox padahal
ketika masih pake
BE access tidak ada
masalah.Row Source pada
combobox
sbb:SELECT
"--
All -- " From
TblCultureProduction  UNION SELECT
left([Week_Subc],5) From
TblCultureProduction;
UNION SELECT
left([Week_Subc],5) From
TblCultureIncoming;  Jadi saya hanya akan
mengambil 5 karakter sebelah kiri saja.Nah
ketika BE sudah link ke
MySql
tiba2 ketika
combobox tadi sy klik muncul
error
sbb:
 Mohon
pencerahanya kira2 apa
masalahnya
ya?
 Best RegardsCW

__._,_.___

Posted by: "Cecep" <cepwahyu@gmail.com>
Reply via web post Reply to sender Reply to group Start a New Topic Messages in this topic (2)

Save time and get your email on the go with the Yahoo Mail app
Get the beautifully designed, lighting fast, and easy-to-use Yahoo Mail today. Now you can access all your inboxes (Gmail, Outlook, AOL and more) in one place. Never delete an email again with 1000GB of free cloud storage.

SPAM IS PROHIBITED

.

__,_._,___

Re: [MS_AccessPros] Replace field if another exists.

 

Yes.  0 is not the same as Null.  You need:

IIf(IsNull([PersonID]) Or ([PersonID] = 0), ...

John

Sent from my iPhone

On Jul 31, 2016, at 5:39 PM, Art Lorenzini dbalorenzini@yahoo.com [MS_Access_Professionals] <MS_Access_Professionals@yahoogroups.com> wrote:

I guess I was not quite done. When I run the query it pulls in the person name from the tbl_persons table, but if there is no personId (PersonID = 0), then it should be pulling the name from the tbl_Citation but for some reason it is not seeing even though there is an entry there.

SELECT tbl_citations.CitationID, tbl_citations.FirstName, tbl_citations.MiddleName, tbl_citations.LastName, tbl_citations.IsLicensed, tbl_citations.Person_ID, tbl_citations.Fee, tbl_citations.ViolationCodeID, tbl_citations.WardenID, tbl_citations.CourtDateTime, tbl_citations.ReasonForViolation, tbl_citations.Priority, tbl_citations.OpenDate, tbl_citations.Close, tbl_citations.Comments, tbl_citations.Created_By, tbl_citations.Created_On, tbl_citations.Edited_By, tbl_citations.Edited_On, IIf(IsNull([Person_ID]),[FirstName] & " " & [LastName],DLookUp("cPersonName","qryAdd_People","Person_ID = " & [Person_ID])) AS PersonName, tbl_lkpViolationCode.Code, DLookUp("WardenName","qryLookup_WardenName","WardenID = " & [WardenID]) AS WardenName
FROM tbl_citations LEFT JOIN tbl_lkpViolationCode ON tbl_citations.ViolationCodeID = tbl_lkpViolationCode.ViolationCodeID;

Is it the case that PersonID contains a 0 and is not truly NUll? 

With Warm Regards,
 
Arthur D. Lorenzini
IT System Manager
Cheyenne River Housing Authority
Wk.(605)964-4265  Ext. 130
Fax (605)964-1070

"Anyone who claimed that old age had brought them patience was either lying or senile."  







From: "John Viescas JohnV@msn.com [MS_Access_Professionals]" <MS_Access_Professionals@yahoogroups.com>
To: MS_Access_Professionals@yahoogroups.com
Sent: Friday, July 29, 2016 2:54 PM
Subject: Re: [MS_AccessPros] Replace field if another exists.

 
Art-

Sorry, I didn't notice the extra ( in your original syntax.  Try:

IIf(IsNull(PersonID]), [FirstName] & " " & [LastName], DLookup("LastName", "tbl_Persons", "PersonID = " & [PersonID]))

John Viescas, author
Effective SQL
SQL Queries for Mere Mortals
Microsoft Office Access 2010 Inside Out
Microsoft Office Access 2007 Inside Out
Building Access Applications

On Jul 29, 2016, at 15:00, Art Lorenzini dbalorenzini@yahoo.com [MS_Access_Professionals] <MS_Access_Professionals@yahoogroups.com> wrote:

THis is what I have got so far:

SELECT tbl_citations.CitationID, tbl_citations.FirstName, tbl_citations.MiddleName, tbl_citations.LastName, tbl_citations.IsLicensed, tbl_citations.Person_ID, tbl_citations.Fee, tbl_citations.ViolationCodeID, tbl_citations.OfficerID, tbl_citations.CourtDateTime, tbl_citations.ReasonForViolation, tbl_citations.Priority, tbl_citations.OpenDate, tbl_citations.Close, tbl_citations.Comments, tbl_citations.Created_By, tbl_citations.Created_On, tbl_citations.Edited_By, tbl_citations.Edited_On, [FirstName] & " " & [LastName] AS FullName, IIf(IsNull((PersonID]), [FirstName] & " " & [LastName], DLookup("LastName", "tbl_Persons", "PersonID = " & [PersonID]))
FROM tbl_citations;

I am receiving a syntax error in the following line:

IIf(IsNull((PersonID]), [FirstName] & " " & [LastName], DLookup("LastName", "tbl_Persons", "PersonID = " & [PersonID]))
FROM tbl_citations;
 

With Warm Regards,
 
Arthur D. Lorenzini
IT System Manager
Cheyenne River Housing Authority
Wk.(605)964-4265  Ext. 130
Fax (605)964-1070

"Anyone who claimed that old age had brought them patience was either lying or senile."  







From: "John Viescas JohnV@msn.com [MS_Access_Professionals]" <MS_Access_Professionals@yahoogroups.com>
To: MS_Access_Professionals@yahoogroups.com
Sent: Friday, July 29, 2016 12:39 PM
Subject: Re: [MS_AccessPros] Replace field if another exists.

 
Art-

If this expression is in the underlying query, then don't refer to the form control.  Do this:

IIf(IsNull((PersonID]), [FirstName] & " " & [LastName], DLookup("PersonName", "tblPerson", "PersonID = " & [PersonID]))

John Viescas, author
Effective SQL
SQL Queries for Mere Mortals
Microsoft Office Access 2010 Inside Out
Microsoft Office Access 2007 Inside Out
Building Access Applications

On Jul 29, 2016, at 14:15, Art Lorenzini dbalorenzini@yahoo.com [MS_Access_Professionals] <MS_Access_Professionals@yahoogroups.com> wrote:

OK. I knew I was missing something. THis query will be used on a continuous form where the combobox does not exist.

IIf(IsNull((PersonID]), [FirstName] & " " & [LastName], DLookup("PersonName", "tblPerson", "PersonID = " & [Forms]![Myform]![cmbPersonID]))
 
Something like this maybe.

Here is the structure of the tblCitation that this query is derived from:

SELECT tbl_citations.CitationID, tbl_citations.FirstName, tbl_citations.MiddleName, tbl_citations.LastName, tbl_citations.IsLicensed, tbl_citations.Person_ID, tbl_citations.Fee, tbl_citations.ViolationCodeID, tbl_citations.OfficerID, tbl_citations.CourtDateTime, tbl_citations.ReasonForViolation, tbl_citations.Priority, tbl_citations.OpenDate, tbl_citations.Close, tbl_citations.Comments, tbl_citations.Created_By, tbl_citations.Created_On, tbl_citations.Edited_By, tbl_citations.Edited_On
FROM tbl_citations;

With Warm Regards,
 
Arthur D. Lorenzini
IT System Manager
Cheyenne River Housing Authority
Wk.(605)964-4265  Ext. 130
Fax (605)964-1070

"Anyone who claimed that old age had brought them patience was either lying or senile."  







From: "John Viescas JohnV@msn.com [MS_Access_Professionals]" <MS_Access_Professionals@yahoogroups.com>
To: MS_Access_Professionals@yahoogroups.com
Sent: Thursday, July 28, 2016 6:52 PM
Subject: Re: [MS_AccessPros] Replace field if another exists.

 
Art-

Yup.  But it sounds like you need something like:

IIf(IsNull([Forms]![Myform]![cmbPersonID]), [FirstName] & " " & [LastName], DLookup("PersonName", "OtherTable", "PersonID = " & [Forms]![Myform]![cmbPersonID]))

John Viescas, author
Effective SQL
SQL Queries for Mere Mortals
Microsoft Office Access 2010 Inside Out
Microsoft Office Access 2007 Inside Out
Building Access Applications

On Jul 28, 2016, at 18:23, Art Lorenzini dbalorenzini@yahoo.com [MS_Access_Professionals] <MS_Access_Professionals@yahoogroups.com> wrote:

Here is example of what you are talking about I have in another application:

SELECT [Learning Center Non Client Activities].NonClientActivityID, [Learning Center Non Client Activities].LearningCenterID, [Learning Center Non Client Activities].ActivityDate, IIf(IsNull([ProgramName]),[LastName] & ", " & [FirstName],[ProgramName]) AS [Non-ClientName], [Learning Center Non Client Activities].Note, [Lookup Learning Center Client Activity Type].ClientActivityDetailName, [Learning Center Non Client Activities].ActivityTypeID
FROM [Learning Center Non Client Activities] INNER JOIN [Lookup Learning Center Client Activity Type] ON [Learning Center Non Client Activities].ActivityTypeID=[Lookup Learning Center Client Activity Type].ClientActivityDetailID;

 IIf(IsNull([ProgramName]),[LastName] & ", " & [FirstName],[ProgramName]) AS [Non-ClientName

But in my new case, Instead of ProgramName I have PersonID, BUt I need to use the DLOOKUP to pull the person name out of tblPerson based on the PersonID selected.

With Warm Regards,
 
Arthur D. Lorenzini
IT System Manager
Cheyenne River Housing Authority
Wk.(605)964-4265  Ext. 130
Fax (605)964-1070

"Anyone who claimed that old age had brought them patience was either lying or senile."  







From: "John Viescas JohnV@msn.com [MS_Access_Professionals]" <MS_Access_Professionals@yahoogroups.com>
To: MS_Access_Professionals@yahoogroups.com
Sent: Thursday, July 28, 2016 2:26 PM
Subject: Re: [MS_AccessPros] Replace field if another exists.

 
Art-

Sounds like you need an IIf function that tests the selection in the combo box and returns either the concatenated name or a DLookup to the other table.  Without more info, I can't give you specific code.

John Viescas, author
Effective SQL
SQL Queries for Mere Mortals
Microsoft Office Access 2010 Inside Out
Microsoft Office Access 2007 Inside Out
Building Access Applications

On Jul 28, 2016, at 16:11, dbalorenzini@yahoo.com [MS_Access_Professionals] <MS_Access_Professionals@yahoogroups.com> wrote:

I have a query in which I need to display either the concatenated fullname or a name based on a ID in another table.

Select [FirstName] & " " & [LastName] as FullName, (or associated name from tblPerson if PersonID is selected, CitationID, Code, CitiationDate
FROM tblCitation

I would need to pull the [FirstName] & " " & [LastName] from tblPerson If the user selects a PersonID from the dropdown.

Clear as mud  know

Thank you,
Art Lorenzini
Sioux Falls, SD








__._,_.___

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 (11)

Save time and get your email on the go with the Yahoo Mail app
Get the beautifully designed, lighting fast, and easy-to-use Yahoo Mail today. Now you can access all your inboxes (Gmail, Outlook, AOL and more) in one place. Never delete an email again with 1000GB of free cloud storage.


.

__,_._,___

Re: [MS_AccessPros] Replace field if another exists.

 

I guess I was not quite done. When I run the query it pulls in the person name from the tbl_persons table, but if there is no personId (PersonID = 0), then it should be pulling the name from the tbl_Citation but for some reason it is not seeing even though there is an entry there.

SELECT tbl_citations.CitationID, tbl_citations.FirstName, tbl_citations.MiddleName, tbl_citations.LastName, tbl_citations.IsLicensed, tbl_citations.Person_ID, tbl_citations.Fee, tbl_citations.ViolationCodeID, tbl_citations.WardenID, tbl_citations.CourtDateTime, tbl_citations.ReasonForViolation, tbl_citations.Priority, tbl_citations.OpenDate, tbl_citations.Close, tbl_citations.Comments, tbl_citations.Created_By, tbl_citations.Created_On, tbl_citations.Edited_By, tbl_citations.Edited_On, IIf(IsNull([Person_ID]),[FirstName] & " " & [LastName],DLookUp("cPersonName","qryAdd_People","Person_ID = " & [Person_ID])) AS PersonName, tbl_lkpViolationCode.Code, DLookUp("WardenName","qryLookup_WardenName","WardenID = " & [WardenID]) AS WardenName
FROM tbl_citations LEFT JOIN tbl_lkpViolationCode ON tbl_citations.ViolationCodeID = tbl_lkpViolationCode.ViolationCodeID;

Is it the case that PersonID contains a 0 and is not truly NUll? 

With Warm Regards,
 
Arthur D. Lorenzini
IT System Manager
Cheyenne River Housing Authority
Wk.(605)964-4265  Ext. 130
Fax (605)964-1070

"Anyone who claimed that old age had brought them patience was either lying or senile."  







From: "John Viescas JohnV@msn.com [MS_Access_Professionals]" <MS_Access_Professionals@yahoogroups.com>
To: MS_Access_Professionals@yahoogroups.com
Sent: Friday, July 29, 2016 2:54 PM
Subject: Re: [MS_AccessPros] Replace field if another exists.

 
Art-

Sorry, I didn't notice the extra ( in your original syntax.  Try:

IIf(IsNull(PersonID]), [FirstName] & " " & [LastName], DLookup("LastName", "tbl_Persons", "PersonID = " & [PersonID]))

John Viescas, author
Effective SQL
SQL Queries for Mere Mortals
Microsoft Office Access 2010 Inside Out
Microsoft Office Access 2007 Inside Out
Building Access Applications

On Jul 29, 2016, at 15:00, Art Lorenzini dbalorenzini@yahoo.com [MS_Access_Professionals] <MS_Access_Professionals@yahoogroups.com> wrote:

THis is what I have got so far:

SELECT tbl_citations.CitationID, tbl_citations.FirstName, tbl_citations.MiddleName, tbl_citations.LastName, tbl_citations.IsLicensed, tbl_citations.Person_ID, tbl_citations.Fee, tbl_citations.ViolationCodeID, tbl_citations.OfficerID, tbl_citations.CourtDateTime, tbl_citations.ReasonForViolation, tbl_citations.Priority, tbl_citations.OpenDate, tbl_citations.Close, tbl_citations.Comments, tbl_citations.Created_By, tbl_citations.Created_On, tbl_citations.Edited_By, tbl_citations.Edited_On, [FirstName] & " " & [LastName] AS FullName, IIf(IsNull((PersonID]), [FirstName] & " " & [LastName], DLookup("LastName", "tbl_Persons", "PersonID = " & [PersonID]))
FROM tbl_citations;

I am receiving a syntax error in the following line:

IIf(IsNull((PersonID]), [FirstName] & " " & [LastName], DLookup("LastName", "tbl_Persons", "PersonID = " & [PersonID]))
FROM tbl_citations;
 

With Warm Regards,
 
Arthur D. Lorenzini
IT System Manager
Cheyenne River Housing Authority
Wk.(605)964-4265  Ext. 130
Fax (605)964-1070

"Anyone who claimed that old age had brought them patience was either lying or senile."  







From: "John Viescas JohnV@msn.com [MS_Access_Professionals]" <MS_Access_Professionals@yahoogroups.com>
To: MS_Access_Professionals@yahoogroups.com
Sent: Friday, July 29, 2016 12:39 PM
Subject: Re: [MS_AccessPros] Replace field if another exists.

 
Art-

If this expression is in the underlying query, then don't refer to the form control.  Do this:

IIf(IsNull((PersonID]), [FirstName] & " " & [LastName], DLookup("PersonName", "tblPerson", "PersonID = " & [PersonID]))

John Viescas, author
Effective SQL
SQL Queries for Mere Mortals
Microsoft Office Access 2010 Inside Out
Microsoft Office Access 2007 Inside Out
Building Access Applications

On Jul 29, 2016, at 14:15, Art Lorenzini dbalorenzini@yahoo.com [MS_Access_Professionals] <MS_Access_Professionals@yahoogroups.com> wrote:

OK. I knew I was missing something. THis query will be used on a continuous form where the combobox does not exist.

IIf(IsNull((PersonID]), [FirstName] & " " & [LastName], DLookup("PersonName", "tblPerson", "PersonID = " & [Forms]![Myform]![cmbPersonID]))
 
Something like this maybe.

Here is the structure of the tblCitation that this query is derived from:

SELECT tbl_citations.CitationID, tbl_citations.FirstName, tbl_citations.MiddleName, tbl_citations.LastName, tbl_citations.IsLicensed, tbl_citations.Person_ID, tbl_citations.Fee, tbl_citations.ViolationCodeID, tbl_citations.OfficerID, tbl_citations.CourtDateTime, tbl_citations.ReasonForViolation, tbl_citations.Priority, tbl_citations.OpenDate, tbl_citations.Close, tbl_citations.Comments, tbl_citations.Created_By, tbl_citations.Created_On, tbl_citations.Edited_By, tbl_citations.Edited_On
FROM tbl_citations;

With Warm Regards,
 
Arthur D. Lorenzini
IT System Manager
Cheyenne River Housing Authority
Wk.(605)964-4265  Ext. 130
Fax (605)964-1070

"Anyone who claimed that old age had brought them patience was either lying or senile."  







From: "John Viescas JohnV@msn.com [MS_Access_Professionals]" <MS_Access_Professionals@yahoogroups.com>
To: MS_Access_Professionals@yahoogroups.com
Sent: Thursday, July 28, 2016 6:52 PM
Subject: Re: [MS_AccessPros] Replace field if another exists.

 
Art-

Yup.  But it sounds like you need something like:

IIf(IsNull([Forms]![Myform]![cmbPersonID]), [FirstName] & " " & [LastName], DLookup("PersonName", "OtherTable", "PersonID = " & [Forms]![Myform]![cmbPersonID]))

John Viescas, author
Effective SQL
SQL Queries for Mere Mortals
Microsoft Office Access 2010 Inside Out
Microsoft Office Access 2007 Inside Out
Building Access Applications

On Jul 28, 2016, at 18:23, Art Lorenzini dbalorenzini@yahoo.com [MS_Access_Professionals] <MS_Access_Professionals@yahoogroups.com> wrote:

Here is example of what you are talking about I have in another application:

SELECT [Learning Center Non Client Activities].NonClientActivityID, [Learning Center Non Client Activities].LearningCenterID, [Learning Center Non Client Activities].ActivityDate, IIf(IsNull([ProgramName]),[LastName] & ", " & [FirstName],[ProgramName]) AS [Non-ClientName], [Learning Center Non Client Activities].Note, [Lookup Learning Center Client Activity Type].ClientActivityDetailName, [Learning Center Non Client Activities].ActivityTypeID
FROM [Learning Center Non Client Activities] INNER JOIN [Lookup Learning Center Client Activity Type] ON [Learning Center Non Client Activities].ActivityTypeID=[Lookup Learning Center Client Activity Type].ClientActivityDetailID;

 IIf(IsNull([ProgramName]),[LastName] & ", " & [FirstName],[ProgramName]) AS [Non-ClientName

But in my new case, Instead of ProgramName I have PersonID, BUt I need to use the DLOOKUP to pull the person name out of tblPerson based on the PersonID selected.

With Warm Regards,
 
Arthur D. Lorenzini
IT System Manager
Cheyenne River Housing Authority
Wk.(605)964-4265  Ext. 130
Fax (605)964-1070

"Anyone who claimed that old age had brought them patience was either lying or senile."  







From: "John Viescas JohnV@msn.com [MS_Access_Professionals]" <MS_Access_Professionals@yahoogroups.com>
To: MS_Access_Professionals@yahoogroups.com
Sent: Thursday, July 28, 2016 2:26 PM
Subject: Re: [MS_AccessPros] Replace field if another exists.

 
Art-

Sounds like you need an IIf function that tests the selection in the combo box and returns either the concatenated name or a DLookup to the other table.  Without more info, I can't give you specific code.

John Viescas, author
Effective SQL
SQL Queries for Mere Mortals
Microsoft Office Access 2010 Inside Out
Microsoft Office Access 2007 Inside Out
Building Access Applications

On Jul 28, 2016, at 16:11, dbalorenzini@yahoo.com [MS_Access_Professionals] <MS_Access_Professionals@yahoogroups.com> wrote:

I have a query in which I need to display either the concatenated fullname or a name based on a ID in another table.

Select [FirstName] & " " & [LastName] as FullName, (or associated name from tblPerson if PersonID is selected, CitationID, Code, CitiationDate
FROM tblCitation

I would need to pull the [FirstName] & " " & [LastName] from tblPerson If the user selects a PersonID from the dropdown.

Clear as mud  know

Thank you,
Art Lorenzini
Sioux Falls, SD








__._,_.___

Posted by: Art Lorenzini <dbalorenzini@yahoo.com>
Reply via web post Reply to sender Reply to group Start a New Topic Messages in this topic (10)

Save time and get your email on the go with the Yahoo Mail app
Get the beautifully designed, lighting fast, and easy-to-use Yahoo Mail today. Now you can access all your inboxes (Gmail, Outlook, AOL and more) in one place. Never delete an email again with 1000GB of free cloud storage.


.

__,_._,___