Query SQL Syntax

  • Thread starter Thread starter Rob Hamlin
  • Start date Start date
R

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 Phone:(Select [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,
 
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 Phone:(Select [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,
Click to expand...

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!
 
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 Phone:(Select First([Business Phone]) FROM [Contacts Extended] Where
[Contacts Extended].[Contact Name]=[Target_Stores_MRRS_Extended].[DPM]
AND [Business Phone] Is Not Null)

OR
DPM Phone:(Select 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
 
Back
Top