Refer to field in query when tables use same name

[reginaldmerritt]

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

DoCmd.OpenForm "FRMTasksByProjectOverDue", , ,_
 "([ProjectID] = " & ChosenProjectOverDue & "And [StatusID] = 1 And [StartDate] <= #" & Now & "# And [Enddate] <= #" & Now & "#)_
 AND ([ProjectID] = " & ChosenProjectOverDue & "And [StatusID] = 1 And [StartDate] <= #" & Now & "# And [Enddate] >= #" & Now & "#)_
 AND ([ProjectID] = " & ChosenProjectOverDue & "And [StatusID] = 2 And [Enddate] <= #" & Now & "#)"
 

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:

SELECT DISTINCT TBProjects.ProjectID, TBProjects.ProjectName,_ 
TBProjects.StatusID, TBProjects.StaffID, TBTasks.TaskID,_ 
TBTasks.Title, TBTasks.StaffID, TBTasks.StartDate, TBTasks.EndDate,_ 
TBTasks.StatusID, TBProjects.ProjectStartDate, TBProjects.ProjectEnddate
FROM TBProjects 
INNER JOIN TBTasks ON TBProjects.ProjectID = TBTasks.ProjectID;
 

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.

[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

SELECT DISTINCT TBProjects.ProjectID, TBProjects.ProjectName,_  
TBProjects.StatusID As ProjStatusID, TBProjects.StaffID, TBTasks.TaskID,_  
TBTasks.Title, TBTasks.StaffID, TBTasks.StartDate, TBTasks.EndDate,_  
TBTasks.StatusID As TaskStatusID, TBProjects.ProjectStartDate, TBProjects.ProjectEnddate 
FROM TBProjects  
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

DoCmd.OpenForm "FRMTasksByProjectOverDue", , ,_ 
 "([ProjectID] = " & ChosenProjectOverDue & "And [ProjStatusID] = 1 And [StartDate] <= #" & Now & "# And [Enddate] <= #" & Now & "#)_ 
 AND ([ProjectID] = " & ChosenProjectOverDue & "And [ProjStatusID] = 1 And [StartDate] <= #" & Now & "# And [Enddate] >= #" & Now & "#)_ 
 AND ([ProjectID] = " & ChosenProjectOverDue & "And [TaskStatusID] = 2 And [Enddate] <= #" & Now & "#)" 

(Don't know which SatusID is which!)

HTH


MTB

[NeoPa]

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].

[MikeTheBike]

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

[reginaldmerritt]

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.

[NeoPa]

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 :

SELECT *
FROM ({Original SQL of form here}) AS sub
WHERE {Filter parameter here}

[NeoPa]

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 :)

[reginaldmerritt]

Aliases work but only for the first condition of the where statement.

i.e.

DoCmd.OpenForm "FRMTasksByProjectOverDue", , ,
 "([ProjectID] = " & ChosenProjectOverDue & "
And [TaskStatusID] = 1
And [StartDate] <= #" & Format(Now, "dd/mm/yy") & "#
And [Enddate] <= #" & Format(Now, "dd/mm/yy") & "#)" 
 

But as soon as i add the second condition

DoCmd.OpenForm "FRMTasksByProjectOverDue", , ,
 
 "([ProjectID] = " & ChosenProjectOverDue & "
And [TaskStatusID] = 1
And [StartDate] <= #" & Format(Now, "dd/mm/yy") & "#
And [Enddate] <= #" & Format(Now, "dd/mm/yy") & "#) 
 
AND ([ProjectID] = " & ChosenProjectOverDue & "
And [TaskStatusID] = 1 
And [StartDate] <= #" & Format(Now, "dd/mm/yy") & "# 
And [Enddate] >= #" & Format(Now, "dd/mm/yy") & "#)"
 
 

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 .

[reginaldmerritt]

Strangely I get the same error for only the first condition when I split the where statement up.

Dim ConditionA, ConditionB, ConditionC As String
 
' ConditionA ProgramID, Not Started, Start Date < Today, End Date > Today
ConditionA = "([ProjectID] = " & ChosenProjectOverDue & "And [TaskStatusID] = 1 And [StartDate] <= #" & Format(Now, "m/d/y") & "# And [Enddate] <= #" & Format(Now, "m/d/y") & "#)"
 
'Condition B Program ID, Not Started, Start Date < Today, End Date < Today
ConditionB = "([ProjectID] = " & ChosenProjectOverDue & "And [TaskStatusID] = 1 And [StartDate] <= #" & Format(Now, "m/d/y") & "# And [Enddate] >= #" & Format(Now, "m/d/y") & "#)"
 
'Condition C Program ID, In Progress, End Date < Today
ConditionC = "([ProjectID] = " & ChosenProjectOverDue & "And [TaskStatusID] = 2 And [Enddate] <= #" & Format(Now, "dd/mm/yy") & "#)"
 
DoCmd.OpenForm "FRMTasksByProjectOverDue", , , ConditionA 
 

[reginaldmerritt]

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

DoCmd.OpenForm "FRMTasksByProjectOverDue", , , ConditionA
 

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.

[NeoPa]

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.

[reginaldmerritt]

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.

[NeoPa]

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).

[reginaldmerritt]

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#)

[reginaldmerritt]

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!

Private Sub ChosenProjectOverDue_AfterUpdate()
Dim ConditionABC, ConditionA, ConditionB, ConditionC As String
Dim TodayFormated
 
ConditionABC = ""
ConditionA = ""
ConditionB = ""
ConditionC = ""
 
TodayFormated = Format(Date, "dd/mm/yyyy")
 
' ConditionA ProgramID, Not Started, Start Date < Today, End Date > Today
ConditionA = "([ProjectID] = " & ChosenProjectOverDue & " And [TaskStatusID] = 1 And [StartDate] <= #" & TodayFormated & "# And [Enddate] >= #" & TodayFormated & "#)"
 
If DCount("TaskID", "QYTasks", "([ProjectID] = " & ChosenProjectOverDue & " And [TaskStatusID] = 1 And [StartDate] <= #" & TodayFormated & "#  And [Enddate] >= #" & TodayFormated & "#)") > 0 Then
ConditionABC = ConditionA
End If
 
'Condition B Program ID, Not Started, Start Date < Today, End Date < Today
ConditionB = "([ProjectID] = " & ChosenProjectOverDue & " And [TaskStatusID] = 1 And [StartDate] <= #" & TodayFormated & "# And [Enddate] <= #" & TodayFormated & "#)"
 
If DCount("TaskID", "QYTasks", "([ProjectID] = " & ChosenProjectOverDue & " And [TaskStatusID] = 1 And [StartDate] <= #" & TodayFormated & "# And [Enddate] <= #" & TodayFormated & "#)") > 0 Then
If ConditionABC = "" Then
    ConditionABC = ConditionB
End If
If ConditionABC <> "" Then
    ConditionABC = ConditionABC & " OR " & ConditionB
End If
End If
 
'Condition C Program ID, In Progress, End Date < Today
ConditionC = "([ProjectID] = " & ChosenProjectOverDue & " And [TaskStatusID] = 2 And [Enddate] <= #" & TodayFormated & "#)"
 
If DCount("TaskID", "QYTasks", "([ProjectID] = " & ChosenProjectOverDue & "And [TaskStatusID] = 2 And [Enddate] <= #" & TodayFormated & "#)") > 0 Then
If ConditionABC = "" Then
    ConditionABC = ConditionC
End If
If ConditionABC <> "" Then
    ConditionABC = ConditionABC & " OR " & ConditionC
End If
End If
 
If ConditionABC = "" Then 
    ResponceA = MsgBox("No Overdue tasks for chosen project", vbInformation + vbOKOnly, "No Tasks")
Else
    DoCmd.OpenForm "FRMTasksByProjectOverDue", , , ConditionABC
End If
 
Me.ChosenProjectOverDue = ""
End Sub
 

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.

[NeoPa]

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

([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#)

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.

[reginaldmerritt]

Thanks, I understand what your saying. "m/d/yyyy" also works which I'm now using.

[NeoPa]

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 :)