User-defined variables in MS access query

One issue that I see is that your variables are inside of the quoted string effectively making them text (i.e. they do not resolve to the value they point to).

Partial code

Please Login or Register  to view this content.

should have the variables outside of the quotes using the & operator to concatenate the full string.

Please Login or Register  to view this content.

Ahhh I did not even see that it was a concatenation issue.

Makes sense, but now it's giving me a SQL Syntax Error, and highlighting the ".Refresh Backgroundquery=False"

Can you post the full query? Include the block of code before and after it. In MS-Access you can set a variable equal to the SQL String and then pass that SQL string over the connection to the database to return a result set using ADO. You might try this.

I'm afraid I only have a couple of educational years of VB coding experience (1st year of college as IT major, then switched to finance). I am not familiar with SQL.

I will post the full code, but first I wanted to explain my new idea:
I made a combination box with 3 operators that the user can choose to query for accounting periods (=, <, >), so I am trying to concatenate this operator in my search criteria. I think the problem, again, is that for some reason this query does not like to use Strings in its query criteria. Hence the reason I had to remove the letter from the "ProjectNo" to fix my previous issue.

P.S. I've switched from querying access to querying our Oracle ODBC directly (the Access query was merely a middle-man, if you will).

Here's the full query code:

Please Login or Register  to view this content.

Please Login or Register  to view this content.

Gotta bump this.
I'm so close!

Here's a snippet (the important stuff) of the VB criteria that I'm trying to implement into my ODBC query:

Please Login or Register  to view this content.

...

Please Login or Register  to view this content.

...

Please Login or Register  to view this content.

It's giving me a General ODBC error. that tells me that it doesn't like my "Operator" statement, because without that variable and with a plain "=" the query runs perfectly.

P.S the query code that IS working is:

Please Login or Register  to view this content.

Make sure you are referencing the combo box correctly by referencing the sheet where it exists. You can do this by using the "Me" operator if the code you are executing is behind the sheet where the combo box lives, otherwise use the standard worksheet referencing.

Please Login or Register  to view this content.

Here is the revised code. You dont need to place the operator inside of the tick marks. Also, watch your "spaces"; make sure you have a space in between your column and the operator otherwise you will be passing a string like COLUMNNAMELIKE which is probably not what you want. I inserted a space after PERIOD.

Also, instead of dealing with quoting rules, alot of times it is easier to just use the ASCII equivalent. Chr(39) resolves to a tick mark: '

Another area that may give you problems is the LIKE operator. You will want to use a different string because you will need to pass Oracles wildcard string %

Please Login or Register  to view this content.