Well, I guess everyone has had a go so I will too:
SUB asset_search
DIM assetid, arrTables(4) AS STRING,
i As Integer
DIALOG
TITLE "Asset ID Search"
CONTROL STATICTEXT
TITLE "Enter Asset ID to Search For"
CONTROL EDITTEXT
INTO assetid
CONTROL OKBUTTON
CONTROL CANCELBUTTON
if not CoammandInfo(CMD_INFO_DLG_OK) Then
Exit Sub
end if
arrTables(1) = "VALVES"
arrTables(2) = "HYDRANTS"
arrTables(3) = "METERS"
arrTables(4) = "PRV"
For i = 1 To Ubound(arrTables)
SELECT *
FROM arrTables(i)
WHERE ASSET_ID = assetid
INTO assetidresult NoSelect
If TableInfo("assetidresult", TAB_INFO_NROWS) > 0 Then
BROWSE *
FROM assetidresult
Else
Close Table assetidresult
if i = Ubound(arrTables) Then
Note "Sorry, but we were not able to find this asses#:
" & assetid
end if
end if
Loop
END SUB 'asset_search
HTH,
Peter Horsb�ll M�ller
GIS Developer
Geographical Information & IT
COWI A/S
Odensevej 95
DK-5260 Odense S
Denmark
Tel +45 6311 4900
Direct +45 6311 4908
Mob +45 5156 1045
Fax +45 6311 4949
E-mail [EMAIL PROTECTED]
http://www.cowi.dk
*****CHANGE OF ADDRESS - COWI's offices in Odense are now under the same roof*****
On December 8th. 2003, Geographical Information & IT moved from Rugaardsvej 55, 5000
Odense C to Odensevej 95, 5260 Odense S.
Please note our new telephone, direct and telefax numbers.
COWI wish you a merry Christmas and a happy New Year.
> -----Original Message-----
> From: Jonathan Gledhill [mailto:[EMAIL PROTECTED]
> Sent: Wednesday, January 07, 2004 1:01 PM
> To: [EMAIL PROTECTED]
> Subject: MI-L Select Problem
>
>
> Hi
>
> Wonder if one of you guru's could help me with this
> relatively simple query!
>
> I'm trying to search through several tables called VALVES,
> HYDRANTS, METERS and PRV. They all have one field in common
> called ASSET_ID in which a unique id number is held.
>
> What i want to do is through one button, search for an input
> asset id number through all these four tables at once.
>
> I'm able to make it work through a seperate mbx for each
> table, but this is a nuisance if i need to update anything
> and I tried to write something that would search all 4 tables
> in one go. I assumed (probably
> wrongly) that as i'm not bothered about joining these tables
> in any way and that as they all contain an ASSET_ID field
> that i could just search them all at once, like this
>
> **************
>
> SUB asset_search
>
> DIM assetid AS STRING
>
> DIALOG
> TITLE "Asset ID Search"
>
> CONTROL STATICTEXT
> TITLE "Enter Asset ID to Search For"
>
> CONTROL EDITTEXT
> INTO assetid
>
> CONTROL OKBUTTON
>
> CONTROL CANCELBUTTON
>
> SELECT *
> FROM VALVES,HYDRANTS,METERS,PRV
> WHERE ASSET_ID = assetid
> INTO assetidresult
>
> BROWSE *
> FROM assetidresult
>
> END SUB asset_search
>
> **************
>
> But when i run the program, i get warned that there is "No
> Join Specified Between Tables VALVES and HYDRANTS, invalid
> join in WHERE clause".
>
> I'm now stumped and cant seem to find an answer in the
> books... Could anyone suggest how i could search these tables
> in one go?
>
> Thanks
>
> Jonathan
>
>
> **********************************************************************
>
> CONFIDENTIALITY
>
> The information contained in this message and any attachments
> are intended solely for the use of the individual or entity
> to whom they are addressed. It may contain privileged or
> confidential
> information. If you are not the intended recipient you must
> not copy, distribute or take any action in reliance on it.
> If you have received this e-mail in error please notify the
> sender. Thank You
>
> This e-mail message has been swept by MIMEsweeper for the
> presence of known computer viruses.
> **********************************************************************
>
>
> ---------------------------------------------------------------------
> List hosting provided by Directions Magazine |
> www.directionsmag.com | To unsubscribe, e-mail:
> [EMAIL PROTECTED]
> For additional commands, e-mail:
> [EMAIL PROTECTED]
> Message number: 9770
>
>
>
