DoCmd.OpenForm, multiple criteria in WHERE clause

[SDM]

Hiya

Im quite new to all of this and need a bit of help with something.

Basically i'm trying to open a form using DoCmd.OpenForm and wnat to have multiple 2 criteria in the WHERE part (the criteria are stored in a string called stLinkCriteria) :

"[MEMBER NO]=" & Me![MEMBER NO]

"[CLASS]=" & "'" & Me![ClassName invisible] & "'"

I've tried an AND operator and god knows how many variations of the code but I get either get a Type Mismatch or it is unable to find fields etc etc.

I have no doubt the solution will be incredibly simple, but so far it has me stumped so any help would be greatly appreciated.

TIA

 

[Alexandre]

That should do:

docmd.OpenForm "YourFormName",,,"[MEMBER NO]=" & Me![MEMBER NO] & " AND [CLASS]= '" & Me![ClassName invisible] & "'"

Alex

 

[SDM]

aaaaaaaaaaaaaaaaaaaaaaaaah

silly sod

thankyou very much!

 

[Misiek]

sorry for digging out,
what to do if nothing selected in either of the combos but still want to see whatever there is based only single combo?

DoCmd.OpenReport "R_printTask", acPreview, , _
"tTaskCompleted = False And tUserFK = " & Me!cboMyActionsUser & " Or qaAreaFK = '" & Me!cboMyActionsArea & "'"

 

[Misiek]

Anyone please?

 

[bob fitz]

sorry for digging out,
what to do if nothing selected in either of the combos but still want to see whatever there is based only single combo?

DoCmd.OpenReport "R_printTask", acPreview, , _
"tTaskCompleted = False And tUserFK = " & Me!cboMyActionsUser & " Or qaAreaFK = '" & Me!cboMyActionsArea & "'"

Try using an IF/THEN/ELSE to test the value/s of the combo boxes and then create your OpenReport command accordingly.

 

[Misiek]

I managed to get this working:

Dim i As Integer
i = DCount("*", "Q_printTask", "tTaskCompleted=False AND (qaAreaFK=cboMyActionsArea OR isnull(cboMyActionsArea)) AND (tUserFK=cboMyActionsUser OR isnull(cboMyActionsUser))")
MsgBox "The count of rows is " & i

But I don't know how to convert it into the DOCMD.... , I'm struggling with quotation marks.

 

[Misiek]

made it!

Dim strWhere As String
Dim lngLen As Long
Dim strWhereTaskCompleted As String

strWhereTaskCompleted = "tTaskCompleted = False"

If Not IsNull(Me.cboMyActionsArea) Then
strWhere = strWhere & "([qaAreaFK] = " & Me.cboMyActionsArea & ") AND "
End If

If Not IsNull(Me.cboMyActionsUser) Then
strWhere = strWhere & "([tUserFK] = " & Me.cboMyActionsUser & ") AND "
End If

lngLen = Len(strWhere) - 5
If lngLen <= 0 Then 'print all
DoCmd.OpenReport "R_printTask", acPreview, , strWhereTaskCompleted
Else 'print by selected combos
strWhere = Left$(strWhere, lngLen)
DoCmd.OpenReport "R_printTask", acPreview, , strWhere & " And " & strWhereTaskCompleted

End If

another question:
Is that even possible to have a "where statement" in DoCmd.openreport.... with values from combo and if combo is blank?

or is above code the only way of dealing with combos with selected value and nulls?

 

[shoji]

Hi Misiek,

Another way is to create the WHERE clause in the form and send it to the report using OpenArgs.

In the form, you run DoCmd like:

    DoCmd.OpenForm 
    DoCmd.OpenReport "R_printTask", acPreview, , , , strWhere

When you open the report, you can retrieve strWhere immediately and apply that as the filter like:

Private Sub Report_Open(Cancel as Integer)
Dim strWhere As String

    strWhere = Nz(Me.OpenArgs)
    If strWhere <> "" Then
      Me.FilterOn = True
      Me.Filter = strWhere
    Else
      Me.FilterOn = False
    End If
    ...

By the way, what is this doing?

strWhereTaskCompleted = "tTaskCompleted = False"

Why can't you do it this way?

    strWhere = ""
    If Not IsNull(Me.cboMyActionsArea) Then
        strWhere = strWhere & "([qaAreaFK] = " & Me.cboMyActionsArea & ") AND "
    End If

    If Not IsNull(Me.cboMyActionsUser) Then
      strWhere = strWhere & "([tUserFK] = " & Me.cboMyActionsUser & ") AND "
    End If

    If strWhere <> "" Then
       strWhere = Left(strWhere, Len(strWhere) - 5)
    End If

    DoCmd.OpenReport "R_printTask", acPreview, , strWhere

Shoji

 

[Misiek]

strWhereTaskCompleted = "tTaskCompleted = False"

is looking for records without close date, (looking for nulls)

 

[shoji]

OK. In that case, instead of

strWhere = ""

you can simply use

strWhere = "tTaskCompleted = False AND "

You don't need a special string just for that condition.

Shoji

 

[arnelgp]

DoCmd.OpenReport "R_printTask", acPreview, , "tTaskCompleted = False" & _
iif(Isnull(Me.cboMyActionsArea),"", " And [qaAreaFK] = " & Me.cboMyActionsArea) & _
iif(isnull(Me.cboMyActionsUser),"", " AND [tUserFK] = " & Me.cboMyActionsUser)

 

[Misiek]

Shoji,
this didn't work, after clicking on the button nothing has happened.

DoCmd.OpenReport "R_printTask", acPreview, , "tTaskCompleted = False" & _
iif(Isnull(Me.cboMyActionsArea),"", " And [qaAreaFK] = " & Me.cboMyActionsArea) & _
iif(isnull(Me.cboMyActionsUser),"", " AND [tUserFK] = " & Me.cboMyActionsUser)

Thanks

I will just stick to my working version.

 

[down on the upside]

I am attempting something similar to SDM and have got it to work with one issue, when I use the command button to run the macro it opens up a message box asking for the value to be entered instead of referencing the control. The message box happens to reference the control. It is the first part of the where condition that generates the box (dbAFENumber). I am a bit of a rookie so I thought it might be spelling, but the spelling is correct. code below:

Dim dbeAFENumber As String
Dim dbePeriod As String
Dim txtMMdbAFENumber As String
Dim txtMMdbPeriod As String

DoCmd.OpenForm "Record Review and Update", , , "[dbAFENumber]=" & Me![txtMMdbAFENumber] & " AND [dbPeriod]='" & Me![txtMMdbPeriod] & "'"

 

[pbaldy]

What are the data types of the fields? You're treating the first as a number and the second as text. My guess is the first needs single quotes like the second.

 

[down on the upside]

hmm I updating the quotes but I might not be using the quotation marks correctly still coding below:

DoCmd.OpenForm "Record Review and Update", , , [dbAFENumber] = "& Me![txtMMdbAFENumber] & AND [dbPeriod]='" & Me![txtMMdbPeriod] & "'"

 

[pbaldy]

You didn't answer my question, but try:

DoCmd.OpenForm "Record Review and Update", , , "[dbAFENumber]='" & Me![txtMMdbAFENumber] & "' AND [dbPeriod]='" & Me![txtMMdbPeriod] & "'"

 

[down on the upside]

you guessed it, both fields were text and yes your code works, thanks!

 

[pbaldy]

No problem, and welcome to the site by the way!