RecordSet Query balking as use of variable

[Rick_S.]

Hi folks,

This code, with a hardwired value of "KORG" (numeric field in a table) works like expected:

m_SQL = "SELECT ORG, Campus_Dept, EMPID, LName, FName, Job_Title" _
& " FROM Input_from_Lawson" _
& " WHERE (KORG = 125)" _


& " ORDER BY Lname, Fname;"

Set m_Rs2 = m_Db1.OpenRecordset(m_SQL, dbOpenTable)

My dilemma is that I want to substitute a numeric variable whose value will change as a "While" loop executes. Here's the code that fails:

m_SQL = "SELECT ORG, Campus_Dept, EMPID, LName, FName, Job_Title" _
& " FROM Input_from_Lawson" _
& " WHERE (KORG = m_KORG)" _
& " ORDER BY Lname, Fname;"

Set m_Rs2 = m_Db1.OpenRecordset(m_SQL, dbOpenTable)

The m_KORG variable is dimensioned as LONG at the top of the routine, and is a Private variable.

I've done some Debug.Print statements and know that m_KORG is being populated with a correct value, but I when I run the routine, I get this error message:

SQRun-time error '3061': Too few parameters. Expected 1

Any thoughts ? All help gratefully accepted.

Hope all is well & till later,
Rick S.
San Antonio, TX

[Beetle]

Reference the variable outside of the statement;

m_SQL = "SELECT ORG, Campus_Dept, EMPID, LName, FName, Job_Title" _
& " FROM Input_from_Lawson" _
& " WHERE (KORG = " & m_KORG & ")" _
& " ORDER BY Lname, Fname;"

[Rick_S.]

Hi Sean,

Perfect !!! If you're ever in SATX, I'll personally help you execute "Thirsty"... :-)

Much appreciated & till later,
Rick S.

[Rick_S.]

Hi again,

Sorry to pester you, but have one more "challenge" that I've been unable to solve.

Is it possible to invoke formatting on a field when isolating records with an SQL statement ? The code below works, but I'd like to force the EMPID field to a fixed length of 6 with leading zeroes :

m_SQL = "SELECT EMPID " _
& " FROM Input_from_Lawson" _
& " WHERE (KORG = " & m_KORG & ")" _
& " ORDER BY Lname, Fname;"

I've tried a number of "Format" statements, but after a couple of hours, have come up empty...(so to speak). AN EMPID of 9524 would be "selected" as 009524.

You'll notice that the "m_KORG" problem solved with your input from yesterday... many thanks for that.

Please advise when you get a moment.

Much appreciated & till later,
Rick S.

[Beetle]

Glad you got your first problem solved. In regards to the new issue, you don't say specifically what you tried or what the result was (i.e. you got errors, you got incorrect results, etc.), but my first suggestion would be;

"SELECT Format(EmpID, "000000") AS EmpNo.........."

[Rick_S.]

Hi Sean,

I tried a whole slew if things... some produced compiler "errors-to-be" (in red font), and others produce run-time errors.

Here's a look at the results of your first recommendation... (I had tried this early on, as it seemed to be the most straightforward):

m_SQL = "SELECT Campus_Dept, format(EMPID, "000000") as EMPNO, FName, LName, Job_Title " _
& " FROM Input_from_Lawson" _
& " WHERE (KORG = " & m_KORG & ")" _
& " ORDER BY Lname, Fname;"

This shows us in red font and when I compile gives me an "Expected: end of statement" message.

Just so you know, I've piles of years of doing this stuff in xBase & FoxPro, but an a relative newbie with Access and VBA... your help really appreciated.


Hope all is well & till later,
Rick S.

[Beetle]

Is it possible for you to post a sanitized copy of your application that reproduces the problem?

[Rick_S.]

Hi again,

Yes, I can do that... but it will take me some time to build a "sanitized" version.

Just for grins, I also built a Query using Query Design and selected a single field based on the desired format I've described. The SQL view is pretty much the same as your earlier recommendation... but in Query Design, it worked perfectly !! When I did a cut/paste of that SQL into my Sub, got the same idiot message... so it appears that there is some sort of disconnect with the SQL Select options between what works in Query Design and VBA.

Again, your help very much appreciated; more to follow in a day or so.

Till later,
Rick S.


BTW: I do have a work-around if this issue turns out to be non-solvable.... :-)

[Beetle]

Nevermind the sanitized copy, I was just having a brain cramp and forgot for a minute that you were working in VBA. Replace the double quotes in the format function with single quotes. Those double quotes re what is causing the End Of Statement error;

"SELECT Format(EmpID, '000000') AS EmpNo.........."

[Rick_S.]

Hi again,

OK... you are "two for two" in helping me successfully automate what would otherwise be a half day job into one that takes less than a minute.

Very much appreciated !!

Hope all is well @ Swampy & till later,
Rick S.

ps: I experience Brain Cramps (and other related Brain "things") on a daily basis...:-)