OpenReport with WHERE clause problem

[SCubed]

I hope someone can help me! I've spent all day trying to get this to work!!

I have a report menu that allows users to select specific options or all, view/print, etc. I have collected their selections and formatted them into this WHERE clause:

lsWHERE = "Assets.EquipmentType = '" & globalEquipmentType & "' and Assets.AssetOwner in " & _
lsAssetOwner & " and Moves.DateMoved between DateValue('" & [Forms]![ReportMainMenu]![FromDate] & "') and " & _
"DateValue('" & [Forms]![ReportMainMenu]![ToDate] & "') and " & _
"Moves.DateMoved In (SELECT TOP 1 DateMoved FROM Moves WHERE " & _
" SerialNumber = Assets.SerialNumber ORDER BY DateMoved desc) "

Then I have this statement:

DoCmd.OpenReport globalOption, lsMode, , lsWHERE

I can print the WHERE clause in a MsgBox and it looks just like what I want it to be. However, when I execute the query, it prompts me for the EquipmentType and AssetOwner. Any help is appreciated!

SCubed

 

[grtammi]

SCubed:

I've always found that when Access is prompting me for values, either the report I am trying to open doesn't have the fields I'm requesting, or I've spelled the field names wrong in my query (yes, even 2-finger typists make mistakes from time to time )

PS: That's a pretty complex SQL statement - what exactly are you trying to do with it?

HTH

Greg Tammi, IT Design & Consultation
Work:

http://www.atsbell.com

Home:

http://members.shaw.ca/gregandliz

 

[SCubed]

I double-checked, again my names and that was not the problem. I have discovered the problem, but don't understand it. Apparently, Access requires you to have any columns included in the criteria in your select statement as well. This is not standard sql requirements, so it must be an Access "feature". Being new to Access, I was not familiar with it, but it did fix my problem. I thought I'd post the solution in case anyone was having the same problem.

Thanks,

SCubed

 

[SCubed]

I am still not getting this report to work as designed. I am past the original problem, but my report appears to be ignoring my WHERE clause. It brings me all the records that meets the sql statement attached to my report. If I take that sql statement adn attach my WHERE clause in a QUERY, I get exactly what I'm wanting. Any ideas?!?

Here is my WHERE clause I am building in my code:

lsWHERE = " [Assets].[EquipmentType] = '" & globalEquipmentType & "' and [Assets].[AssetOwner] in " & _
lsAssetOwner & " and " & _
"[Moves].[DateMoved] In (SELECT TOP 1 [DateMoved] FROM Moves WHERE " & _
" [SerialNumber] = [Assets].[SerialNumber] ORDER BY [DateMoved] desc) " & _
" ORDER BY [Moves].[EquipmentType], [Assets].[Description] "

 

[grtammi]

SCubed:

Since you're passing the WHERE clause in the report's OpenArgs, you can use that to create a SQL statement in the ReportOpen event of your report, and then set that SQL statement as your Report Recordsource, such as:

DoCmd.OpenReport globalOption, lsMode, , lsWHERE

and when the report in globalOption opens, you could then have:

Private Sub Report_Open(Cancel As Integer)
    ' check for opening arguments
    If Not IsNull(Me.OpenArgs) Then
        Me.RecordSource = "MY SQL SELECT STATEMENT HERE" & Me.OpenArgs
    Else
        ' no opening arguments, cancel opening of this report
        Cancel = True
    End If
End Sub

Hope that puts you on the right track ...



Greg Tammi, IT Design & Consultation
Work:

http://www.atsbell.com

Home:

http://members.shaw.ca/gregandliz

 

[SCubed]

I'm still doing something wrong. It doesn't like me using the word "OpenArgs". I've looked through the list of options on the "Me" object, but none made sense to me that it would store the passed arguments. The closest thing was "InputParameters", but it said I was using it incorrectly. Any help?!?

 

[grtammi]

can you send a stripped down copy of your database to me? I'll have a look & post a solution for you soon as I can.

gregandliz@shaw.ca

PS: Make sure you zip the db ... my Outlook likes to strip attachments ...



Greg Tammi, IT Design & Consultation
Work:

http://www.atsbell.com

Home:

http://members.shaw.ca/gregandliz