Passing Variables To More Than One Field As Query Criteria (1 Viewer)

[AlexDoran]

Hello Everyone! I'm new to the site and thought i would sign up to try and get some advice. Firstly i apologize if i'm being dense in any way as i have not had to do any VB for some time now and i'm REALLY struggling to get back into it :banghead:

My problem is as follows, i have created a report that calculates the total volume of FSC Materials. The user picks two dates from Calender controls that the report will range from. However the needs have now changed and i am required to make the report filter further based on user input, the problem i'm facing is that i cannot figure out a way to pass values from different variables to the report separate from another here is the code i would usually use to pass data to a query/report: (again i apologize if this looks dreadful)

Private Sub MonthlyFSC_Click()


Msg = MsgBox("Select the Start and Finish Dates you wish to Query.", , "Start / Finish")
Start = adhDoCalendar()
Finish = adhDoCalendar()

sql1 = "[ORDER DATE]<#" & Format(Finish, "MM/DD/YY") & "#"
sql2 = "[ORDER DATE]>#" & Format(Start, "MM/DD/YY") & "#"
sql = "((" & sql1 & ") And (" & sql2 & "))"


Dim stDocName As String
stDocName = "rptMonthlyFSCVolumeReport"
DoCmd.OpenReport stDocName, acPreview, , sql

However i am now trying to do this, but it gives me an error as it is trying to pass the values to one field:

Msg = MsgBox("Select the Start and Finish Dates you wish to Query.", , "Start / Finish")
Start = adhDoCalendar()
Finish = adhDoCalendar()

sql1 = "[ORDER DATE]<#" & Format(Finish, "MM/DD/YY") & "#"
sql2 = "[ORDER DATE]>#" & Format(Start, "MM/DD/YY") & "#"
sql = "((" & sql1 & ") And (" & sql2 & "))"


Dim stDocName As String
stDocName = "rptMonthlyFSCVolumeReport"

Filter = InputBox("Please accept 100 for FSC 100% Items or Enter 'Mix' for FSC Mix Items", "Input FSC Volume Report", "100")

Select Case Filter

    Case "100"
        
        sql3 = "[DESCRIPTION] Like *FSC* And Like *100*"
        DoCmd.OpenReport stDocName, acPreview, , sql & sql3
        
    Case "Mix"
    
        sql3 = "[DESCRIPTION] Like *FSC* And Not Like *100*"
        DoCmd.OpenReport stDocName, acPreview, , sql & sql3

End Select

I have done the rounds on the internet but couldn't find anything that exactly matched what i'm trying to do. I have been reading about OpenArgs but am struggling to get to grips with them!

Any advice or help would be greatly appreciated.

Thanks

Alex


EDIT: I have now updated to try and use OpenArgs using this method:

Msg = MsgBox("Select the Start and Finish Dates you wish to Query.", , "Start / Finish")
Start = adhDoCalendar()
Finish = adhDoCalendar()

sql1 = "[ORDER DATE]<#" & Format(Finish, "MM/DD/YY") & "#"
sql2 = "[ORDER DATE]>#" & Format(Start, "MM/DD/YY") & "#"
sql = "((" & sql1 & ") And (" & sql2 & "))"


Dim stDocName As String
stDocName = "rptMonthlyFSCVolumeReport"

Filter = InputBox("Please accept 100 for FSC 100% Items or Enter 'Mix' for FSC Mix Items", "Input FSC Volume Report", "100")

Select Case Filter

    Case "100"
        
        sql3 = "[DESCRIPTION] Like *FSC* And Like *100*"
        DoCmd.OpenReport stDocName, acPreview, , , , sql & sql3
        
    Case "Mix"
    
        sql3 = "[DESCRIPTION] Like *FSC* And Not Like *100*"
        DoCmd.OpenReport stDocName, acPreview, , , , sql & sql3

End Select

It is performing incorrectly within the case select and passing the wrong criteria, as it will only display results that meet the default values' criteria. However the date criteria is not be passed either. Does anybody have any idea why?

Thanks

 

[namliam]

Your problem is here:
sql & sql3

There will not be any "AND" or "OR" between the statements which there obviously needs to be....

Plus your like statement is "way off"

sql3 = " [COLOR="red"]AND ( [/COLOR][DESCRIPTION] Like [COLOR="red"]""[/COLOR]*FSC*[COLOR="red"]""[/COLOR] And [COLOR="Red"][DESCRIPTION][/COLOR] Like [COLOR="red"]""[/COLOR]*100*[COLOR="red"]"" ) [/COLOR]"

Please notice the red added parts as well as the AND infront, the brackets may or may not be needed depending on the total clause, but better be save than sorry.

 

[AlexDoran]

Hi namliam, thank you for the fast response and help. I have now managed to get the filter on [DESCRIPTION] to work correctly from the variable sql3.

However i am still at a loss as how i can also filter the [ORDER DATE] field using the value stored in sql. Unless i am misreading your reply?

So i am trying to pass the variables as follows:

Pass sql to [ORDER DATE]
Pass sql3 to [DESCRIPTION]

However i think the [WhereCondition] section of DoCmd.OpenReport only allows for one?

Sorry if i am being obviously stupid.

Thank You.

Alex.

 

[namliam]

The WhereCondition indeed allows for only one STRING, not one VARIABLE!

Using the variable you must make a complete valid where string to parse to your openreport method.

Thus the full combination of SQL & SQL3 must be valid, not 2 valid parts stuck together in an invalid way.

 

[AlexDoran]

Doh!! I understand your original reply now, i the report is now working as i wanted it to using the following:

Msg = MsgBox("Select the Start and Finish Dates you wish to Query.", , "Start / Finish")
Start = adhDoCalendar()
Finish = adhDoCalendar()

sql1 = "[ORDER DATE]<#" & Format(Finish, "MM/DD/YY") & "#"
sql2 = "[ORDER DATE]>#" & Format(Start, "MM/DD/YY") & "#"
sql = "((" & sql1 & ") And (" & sql2 & "))"


Dim stDocName As String
stDocName = "rptMonthlyFSCVolumeReport"

Filter = InputBox("Please accept 100 for FSC 100% Items or Enter 'Mix' for FSC Mix Items", "Input FSC Volume Report", "100")

Select Case Filter

    Case "100"
        
        sql3 = sql & "AND [DESCRIPTION] Like ""*FSC*"" And [DESCRIPTION] Like ""*100%*"""
        DoCmd.OpenReport stDocName, acPreview, , sql3
        
    Case "Mix"
    
        sql3 = sql & "AND [DESCRIPTION] Like ""*FSC*"" And [DESCRIPTION] Not Like ""*100%*"""
        DoCmd.OpenReport stDocName, acPreview, , sql3

End Select

Thank you again for the help it is greatly appreciated.

I think i need to get back to basics and refresh my skills!

Alex