Access Multiple Criteria OpenForm

[Beejer]

Greeting,

I'm trying to launch an OpenForm function with a double criteria. I
can't seem to make them work together, the two criteria work seperatly
so I assume my code to synchronise both together isn't working.

The idea is to open a form that will sum sales per month. I wish to
have the form filtered on open by year and by currency.

Here's my code:

Private Sub Commande13_Click()

Dim stDocName, var1, var2 As String
Dim stLinkCriteria As String

If IsNull(Me.Combo1) Or Me.Combo1 = "" Then
var1 = " "
Else
var1 = "CAPYear=" & Me.Combo1
End If
If IsNull(Me.Combo2) Or Me.Combo2 = "" Then
var2 = " "
Else
var2 = "CAPCurrency=" & "'" & Me.Combo2 & "'"
End If

stLinkCriteria = var1 + var2
' (I've tried using AND instead but I get an incompatibility error,
probably numeric VS Text)

stDocName = "FRM_MonthSales"

DoCmd.OpenForm stDocName, , , stLinkCriteria

Exit_Commande13_Click:
Exit Sub
Err_Commande13_Click:
MsgBox Err.Description
Resume Exit_Commande13_Click
End Sub

Thank for all the help!!!

[paii, Ron]

"Beejer" <bj********@yahoo.ca> wrote in message
news:11**********************@g44g2000cwa.googlegr oups.com...

Greeting,

I'm trying to launch an OpenForm function with a double criteria. I
can't seem to make them work together, the two criteria work seperatly
so I assume my code to synchronise both together isn't working.

The idea is to open a form that will sum sales per month. I wish to
have the form filtered on open by year and by currency.

Here's my code:

Private Sub Commande13_Click()

Dim stDocName, var1, var2 As String
Dim stLinkCriteria As String

If IsNull(Me.Combo1) Or Me.Combo1 = "" Then
var1 = " "
Else
var1 = "CAPYear=" & Me.Combo1
End If
If IsNull(Me.Combo2) Or Me.Combo2 = "" Then
var2 = " "
Else
var2 = "CAPCurrency=" & "'" & Me.Combo2 & "'"
End If

stLinkCriteria = var1 + var2
Try something like this

if var1 <> " " then
strLinkCriteria = var1
if var2 <> " " then
strLinkCriteria = strLinkCriterial & " AND " & var2
endif
else
if var2 <> " " then
strLinkCriteria = var2
endif
endif

You may need to add some parentheses around your criteria.
' (I've tried using AND instead but I get an incompatibility error,
probably numeric VS Text)

stDocName = "FRM_MonthSales"

DoCmd.OpenForm stDocName, , , stLinkCriteria

Exit_Commande13_Click:
Exit Sub
Err_Commande13_Click:
MsgBox Err.Description
Resume Exit_Commande13_Click
End Sub

Thank for all the help!!!

[Beejer]

I've added your suggestion and it worked very nicely. Didn't have to
add anything to it except correct the typo in :

strLinkCriterial & " AND " & var2

by removing the "l" in strLinkCriteria

Thanks

Beejer

[paii, Ron]

Glad I could help
The compiler does a good job finding typos!
A good reason to use Option Explicit.

"Beejer" <bj********@yahoo.ca> wrote in message
news:11**********************@z14g2000cwz.googlegr oups.com...

I've added your suggestion and it worked very nicely. Didn't have to
add anything to it except correct the typo in :

strLinkCriterial & " AND " & var2

by removing the "l" in strLinkCriteria

Thanks

Beejer

[paul]

And along the same lines but in a different manner here's something I
put together that may give you some ideas about making time periods and
other criteria dynamic.

The free downloadable sample database at www.bullschmidt.com/access
uses the query by form concept so that on the invoices dialog one can
optionally choose a rep, a customer, and perhaps a date range, click on
a button that says "Input," and then have the invoice form open up
showing all the invoices that match the criteria.

Best regards,
J. Paul Schmidt, Freelance Web and Database Developer
http://www.Bullschmidt.com
Access Database Sample, Web Database Sample, ASP Design Tips