Can dlookup (or anything else) do this?

N

Nick X

Hello,
Expr1: DLookUp("[txtDesc]","tbl15702UT","[txtDesc]=" &
[tblUTFIELDCODES]![FCODE])

I need to evaluate a field (txtDesc) look up the value in another table
(tblUTFIELDCODES) and replace that value in 15702UT with the associated text
value. eg: txtDesc = 409, FCODE = 409, FDESC = STM MH; replace txtDesc with
FDESC.

txtDesc is the following expression not an actual field in a table: txtDesc:
Left([Desc],+3)

Thank you for your help,
Nick

PS: I also tried this but Access said the expression was to complex-
Switch(txtDesc=503,"SAN MH",txtDesc=506,"SAN
LH",txtDesc=401,"ACB",txtDesc=402,"BCB",txtDesc=403,"CCB",txtDesc=404,"DCB",txtDesc=405,"ECB",txtDesc=406,"Single
ECB",txtDesc=408,"Open Top MH",txtDesc=409,"STM MH",txtDesc=412,"Strip Trench
Drain",txtDesc=413,"Curb Inlet",txtDesc=414,"ODOT CB3",txtDesc=415,"ODOT
CB3A",txtDesc=319,"WV")
 
M

Michel Walsh

I think something is wrong with the DLookup, or with the table design.

I assume your table tblUTFIELDCODES has (at least) two fields,

fcode txtDesc ' fields name
503 "SAN MH"
506 "SAN LH"
401 "ACB"
.... data sample



so, your DLookup should be



Expr1: DLookUp("txtDesc","tblUTFIELDCODES","[fcode]=" & tbl15702UT.FCODE)


since you want find, inside tblUTFIELDCODES, which row has its fcode value =
to the one in tbl15702UT, and, having found that row, return its txtDesc
field.



Hoping it may help,
Vanderghast, Access MVP
 
J

John W. Vinson

Hello,
Expr1: DLookUp("[txtDesc]","tbl15702UT","[txtDesc]=" &
[tblUTFIELDCODES]![FCODE])

I need to evaluate a field (txtDesc) look up the value in another table
(tblUTFIELDCODES) and replace that value in 15702UT with the associated text
value. eg: txtDesc = 409, FCODE = 409, FDESC = STM MH; replace txtDesc with
FDESC.
Click to expand...

You're going all around the barn to do something that seems very simple.

Create a Query joining your table to tbl15702UT, joining your txtDesc field to
that table's FCode field. Display FDESC.

Am I missing the point...!?

John W. Vinson [MVP]
 
N

Nick X

Thanks for your reply,
Let me try to lay this out better. I use this database to parse comma
delimited text files. I used to do this function manually, it is basically a
find & replace. At its core there are to make-table queries; one to filter
the data down to what i need, one to concatenate two fields. In the past,
after I ran the last query I would open the table and do the find and
replace: Find 409 - Replace w/ STM MH; Find 319 - Replace w/ WV...

What I have done this time is create a table that has the number associated
with the text:
tblUTFIELDCODES
FCODE FDESC
319 WV
401 ACB
402 BCB
409 STM MH
etc.

This data from tbl15702UT(imported txt file):
Desc name Elev
319 * 13 6158 770.802
319 6101 770.47
503 * 08 6100 771.323
409 * 14 5993 767.505
as you can see there is no direct connection between tbl15702UT.DESC and
tblUTFIELDCODES.FCODE

This is what I have for the query:
SELECT Left([Desc],+3) AS txtDesc, [15702UT].name AS txtName, IIf([Desc] Not
Like "* *","00",Mid([Desc],InStr([Desc],"*")+2)) AS txtDescNum,
txtDesc.column(1) & " " & [txtDescNum] & " - " & [txtName] AS txtDescName
INTO temp_01
FROM 15702UT;

tbl15702UT.DESC is the field the issue. Need to remove the "*" and replace
the first three digits with tblUTFIELDCODES.FDESC

The end result will be:
(UT= [Desc]&"-"&[name])
UT Elev
DCB 25-15297 815.394
STM 06-15301 815.237
WV 10-15299 815.183
SAN 04-15296 815.047

I'm certain there is a simpler solution. (Probably would only taken me a
half hour to do it manually, but what's the fun in that)

Thanks for your help,
Nick
 
N

Nick X

Michel Walsh said:
I think something is wrong with the DLookup, or with the table design.

I assume your table tblUTFIELDCODES has (at least) two fields,

fcode txtDesc ' fields name
503 "SAN MH"
506 "SAN LH"
401 "ACB"
.... data sample



so, your DLookup should be



Expr1: DLookUp("txtDesc","tblUTFIELDCODES","[fcode]=" & tbl15702UT.FCODE)


since you want find, inside tblUTFIELDCODES, which row has its fcode value =
to the one in tbl15702UT, and, having found that row, return its txtDesc
field.



Hoping it may help,
Vanderghast, Access MVP



Nick X said:
Hello,
Expr1: DLookUp("[txtDesc]","tbl15702UT","[txtDesc]=" &
[tblUTFIELDCODES]![FCODE])

I need to evaluate a field (txtDesc) look up the value in another table
(tblUTFIELDCODES) and replace that value in 15702UT with the associated
text
value. eg: txtDesc = 409, FCODE = 409, FDESC = STM MH; replace txtDesc
with
FDESC.

txtDesc is the following expression not an actual field in a table:
txtDesc:
Left([Desc],+3)

Thank you for your help,
Nick

PS: I also tried this but Access said the expression was to complex-
Switch(txtDesc=503,"SAN MH",txtDesc=506,"SAN
LH",txtDesc=401,"ACB",txtDesc=402,"BCB",txtDesc=403,"CCB",txtDesc=404,"DCB",txtDesc=405,"ECB",txtDesc=406,"Single
ECB",txtDesc=408,"Open Top MH",txtDesc=409,"STM MH",txtDesc=412,"Strip
Trench
Drain",txtDesc=413,"Curb Inlet",txtDesc=414,"ODOT CB3",txtDesc=415,"ODOT
CB3A",txtDesc=319,"WV")
Click to expand...
Click to expand...
 
M

Michel Walsh

SELECT tblUTFIELDCODES.fDesc & tbl15702UT.name
FROM tblUTFIELDCODES RIGHT JOIN tbl15702UT
ON tblUTFIELDCODES.fCode = val(tbl15702UT.desc)


works?


Desc name Elev '' 3 names
and
319 * 13 6158 770.802 ' 4, if not 5 columns
319 6101 770.47
503 * 08 6100 771.323
409 * 14 5993 767.505




I assumed tblUTFIELDCODES.fCode is numerical, not string.

Vanderghast, Access MVP


Note that I don't know what is what in tbl15702UT, you have 3 fields name,
but 4 columns of data
 
N

Nick X

SELECT tblUTFIELDCODES.fDesc & tbl15702UT.name
FROM tblUTFIELDCODES RIGHT JOIN tbl15702UT
ON tblUTFIELDCODES.fCode = val(tbl15702UT.desc)

works?
Click to expand...

I am getting "Syntax error( missing operator) inquery expression
'tblUTFIELDCODES.fDesc & tbl15702UT.name'."
Desc name Elev '' 3 names
and
319 * 13 6158 770.802 ' 4, if not 5 columns
319 6101 770.47
503 * 08 6100 771.323
409 * 14 5993 767.505
Click to expand...
Sorry, it looked more sensical in my news reader:
3 columns: Desc= "319 * 13", Name= "6158", Elev= "770.802"
-The end result will be a new table with two columns:
Desc+Name , Elev
"WV 13 - 6158", "770.802"
 
N

Nick X

Thank you for your time in this effort,
I got it to work by adding [] around one of the tables. This is the result:
'1 Column
WV 5995
WV 6101
WV 2351
WV 2338
WV 2302
WV 2284
WV 2697

What is missing now though is "WV 01 - 2351"
'1 Column
WV 5995
WV 6101
WV 01 - 2351
WV 02 - 2338
WV 03 - 2302
WV 04 - 2284
WV 05 - 2697
(The first 2 had no numerals.)
 
M

Michel Walsh

Change


SELECT tblUTFIELDCODES.fDesc & tbl15702UT.name


to


SELECT tblUTFIELDCODES.fDesc & Replace(Mid(tbl15702UT.name & " ",
InStr(tbl15702UT.name & " ", " ")), "*", "")



That may not work in Access 2002 or previous version (due to that Replace
won't work in a query).



Vanderghast, Access MVP
 
N

Nick X

Thanks,
I got it working.
SELECT [15702UT].Elev, UTFIELDCODES.fDesc & " " & IIf([Desc] Not Like "*
*","00",Mid([Desc],InStr([Desc],"*")+2)) & " - " & [15702UT].name AS FinalDesc
FROM UTFIELDCODES RIGHT JOIN 15702UT ON
UTFIELDCODES.fCode=val([15702UT].desc);

I am just pulling the numerals in seperately
Thanks,
Nick

Michel Walsh said:
Change


SELECT tblUTFIELDCODES.fDesc & tbl15702UT.name


to


SELECT tblUTFIELDCODES.fDesc & Replace(Mid(tbl15702UT.name & " ",
InStr(tbl15702UT.name & " ", " ")), "*", "")



That may not work in Access 2002 or previous version (due to that Replace
won't work in a query).



Vanderghast, Access MVP



Nick X said:
Thank you for your time in this effort,
I got it to work by adding [] around one of the tables. This is the
result:
'1 Column
WV 5995
WV 6101
WV 2351
WV 2338
WV 2302
WV 2284
WV 2697

What is missing now though is "WV 01 - 2351"
'1 Column
WV 5995
WV 6101
WV 01 - 2351
WV 02 - 2338
WV 03 - 2302
WV 04 - 2284
WV 05 - 2697
(The first 2 had no numerals.)
Click to expand...
Click to expand...
 
Top