-1

I created a query of what I want. I then looked at the SQL View window in Access to copy/paste into VBA. The only thing I changed is the "WHERE" section to include an AutoNumber ID (here referenced as num_PM which is a textbox in a form) I've looked at this for a while and I can't see what I am doing wrong. I am now getting a

error 2465 can't find the field '|1' referred to in your expression.

Set rst_UnitEntryCounts = CurrentDb.OpenRecordset("SELECT tbl_UnitTypes.UnitTypes_WeeklyTextBoxUserEntryForm, " _
                                                    & "        tbl_UnitTypes.UnitTypes_CumalativeTextBoxUserEntryForm, " _
                                                    & "        tbl_PMEntry.PMEntry_Week_Ending, " _
                                                    & "        Sum(tbl_UnitEntry.UnitEntry_Unit_Count) AS SumOfUnitEntry_Unit_Count " _
                                                    & "FROM (tbl_UnitTypes " _
                                                    & "         INNER JOIN tbl_UnitEntry ON tbl_UnitTypes.UnitTypes_ID = tbl_UnitEntry.UnitEntry_UnitTypes_ID) " _
                                                    & "         INNER JOIN (tbl_PMHeader INNER JOIN tbl_PMEntry ON tbl_PMHeader.PMHeader_ID = tbl_PMEntry.PMEntry_PMHeader_ID) ON tbl_UnitEntry.UnitEntry_PMEntry_ID = tbl_PMEntry.PMEntry_PMHeader_ID " _
                                                    & "WHERE ((tbl_PMHeader.PMHeader_ID) = " & num_PM & " )" _
                                                    & "GROUP BY tbl_UnitTypes.UnitTypes_WeeklyTextBoxUserEntryForm, " _
                                                    & "          tbl_UnitTypes.UnitTypes_CumalativeTextBoxUserEntryForm, " _
                                                    & "          tbl_PMEntry.PMEntry_Week_Ending")
Erik A
  • 31,639
  • 12
  • 42
  • 67
Dan
  • 21
  • 4
  • try to Debug.Print the sql statement to check the value of num_PM – winghei Sep 13 '16 at 16:52
  • Im new to access/vba. Do I just type this in the VBA window? or the immediate window? – Dan Sep 13 '16 at 16:59
  • it should be on vba. since you need the reference on num_PM e.g. `Debug.Print "SELECT.... "`. – winghei Sep 13 '16 at 17:01
  • Thank you, I did print out what I was expecting (an autonumber id): See partial code below: `WHERE ((tbl_PMHeader.PMHeader_ID) = 802 ) GROUP BY tbl_UnitTypes.UnitTypes_WeeklyTextBoxUserEntryForm, tbl_UnitTypes.UnitTypes_CumalativeTextBoxUserEntryForm, tbl_PMEntry.PMEntry_Week_Ending` ... – Dan Sep 13 '16 at 17:25
  • did you solved your issue? – winghei Sep 13 '16 at 18:08
  • This looks very similar to your unanswered question http://stackoverflow.com/questions/39460488/use-text-from-record-to-paste-into-an-access-form-controlbox - comments were same for that one - Assign your sql to a variable - and use Debug.Print to print the actual SQL to the Immediate Window - then copy/paste into your question - or to troubleshoot yourself - paste it into a SQL query window to debug – dbmitch Sep 13 '16 at 20:04
  • 1
    [How to debug dynamic SQL in VBA](http://stackoverflow.com/questions/418960/managing-and-debugging-sql-queries-in-ms-access/1099570#1099570) – Andre Sep 14 '16 at 11:46

1 Answers1

0

You statement is simply missing a required space character.

Here's the issue:

& "WHERE ((tbl_PMHeader.PMHeader_ID) = " & num_PM & " )" _
& "GROUP BY tbl_UnitTypes.UnitTypes_WeeklyTextBoxUserEntryForm, " _

)GROUP doesn't refer to anything... solution: 

& "WHERE ((tbl_PMHeader.PMHeader_ID) = " & num_PM & ") " _
& "GROUP BY tbl_UnitTypes.UnitTypes_WeeklyTextBoxUserEntryForm, " _

This is assuming that you haven't made any field name spelling errors. I notice that you have a field called UnitTypes_CumalativeTextBoxUserEntryForm where the correct spelling is cumulative but as long as you've been consistent that shouldn't be an issue.

LiamH
  • 1,492
  • 3
  • 20
  • 34