Chris__T
Posts: 339
|
| Posted: 10/03/2007, 12:05 PM |
|
Is there a way to do the following select (like in Dlookup?)
SELECT Permit_No FROM Table1 WHERE Table1.Address like Table2.Address
I want to get Permit_No from table1, but its WHERE condition depends on comparing two address fields in different tables. Will that work?
|
 |
 |
Bubba
Posts: 33
|
| Posted: 10/18/2007, 6:01 PM |
|
This is not exact coding, but should give you a rough idea of how you could do it.
Dim Address2
Address2 = CCDLookUp("Address","[ Table2]" DBConnection1)
Permit_No = CCDLookUp("Permit_No","[ Table1]"," Table1.Address = " & Address2 , DBConnection1)
|
|
|
|
Chris__T
Posts: 339
|
| Posted: 10/19/2007, 9:08 AM |
|
Ah, that looks very logical. I'll have to give it a try. Thanks!
|
|
|
|
fabio
Posts: 19
|
| Posted: 10/23/2007, 7:46 PM |
|
you could solve it using an SQL query like this
select
Permit_No
from
table1 t1 inner join
table2 t2 on ( t1.address = t2.address )
Let me see if I understood. If you want to get the Permit_no from table1 only when there's a record with the same addres on Table2, the query above will do it.
|
|
|
|
fabio
Posts: 19
|
| Posted: 10/23/2007, 7:49 PM |
|
or you could use the following query in CCDLookup
CCDLookup("Permit_no","table1 t1 left join table2 t2 on (t1.address = t2.address)","t2.address is not null", dbConnection)
|
|
|
|
Chris__T
Posts: 339
|
| Posted: 10/24/2007, 8:40 AM |
|
Quote fabio:
you could solve it using an SQL query like this
select
Permit_No
from
table1 t1 inner join
table2 t2 on ( t1.address = t2.address )
Let me see if I understood. If you want to get the Permit_no from table1 only when there's a record with the same addres on Table2, the query above will do it.
Yes, that is what I'm trying to do. If the address in table2 matches the addresss in table 1, then give me the Permit Number from table1 so I can stick it in table2.
I'll try to look into that when I get a chance...Thanks fabio.
|
|
|
|
wkempees
|
| Posted: 10/29/2007, 9:40 AM |
|
BusinessLicense.BUS.Value = CCDLookUp('Permit_No','Permit t1 left join
BusinessLicense t2 on (t1.Job_Site = t2.CC_address)','t2.address is not
null',$DBConnect)
- use of single qoutes
- $DBConnect not DBConnect
Walter
|
|
|
|
Chris__T
Posts: 339
|
| Posted: 10/29/2007, 10:13 AM |
|
I'm getting an error with the CCDlookup.
BusinessLicense.BUS.Value = CCDLookUp(Permit_No,Permit t1 left join BusinessLicense t2 on (t1.Job_Site = t2.CC_address),"t2.address is not null",DBConnect)
gives me:
Error Type:
Microsoft VBScript compilation (0x800A03EE)
Expected ')'
/BusinessLicense/NewPage2_events.asp, line 58, column 55
Column 55 would be the space between Permit and t1
Is it giving me an error because one of my tables is named BusinessLicense and the grid is named BusinessLicense?
|
|
|
|
Chris__T
Posts: 339
|
| Posted: 10/29/2007, 11:21 AM |
|
I tried the single quotes and $dbconnect but still got:
Error Type:
Microsoft VBScript compilation (0x800A03EA)
Syntax error
/BusinessLicense/NewPage2_events.asp, line 58, column 38
BusinessLicense.BUS.Value = CCDLookUp('Permit_No','Permit t1 left join BusinessLicense t2 on (t1.Job_Site = t2.CC_address)','t2.address is not null',$DBConnect)
In the code section it shows everything in the ( ) as comments (in green)
|
|
|
|
wkempees
|
| Posted: 10/30/2007, 1:27 AM |
|
Then I am lost too, but for being extra friendly to MS VB by doing :
BusinessLicense.BUS.Value = CCDLookUp('Permit_No','Permit as t1 left join
BusinessLicense as t2 on (t1.Job_Site = t2.CC_address)','t2.address is not
null',$DBConnect)
- not the addition of the word 'as'.
And asking you to thoroughly check the spelling and Caps of all fields.
I f.i. notice CC_address, t2.address, Job_Site, as being all different in
camelrules
Walter
"Chris__T" <Chris__T@forum.codecharge> schreef in bericht
news:64726248de9e14@news.codecharge.com...
>I tried the single quotes and $dbconnect but still got:
>
> Error Type:
> Microsoft VBScript compilation (0x800A03EA)
> Syntax error
> /BusinessLicense/NewPage2_events.asp, line 58, column 38
>
> BusinessLicense.BUS.Value = CCDLookUp('Permit_No','Permit t1 left join
> BusinessLicense t2 on (t1.Job_Site = t2.CC_address)','t2.address is not
> null',$DBConnect)
>
> In the code section it shows everything in the ( ) as comments (in green)
> ---------------------------------------
> Sent from YesSoftware forum
> http://forums.codecharge.com/
>
|
|
|
|
|
