Join issue Access

[AdamPoz]

Hi,

I have an issue with a join query. I have 2 tables 1 containing information
on monitors the 2nd table contains information about computers. They can be
joined on the hostname field as they are the for the most part the same.
However some of the computers have a suffix of the domain and I can’t seem to
get the sql right

Table definition
computer_tbl a monitor_tbl
hostname hostname

table data
computer_tbl a monitor_tbl
host101 host101
host102.domain host102


My sql code

a.hostname, b.hostname
from computer_tbl a, from computer_tbl b
where a.hostname = mid(b.hostname,1,(length(b.hostname) -7))
or a.hostname = b.hostname

The sql runs in access and then returns the results and 3 seconds later a
function error is displayed and the dataset is replaced with question marks

Can some please advice me where I am going wrong

 

[MGFoster]

Hi,

I have an issue with a join query. I have 2 tables 1 containing information
on monitors the 2nd table contains information about computers. They can be
joined on the hostname field as they are the for the most part the same.
However some of the computers have a suffix of the domain and I can’t seem to
get the sql right

Table definition
computer_tbl a monitor_tbl
hostname hostname

table data
computer_tbl a monitor_tbl
host101 host101
host102.domain host102


My sql code

a.hostname, b.hostname
from computer_tbl a, from computer_tbl b
where a.hostname = mid(b.hostname,1,(length(b.hostname) -7))
or a.hostname = b.hostname

The sql runs in access and then returns the results and 3 seconds later a
function error is displayed and the dataset is replaced with question marks

Can some please advice me where I am going wrong

-----BEGIN PGP SIGNED MESSAGE-----
Hash: SHA1

First: Length() is incorrect. Should be Len().
Second: If the value of b.hostname is less than 7 the Mid() function
will fail 'cuz the "length to read" value will be a negative number.

Try using the IIf() function and the InStr() function to find the period
"." before shortening the hostname value.

If the hostname number is always 3 characters then try this:

IIf(InStr(b.hostname,".")>0, Left$(b.hostname,3),b.hostname)

OR

If the number is always numeric characters and the domain is always
letters then this might be better

a.hostname = Val(b.hostname)

Val(123hi_there) returns 123.

HTH,
--
MGFoster:::mgf00 <at> earthlink <decimal-point> net
Oakland, CA (USA)
** Respond only to this newsgroup. I DO NOT respond to emails **

-----BEGIN PGP SIGNATURE-----
Version: PGP for Personal Privacy 5.0
Charset: noconv

iQA/AwUBSqlLIIechKqOuFEgEQLTxQCfQaUXXfMZt1MTU2d10NBROfFGmn0Anjtz
Ezp5axmfpA64n7WO7yHUHtKq
=D5kT
-----END PGP SIGNATURE-----

 

[Marshall Barton]

I have an issue with a join query. I have 2 tables 1 containing information
on monitors the 2nd table contains information about computers. They can be
joined on the hostname field as they are the for the most part the same.
However some of the computers have a suffix of the domain and I can’t seem to
get the sql right

Table definition
computer_tbl a monitor_tbl
hostname hostname

table data
computer_tbl a monitor_tbl
host101 host101
host102.domain host102


My sql code

a.hostname, b.hostname
from computer_tbl a, from computer_tbl b
where a.hostname = mid(b.hostname,1,(length(b.hostname) -7))
or a.hostname = b.hostname

The sql runs in access and then returns the results and 3 seconds later a
function error is displayed and the dataset is replaced with question marks

When you post code, expressions or SQL, please post a
Copy/Paste of what you have so we don't waste time going
back and forth over typos.

If the hostname field in the monitors table never has a
suffix, then try this kind of WHERE clause:

WHERE b.hostname = Left(a.hostname, Len(b.hostname))
OR a.hostname = b.hostname

 

[AdamPoz via AccessMonster.com]

Hi Marshall,

Apologies for not pasting the actual code, I wil make sure that I don't just
type it out.

Your where clause works.

Thankyou

 

[AdamPoz via AccessMonster.com]

Hi MGFoster,

Thanks for that I have tried it and it works to. Thanks for the info on mid
and val function,