469,284 Members | 2,505 Online
Bytes | Developer Community
New Post

Home Posts Topics Members FAQ

Post your question to a community of 469,284 developers. It's quick & easy.

Refer to field in query when tables use same name

reginaldmerritt
201 100+
I have an OpenForm with a where statement which throws up an error because the form i'm trying to open uses a query which has two fields from different tables sharing the same name.

Let me explain how this relates. I've create a Project Manager program.

I have a table for Projects (TBProjects) with fields 'ProjectID', 'ProjectName' and 'StatusID'(plus others).

Another table for the Tasks (TBTasks). The Tasks table has fields for 'TaskID', 'ProjectID', 'StartDate', 'EndDate' and 'StatusID'(plus others).

Then another table for Status (TBStatus) with the 'StatusID' indicating 1 for Not Started, 2 for In Progress and 3 for Completed.

I want to open a form that shows only those records in TBTasks that are for a specific Project(via ProjectID) and are (A) Not Started AND (B) Not Started and Over Due AND (C) Started but Over Due.

To do this the all three fields in TBTasks mentioned above must be used, 'StartDate', 'EndDate' and 'Status'.
Therefore my OpenForm looks quite complex

Expand|Select|Wrap|Line Numbers
  1. DoCmd.OpenForm "FRMTasksByProjectOverDue", , ,_
  2.  "([ProjectID] = " & ChosenProjectOverDue & "And [StatusID] = 1 And [StartDate] <= #" & Now & "# And [Enddate] <= #" & Now & "#)_
  3.  AND ([ProjectID] = " & ChosenProjectOverDue & "And [StatusID] = 1 And [StartDate] <= #" & Now & "# And [Enddate] >= #" & Now & "#)_
  4.  AND ([ProjectID] = " & ChosenProjectOverDue & "And [StatusID] = 2 And [Enddate] <= #" & Now & "#)"
  5.  
The Error Message I get is:
"The Specific field '[StatusID]' could refer to more than one table in the FROM clause of your SQL satatement."

The Query SQL is:
Expand|Select|Wrap|Line Numbers
  1. SELECT DISTINCT TBProjects.ProjectID, TBProjects.ProjectName,_ 
  2. TBProjects.StatusID, TBProjects.StaffID, TBTasks.TaskID,_ 
  3. TBTasks.Title, TBTasks.StaffID, TBTasks.StartDate, TBTasks.EndDate,_ 
  4. TBTasks.StatusID, TBProjects.ProjectStartDate, TBProjects.ProjectEnddate
  5. FROM TBProjects 
  6. INNER JOIN TBTasks ON TBProjects.ProjectID = TBTasks.ProjectID;
  7.  
Without the where statment the OpenForm works perfectly and I get no error message in regard to the SQL statement.

I've tried using [TBTasks.StatusID] instead of [StatusID] in the where statement but then i get a different error message
"You've enterted a expresion that has no value"

Is there something within the SQL statement that I have to do or do i need to change the syntax of the where statement?

Usually i would keep most of the criteria within the query itself but in this case I need 3 different types of criteria (A) Not Started,(B) Not Started and Over Due and (C) Started but Over Due. I could create 3 separate queries which will work using the conditions in either A, B and C. So perhaps is there a way of combining the records in 3 different queries onto 1 form ?

Thanks.
Aug 24 '10 #1

✓ answered by MikeTheBike

Hi

On the basis that the SQL is the Record Source for the ‘FRMTasksByProjectOverDue’ form, then I would suggest using aliases in this SQL like this

Expand|Select|Wrap|Line Numbers
  1. SELECT DISTINCT TBProjects.ProjectID, TBProjects.ProjectName,_  
  2. TBProjects.StatusID As ProjStatusID, TBProjects.StaffID, TBTasks.TaskID,_  
  3. TBTasks.Title, TBTasks.StaffID, TBTasks.StartDate, TBTasks.EndDate,_  
  4. TBTasks.StatusID As TaskStatusID, TBProjects.ProjectStartDate, TBProjects.ProjectEnddate 
  5. FROM TBProjects  
  6. INNER JOIN TBTasks ON TBProjects.ProjectID = TBTasks.ProjectID;
This will require suitable adjustment the Control Source of any controls bound to these fields.

Then mod the opem form command like this
Expand|Select|Wrap|Line Numbers
  1. DoCmd.OpenForm "FRMTasksByProjectOverDue", , ,_ 
  2.  "([ProjectID] = " & ChosenProjectOverDue & "And [ProjStatusID] = 1 And [StartDate] <= #" & Now & "# And [Enddate] <= #" & Now & "#)_ 
  3.  AND ([ProjectID] = " & ChosenProjectOverDue & "And [ProjStatusID] = 1 And [StartDate] <= #" & Now & "# And [Enddate] >= #" & Now & "#)_ 
  4.  AND ([ProjectID] = " & ChosenProjectOverDue & "And [TaskStatusID] = 2 And [Enddate] <= #" & Now & "#)" 
(Don't know which SatusID is which!)

HTH


MTB

17 7755
MikeTheBike
637 Expert 512MB
Hi

On the basis that the SQL is the Record Source for the ‘FRMTasksByProjectOverDue’ form, then I would suggest using aliases in this SQL like this

Expand|Select|Wrap|Line Numbers
  1. SELECT DISTINCT TBProjects.ProjectID, TBProjects.ProjectName,_  
  2. TBProjects.StatusID As ProjStatusID, TBProjects.StaffID, TBTasks.TaskID,_  
  3. TBTasks.Title, TBTasks.StaffID, TBTasks.StartDate, TBTasks.EndDate,_  
  4. TBTasks.StatusID As TaskStatusID, TBProjects.ProjectStartDate, TBProjects.ProjectEnddate 
  5. FROM TBProjects  
  6. INNER JOIN TBTasks ON TBProjects.ProjectID = TBTasks.ProjectID;
This will require suitable adjustment the Control Source of any controls bound to these fields.

Then mod the opem form command like this
Expand|Select|Wrap|Line Numbers
  1. DoCmd.OpenForm "FRMTasksByProjectOverDue", , ,_ 
  2.  "([ProjectID] = " & ChosenProjectOverDue & "And [ProjStatusID] = 1 And [StartDate] <= #" & Now & "# And [Enddate] <= #" & Now & "#)_ 
  3.  AND ([ProjectID] = " & ChosenProjectOverDue & "And [ProjStatusID] = 1 And [StartDate] <= #" & Now & "# And [Enddate] >= #" & Now & "#)_ 
  4.  AND ([ProjectID] = " & ChosenProjectOverDue & "And [TaskStatusID] = 2 And [Enddate] <= #" & Now & "#)" 
(Don't know which SatusID is which!)

HTH


MTB
Aug 24 '10 #2
NeoPa
32,173 Expert Mod 16PB
Good thinking Mike. Unfortunately I don't believe Jet SQL handles ALIASes in the WHERE clause though. To be fair Reginald was almost there when he tried [TBTasks.StatusID], as it should be [TBTasks].[StatusID]. TBTasks.StatusID may also work. The square brackets ([]) should surround elemental items only. Not qualified references. [TBTasks] and [StatusID], but never [TBTasks.StatusID].
Aug 24 '10 #3
MikeTheBike
637 Expert 512MB
Hi NeoPa

All I can say is that aliases have always worked for me where ever I have used then, including 'Where' clause opening forms!!

May be I havn't tried had enough?

MTB
Aug 24 '10 #4
reginaldmerritt
201 100+
Thanks guys. I assumed aliases wouldn't work. I'm working on it now, aliases seem to work.

Trouble with the dates in the where statement now, but will get that sorted shortly.

Will let you know if it works fully when I'm done.
Aug 24 '10 #5
NeoPa
32,173 Expert Mod 16PB
I may have jumped to an assumption there Mike. Certainly, a WHERE clause within the same SQL set doesn't recognise an ALIAS (in Jet SQL I mean). How form filters are applied is less clear. If it essentially merges the filter into the same SQL set as a WHERE clause then this wouldn't work. As it does (so you've reported), I assume the filter must be applied by enveloping the original SQL as a Subquery (See Subqueries in SQL).

This would be similar to :
Expand|Select|Wrap|Line Numbers
  1. SELECT *
  2. FROM ({Original SQL of form here}) AS sub
  3. WHERE {Filter parameter here}
Aug 24 '10 #6
NeoPa
32,173 Expert Mod 16PB
Reginald:
Trouble with the dates in the where statement now, but will get that sorted shortly.
You may find Literal DateTimes and Their Delimiters (#) helps with that :)
Aug 24 '10 #7
reginaldmerritt
201 100+
Aliases work but only for the first condition of the where statement.

i.e.
Expand|Select|Wrap|Line Numbers
  1. DoCmd.OpenForm "FRMTasksByProjectOverDue", , ,
  2.  "([ProjectID] = " & ChosenProjectOverDue & "
  3. And [TaskStatusID] = 1
  4. And [StartDate] <= #" & Format(Now, "dd/mm/yy") & "#
  5. And [Enddate] <= #" & Format(Now, "dd/mm/yy") & "#)" 
  6.  
But as soon as i add the second condition
Expand|Select|Wrap|Line Numbers
  1. DoCmd.OpenForm "FRMTasksByProjectOverDue", , ,
  2.  
  3.  "([ProjectID] = " & ChosenProjectOverDue & "
  4. And [TaskStatusID] = 1
  5. And [StartDate] <= #" & Format(Now, "dd/mm/yy") & "#
  6. And [Enddate] <= #" & Format(Now, "dd/mm/yy") & "#) 
  7.  
  8. AND ([ProjectID] = " & ChosenProjectOverDue & "
  9. And [TaskStatusID] = 1 
  10. And [StartDate] <= #" & Format(Now, "dd/mm/yy") & "# 
  11. And [Enddate] >= #" & Format(Now, "dd/mm/yy") & "#)"
  12.  
  13.  
i get an error message
You entered an expression that has no value

I think that might be down to the syntax of the where statement rather than the SQL aliases .
Aug 24 '10 #8
reginaldmerritt
201 100+
Strangely I get the same error for only the first condition when I split the where statement up.

Expand|Select|Wrap|Line Numbers
  1. Dim ConditionA, ConditionB, ConditionC As String
  2.  
  3. ' ConditionA ProgramID, Not Started, Start Date < Today, End Date > Today
  4. ConditionA = "([ProjectID] = " & ChosenProjectOverDue & "And [TaskStatusID] = 1 And [StartDate] <= #" & Format(Now, "m/d/y") & "# And [Enddate] <= #" & Format(Now, "m/d/y") & "#)"
  5.  
  6. 'Condition B Program ID, Not Started, Start Date < Today, End Date < Today
  7. ConditionB = "([ProjectID] = " & ChosenProjectOverDue & "And [TaskStatusID] = 1 And [StartDate] <= #" & Format(Now, "m/d/y") & "# And [Enddate] >= #" & Format(Now, "m/d/y") & "#)"
  8.  
  9. 'Condition C Program ID, In Progress, End Date < Today
  10. ConditionC = "([ProjectID] = " & ChosenProjectOverDue & "And [TaskStatusID] = 2 And [Enddate] <= #" & Format(Now, "dd/mm/yy") & "#)"
  11.  
  12. DoCmd.OpenForm "FRMTasksByProjectOverDue", , , ConditionA 
  13.  
Aug 24 '10 #9
reginaldmerritt
201 100+
I don't know whats going on here. If I cut it right back to just two criteria then ConditionA works.

for example if using

Expand|Select|Wrap|Line Numbers
  1. DoCmd.OpenForm "FRMTasksByProjectOverDue", , , ConditionA
  2.  
ConditionA = "([ProjectID] = " & ChosenProjectOverDue & "And [TaskStatusID] = 1)"

or

ConditionA = "([TaskStatusID] = 1 And [StartDate] <= #" & TodayFormated & "#)"

But as soon as I put in the 3rd criteria I get the same error message.
You entered an expression that has no value

Perhaps you can not put that many parts to a condition within a where statement?

By the way thanks for your replies Neo Pa and MikeTheBike.
Aug 24 '10 #10
NeoPa
32,173 Expert Mod 16PB
Reginald, your dates criteria are all wrong. I posted a link earlier which explained this in detail. It looks as if you may have checked out the linked article but possibly missed an important point within it. Dates in SQL commands must be in m/d/y order (or other completely unambiguous format). This is not dependent on local settings. It is globally true.
Aug 24 '10 #11
reginaldmerritt
201 100+
Thanks Neo Pa, I did go through the article. Added it as a favourite.

TodayFormated is Format(Now,"m/d/y"). I've been trying all different date formats to see if they work.

The error I'm experiencing seems not to be based on the date format as the where statement works with the date criteria as long as i only have two criteria within the condition.
Aug 24 '10 #12
NeoPa
32,173 Expert Mod 16PB
I see now that post #9 (I was looking at post #8 and didn't notice it was done differently in #9) has it round the right way. Technically, Date() is a better functions to use than Now() when only the date is required, but either works as well. Now() is only a problem because it always indicates to an experienced programmer that either the coder had intentions of working with the time element as well, or has little idea of what they're working within. Believe me, that's important to know when trying to help across a forum.

What I suggest you do now is to post the SQL that works, as well as the SQL that gives the error message.

NB. I'm not interested in seing your VBA here. Just the SQL strings.

If you need help with this then check the indented instructions below :
One of the most popular (frequently occurring rather than best liked) problems we get is with SQL strings being manipulated in VBA code.

The reason this is so difficult is that all the work is being done at a level of redirection. What I mean by this is that the coder is never working directly with the SQL itself, but rather with code which in turn, is relied on to produce the SQL that they are envisaging is required. It's rather similar to the problems coders have historically had dealing with pointers.

Anyway, a technique I often suggest to coders struggling with this (at any level. This happens to experienced coders too.) is to use either the MsgBox() function, or Debug.Print into the Immediate Pane of the debugger window, to display the value of the SQL in the string before using it (That's assuming you're not good with debugging generally. Personally I would trace to the line, then display the value prior to allowing execution of the string - See Debugging in VBA). It's really much easier to appreciate what a SQL string is meant to do, and where there may be problems, when you can see it in its entirety, and in its true form, rather than as the code is about to create it.

If the problem is still unclear then take the resultant SQL string (copied to clipboard in previous paragraph) and paste it into the SQL view of any query. From here you can try to switch to Design View. If that works, then you may try to run it (or simply switch to Datasheet View which shows data but doesn't apply any changes - for action queries).
Aug 24 '10 #13
reginaldmerritt
201 100+
Thank you Neo Pa I really appreciate the information. The more I learn the less I know.

Does Work
([ProjectID] = 10 And [TaskStatusID] = 1 And [StartDate] <= #24/08/2010#)

Does Not Work
([ProjectID] = 10 And [TaskStatusID] = 1 And [StartDate] <= #24/08/2010# And [Enddate] <= #24/08/2010#)

Incidental the SQL that works only works when i format the date as "dd/mm/yyyy". If i set the format to be "m/d/y" I get the following result.

([ProjectID] = 10 And [TaskStatusID] = 1 And [StartDate] <= #8/24/236#)
Aug 24 '10 #14
reginaldmerritt
201 100+
I've copied the table and then kept all but one record.
I've then manipulated the data so I can see how the program reacts.

From what I can tell I get the error message "You entered an expression that has no value " when there are no records that fit the criteria.
i.e. there is no StartDate <= TodayFormated. So it's not that the expression has no value but the resulting query has no records.

I have to test to see if the query will return a result before using it. I've done this by using DCount on each condition and then added all the conditions together if they show a result.

The following code now works!
Expand|Select|Wrap|Line Numbers
  1. Private Sub ChosenProjectOverDue_AfterUpdate()
  2. Dim ConditionABC, ConditionA, ConditionB, ConditionC As String
  3. Dim TodayFormated
  4.  
  5. ConditionABC = ""
  6. ConditionA = ""
  7. ConditionB = ""
  8. ConditionC = ""
  9.  
  10. TodayFormated = Format(Date, "dd/mm/yyyy")
  11.  
  12. ' ConditionA ProgramID, Not Started, Start Date < Today, End Date > Today
  13. ConditionA = "([ProjectID] = " & ChosenProjectOverDue & " And [TaskStatusID] = 1 And [StartDate] <= #" & TodayFormated & "# And [Enddate] >= #" & TodayFormated & "#)"
  14.  
  15. If DCount("TaskID", "QYTasks", "([ProjectID] = " & ChosenProjectOverDue & " And [TaskStatusID] = 1 And [StartDate] <= #" & TodayFormated & "#  And [Enddate] >= #" & TodayFormated & "#)") > 0 Then
  16. ConditionABC = ConditionA
  17. End If
  18.  
  19. 'Condition B Program ID, Not Started, Start Date < Today, End Date < Today
  20. ConditionB = "([ProjectID] = " & ChosenProjectOverDue & " And [TaskStatusID] = 1 And [StartDate] <= #" & TodayFormated & "# And [Enddate] <= #" & TodayFormated & "#)"
  21.  
  22. If DCount("TaskID", "QYTasks", "([ProjectID] = " & ChosenProjectOverDue & " And [TaskStatusID] = 1 And [StartDate] <= #" & TodayFormated & "# And [Enddate] <= #" & TodayFormated & "#)") > 0 Then
  23. If ConditionABC = "" Then
  24.     ConditionABC = ConditionB
  25. End If
  26. If ConditionABC <> "" Then
  27.     ConditionABC = ConditionABC & " OR " & ConditionB
  28. End If
  29. End If
  30.  
  31. 'Condition C Program ID, In Progress, End Date < Today
  32. ConditionC = "([ProjectID] = " & ChosenProjectOverDue & " And [TaskStatusID] = 2 And [Enddate] <= #" & TodayFormated & "#)"
  33.  
  34. If DCount("TaskID", "QYTasks", "([ProjectID] = " & ChosenProjectOverDue & "And [TaskStatusID] = 2 And [Enddate] <= #" & TodayFormated & "#)") > 0 Then
  35. If ConditionABC = "" Then
  36.     ConditionABC = ConditionC
  37. End If
  38. If ConditionABC <> "" Then
  39.     ConditionABC = ConditionABC & " OR " & ConditionC
  40. End If
  41. End If
  42.  
  43. If ConditionABC = "" Then 
  44.     ResponceA = MsgBox("No Overdue tasks for chosen project", vbInformation + vbOKOnly, "No Tasks")
  45. Else
  46.     DoCmd.OpenForm "FRMTasksByProjectOverDue", , , ConditionABC
  47. End If
  48.  
  49. Me.ChosenProjectOverDue = ""
  50. End Sub
  51.  
Neo Pa, thank you for the information on Debuging. I've selected MikeTheBike's reply as the best answer because it relates to the first post but I couldn't of got here without your Debuging help.
Aug 24 '10 #15
NeoPa
32,173 Expert Mod 16PB
I'm sorry Reginald. I should have known better. Format using "y" just gives the day number in the year. I was trying to specify m/d/yy or m/d/yyyy without having to say both. yyyy is always preferred to yy nowadays. There is no good reason for using two-digit years except in an extremely small number of cases where the display space is very restricted. For processing your data yyyy is always better than yy.

Reginald:
Does Work
Expand|Select|Wrap|Line Numbers
  1. ([ProjectID] = 10 And [TaskStatusID] = 1 And [StartDate] <= #24/08/2010#)
Does Not Work
Expand|Select|Wrap|Line Numbers
  1. ([ProjectID] = 10 And [TaskStatusID] = 1 And [StartDate] <= #24/08/2010# And [Enddate] <= #24/08/2010#)
This is saying I want only records where the [EndDate] <= [StartDate].

My guess is that there would be very few items where [EndDate] < [StartDate], and not many more where [EndDate] = [StartDate]. I expect this explains why no records were returned.

Moving on. let me say again that as someone working in Britain, you cannot afford to use the local date format (d/m/yyyy) for your SQL dates. 24 August will be fine (as it's entirely unambiguous and Access catches this and fixes it for you on the fly). When you start to use dates with days less than 13 things will go wrong. Consider 12 August for instance. If that is portrayed as 12/8/2010 then it will be interpreted by the SQL as 8 December. SQL is absolutely not local format aware.
Aug 25 '10 #16
reginaldmerritt
201 100+
Thanks, I understand what your saying. "m/d/yyyy" also works which I'm now using.
Aug 25 '10 #17
NeoPa
32,173 Expert Mod 16PB
Reginald:
Neo Pa, thank you for the information on Debuging. I've selected MikeTheBike's reply as the best answer because it relates to the first post but I couldn't have got here without your Debuging help.
Always a pleasure. Don't worry too much about the Best Answer thing. Sure it's good to get them, but the most important thing about them is that they give a shortcut to the answer for other searchers. Some threads are too complicated to make that very worthwhile, but overall it's a decent facility. I'm happy for Mike to get the credit for this one anyway. He deserves it :)
Aug 25 '10 #18

Post your reply

Sign in to post your reply or Sign up for a free account.

Similar topics

9 posts views Thread by Ton den Hartog | last post: by
5 posts views Thread by rdemyan via AccessMonster.com | last post: by
1 post views Thread by CARIGAR | last post: by
reply views Thread by zhoujie | last post: by
By using this site, you agree to our Privacy Policy and Terms of Use.