1

Coming from years of web development where PHP and SQL statements were so simple, this recent task I've been required to undergo with MS Access and VBA is absolutely doing my head in at how much it complicates SQL statements. Mind you I have no prior knowledge about VBA so it could be extremely simple and I'm not just getting it, but all I want to do is

"SELECT type FROM tblMatter WHERE id='$id'"

When I wear my PHP cap, I want to think okay, we are going to have one row of data, that's going to be an array, and I want one object out of that array. Simple.

VBA, however, complicates the $#!t out of it. So far my code looks something like this

Dim matterSQL As String
Dim matterRS As Recordset

matterSQL = "SELECT type FROM tblMatter WHERE id'" & id & "'"
Set matterRS = CurrentDb.OpenRecordset(matterSQL)

MsgBox matterRS![type]

CurrentDb is defined much much earlier in the code to open the connection to the database, and the error is on the line containing OpenRecordset with the error: Data type mismatch in criteria expression.
As I said, I'm new to VBA so I don't know what the heck I'm doing, and all the documentation on the internet is nowhere near helpful. But all I want to do is to get one piece of data from the table.

Thanks in advance!

Edit: I needed to build upon this with another query that takes the info from the last query to run. Same kind of ordeal:

Dim costSQL As String
Dim costRS as Recordset

costSQL = "SELECT email FROM tblScaleOfDisb WHERE category=" & category
Set costRS = CurrentDb.OpenRecordset(costSQL)

MsgBox costRS![email]

This time I'm getting an error on the line containing OpenRecordset with the error: Too few parameters. Expected 1.
Which I don't understand because the code is practically the same as the first half of the question. What have I done wrong?

simhumileco
  • 31,877
  • 16
  • 137
  • 115
SteppingHat
  • 1,199
  • 4
  • 19
  • 50
  • 1
    `where id ='"&id&"'"`? Also try to use parameterized query – Sachu Feb 18 '16 at 06:19
  • whats the datatype of id? – Sachu Feb 18 '16 at 06:21
  • [How to debug dynamic SQL in VBA](http://stackoverflow.com/questions/418960/managing-and-debugging-sql-queries-in-ms-access/1099570#1099570) -- Also: strings, numbers and dates need different formats in SQL. But this is in no way special to Access VBA/SQL. – Andre Feb 18 '16 at 10:26

2 Answers2

2

You are missing = in the condition

try below

matterSQL = "SELECT type FROM tblMatter WHERE id='" & id & "'"

Also if id is numeric you don't need '

matterSQL = "SELECT type FROM tblMatter WHERE id=" & id

Too few parameters. Expected 1. This happens when the field name in your sql query do not match the table field name if the field name are correct i believe the the datatype of category is not numeric then you have to use '

costSQL = "SELECT email FROM tblScaleOfDisb WHERE category='" & category &"'"

Always try to use parameterised query to avoid SQL injection

Gustav
  • 53,498
  • 7
  • 29
  • 55
Sachu
  • 7,555
  • 7
  • 55
  • 94
  • Stupid me didn't copy the `=` across when creating the question. But I did implement the second suggestion in your question and that did fix my question, but there's another half thats got me confused, edited the question to reflect the struggles – SteppingHat Feb 18 '16 at 07:01
  • 1
    @SteppingHat category is numeric or charachter? – Sachu Feb 18 '16 at 07:07
  • I'm not adding this as a different answer because I agree with @sachu.... But I think the first query could be made simpler with a `dlookup()` – LiamH Feb 18 '16 at 07:58
  • @Sachu ID is a number, category is a string. Everything all makes sense now. Thanks for the help! – SteppingHat Feb 19 '16 at 02:56
  • @SteppingHat glad it helped. – Sachu Feb 20 '16 at 14:39
0

You must understand or prepare few things before you start coding on a new platform. such as

  1. Using Keywords/ Reserved keywords
  2. Capturing Errors
  3. basic arithmetic operations/ string operations.
  4. Available functions / methods
  5. Ways of cleaning your variables after using it

in your case you also need to learn about MS ACCESS SQL. Which is pretty similar to standard SQL but (limited to and) strongly influenced by MS Access internal functions.

SQL execution will return n Rows as result. Each row will have n number of columns. You need to understand how you need to loop through result sets.

Please do have some error capturing method. I will help you to understand the direction before spending hours in Google.

in your first SQL: you have a reserved keyword Type. use square brackets to escape reserved keywords. In where condition numeric fields must not have string quotes and strings must have them.

Tip: You can use the MS Access visual query builder to build your query and copy the SQL to VBA.

list of reserved keywords in MS ACCESS: https://support.microsoft.com/en-us/kb/286335

list of functions: http://www.techonthenet.com/access/functions/

Error handling : http://allenbrowne.com/ser-23a.html

Clean/close your objects after usage by explicitly setting as nothing: Is there a need to set Objects to Nothing inside VBA Functions

Community
  • 1
  • 1
Krish
  • 5,917
  • 2
  • 14
  • 35