Retrieve Actual Value Rather Than Bound ID Number

[CharlesWhiteman]

In my Db I have in tblSource two fields 1)SourceID, 2)SourceName

ID1 / Telephone
ID2 / Email

The ID value is stored as the source of enquiries in a main table.

I want to have a listbox which will show multiple rows based on the above principal. However, when i construct the query I only get to see the ID value and not the user value.

Can I use QBE to get the the user value?? If so whats the best way to go about it? Thanks

 

[Kryst51]

change the column count to 2, and fix the widths to 0;1 in the properties

 

[CharlesWhiteman]

my code so far: (doesnt work yet "missing operator")

SELECT TblEnquiry.*, TblEnquiry.AutoJobNumber AS [Job Number], (DLookup"[EnquiryType]","TblEnquiryType","[EnquiryTypeID] = EnquiryTypeID" AS Expr1, TblEnquiry.jobstatus AS Status, TblEnquiry.jobowner AS Owner
FROM TblEnquiry;

 

[CharlesWhiteman]

TblEnquiry doesnt hold the second value so the column count thing doesnt apply

 

[boblarson]

You don't need a DLookup. You just need to have both tables in your query's source and select the field with the description from the table with the description.

 

[Kryst51]

I don't work well with SQL, but can you post a snapshot of the QBE?

 

[CharlesWhiteman]

I've (Yeee Haaaa) answered my own question but for the benefit of others here is my answer: (there's not too much on the forum on this specific question)

This goes in 'Field' in the qbe window:

Job Type: DLookUp("[EnquiryType]","TblEnquiryType","[EnquiryTypeID] = EnquiryTypeID")

Here's the SQL view

SELECT TblEnquiry.*, TblEnquiry.AutoJobNumber AS [Job Number], DLookUp("[EnquiryType]","TblEnquiryType","[EnquiryTypeID] = EnquiryTypeID") AS [Job Type], TblEnquiry.jobstatus AS Status, TblEnquiry.jobowner AS Owner
FROM TblEnquiry;

 

[CharlesWhiteman]

The missing operator error seems to be a difference in the use of brackets when using QBE compared to VBa

 

[boblarson]

But you DO NOT NEED A DLOOKUP to get that value!!!!!! Get that out of your head. Join the other table and use the field. It is way more efficient.

 

[CharlesWhiteman]

OK BOB!!!!

I got it that way too

Here's the SQL

SELECT TblEnquiry.*, TblEnquiry.AutoJobNumber AS [Job Number], TblEnquiry.jobstatus AS Status, TblEnquiry.jobowner AS Owner, TblEnquiryType.EnquiryType
FROM TblEnquiry INNER JOIN TblEnquiryType ON TblEnquiry.EnquiryTitle = TblEnquiryType.EnquiryTypeID;

 

[CharlesWhiteman]

Technically though, why is it more efficient?

 

[boblarson]

Technically though, why is it more efficient?

A Dlookup goes through more steps when finding the value than does a straight SQL column included. I kind of liken it to driving to a store and, while there is a driveway right in front of you, you decide to drive around the block to the rear of the store to come in. They both get you there but one takes longer to do it. The same is for the DLookup. I can't tell you from a technical standpoint why it is the longer method, but it is.