3061: Too few parameters. Expected 1.

[Stang70Fastback]

I'm having trouble trying to get one snippet of code to function properly, and end up with the error in the title of this thread.

Option Compare Database

Private Sub Report_Load()

If (TempVars!ShowBuses) Then
DoCmd.SetProperty "txtBus", acPropertyVisible, "-1"
Else
DoCmd.SetProperty "txtBus", acPropertyVisible, "0"
End If

Dim rs As Recordset
Dim strSQL As String
strSQL = "SELECT FixedRoutes.[Shift Description] FROM FixedRoutes WHERE (((Left([FixedRoutes].[Shift Description],3))='11B'))"
Set rs = CurrentDb.OpenRecordset(strSQL)
If rs.RecordCount > 0 Then
MsgBox "This is a full service weekday!"
End If

End Sub

I would have thought it would be an error in the query statement, but if I create a new query using what I wrote, it works just fine and pulls that one record. So now I don't know what's wrong. :banghead:

Any thoughts?

 

[Alansidman]

It look like you have an extra closed parens at the end of the Left statement that should be at the end of the SQL statement. Also, not sure if necessary in VBA, but usually SQL statements end with a semi-colon.

alan

 

[Stang70Fastback]

I don't believe the semicolon is necessary, but I have tried it both with and without to no avail. I also moved the closed parens around with no success. I still get that error not matter what I do!

 

[boerbende]

think also that the right ) is not correct. The statement is:
Left([FixedRoutes].[Shift Description],3)='11B' or
(Left([FixedRoutes].[Shift Description],3)='11B')
You work with
(Left([FixedRoutes].[Shift Description],3))='11B'

 

[Stang70Fastback]

Yeah, those parenthesis confuse me too. The original statement didn't have that many, but when I made a new query and put this statement in it to see if it would work, this was the "auto-corrected" statement that it spit back at me. As I said, it works fine as a stand-alone query in access, but using it in this .OpenRecordset statement leads to the error I am getting.

For the record, I have tried both of your alternative suggestions, boerbende, but I still get the same result.

 

[jdraw]

You could try

strSQL = "SELECT [Shift Description] FROM FixedRoutes " _
 & " WHERE [Shift Description]  Like '11B*'"
Set rs = CurrentDb.OpenRecordset(strSQL)

It would help the reader if you showed all the code in a particular routine.

 

[Stang70Fastback]

Thanks for your help. I have amended the original post with the entire block of code. I tried your suggestion but I still get the same error.

I'm starting to think it isn't an error with the way the statement is written, but more an error related to the way in which I have implemented the OpenRecordset command, since, as I said earlier, the statement I have works fine as its own query.

Perhaps if I explain my goal, you might have a better suggestion than what I am trying to do. I am merely trying to come up with a way of determining which paper size to set the report to before the print window appears (this report appears and then the print command is fed to it.) On some days, the report is large enough to require Legal paper, and on other days, Letter will suffice. I know that the only days when we have enough shifts for "Legal" paper is when the 11A shift is present, so my goal was to check if the 11A was part of the query, and if so, to set the paper to Legal.

Is there perhaps a better way to go about it? I wish there was something as simple as, "If this won't fit on letter, make it legal."

Again, thanks for your help!

 

[jdraw]

Please post all of the vba code in your routine.

 

[Stang70Fastback]

Here you go. This is what runs when my Report is opened. The first little bit you can ignore - that has to do with setting the visibility of something on the report.

Option Compare Database

Private Sub Report_Load()

If (TempVars!ShowBuses) Then
DoCmd.SetProperty "txtBus", acPropertyVisible, "-1"
Else
DoCmd.SetProperty "txtBus", acPropertyVisible, "0"
End If

Dim rs As Recordset
Dim strSQL As String
strSQL = "SELECT FixedRoutes.[Shift Description] FROM FixedRoutes WHERE (((Left([FixedRoutes].[Shift Description],3))='11B'))"
Set rs = CurrentDb.OpenRecordset(strSQL)
If rs.RecordCount > 0 Then
MsgBox "This is a full service weekday!"
End If

