Using DLookUp to find data

[floyd33 via AccessMonster.com]

I am developing a query that needs to show several fields of straight data,
along with one field that needs to look up someone's name, if they fit
certain criteria.

Query

FirstName LastName Suffix MeetingID ProjectCode Faculty

Basically, this faculty person is also someone registered to this meeting,
under a different attendee type and needs to appear in the data row for each
attendee we would invite, so it would be the same name for as many people as
are invited. If I just do a formula to look up the name of the person
matching that typeID, then it will only show the faculty's name when it hits
the faculty row, which doesn't work for the user. So I thought using a
DLookUp from another query (qryConfirmedFaculty) would make sense. I am
having issues lining up the meeting ID's though and am stumped.

It should look like this:

=DLookUp("FacultyName","qryConfirmedFaculty","MeetingID=MeetingID")

but of course that isn't working because it's reading the MeetingID funky and
giving error messages. I know how to use DLookUp matching a value in the
query to a value in a form, but since this is searching a value in another
query, how do I proceed? Any help is appreciated!

 

[Ken Snell]

You're on the right track. Move the value outside the " characters in third
argument:

=DLookUp("FacultyName","qryConfirmedFaculty","MeetingID=" & MeetingID)

The above assumes that MeetingID field is a numeric datatype.

 

[floyd33 via AccessMonster.com]

Thanks, Ken. I am now getting the error "The specified field '[MeetingID]'
could refer to more than one table listed in the from clause of your
statement."

You're on the right track. Move the value outside the " characters in third
argument:

=DLookUp("FacultyName","qryConfirmedFaculty","MeetingID=" & MeetingID)

The above assumes that MeetingID field is a numeric datatype.

I am developing a query that needs to show several fields of straight data,
along with one field that needs to look up someone's name, if they fit

[quoted text clipped - 25 lines]

query to a value in a form, but since this is searching a value in another
query, how do I proceed? Any help is appreciated!

 

[John W. Vinson]

Thanks, Ken. I am now getting the error "The specified field '[MeetingID]'
could refer to more than one table listed in the from clause of your
statement."

Then change it to [tablename].[MeetingID], specifying the name of a table in
your query which contains the field.

 

[floyd33 via AccessMonster.com]

I tried that immediately and now just get the #error displayed in the field.

Thanks, Ken. I am now getting the error "The specified field '[MeetingID]'
could refer to more than one table listed in the from clause of your
statement."

Then change it to [tablename].[MeetingID], specifying the name of a table in
your query which contains the field.

 

[John W. Vinson]

I tried that immediately and now just get the #error displayed in the field.

Thanks, Ken. I am now getting the error "The specified field '[MeetingID]'
could refer to more than one table listed in the from clause of your
statement."

Then change it to [tablename].[MeetingID], specifying the name of a table in
your query which contains the field.

Please post the SQL of your query and indicate the datatypes of the relevant
fields.

 

[floyd33 via AccessMonster.com]

SELECT tblMeeting.Faculty, tblAttendee.[First Name], tblAttendee.[Last Name],
tblAttendee.Suffix, IIf(IsNull([Suffix]),[FirstName] & " " & [LastName],
[FirstName] & " " & [LastName] & ", " & [Suffix]) AS FullName, tblAttendee.
City, tblAttendee.State, tblMeeting.StartDate, Format([StartDate],"dddd") AS
[Day], tblMeeting.MeetingTimeStart, tblMeeting.Venue, tblMeeting.VenueAddress,
tblMeeting.VenueCity, tblMeeting.VenueState, tblMeeting.VenueZip, tblMeeting.
VenuePhone, tblMeeting.FuturaRep, tblMeeting.FuturaPhone, tblRepInformation.
FirstName & " " & tblRepInformation.LastName AS [Integra Rep],
tblRepInformation.Contact AS [Integra Rep Phone], tblAttendeeType.
AttendeeType1
FROM ((tblAttendee RIGHT JOIN tblMeeting ON tblAttendee.MeetingID =
tblMeeting.MeetingID) LEFT JOIN tblRepInformation ON tblMeeting.RepID =
tblRepInformation.RepID) LEFT JOIN tblAttendeeType ON tblAttendee.
AttendeeType2 = tblAttendeeType.AttendeeTypeID
WHERE (((tblMeeting.ProgramNumber) Like [Enter Program Number])) OR (((
[ProgramNumber] Like [Enter Program Number]) Is Null));


