By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
432,441 Members | 998 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 432,441 IT Pros & Developers. It's quick & easy.

Long Query in VBA

P: 77
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

Expand|Select|Wrap|Line Numbers
  1. 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
  2. FROM EMP_MASTER INNER JOIN HCRecon ON  EMP_MASTER.EMP_ID=HCRecon.EMP_ID where hcrecon.function='Production'
  3. UNION ALL
  4. 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
  5. 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')
  6. UNION ALL
  7. 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
  8. 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';
  9.  
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:

Expand|Select|Wrap|Line Numbers
  1. Msgbox vSQL1 & vSQL2 & vSQL3
  2.  
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?
Sep 4 '08 #1
Share this Question
Share on Google+
9 Replies


FishVal
Expert 2.5K+
P: 2,653
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
Sep 4 '08 #2

P: 77
Hi Fish,

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

I have also tried declaring them using the following syntaxes:

Expand|Select|Wrap|Line Numbers
  1. Dim vSQL$
  2. Dim vSQL1 as String
  3. Dim vSQL2 as Variant
  4.  
Still these variables are not storing the complete query string... Any clues please?
Sep 4 '08 #3

NeoPa
Expert Mod 15k+
P: 31,475
I suggest you add the line :
Expand|Select|Wrap|Line Numbers
  1. 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.
Sep 5 '08 #4

P: 77
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?
Sep 5 '08 #5

NeoPa
Expert Mod 15k+
P: 31,475
Only in trying to follow my instructions :( I was confused between different languages. I meant :
Expand|Select|Wrap|Line Numbers
  1. Debug.Print Len(strSQL)
It's been nearly two decades since I last coded in c. How bizarre is that.
Sep 5 '08 #6

P: 77
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?
Sep 5 '08 #7

NeoPa
Expert Mod 15k+
P: 31,475
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 :
Expand|Select|Wrap|Line Numbers
  1. Debug.Print strSQL
From there you can copy/paste the results somewhere where you can study it in more detail.
Sep 5 '08 #8

P: 77
The Debug.Print statement gives the same output as in the Message Box... No luck :-(
Sep 5 '08 #9

NeoPa
Expert Mod 15k+
P: 31,475
OK then, try :
Expand|Select|Wrap|Line Numbers
  1. 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.
Sep 5 '08 #10

Post your reply

Sign in to post your reply or Sign up for a free account.