String length issues?

[dbaJSmith]

Hi all:
I'm trying to run the following code, and I'm running into issues:

dim submitSQL as string

submitSQL = "insert into tbl_Record (auditID, clientID, recordAuditNum, medReviewer, medProvider, medDateSvc, reviewToolID, "
submitSQL = submitSQL & "finReviewer, finProvider, finDateEntered, CPT, ICD9, status, record_timestamp) values "
submitSQL = submitSQL & "(" & Me.txt_AuditID.Value & ", '" & Me.txt_ClientID.Value & "', " & Me.lst_Records.ListCount
submitSQL = submitSQL & ", " & Me.cbo_SvcReviewer.Value & ", " & Me.cbo_SvcProvider.Value & ", #" & Me.cbo_SvcDate.Value
submitSQL = submitSQL & "#, " & Me.cbo_ReviewTool.Value & ", " & Me.cbo_BillReviewer.Value & ", " & Me.cbo_BillProvider.Value
submitSQL = submitSQL & ", #" & Me.cbo_BillDate.Value & "#, '" & Me.txt_CPTCode.Value & "', '" & Me.txt_ICD9Code.Value & "', "
submitSQL = submitSQL & "'Not Started', Now());"
DoCmd.RunSQL submitSQL

Running this code produces an error on the DoCmd.RunSQL error. In debug mode, I checked the value of SubmitSQL:

"insert into tbl_Record (auditID, clientID, recordAuditNum, medReviewer, medProvider, medDateSvc, reviewToolID, finReviewer, finProvider, finDateEntered, CPT, ICD9, status, record_timestamp) values (1, 'AAA010101', 1, 6, 6, #3/3/2009#, 5, 4, 6, #3/3/2009"

Now correct me if I'm wrong, but doesn't the string variable have a maximum somewhere around 65,000 characters? If yes, then why is my string not reaching this length? If no, how do I get around this problem? Thanks!

 

[MajP]

put a couple of "debug.print submitSql" into your code to see where the problem starts. No you are not hitting a limit, probably a limitation of the debugmode.

If you can get a complete debug.print then you can drop the sql into the qde and see if it runs.

 

[dbaJSmith]

Hey, learn something new every day. Didn't know about the debug.print command, that helps save a lot of time.

I must have been catching the error at a wrong point, or already fixed that one, because now it accepts the string (which was a limitation of the debug, thanks for that knowledge too). Now I'm erroring out somewhere else.

Thanks again!

 

[MajP]

you did a good job initially. This approach

strSql = strSql & "...."

allows you to error check as you go since each line builds. Often people try to post a huge complex query in one shot and ask why it is not working. I always use the approach above and always do it something like

strSql = "..."
debug.print strSql
strSql = strSql & "..."
debug.print strSql
...

then I code out the debug.prints as each line passes correctly.

 

[dbaJSmith]

Yeah, I prefer to split my queries into multiple lines. First, it's a lot easier to read it when broken into logical sections; second, you can see the whole query in one screen (without scrolling).

If I'm developing a query for a database (unless it's a super simple query), I always load the query into a test query/table. It takes up a little more time in development, but it makes life much easier during debugging and testing. Very rarely do I have to debug a query string during testing, which is why I was so confused with that string error. I don't know if multiple debug.prints would provide me any additional info; I think I would only go that route if the situation called for it.

I'm going to start incorporating that debug object into more of my development now. Thanks again!

 

[kjv1611]

dbaJSmith,

So, is your problem(s) solved now, or else where are you at with the situation?

--

"If to err is human, then I must be some kind of human!" -Me

 

[dbaJSmith]

Oh sorry, thought that was clarified. Yes, situation resolved. Thanks!