SQL invalid syntax?

  • Thread starter Thread starter SokkiHong
  • Start date Start date
S

SokkiHong

Can someone please tell me what's wrong with my syntax
here?

=DLookUp("[Status]","[Issues_SubTable Query]") WHERE
(("[Issue ID]","[Issues Master Table Query]") =
("[ID]","[Issues_SubTable Query]"))

I am trying to Pull [Status] from the [Issues_SubTable
Query] only when the [ID] field of [Issues_Subtable Query]
matches up with [Issue ID] with the [Issues Master Table
Query]. Please help, thanks.
 
DLookup doesn't work like that. It can only look at a single table: there's
no capability to compare to other tables. As well, it only returns a single
value (the first it encounters, I believe), so you won't have any way of
knowing the ID to which it refers.

Depending on what you're trying to do with the value(s), your best bet would
be to create a query and use it.
 
Can someone please tell me what's wrong with my syntax
here?

=DLookUp("[Status]","[Issues_SubTable Query]") WHERE
(("[Issue ID]","[Issues Master Table Query]") =
("[ID]","[Issues_SubTable Query]"))

I am trying to Pull [Status] from the [Issues_SubTable
Query] only when the [ID] field of [Issues_Subtable Query]
matches up with [Issue ID] with the [Issues Master Table
Query]. Please help, thanks.

Check the online help for DLookUp (open the VBA editor before
searching help). You'll find that the DLookUp function takes THREE
arguments; you're only giving it two. And the word WHERE is
meaningless in this context.

I think you'll need to create a new Query joining the Issues Master
Table Query to the Issues_SubTable Query and use this query. What
value do you want returned if (say) 418 issues match?
 
Thank you for your previous advice. I believe I
miscommunicated my situation. I already have a query that
is joining the [Issues_SubTable Query] and the [Issues
Master Table Query] by joining their ID's together. What
I want is to show the Auto number (in this case it'll be 1-
100) and show the latest status of that each corresponding
issue if status is showing (in this case ID 2,3 & 18).
However what I am getting currently is the record set of
2,3, & 18 only. I want it to show the records from 1 to
100, with satus for items 2,3, & 18. Please let me know
if you have any idea how to do this. Thank you.

-----Original Message-----
Can someone please tell me what's wrong with my syntax
here?

=DLookUp("[Status]","[Issues_SubTable Query]") WHERE
(("[Issue ID]","[Issues Master Table Query]") =
("[ID]","[Issues_SubTable Query]"))

I am trying to Pull [Status] from the [Issues_SubTable
Query] only when the [ID] field of [Issues_Subtable Query]
matches up with [Issue ID] with the [Issues Master Table
Query]. Please help, thanks.

Check the online help for DLookUp (open the VBA editor before
searching help). You'll find that the DLookUp function takes THREE
arguments; you're only giving it two. And the word WHERE is
meaningless in this context.

I think you'll need to create a new Query joining the Issues Master
Table Query to the Issues_SubTable Query and use this query. What
value do you want returned if (say) 418 issues match?



.
 
Thank you for your previous advice. I believe I
miscommunicated my situation. I already have a query that
is joining the [Issues_SubTable Query] and the [Issues
Master Table Query] by joining their ID's together. What
I want is to show the Auto number (in this case it'll be 1-
100) and show the latest status of that each corresponding
issue if status is showing (in this case ID 2,3 & 18).
However what I am getting currently is the record set of
2,3, & 18 only. I want it to show the records from 1 to
100, with satus for items 2,3, & 18. Please let me know
if you have any idea how to do this.

Please post the SQL view of this query, and some suggestions as to
which query (if either) contains all 100 ID's Since I know absolutely
nothing about the structure of your tables or the queries, all I can
do at the moment is suggest an Outer Join.
 
Here is the SQL code I used to get the latest status for
the respective ID's.

SELECT T.*
FROM [Issues SubTable] AS T
WHERE (((T.Date)=(SELECT Max(X.Date) FROM [Issues
SubTable] AS X WHERE T.ID =X.ID)));

This query is pulling from the master table which has all
100 ID's. However, if a particular ID does not contain
any date & status entry it will simply not output
anything. I would want to get it to output a blank if it
does not contain anything. Thanks for your help.
-----Original Message-----
Thank you for your previous advice. I believe I
miscommunicated my situation. I already have a query that
is joining the [Issues_SubTable Query] and the [Issues
Master Table Query] by joining their ID's together. What
I want is to show the Auto number (in this case it'll be 1-
100) and show the latest status of that each corresponding
issue if status is showing (in this case ID 2,3 & 18).
However what I am getting currently is the record set of
2,3, & 18 only. I want it to show the records from 1 to
100, with satus for items 2,3, & 18. Please let me know
if you have any idea how to do this.

Please post the SQL view of this query, and some suggestions as to
which query (if either) contains all 100 ID's Since I know absolutely
nothing about the structure of your tables or the queries, all I can
do at the moment is suggest an Outer Join.


.
 
Here is the SQL code I used to get the latest status for
the respective ID's.

SELECT T.*
FROM [Issues SubTable] AS T
WHERE (((T.Date)=(SELECT Max(X.Date) FROM [Issues
SubTable] AS X WHERE T.ID =X.ID)));

This query is pulling from the master table which has all
100 ID's. However, if a particular ID does not contain
any date & status entry it will simply not output
anything. I would want to get it to output a blank if it
does not contain anything. Thanks for your help.

I guess I'm confused. Is the name of your master table which contains
all 100 ID's [Issues SubTable]? If you have twenty records for an ID
in [Issues Subtable] and none of them have a Date or Status, which
record do you want to see?
 
Back
Top