Run-time error 3846

[TomJ58]

I am testing a function in the Immediate window. The function uses input from two Inputboxs to select a query and and open a report based on the query selected.

     strGetShift = InputBox("Enter Shift - First, Second or Closing:")
     dtGetDate = InputBox("Enter Schedule Date:")

When I test the function using "Second" or "Closing" as the shift input, I receive the following error "The multi-valued field 'tblCostarJobMaster.SchedSequence.Value' in an ORDER BY must also appear in the SELECT list."

Second shift SQL statement

'Create a standard second shift schedule from following query:
        Case "Second"
           strShiftLogSQL = "SELECT tblCostarJobMaster.JobNumber, tblCostarJobMaster.JobName, tblCostarJobMaster.JobNotes, tblCostarJobMaster.SchedSequence.Value " & _
                            "FROM tblCostarJobMaster LEFT JOIN tblSequence ON tblCostarJobMaster.SchedSequence.Value = tblSequence.SequenceType " & _
                            "WHERE (((tblCostarJobMaster.JobStatus)=""Active"") " & _
                            "AND ((tblCostarJobMaster.SchedDay" & intCalcWeekdayNo & ")=True) AND ((tblCostarJobMaster.SchedShift)=""Second"")) " & _
                            "ORDER BY tblCostarJobMaster.SchedSequence.Value, tblCostarJobMaster.SchedTime;"

Closing shift SQL statememt

     'Create a standard closing schedule from the following query:
        Case "Closing"
           strShiftLogSQL = "SELECT tblCostarJobMaster.JobNumber, tblCostarJobMaster.JobName, tblCostarJobMaster.JobNotes, tblCostarJobMaster.SchedSequence.Value " & _
                            "FROM tblCostarJobMaster LEFT JOIN tblSequence ON tblCostarJobMaster.SchedSequence.Value = tblSequence.SequenceType " & _
                            "WHERE (((tblCostarJobMaster.JobStatus)=""Active"") AND ((tblCostarJobMaster.JobType)=""Closing"")) " & _
                            "ORDER BY tblCostarJobMaster.SchedSequence.Value, tblCostarJobMaster.SchedTime;"

The error is highlighted on the DoCmd.OpenReport code.

DoCmd.OpenReport "rptShiftLog", acViewPreview, strShiftLogSQL

Any help with this error would be appreciated.

 

[mdlueck]

Since you already are aware of the power of the Immediate window, I would suggest performing a:

Debug.Print strShiftLogSQL

one past initialization of the variable. That will output the current value into the Immediate window. Copy / paste from there into a QueryDef. Then you may debug the query interactively which might shed more light on what exactly is wrong with the query.

 

[TomJ58]

Thanks for the reply mdlueck; however, I have already done that in reverse. The SQL statement for "Closing" was copied from a query's SQL view:

SELECT tblCostarJobMaster.JobNumber, tblCostarJobMaster.JobName, tblCostarJobMaster.JobNotes, tblCostarJobMaster.SchedSequence.Value
FROM tblCostarJobMaster LEFT JOIN tblSequence ON tblCostarJobMaster.SchedSequence.Value = tblSequence.SequenceType
WHERE (((tblCostarJobMaster.JobStatus)="Active") AND ((tblCostarJobMaster.JobType)="Closing"))
ORDER BY tblCostarJobMaster.SchedSequence.Value, tblCostarJobMaster.SchedTime;

The SQL statement for "Second" is also a copy of a query's SQL view.

When I use either of the SQL views as the report's Record Source, the report works.

 

[mdlueck]

All right, next suspect:

tblCostarJobMaster.SchedSequence.Value

In VBA that looks correct, but in SQL I question including the .Value bit. Perhaps try removing all instances of .Value from the SQL.

 

[spikepl]

You are dooing 2 things here: using multivalued fileds which is not recommended, but if you can make it work then fine. But also, you are using a query as a filter. Having tried that a number of times I gave up on that feature, and I change the reports underlying record source instead. It also makes me wonder, whether the error message actually applies to the report's record source. If the field you want to ORDER BY is not there, then the thing obviously ought to complain.

I am also not too sure about how much attention the sort order of the report would pay to the filter - reports are weird that way and tend to only follow the sort order explicitly specified in the report itself.

 

[mdlueck]

reports are weird that way and tend to only follow the sort order explicitly specified in the report itself.

Agreed. I define a sort in the report UI itself, and happen to have a matching index on the table to assist with speeding up the sorting.

Queries I use to populate the FE temp table the report is bound to, I generally sort the same way as the report will wish to display the records in, hoping that if Access stacks the records into the FE temp table in that order, it will likely be fastest to populate the report.

I directly bind my reports to the FE temp table which supplies data to the report. I do not create a Query in-between the FE temp table and the report.

 

[TomJ58]

The .Value is the bound value of a multi-value field . I am trying to use the bound value as a sort parameter. The field options are (1,Before), (2,At) and (3,After). I am using the multi-value field to create a shift log report that starts at 7:00 PM and ends at 7:00 AM. This is the only way I know of to have a record with a time of 20:00 appear in the report ahead of a record with a time of 03:00 when time in assending order is also being used in the query.

 

[spikepl]

This is the only way I know of to have a record with a time of 20:00 appear in the report ahead of a record with a time of 03:00.

Yes - if you explicitly abstain from saving the date-component of the datetime. If you saved the date in the data then that problem would not exist.

 

[mdlueck]

The .Value is the bound value of a multi-value field.

ORDER BY [B][COLOR=Red]tblCostarJobMaster.SchedSequence.Value[/COLOR][/B], tblCostarJobMaster.SchedTime;

Form controls are not magically interpreted / read by the SQL engine. You need to do at the minimum some string concatenation in order to have VBA read the value of that control and insert its current value into the SQL string headed to the query engine.

Perhaps try code like:

ORDER BY " & tblCostarJobMaster.SchedSequence.Value & ", tblCostarJobMaster.SchedTime;

 

[TomJ58]

spikelp,

The shift logs are created in advance of when they are actually used. Therefore, only the time value is contained in each record and used to determine the order of the records. First shift is from 07:00 to 19:00 and second shift is from 19:00 to 07:00. First shift is easy to sort by time in assending order; however, second shift becomes the problem.

 

[TomJ58]

I was able to resolve this error with the following code:

     Set rst = dbs.OpenRecordset(strShiftLogSQL, dbOpenSnapshot)
     With dbs
        Set qdf = .CreateQueryDef("tmpShiftLog", strShiftLogSQL)
        DoCmd.OpenReport "rptShiftLog", acViewDesign
        With Reports!rptShiftLog
           .RecordSource = "tmpShiftLog"
        End With
        DoCmd.Save acReport, "rptShiftLog"
        DoCmd.Close acReport, "rptShiftLog"
        DoCmd.OpenReport "rptShiftLog", acViewPreview
        .QueryDefs.Delete "tmpShiftLog"
     End With
     dbs.Close
     qdf.Close

I first had set my query to a recordset then make the recordset a temporary query. By opening the report in design view, I was able to manipulate the form's Record Set property and assign it the temporary query. The report is then saved and closed with new Record Set. Finally, I reopen the report in preview mode. Using these steps in the function, allows me to use the same report fomat over and over again.