Results 1 to 14 of 14
  1. #1
    Lounger
    Join Date
    Mar 2010
    Location
    UK
    Posts
    47
    Thanks
    0
    Thanked 0 Times in 0 Posts
    Good afternoon loungers!

    i have been racking my brain over this problem for a week or two now, and i cant even work out why its happening, let alone try to work out a solution.

    i have been developing a front end in access 2003 (SQL server 2005 back end) to allow data loads from the users clipboard (with fluctuating records and the fact that this data is pulled from half a dozen other systems, this is the only viable solution.) when the data is imported various columns in the underlying table are deliberatly left out, and filled in by code, depending on the values of 3 combo boxes.

    The fields are, Fiscal Year, Fiscal month and Group.

    the code that fills them in works fine! as long as your pasting 2 rows or more (the most ever pasted in was 773, and that had no problems) however, one of the departments puts in a single row, but the coe will not fill in the fiscal year/month or group.

    if after pasting in the solitary row i immediatley pasted in the same row (or another) it will change both fiscal year/month/group etc, just not one row on its own.

    Investigation shows that:

    - it isnt an issue of pasting from Excel
    - the fields are not showing as NULL


    have any of you had similar problems? or know the cause?

    like i say this has been troubling me for a few weeks now, and i would really like to see that back of this project,

    Regards,
    Chris

    P.S. if it helps here is my code:

    Code:
    strPara(1) = Me.cmbFiscalYear
    strPara(2) = Me.cmbFiscalMonth
    strPara(3) = Me.cmbGroup
    
    strName(1) = "fiscal_year"
    strName(2) = "fiscal_month"
    strName(3) = "split_type"
    b = 1
    Do While b <= 3
    
    'Last Parameter changes depending on instance this is used...
    Call InsertNull("dbo_tbl_dataload_performance_review_pack", strPara(b), strName(b), "Where " & "" & strName(b) & "" & " IS NULL")
    
    b = (b + 1)
    Loop
    
    
    '''''''''''''''''''''''''''''''''''''''''''''
    
    Function InsertNull(strTable As String, ByVal strPara As String, ByVal strColumn As String, Optional WhereClause As String)
    
    strSQL = "UPDATE " & "" & strTable & "" & " SET " & "" & strColumn & "" & " = " & """" & strPara & """" & ""
    
    If IsNull(WhereClause) = False Then
    strSQL = strSQL & " " & WhereClause
    End If
    
    'Setting warnings off to avoid annoying "Are you sure" Questions
    DoCmd.SetWarnings (WarningsOFF)
    DoCmd.RunSQL (strSQL)
    'Put Warnings back on to allow genuine errors to show.
    DoCmd.SetWarnings (WarningsON)
    
    
    End Function

  2. #2
    Star Lounger
    Join Date
    Dec 2009
    Location
    Bellevue, WA
    Posts
    61
    Thanks
    1
    Thanked 0 Times in 0 Posts
    One thing come to mind: With a single row paste, when does the record get committed (saved)? And on what event does the code run? And does the code get executed for the last record always? If 773 records are pasted, is the 773rd properly filled in?

    Some details might help us all to help you figure out what is occurring.

  3. #3
    Lounger
    Join Date
    Mar 2010
    Location
    UK
    Posts
    47
    Thanks
    0
    Thanked 0 Times in 0 Posts
    im sorry i was clear enough.

    the event is the on click of a button named "Load".

    When the button is clicked it pastes the contents of the users clipboard into a form linked directly off of a table, this form ommits 3 columns (Fiscal_year, Fiscal_month and Group), however they are still in the table.

    as the form bolted straight onto the table, the record is commited as it is pasted.

    and, the 773rd record (and all other final records) are all filled out. this is why its getting confusing, if it wsa the first row, or the last row, then it would be explainable, as long as 2 or more rows are pasted all rows have the correct data pasted in. (this has been verified by using SSMS to do a query on those rows where Value is not = the test value we put in.

  4. #4
    Star Lounger
    Join Date
    Dec 2009
    Location
    Bellevue, WA
    Posts
    61
    Thanks
    1
    Thanked 0 Times in 0 Posts
    Is the code being run for the single case? Have you stepped through the code in debug mode in that case? If not, you should.

  5. #5
    Lounger
    Join Date
    Mar 2010
    Location
    UK
    Posts
    47
    Thanks
    0
    Thanked 0 Times in 0 Posts
    again i probably wasnt clear enough.

    The code is fired for once per paste, if that makes sense, so it will paste what ever is on your clipboard once at a time, if that is what you mean by "single case"

    Stepping through the code was the first thing i tried when i realised the problem. and it all seems to work fine.

    The problem is likley the SQL statement or how access chooses to treat the nulls of that single row.

    The SQL statement (without strValuyes etc) would be

    Code:
    UPDATE tblTable Set [Fiscal_year/Fiscal_Month/Group] = [Corresponding Value] WHERE [Fiscal_year/Fiscal_Month/Group] IS NULL
    This code is in a procedure (as it is used in a numer of locations) and is looped through for the desired number of times, for this example it is looped through 3 times. Each time it only updates 1 column where that column is null.

    so if fiscal_year is null, it will update it to the value in the combo box.

    Obviously as the code doesnt error, and it seems to run fine, Access isnt treating the empty columns as Null, and i dont know why. as we can go into SSMS run the same command on the same table wit hthe same fields, and it works.

    this is why it is more than a little confusing.

  6. #6
    Star Lounger
    Join Date
    Dec 2009
    Location
    Bellevue, WA
    Posts
    61
    Thanks
    1
    Thanked 0 Times in 0 Posts
    Try stepping through the code with a single row being pasted. Look at the values before and after the SQL statement. If it does not behave as you expect, then take that exact SQL into a standalone query and do some testing.

    One thing I notice - the semicolon at the end of the SQL is missing. I was not aware of it being optional. Add it and test.

  7. #7
    5 Star Lounger AndrewKKWalker's Avatar
    Join Date
    Apr 2001
    Location
    Cambridge, UK
    Posts
    1,020
    Thanks
    0
    Thanked 3 Times in 3 Posts
    The ; is good practice in SQL, but not compulsory.
    It become more relevant when writing Procedures in SQL Server and other databases.
    Andrew

  8. #8
    Star Lounger
    Join Date
    Dec 2009
    Location
    Bellevue, WA
    Posts
    61
    Thanks
    1
    Thanked 0 Times in 0 Posts
    Quote Originally Posted by AKW View Post
    The ; is good practice in SQL, but not compulsory.
    It become more relevant when writing Procedures in SQL Server and other databases.
    Interesting. I just eliminated the ";' in an SQL statement and it worked. I could have sworn that it did not use to work (that could have been anywhere from Access 1.0 onward). Any knowledge about that or was I just imagining it?

  9. #9
    Super Moderator
    Join Date
    Aug 2001
    Location
    Evergreen, CO, USA
    Posts
    6,624
    Thanks
    3
    Thanked 60 Times in 60 Posts
    It's been that was at least since Access 2.0 - I was surprised the first time my partner told me I didn't need the semi-colon in a SQL String. The designer will replace it if you delete it, but VBA works like a champ without it.
    Wendell

  10. #10
    Lounger
    Join Date
    Mar 2010
    Location
    UK
    Posts
    47
    Thanks
    0
    Thanked 0 Times in 0 Posts
    Quote Originally Posted by Larry Engles View Post
    Try stepping through the code with a single row being pasted. Look at the values before and after the SQL statement. If it does not behave as you expect, then take that exact SQL into a standalone query and do some testing.

    One thing I notice - the semicolon at the end of the SQL is missing. I was not aware of it being optional. Add it and test.
    As i said, this was the first thing i tried, and everything is working as it should, as in the code is executing properly with no errors, in the occasion i used IFs they are executing properly and the SQL doesnt error whether i use it there, in a query or even in SSMS, i believe this is because the syntax is correct but it is not seeing the column as null.

    but how 1 row cant have nulls, where a dozen can, makes no sense to me.


    Quote Originally Posted by AKW View Post
    The ; is good practice in SQL, but not compulsory.
    It become more relevant when writing Procedures in SQL Server and other databases.
    Access does generally error if yo get ; in the wrong place, but like you say it isnt compulsory, however when i worked with some oracle databases at uni, the client we used wouldnt recognise a sql string without one, which was a bit of a pain at times!

    i must admit i didnt realise it hadnt been compulsory for so long, i guess its true what they say! you learn something new every day!

  11. #11
    Super Moderator
    Join Date
    Aug 2001
    Location
    Evergreen, CO, USA
    Posts
    6,624
    Thanks
    3
    Thanked 60 Times in 60 Posts
    I must confess that I've looked at your code several times, and still don't understand the actual process you are using, so this may be completely off the wall. You have the counter "b" which starts at 1 and goes to 2 - what that has to do with the number of records isn't clear, but is it possible that with only a single record that the second pass never runs? Or is it possible that with a single record you have a race condition where the code executes before the single record is actually completely pasted? Just trying to explore various alternatives .....
    Wendell

  12. #12
    Star Lounger
    Join Date
    Dec 2009
    Location
    Bellevue, WA
    Posts
    61
    Thanks
    1
    Thanked 0 Times in 0 Posts
    Quote Originally Posted by WendellB View Post
    You have the counter "b" which starts at 1 and goes to 2 - what that has to do with the number of records isn't clear, but is it possible that with only a single record that the second pass never runs?
    The counter loops thru the 3 controls. I think the procedure is invoked once per record.

  13. #13
    Star Lounger
    Join Date
    Dec 2009
    Location
    Bellevue, WA
    Posts
    61
    Thanks
    1
    Thanked 0 Times in 0 Posts
    Christopher: I don't really have a clue here, but try this. I've usually steered away from nulls when working with strings (because I usually want a blank to be treated the same as a null). Since you are working with strings, convert the null to a zero-length string (nz function) and then use the trim function to test the result for zero length. This treats blanks and nulls the same. You can do all this in a single SQL statement. It should not make any difference, but since we're grasping at straws here... Have you used the immediate window to test for null for these fields (ie. ? isnull(field name) (or is it FieldName = null?)? Do the tests before and after the sql runs.

    By the way, the for loop is the better tool for counting type loops. It has a counter built in. You can replace the do with a for and be clearer and simpler (for b = 1 to 3).

    Other than that, I'm clueless at the moment.

  14. #14
    Lounger
    Join Date
    Mar 2010
    Location
    UK
    Posts
    47
    Thanks
    0
    Thanked 0 Times in 0 Posts
    Quote Originally Posted by WendellB View Post
    I must confess that I've looked at your code several times, and still don't understand the actual process you are using, so this may be completely off the wall. You have the counter "b" which starts at 1 and goes to 2 - what that has to do with the number of records isn't clear, but is it possible that with only a single record that the second pass never runs? Or is it possible that with a single record you have a race condition where the code executes before the single record is actually completely pasted? Just trying to explore various alternatives .....
    Sorry, i have this habbit of not putting down enough detail as i seem to subconsciously think people should understand this as well i as i do.

    the loop is because this is the second incarnation of the code, the first didnt have the procedure, but instead had a SQL statement that would pass in all 3 parameters. in the interests of keeping things tidy, and our last ditched attempt to solve this, we decided to loop the code, so it updates each column once at a time.

    So, If Fiscal_year is being pasted in, it would look where Fiscal_year is null. but where Group was being input it would look where Group is null. So, we would atleast see if it was a particular column that wasnt null.


    Quote Originally Posted by Larry Engles View Post
    Christopher: I don't really have a clue here, but try this. I've usually steered away from nulls when working with strings (because I usually want a blank to be treated the same as a null). Since you are working with strings, convert the null to a zero-length string (nz function) and then use the trim function to test the result for zero length. This treats blanks and nulls the same. You can do all this in a single SQL statement. It should not make any difference, but since we're grasping at straws here... Have you used the immediate window to test for null for these fields (ie. ? isnull(field name) (or is it FieldName = null?)? Do the tests before and after the sql runs.

    By the way, the for loop is the better tool for counting type loops. It has a counter built in. You can replace the do with a for and be clearer and simpler (for b = 1 to 3).

    Other than that, I'm clueless at the moment.
    It was a while ago, but i think we did. and i think it came back not null.

    I must admit i forgot about the nz function. I will try that when i next get the chance, and let you know how i get on!

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •