field could refer to more than one table listed in the FROM clause of your SQL

[sschmidt460]

SELECT [Inventory Table].*, [Inventory Table].Part, [Inventory Table].Equipment, Nz(DSum("[Quantity]","[Return Table]","[Part]='" & [Inventory Table].[Part] & "'"))-Nz(DSum("[Quantity]","[Requisition Table]","[Part]='" & [Inventory Table].[Part] & "'"))+Nz(DSum("[Quantity]","[Add Stock Table]","[Part]='" & [Inventory Table].[Part] & "'"))+Nz(DSum("[Quantity]","[Adjustment Table]","[Part]='" & [Inventory Table].[Part] & "'"))+Nz(DSum("[Quantity on Hand]","[Inventory Table]","[Part]='" & [Inventory Table].[Part] & "'")) AS [Current], [Current]*[Cost] AS TotalCost
FROM [Inventory Table]
WHERE ((([Inventory Table].Equipment)=[Forms]![Report Form]![Equipment Combo]))
ORDER BY [Inventory Table].Part;

[Seth Schrock]

That seems strange considering you only have one table in your FROM clause. Try adding [Inventory Table]. before your cost field and see if that fixes it.

[sschmidt460]

I tried adding the [inventory table] before, but had no success. Yea I am not sure how it is possible to have that error coming up

[Seth Schrock]

Try removing the second field listed as it would be duplicated in the all fields * already listed.

[Rabbit]

Seth has the answer. This is the same problem you experienced in your other thread. The answer to both threads is the same, you shouldn't have more than one field in the returned data set that have the same name. When you try to use it later down the road, it's not going to know which field you're referring to if they have the same name.

[Seth Schrock]

@Rabbit I have accidently done this in the past and it just assigned it the alias Expr001, but I never got an error. I was just grasping at straws to guess this. Is it because the * was used in the case and not just referencing the field name twice (which is what I have done)?

[Rabbit]

Yes, it's probably the asterisk that caused the query editor to not realize there was an existing field with the same name so it didn't assign it an alias.

It would only come up if you try to reference the field later on, say in a form, report, or a new query that uses that query as a base.

If you're just viewing the data returned from the query, there's usually not an issue because it just uses the order of the fields to display them.

[sschmidt460]

Ok I fixed it, thanks for the help. The query works now, however I now have a problem with my "submit button" on the form not working now. Any suggestions?

Private Sub Submit_Button_Click()
If IsNull(Me.Type_Text) Or Me.Type_Text = "" Then
    MsgBox "Please select criteria for Report Type.", vbExclamation, "Attention"
Else
    If Me.Type_Text = "Requisitions" Then
        DoCmd.OpenForm "Date Form"
    Else
        If [Forms]![Report Form]![Equipment Combo] = "" Then
            DoCmd.OpenReport "Equipment Inventory Report", acViewPreview
        End If
    End If
End If
End Sub

[sschmidt460]

I believe the problem is after the 2nd else as the requisitions option works fine for me.

[Seth Schrock]

You will need to post this new question in a new thread.