FacultyName is text and both MeetingID's are numbers

I tried that immediately and now just get the #error displayed in the field.

[quoted text clipped - 4 lines]

Then change it to [tablename].[MeetingID], specifying the name of a table in
your query which contains the field.

Please post the SQL of your query and indicate the datatypes of the relevant
fields.

 

[John W. Vinson]

SELECT tblMeeting.Faculty, tblAttendee.[First Name], tblAttendee.[Last Name],
tblAttendee.Suffix, IIf(IsNull([Suffix]),[FirstName] & " " & [LastName],
[FirstName] & " " & [LastName] & ", " & [Suffix]) AS FullName, tblAttendee.
City, tblAttendee.State, tblMeeting.StartDate, Format([StartDate],"dddd") AS
[Day], tblMeeting.MeetingTimeStart, tblMeeting.Venue, tblMeeting.VenueAddress,
tblMeeting.VenueCity, tblMeeting.VenueState, tblMeeting.VenueZip, tblMeeting.
VenuePhone, tblMeeting.FuturaRep, tblMeeting.FuturaPhone, tblRepInformation.
FirstName & " " & tblRepInformation.LastName AS [Integra Rep],
tblRepInformation.Contact AS [Integra Rep Phone], tblAttendeeType.
AttendeeType1
FROM ((tblAttendee RIGHT JOIN tblMeeting ON tblAttendee.MeetingID =
tblMeeting.MeetingID) LEFT JOIN tblRepInformation ON tblMeeting.RepID =
tblRepInformation.RepID) LEFT JOIN tblAttendeeType ON tblAttendee.
AttendeeType2 = tblAttendeeType.AttendeeTypeID
WHERE (((tblMeeting.ProgramNumber) Like [Enter Program Number])) OR (((
[ProgramNumber] Like [Enter Program Number]) Is Null));

A couple of changes here; for one, you can get away from the complicated IIF
nest by using the fact that both & and + are string concatenation operators,
but handle NULL differently. & treats NULL as a zero length string ("John" &
NULL & "Vinson" = "JohnVinson"); + returns NULL if either argument is NULL.

More to the point, I was too terse in my suggestion - you don't need the LIKE
operator AT ALL in your query if you use the IS NULL. Try

SELECT tblMeeting.Faculty, tblAttendee.[First Name], tblAttendee.[Last Name],
tblAttendee.Suffix, [FirstName] & " " & [LastName] & (" " + [Suffix]) AS
FullName, tblAttendee.City, tblAttendee.State, tblMeeting.StartDate,
Format([StartDate],"dddd") AS [Day], tblMeeting.MeetingTimeStart,
tblMeeting.Venue, tblMeeting.VenueAddress,
tblMeeting.VenueCity, tblMeeting.VenueState, tblMeeting.VenueZip, tblMeeting.
VenuePhone, tblMeeting.FuturaRep, tblMeeting.FuturaPhone, tblRepInformation.
FirstName & " " & tblRepInformation.LastName AS [Integra Rep],
tblRepInformation.Contact AS [Integra Rep Phone], tblAttendeeType.
AttendeeType1
FROM ((tblAttendee RIGHT JOIN tblMeeting ON tblAttendee.MeetingID =
tblMeeting.MeetingID) LEFT JOIN tblRepInformation ON tblMeeting.RepID =
tblRepInformation.RepID) LEFT JOIN tblAttendeeType ON tblAttendee.
AttendeeType2 = tblAttendeeType.AttendeeTypeID
WHERE tblMeeting.ProgramNumber = [Enter Program Number] OR [Enter Program
Number] Is Null;

You would really be well advised NOT to use popup prompts, though; create a
small unbound form (frmCrit let's call it) with a textbox txtCriteria, and use

= [Forms]![frmCrit]![txtCriteria] OR [Forms]![frmCrit]![txtCriteria] IS NULL

as a criterion. It's handy to put a command button on frmCrit to open whatever
form or report you're trying to display.

 

[theDBguy]

Hi,

It's hard to imagine what you're dealing with without seeing a sample data
but perhaps try enclosing the last part in brackets:

DLookup("FacultyName", "qryConfirmedFaculty", "MeetingID=[MeetingID]")

Otherwise, are you not able to JOIN the two queries together?

Hope that helps...