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

Dim truncating a string, or watch window truncating?

geolemon
P: 39
I'm having a seriously Twilight Zone moment:

In the code below, I'm building a SQL command into a string variable, declared at the top of my code.
Then, I connect to the database and try to execute the SQL code.

Here's the corresponding snippets:
Expand|Select|Wrap|Line Numbers
  1. 'declare variables:
  2. Dim strProc As String
  3.  
  4. 'build SQL statement:
  5. strProc = "INSERT INTO Assemblies ( AssyPN, Rev, RefID, DNP, MfgPN, CustPN ) " & _
  6. "SELECT """ & AssyPN & """,""" & Rev & """, DBimport.RefID, " & _
  7. "Switch(DBimport.[DNP?]=""true"",""1"", DBimport.[DNP?]=""True"",""1"", DBimport.[DNP?]=""TRUE"",""1"", DBimport.[DNP?]=""false"",""0"", DBimport.[DNP?]=""False"",""0"", DBimport.[DNP?]=""FALSE"",""0"") AS DNP, " & _
  8. "DBimport.MfgPN, DBimport.CustPN FROM DBimport;"
  9.  
  10. 'connect to db and run SQL:
  11. Set conTAGdb = Application.CurrentProject.Connection
  12. conTAGdb.Execute strProc
  13.  
When I run, I get the following error message:
"No value given for one or more required parameters"
Debugging puts me right at the Execute.

Naturally, first thing I did was throw a watch on my 'strProc' variable, suspecting an inadvertant syntax error.
But the watch shows the following:
Expand|Select|Wrap|Line Numbers
  1. "INSERT INTO Assemblies ( AssyPN, Rev, RefID, DNP, MfgPN, CustPN ) SELECT "PCB-000125-R","A", DBimport.RefID, Switch(DBimport.[DNP?]="true","1", DBimport.[DNP?]="True","1", DBimport.[DNP?]="TRUE","1", DBimport.[DNP?]="false","0", DBimport.[DNP?]="Fals
Very strange!
And, I thought worth noting that in my watch window, it shows an opening quote, but none at the end...
...so, now I can't tell if there's some limit on how many characters are displayed in the watch window (Excel counted 250 - an interstingly even number), or if the string is somehow being actually truncated.
I've searched my @$$ off trying to find if such a limitation exists - I can't find info on it, much less a workaround if there is! :argh:


What's going on here?
Is there a character limit on the watch window?
If so, how do I work around it to display what's in strProc for troubleshooting the original error message?
If not, how is my string being truncated - particularly in such an abrupt way?

(the real kick in the pants is that I ran this code successfully in an initial test!)
Sep 4 '08 #1
Share this Question
Share on Google+
15 Replies


Expert Mod 2.5K+
P: 2,545
Hi geolemon. Don't know about the watch problem, which in my view is not likely to be significant. I think it's a blind alley here.

The more substantive difficulty - the parameter issue - arises because unless I am mistaken about your field names you appear to be referring to a user-data-entry parameter, [DNP?], and Execute cannot simply stop and ask the user for the value of the parameter. In circumstances like this the error message is explicit and is pointing you in the right direction. The database engine will object to finding a parameter without an assigned value in an action query like this. Queries run by the use of Execute or RunSQL do not necessarily behave quite as you might expect from testing such a query in the Access Query Editor window, where you will indeed be asked for the value of the parameter.

I think you need to get the required value from the user before you run the INSERT. This will also allow you to do away with the inefficient SWITCH statements and build appropriate versions of your SQL statement to cover the true and false state combinations which are listed.

-Stewart
Sep 4 '08 #2

NeoPa
Expert Mod 15k+
P: 31,494
Hi GeoLemon.

Your question raises some interesting points which I will respond to as I find answers.

Firstly, your ability to understand what is going on in a SQL within VBA situation is markedly increased if the proper quote characters are used (See Quotes (') and Double-Quotes (") - Where and When to use them).

Second easy one is that yes, the watch window is limited to <255 chars. I assume the actual figure is 250 as you've found it to truncate there. Worry not though, as VBA strings go to (circa) 64K bytes. To see the actual data type Ctrl-G from the debugger (Immediate Pane) and type in "? strProc". This will dump the contents out into the same Immediate Pane.
Sep 4 '08 #3

NeoPa
Expert Mod 15k+
P: 31,494
I have done some manual parsing of your string, including reformatting the white-space for clarity and switching the quote chars where appropriate and came up with the following :
Expand|Select|Wrap|Line Numbers
  1. INSERT INTO Assemblies ( AssyPN, Rev, RefID, DNP, MfgPN, CustPN )
  2.  
  3. SELECT ' & AssyPN & ',
  4.        ' & Rev & ',
  5.        DBimport.RefID,
  6.        Switch(DBimport.[DNP?]='true','1',
  7.               DBimport.[DNP?]='True','1',
  8.               DBimport.[DNP?]='TRUE','1',
  9.               DBimport.[DNP?]='false','0',
  10.               DBimport.[DNP?]='False','0',
  11.               DBimport.[DNP?]='FALSE','0') AS DNP,
  12.        DBimport.MfgPN,
  13.        DBimport.CustPN
  14.  
  15. FROM DBimport
I would suggest that having a field name of DNP? is to be avoided. It is liable to confuse.

While I can't find anything that would obviously be the trigger for that particular error message, I would suggest some changes that may make it go away anyway (and hopefully still give you the results you require) :
Expand|Select|Wrap|Line Numbers
  1. strProc = "INSERT INTO Assemblies " & _
  2.                       "( AssyPN, Rev, RefID, DNP, MfgPN, CustPN ) " & _
  3.           "SELECT '" & Me.AssyPN & "', '" & Me.Rev & "', " & _
  4.                  "[RefID], IIf(UCase([DNP?])='TRUE','1','0') AS DNP, " & _
  5.                  "[MfgPN], [CustPN] " & _
  6.           "FROM DBimport"
Sep 4 '08 #4

ADezii
Expert 5K+
P: 8,638
Besides the Parameter issue, the Switch Expression can be condensed and significantly reduced, via:
Expand|Select|Wrap|Line Numbers
  1. 'build SQL statement:
  2. strProc = "INSERT INTO Assemblies ( AssyPN, Rev, RefID, DNP, MfgPN, CustPN ) " & _
  3.           "SELECT """ & AssyPN & """,""" & Rev & """, DBimport.RefID, " & _
  4.           "Switch(Ucase(DBimport.[DNP?])=""TRUE"",""1"",UCase(DBimport.[DNP?])=""FALSE"",""0"") AS DNP, " & _
  5.           "DBimport.MfgPN, DBimport.CustPN FROM DBimport;"
Again, I am not 100% sure on this one, Stewart or NeoPa could probably give you better advice than I in this matter.
Sep 5 '08 #5

ADezii
Expert 5K+
P: 8,638
Hello NePa, sorry for repeating myself, and please do no interpret this as hijacking a Thread, because the question is along the Thread Topic, but will the following work? You know that I am SQL-illiterate! (LOL).
Expand|Select|Wrap|Line Numbers
  1. INSERT INTO Assemblies ( AssyPN, Rev, RefID, DNP, MfgPN, CustPN )
  2.  
  3. SELECT ' & AssyPN & ',
  4.        ' & Rev & ',
  5.        DBimport.RefID,
  6.        Switch(UCase(DBimport.[DNP?])='TRUE','1',
  7.               UCase(DBimport.[DNP?])='FALSE','0') AS DNP,
  8.        DBimport.MfgPN,
  9.        DBimport.CustPN
  10.  
  11. FROM DBimport
Expand|Select|Wrap|Line Numbers
  1. INSERT INTO Assemblies ( AssyPN, Rev, RefID, DNP, MfgPN, CustPN )
  2.  
  3. SELECT ' & AssyPN & ',
  4.        ' & Rev & ',
  5.        DBimport.RefID,
  6.        IIF(UCase(DBimport.[DNP?])='TRUE','1','0') AS DNP,
  7.        DBimport.MfgPN,
  8.        DBimport.CustPN
  9.  
  10. FROM DBimport
Sep 5 '08 #6

NeoPa
Expert Mod 15k+
P: 31,494
Perfectly on-topic ADezii :)

In the two versions you posted the lines which determine [DNP] were both fine. Alternative versions which each should give correct results. In fact, if you check the code I posted (at the bottom of post #4), it suggests something very similar to your second example.

Some of the earlier lines though, were sort of pseudo-SQL I'd used to illustrate how the OP's original code was working. Those parts wouldn't work in quite that form.
Sep 5 '08 #7

geolemon
P: 39
The more substantive difficulty - the parameter issue - arises because unless I am mistaken about your field names you appear to be referring to a user-data-entry parameter, [DNP?], and Execute cannot simply stop and ask the user for the value of the parameter. In circumstances like this the error message is explicit and is pointing you in the right direction.
Thanks for the response, and I apologize if my original post was confusing...
I took only the snippets of significant code from the program to display here.

That code exists inside of a larger script, which is passed two variables...
Or I should say - is executed from a form and references two controls on that form.

However, [DNP?] is actually simply the name of a column in a data import table - as inhereted from a column header in an Excel spreadsheet, a true/false column. So it is an actual, physical column in the table, and it does have data.

I didn't consider if it would cause an issue with Access, having the question mark on the end - I am wondering now however, because it is an error regarding a "parameter".
Sep 5 '08 #8

NeoPa
Expert Mod 15k+
P: 31,494
You're in the best position to test that GeoLemon.

Let us know if changing the field name has any effect?

Possibly try out some of the other suggestions too.
Sep 5 '08 #9

geolemon
P: 39
Great suggestion on the use of the Ucase function also - that solves my case concerns actually MORE completely, as well as more elegantly...

And, as a side benefit, I'm sure my entire SQL statement can be reduced to less than the 250 char limit that I'm wondering about in the watch window, allowing me to ALSO determine if it's a syntax error as I was originally envisioning, or a confusing paramater-esque name, or truncation - which I'd be working around at any rate. ;)
Sep 5 '08 #10

Expert Mod 2.5K+
P: 2,545
Thanks for clarifying the status of the strangely-named field [DNP?]. It is unlikely that its name is causing Access to treat it as a parameter, but as NeoPa said that is one you have to eliminate for yourself. I would have renamed it before import to avoid any possibility of confusion...

You mention in post # 8 that there are two form field references passed to a part of your code (a section you did not post). Be aware that form field references included for interpretation in queries run using the Execute method can indeed give rise to the parameter error message you described. As this was in code you didn't post we can't review it to check for you, but in any event just be aware that passing form field references to the database engine directly can lead to such errors.

-Stewart

ps - if [DNP?] is a true/false column why are you comparing its values as if it is a string in your switch statements??? Boolean (true/false) values are represented as 0 (false) and -1 (true). Unless it is really a string column whose values are the words 'True' and 'False' - but surely not??
Sep 5 '08 #11

NeoPa
Expert Mod 15k+
P: 31,494
Very pertinent point there Stewart (The PS).

This is very likely causing a lot of the confusion GeoLemon.
Sep 5 '08 #12

geolemon
P: 39
OK - first things first.
The SQL ran fine, with the change to an IIF with the Ucase thrown in there - much more readable, much more concise to boot.
Thanks!

So, I do have working code now - and the [DNP?] column wasn't a problem in reality. I was only converting the TRUE/FALSE to 1/0 in observance of how Excel represented the data (the data source) compared to how Access handles the 1/0. If I export an Access file as other file types, I end up with a text representation of "TRUE" or "FALSE" - so I figured I'd convert it - it's in the table as "TRUE" and "FALSE" after importing the spreadsheet.

The code for processing through that imported table is as follows (note the change at "Insert into Assemblies"):
Expand|Select|Wrap|Line Numbers
  1. Public Function cmdImportBOM(AssyPN, Rev)
  2.  
  3.     Dim conTAGdb As ADODB.Connection
  4.     Dim db As Database
  5.     Dim conTAGcheck As DAO.Recordset
  6.     Dim strProc As String
  7.     Dim msgResponse As String
  8.     Dim chkAssemblies As Integer
  9.     Dim chkParts As Integer
  10.     Dim chkAssemblyParts As Integer
  11.  
  12.     'Check for duplicate potential Assembly table values:
  13.     strProc = ""
  14.     strProc = "Select count(*) as DupAssys from( " & _
  15.                "SELECT RefID, count(*) " & _
  16.                "FROM (SELECT DBimport.RefID, IIF(Ucase(DBimport.[DNP?])=""TRUE"", 1, 0) as DNP, " & _
  17.                "DBimport.BOMPN, DBimport.CustPN FROM DBimport) as DBimport " & _
  18.                 "GROUP BY RefID " & _
  19.                 "HAVING count(*)>1 );"
  20.     'open the recordset and run the above SQL query
  21.     Set conTAGcheck = db.OpenRecordset(strProc)
  22.     'fetch count from SQL query through recordset into chkAssemblies variable
  23.     chkAssemblies = conTAGcheck("DupAssys")
  24.  
  25.     'Check for duplicate potential Parts table values:
  26.     strProc = ""
  27.     strProc = "SELECT PurchasedPN, Count(*) AS DupParts " & _
  28.               "FROM (select distinct PurchasedPN, Manufacturer, Description " & _
  29.               "from DBimport where PurchasedPN is not null " & _
  30.               "group by PurchasedPN, Manufacturer, Description) " & _
  31.               "GROUP BY PurchasedPN  HAVING Count(*)>1; "
  32.     'open the recordset and run the above SQL query
  33.     Set conTAGcheck = db.OpenRecordset(strProc)
  34.     'fetch count from SQL query through recordset into chkParts variable
  35.     chkParts = conTAGcheck("DupParts")
  36.  
  37.     'Check for duplicate potential AssemblyParts table values:
  38.     strProc = ""
  39.     strProc = "SELECT UsedPN, Count(*) AS DupAPs " & _
  40.               "FROM (SELECT distinct BOMPN, PurchasedPN as UsedPN, [PN Source] as Documentation " & _
  41.               "from DBimport where PurchasedPN is not null " & _
  42.               "group by BOMPN, PurchasedPN, [PN Source]) " & _
  43.               "GROUP BY UsedPN " & _
  44.               "HAVING Count(*)>1;"
  45.     'open the recordset and run the above SQL query
  46.     Set conTAGcheck = db.OpenRecordset(strProc)
  47.     'fetch count from SQL query through recordset into chkAssemblyParts variable
  48.     chkAssemblyParts = conTAGcheck("DupAPs")
  49.  
  50.     'Check for duplicate values before processing import:
  51.     If (chkAssemblyParts + chkParts + chkAssemblies) > 0 Then
  52.         MsgBox = MsgBox("Duplicate values found in data!  Import aborted. Duplicates: RefID=" & chkAssemblies & ", PN/Man/Desc=" & chkParts & ", BOMPN/PurchPN=" & chkAssemblyParts, vbOKOnly)
  53.         GoTo ErrExit
  54.     End If
  55.  
  56.  
  57.     'Insert into Assemblies:
  58.     strProc = "INSERT INTO Assemblies ( AssyPN, Rev, RefID, DNP, MfgPN, CustPN ) " & _
  59.                 "SELECT """ & AssyPN & """,""" & Rev & """, DBimport.RefID, " & _
  60.                 "IIF(Ucase(DBimport.[DNP?])=""TRUE"", 1, 0) as DNP, " & _
  61.                 "DBimport.BOMPN, DBimport.CustPN FROM DBimport;"
  62.     'Connect and execute:
  63.     Set conTAGdb = Application.CurrentProject.Connection
  64.     conTAGdb.Execute strProc
  65.  
  66.      'clear strProc for next command:
  67.      strProc = ""
  68.  
  69.      'Insert into Parts:
  70.      strProc = "Insert into Parts (MfgPN, Manufacturer, Description)" & _
  71.                "select distinct PurchasedPN, Manufacturer, Description " & _
  72.                "from DBimport where PurchasedPN is not null " & _
  73.                "group by PurchasedPN, Manufacturer, Description"
  74.  
  75.      'insert into Parts:
  76.      conTAGdb.Execute strProc
  77.  
  78.      'clear strProc for next command:
  79.      strProc = ""
  80.  
  81.      'Insert into AssemblyParts:
  82.      strProc = "insert into AssemblyParts (AssyPN, Rev, BOMPN, UsedPN, Documentation) " & _
  83.                "SELECT distinct """ & AssyPN & """,""" & Rev & """, BOMPN, " & _
  84.                "PurchasedPN as UsedPN, [PN Source] as Documentation " & _
  85.                "from DBimport where PurchasedPN is not null " & _
  86.                "group by BOMPN, PurchasedPN, [PN Source]"
  87.  
  88.      'insert into AssemblyParts:
  89.      conTAGdb.Execute strProc
  90.  
  91.      'clear strProc for next command:
  92.      strProc = ""
  93.  
  94.      strProc = "Delete * from DBimport"
  95.      'empty all records from DBimport table
  96.      conTAGdb.Execute strProc
  97.  
  98.      msgResponse = MsgBox("Data was successfully processed!", vbOKOnly)
  99.      DoCmd.Close
  100. ErrExit:
  101. End Function
  102.  
I don't even have this mature enough to have real error handling yet - I was afraid that it would be too long, generate confusion and off-topic commentary, so I simply posted the relevant snippets... not realizing that I had other variable references there (I could have shown them as Dim's for this purpose I suppose).

And again - thanks for the help!
Sep 5 '08 #13

geolemon
P: 39
The only thing I'm a little disturbed by (but don't worry, I won't lose any sleep, lol) is that I really don't understand what my original failure is...

Was it because my SQL string was too long? String data types are supposed to be good out to a billion and a half characters long or something (not literally ;)) as a varchar...

Anyway, changing it to the IIF with the Uchar really worked around the 250 char truncation rather than figuring it out or fixing it.

...anyone have any thoughts on what was causing the truncation? I hate to have something out there like a time-bomb I'm bound to run into again!
Sep 5 '08 #14

Expert Mod 2.5K+
P: 2,545
NeoPa pointed out in post # 3 that there was no truncation; the 250 chars is just what the watch window showed.

Now that all the red herrings have been dealt with (importing True and False values as text indeed!) it would seem that your repeated use of Switch was causing some kind of failure to occur. This can arise in unexpected ways, as the database engine does a lot 'under the hood' that none of us can see to try to interpret what you wrote. We may never know why the complex set of Switch statements failed - and the moral of the tale is twofold: first, that if it looks overcomplex, it is, and second, if you want to use true/false fields then implement them as booleans - had you done so instead of importing them as text you wouldn't have needed to use Switch statements in the first place...

-Stewart
Sep 5 '08 #15

NeoPa
Expert Mod 15k+
P: 31,494
Whenever I get a problem, I try to arrange things as neatly as possible first, before I try to work on the problem. It's a discipline that pays of many-fold when posting questions on a site for instance.

Half the time (more actually), when the problem is arranged neatly and ready to look at properly, it disappears.

Do I worry about that problem resurfacing? No. Because I know that, whatever it is, it is not related to the fundamentals of what I'm doing. It is, in effect, a byproduct of the original untidiness. Not something I'm interested in.

Sometimes when you build back the bells and whistles onto your core, the problem returns. Then it needs looking into. If it turns out that it is related to a habit that you've moved on from, left behind, then leave the worry behind with it.

If you find in this case that it returns, post in here and we'll see where we can take it.

Does that make sense?
Sep 5 '08 #16

Post your reply

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