Long Query in VBA

[yaaara]

Guys, I keep landing into problems (Good for me as I'm learing a lot from this) and here is another one for the brainy ones:

I have the following query that I need to execute in Access VBA

SELECT  HCRecon.*,EMP_MASTER.EMP_ID as WFM_EMPID, EMP_MASTER.EMP_NAME as WFM_EMPNAME,EMP_MASTER.TL_ID as WFM_SUPID, EMP_MASTER.TL_NAME as WFM_SUPNAME,emp_master.designation as WFM_DESIG,emp_master.function as WFM_FUNCTION,iif(((emp_master.function='Production' or emp_master.function='Nesting') and emp_master.status='Active'),'On Floor',emp_master.status) as WFM_STATUS,iif(((emp_master.function='Production' or emp_master.function='Nesting') and emp_master.status='Active'),'Billable','Non-Billable') as WFM_BILLNONBILL,emp_master.voice_nonvoice as WFM_VNV,'Tube' as WFM_PROCESS
FROM EMP_MASTER INNER JOIN HCRecon ON  EMP_MASTER.EMP_ID=HCRecon.EMP_ID where hcrecon.function='Production'
UNION ALL
SELECT  HCRecon.*,EMP_MASTER.EMP_ID as WFM_EMPID, EMP_MASTER.EMP_NAME as WFM_EMPNAME,EMP_MASTER.TL_ID as WFM_SUPID, EMP_MASTER.TL_NAME as WFM_SUPNAME,emp_master.designation as WFM_DESIG,emp_master.function as WFM_FUNCTION,iif(((emp_master.function='Production' or emp_master.function='Nesting') and emp_master.status='Active'),'On Floor',emp_master.status) as WFM_STATUS,iif(((emp_master.function='Production' or emp_master.function='Nesting') and emp_master.status='Active'),'Billable','Non-Billable') as WFM_BILLNONBILL,emp_master.voice_nonvoice as WFM_VNV,'Tube' as WFM_PROCESS
FROM EMP_MASTER LEFT JOIN HCRecon ON  EMP_MASTER.EMP_ID=HCRecon.EMP_ID WHERE HCRecon.EMP_NAME is null and (emp_master.function='Production' or emp_master.function='Nesting' or emp_master.function='Training')
UNION ALL
SELECT  HCRecon.*,EMP_MASTER.EMP_ID as WFM_EMPID, EMP_MASTER.EMP_NAME as WFM_EMPNAME,EMP_MASTER.TL_ID as WFM_SUPID, EMP_MASTER.TL_NAME as WFM_SUPNAME,emp_master.designation as WFM_DESIG,emp_master.function as WFM_FUNCTION,iif(((emp_master.function='Production' or emp_master.function='Nesting') and emp_master.status='Active'),'On Floor',emp_master.status) as WFM_STATUS,iif(((emp_master.function='Production' or emp_master.function='Nesting') and emp_master.status='Active'),'Billable','Non-Billable') as WFM_BILLNONBILL,emp_master.voice_nonvoice as WFM_VNV,'Tube' as WFM_PROCESS
FROM EMP_MASTER RIGHT JOIN HCRecon ON  EMP_MASTER.EMP_ID=HCRecon.EMP_ID WHERE EMP_MASTER.EMP_NAME is null and hcrecon.function='Production';
 

Apparantly, this is too long a query to be stored in any variable?

Even when i divide this query into three parts and store it in three different String or Variant variables namely vSQL1, vSQL3 & vSQL3 and then use the following statement:

Msgbox vSQL1 & vSQL2 & vSQL3
 

I don't get the complete sql statement in the message box. Individual message box statement does give me individual parts as a whole...

Need to join it somehow and execute... Any inputs please?

[FishVal]

String Data Type


There are two kinds of strings: variable-length and fixed-length strings.

A variable-length string can contain up to approximately 2 billion (2^31) characters.


A fixed-length string can contain 1 to approximately 64K (2^16) characters.
Note A Public fixed-length string can't be used in a class module.

The codes for String characters range from 0–255. The first 128 characters (0–127) of the character set correspond to the letters and symbols on a standard U.S. keyboard. These first 128 characters are the same as those defined by the ASCII character set. The second 128 characters (128–255) represent special characters, such as letters in international alphabets, accents, currency symbols, and fractions. The type-declaration character for String is the dollar sign ($).

well enough. ;)

Kind regards,
Fish

[yaaara]

Hi Fish,

The variables I'm using are indeed declared using the "$" symbol..

I have also tried declaring them using the following syntaxes:

Dim vSQL$
Dim vSQL1 as String
Dim vSQL2 as Variant
 

Still these variables are not storing the complete query string... Any clues please?

[NeoPa]

I suggest you add the line :

Debug.Print StrLen(strSQL)

to the end of your code, then post the code, and the result of the added line for us to look at the details of what may be going wrong.

If your variable name is other than strSQL then substitute your name in its place obviously.

[yaaara]

Hi,

When I add this line, it gives me an error message "Compile error: Sub or function not defined"

The highlight is on "strlen"

Any clues about what I may be doing wrong?

[NeoPa]

Only in trying to follow my instructions :( I was confused between different languages. I meant :

Debug.Print Len(strSQL)

It's been nearly two decades since I last coded in c. How bizarre is that.

[yaaara]

Lol.. I was wondering the same and found out that the len command gives the correct length as it is supposed to be.. but still when i put it in a messagebox, it gets truncated?

[NeoPa]

As I understand it, the displaying of it is not your main problem. At least now you know that the string manipulation is working fine.

To display your string simply use :

Debug.Print strSQL

From there you can copy/paste the results somewhere where you can study it in more detail.

[yaaara]

The Debug.Print statement gives the same output as in the Message Box... No luck :-(

[NeoPa]

OK then, try :

For lng=1 To Len(strSQL) By 250:? Mid(strSQL,lng,250);:Next lng

Copy and paste it into the Immediate Pane (Ctrl-G) when the code is stopped and strSQL has been set up.

PS. Alternatively, replace the ? with Debug.Print and include it in the relevant part of your code of course.