Go Back   Access World Forums > Microsoft Access Discussion > Modules & VBA

 
Reply
 
Thread Tools Rate Thread Display Modes
Old 04-17-2018, 07:46 AM   #1
jeran042
Newly Registered User
 
Join Date: Jun 2017
Posts: 61
Thanks: 17
Thanked 0 Times in 0 Posts
jeran042 is on a distinguished road
Open Form with multiple Criteria

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,

jeran042 is offline   Reply With Quote
Old 04-17-2018, 08:01 AM   #2
MarkK
Super Moderator
 
MarkK's Avatar
 
Join Date: Mar 2004
Location: Vancouver BC
Posts: 7,674
Thanks: 10
Thanked 1,266 Times in 1,205 Posts
MarkK is a name known to all MarkK is a name known to all MarkK is a name known to all MarkK is a name known to all MarkK is a name known to all MarkK is a name known to all
Code:
"[COST_CENTER] = " & iCost_Center And "[CATEGORY] = " & "'" & sCategory & "'"
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
__________________
formerly known as lagbolt | Windows 10 | Access 2010 | Visual Studio 2013 | "Institutions have a vested interest in perpetuating the problems to which they are the solution." - Clay Shirky
MarkK is offline   Reply With Quote
The Following User Says Thank You to MarkK For This Useful Post:
jeran042 (04-17-2018)
Old 04-17-2018, 09:22 AM   #3
jeran042
Newly Registered User
 
Join Date: Jun 2017
Posts: 61
Thanks: 17
Thanked 0 Times in 0 Posts
jeran042 is on a distinguished road
Re: Open Form with multiple Criteria

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!

jeran042 is offline   Reply With Quote
Old 04-17-2018, 10:06 AM   #4
MarkK
Super Moderator
 
MarkK's Avatar
 
Join Date: Mar 2004
Location: Vancouver BC
Posts: 7,674
Thanks: 10
Thanked 1,266 Times in 1,205 Posts
MarkK is a name known to all MarkK is a name known to all MarkK is a name known to all MarkK is a name known to all MarkK is a name known to all MarkK is a name known to all
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
__________________
formerly known as lagbolt | Windows 10 | Access 2010 | Visual Studio 2013 | "Institutions have a vested interest in perpetuating the problems to which they are the solution." - Clay Shirky
MarkK is offline   Reply With Quote
Old 04-17-2018, 12:08 PM   #5
jeran042
Newly Registered User
 
Join Date: Jun 2017
Posts: 61
Thanks: 17
Thanked 0 Times in 0 Posts
jeran042 is on a distinguished road
Re: Open Form with multiple Criteria

Quote:
Originally Posted by MarkK View Post
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

jeran042 is offline   Reply With Quote
Reply

Tags
runtime error 13

Thread Tools
Display Modes Rate This Thread
Rate This Thread:

Forum Jump

Similar Threads
Thread Thread Starter Forum Replies Last Post
VBA - Open Form with Multiple Criteria Advisedwolf Forms 7 03-19-2018 05:03 PM
Open form to specific record- using the where condition with multiple criteria cnew Forms 2 01-12-2018 07:24 AM
Open a report with multiple criteria selected from a form David Ball Reports 1 05-31-2017 12:05 AM
Multiple criteria to open a FORM radek225 Modules & VBA 5 04-17-2014 02:50 AM
Open Form from SubForm Using Multiple Criteria OCC_SF_Access Forms 2 09-30-2008 07:05 PM




All times are GMT -8. The time now is 04:56 PM.


Microsoft Access Help
General
Tables
Queries
Forms
Reports
Macros
Modules & VBA
Theory & Practice
Access FAQs
Code Repository
Sample Databases
Video Tutorials

Sponsored Links

How to advertise

Media Kit


Powered by vBulletin®
Copyright ©2000 - 2018, Jelsoft Enterprises Ltd.
(c) copyright 2017 Access World