Open Form with multiple Criteria (1 Viewer)

jeran042 · Apr 17, 2018

I believe I am missing something so obvious. I am trying to open a form with multiple criteria in VBA, here is what I have:

Code:

Private Sub Text2_Click()

Dim iCost_Center As Integer
Dim sCategory As String
Dim sWhere As String
Dim sFormName As String


iCost_Center = Me.txtCOST_CENTER
sCategory = Me.txtTemp_Month_Temp_Per
sWhere = "[COST_CENTER] = " & iCost_Center And "[CATEGORY] = " & "'" & sCategory & "'"   'This line is throwing up a Runtime Error 13
sFormName = "frm: Ledger Detail"


DoCmd.OpenForm sFormName, acViewNormal, , sWhere, , acDialog


End Sub

Is there something I need to do to account for when combining an integer and a string in a WHERE clause?

Very much appreciated,

MarkK · Apr 17, 2018

Code:

[COLOR="DarkRed"]"[COST_CENTER] = " & iCost_Center[/COLOR] [COLOR="Blue"]And[/COLOR] [COLOR="darkred"]"[CATEGORY] = " & "'" & sCategory & "'"[/COLOR]

In your code, the two parts in red are strings, and you are trying to AND them together with a boolean operator. This yields a type mismatch. Probably you mean for the AND to be inside the string?
hth
Mark

jeran042 · Apr 17, 2018

Markk,

You were correct, I did not have the appropriate quotation marks, therefore it was reading the whole line as a string as opposed to the two values I was trying to pull,

The final string looked like this:

Code:

sWhere = "[COST_CENTER] = " & iCost_Center & " And [CATEGORY] = " & "'" & sCategory & "'"

Thank you so much!

MarkK · Apr 17, 2018

Another small tweak you can do is...

Code:

sWhere = "[COST_CENTER] = " & iCost_Center & " And [CATEGORY] = '" & sCategory & "'"

See what changed?
I also sometimes code that as...

Code:

sWhere = _
   "[COST_CENTER] = " & iCost_Center & " " & _
   "And [CATEGORY] = '" & sCategory & "'"

...which I think makes it slightly more readable, but that is very much personal taste.
Mark

jeran042 · Apr 17, 2018

MarkK said:
Another small tweak you can do is...

Code:

sWhere = "[COST_CENTER] = " & iCost_Center & " And [CATEGORY] = '" & sCategory & "'"

See what changed?
I also sometimes code that as...

Code:

sWhere = _ "[COST_CENTER] = " & iCost_Center & " " & _ "And [CATEGORY] = '" & sCategory & "'"

...which I think makes it slightly more readable, but that is very much personal taste.
Mark

Markk,

Thank you again. I much prefer to look at my criteria on multiple lines.
I have used this method in the past for SQL, not sure why I didn't apply that here

Open Form with multiple Criteria (1 Viewer)

jeran042

Registered User.

MarkK

bit cruncher

jeran042

Registered User.

MarkK

bit cruncher

jeran042

Registered User.

Similar threads

Users who are viewing this thread