Query SQL Syntax

[Rob Hamlin]

I have 2 queries that I am building another query off of.

I have on query with the name of project contacts in it.

I have another query with all the associated contact info in it.

I made a cutom colum in the query and used this syntax.
DPM PhoneSelect [Business Phone] FROM [Contacts Extended] Where [Contacts
Extended]![Contact Name]=[Target_Stores_MRRS_Extended]![DPM])

This is not working. Can anyone help or point me to a good resource for how
to use the syntax correctly.

Thanks,

 

[John W. Vinson]

I have 2 queries that I am building another query off of.

I have on query with the name of project contacts in it.

I have another query with all the associated contact info in it.

I made a cutom colum in the query and used this syntax.
DPM PhoneSelect [Business Phone] FROM [Contacts Extended] Where [Contacts
Extended]![Contact Name]=[Target_Stores_MRRS_Extended]![DPM])

This is not working. Can anyone help or point me to a good resource for how
to use the syntax correctly.

Thanks,

Try using . instead of ! as a delimiter. Fieldnames in tables/queries use a
dot delimiter, not a bang.

It would also help to know the context in which you're using this, and what
[Target_Stores_MRRS_Extended] might be. You should also be very, very careful
about trying to link tables using [Contact Name] - names are NOT unique (I
know three guys named Fred Brown), are not stable (is Al Wilson the same
person as Alan Wilson, or is he Albert Wilson...?), and are inappropriate for
joins, unless you're using external data and have no choice in the matter!

 

[John Spencer]

Since you are using that as a field, you must return only on record and Access
must know that only one record is being returned.

Try modifying that to
DPM PhoneSelect First([Business Phone]) FROM [Contacts Extended] Where
[Contacts Extended].[Contact Name]=[Target_Stores_MRRS_Extended].[DPM]
AND [Business Phone] Is Not Null)

OR
DPM PhoneSelect Max([Business Phone]) FROM [Contacts Extended] Where
[Contacts Extended].[Contact Name]=[Target_Stores_MRRS_Extended].[DPM])

John Spencer
Access MVP 2002-2005, 2007-2010
The Hilltop Institute
University of Maryland Baltimore County