End Sub

 

[jdraw]

Try this code and see what happends

Private Sub Report_Load()

    On Error GoTo Report_Load_Error

    If (TempVars!ShowBuses) Then
        DoCmd.SetProperty "txtBus", acPropertyVisible, "-1"
    Else
        DoCmd.SetProperty "txtBus", acPropertyVisible, "0"
    End If

    Dim rs As Recordset
    Dim strSQL As String
    strSQL = "SELECT FixedRoutes.[Shift Description] FROM FixedRoutes WHERE (((Left([FixedRoutes].[Shift Description],3))='11B'))"
    Debug.Print "rendered sql  <"  & strsql & " > "
    Set rs = CurrentDb.OpenRecordset(strSQL)
    If not rs.eof then
     rs.movelast
     rs.movefirst
    End If
    If rs.RecordCount > 0 Then
        MsgBox "This is a full service weekday!"
    End If

    On Error GoTo 0
    Exit Sub

Report_Load_Error:

    MsgBox "Error " & Err.number & " (" & Err.Description & ") in procedure Report_Load of Module AWF_Related"

End Sub

 

[Stang70Fastback]

I get the same error, and it highlights the same line of code:

Set rs = CurrentDb.OpenRecordset(strSQL)

Might there be an alternative means of accomplishing what I want to do? I could probably get away with just counting the total number of records in the query, since there is a big enough difference between the shifts depending on the days that I could pick a number in the middle and just do a greater than statement. Not even sure how to do that, though. I tried messing with the Count function but I couldn't get that working either.

Basically I suck at VBA.

 

[jdraw]

What error message was given?
Did you get a print out in the immediate window?

a 3061 often indicates a spelling error

You have this code in a Report Load Event is that where it belongs?
Can you post a copy of you database --remove anything private/confidential? Best to run Compact and Repair first, then make a zip file.

 

[Stang70Fastback]

Quite honestly, it would be a lot of work to remove all the confidential information. Not trying to be lazy, it would just take some time.

It is the same 3061 error that popped up before the report would open.

The reason I have it in the On Load event of the form is because while researching the .PaperSize command, I learned (perhaps incorrectly) that this must be done when the Report is loaded. Perhaps this is not the case. I don't know.

A spelling error is unlikely because, as I've said, it all works fine if I create a stand-alone query and use the exact same SELECT/FROM/WHEN statement. I'm starting to think I should just give up on this entirely and pursue a different means of accomplishing what I want (some method of counting the total number of Query records as I said earlier, for example) because I've been trying to get this to work for the better part of half a day now and I have made ZERO progress. Blah!

 

[spikepl]

If FixedRoutes is not a table but a query that refers to some control on a form, then that would fit the error message. What is FixedRoutes?

 

[Stang70Fastback]

FixedRoutes is indeed a query that filters out "today's" shifts from another query which holds the entire week's worth of shifts, which itself is the result of combining one .CSV file with the shifts, with an Excel table that holds the vehicle assignments for those shifts.

 

[jdraw]

Please post the SQL for the query FixedRoutes.

 

[Stang70Fastback]

Here ya go.

SELECT *
FROM ShiftsBuses
WHERE (((ShiftsBuses.[Day Of Week])=[TempVars]![Day]) AND ((ShiftsBuses.Category) In ("A","ATR","B","BTR","C","CTR","D","DTR","E","ETR","F","FTR","L","LTR","M","MTR","N","NTR","O","OTR","P","PTR","Q","QTR","R","RTR")) AND ((ShiftsBuses.[Employee Last Name]) Is Null Or (ShiftsBuses.[Employee Last Name])<>"ACCOUNT PENDING"));

 

[Stang70Fastback]

I could use this code instead:

SELECT COUNT( * ) FROM FixedRoutes

That simply, if I understand correctly, returns the number of records in the FixedRoutes query, but I am unsure as to how to set that equal to a variable.