Results 1 to 12 of 12
  1. #1
    Rustin788 is offline Advanced Beginner
    Windows 7 64bit Access 2007
    Join Date
    Mar 2014
    Posts
    47

    Export Data is Changing

    Hey,
    I have a spreadsheet that our Rebates team refreshes. I have the following column in Access: Allowed: Sum(IIf([VARIANCECODES] Like "*QU*",0,[QUANTITY])). IF "QU" is listed as a Variance Code it should list as 0, otherwise just pull the quality since that rebate is allowed. This looks fine in Access, but when exporting to Excel it just pulls in the QUANTITY for everyone. I have tried changing it to "0" or "None", and again it all looks good in Access. It is almost like it is just formatting the 0 on their instead of adding it to the data set. Any ideas on what I can do? Thanks

  2. #2
    Join Date
    Jan 2017
    Location
    Swansea,South Wales,UK
    Posts
    4,858
    I think you would need the if as one field, then sum that?
    Please use # icon on toolbar when posting code snippets.
    Cross Posting: https://www.excelguru.ca/content.php?184
    Debugging Access: https://www.youtube.com/results?sear...bug+access+vba

  3. #3
    Rustin788 is offline Advanced Beginner
    Windows 7 64bit Access 2007
    Join Date
    Mar 2014
    Posts
    47
    Quote Originally Posted by Welshgasman View Post
    I think you would need the if as one field, then sum that?
    Thanks, let me give it a try.

  4. #4
    Rustin788 is offline Advanced Beginner
    Windows 7 64bit Access 2007
    Join Date
    Mar 2014
    Posts
    47
    Same thing, works in Access but doesn't carry over to excel. I think Excel is completely ignoring the Like "*QU*" Statement. Not sure what else to try in place of that. There are multiple variance codes they put in so I have to look for QU specifically.

  5. #5
    Micron is offline Virtually Inert Person
    Windows 10 Access 2016
    Join Date
    Jun 2014
    Location
    Ontario, Canada
    Posts
    12,737
    Is VARIANCECODES a lookup field in the table? Access will handle the lookup value (the one you see), but the export will be the hidden value as far as I know. So your export might be interpreted as IIF 5 is Like "*QU*" then zero (which it is not) otherwise [QUANTITY]
    The more we hear silence, the more we begin to think about our value in this universe.
    Paraphrase of Professor Brian Cox.

  6. #6
    Rustin788 is offline Advanced Beginner
    Windows 7 64bit Access 2007
    Join Date
    Mar 2014
    Posts
    47
    Quote Originally Posted by Micron View Post
    Is VARIANCECODES a lookup field in the table? Access will handle the lookup value (the one you see), but the export will be the hidden value as far as I know. So your export might be interpreted as IIF 5 is Like "*QU*" then zero (which it is not) otherwise [QUANTITY]
    I don't believe so. Just to be sure, I've joined two separate Databases using the Invoice #. The VARIENCECODES is just one of the column entries in one of those tables. I'll try the IIF statement on just that database without a joining one and see what that does.

    Edit:

    So I tried with just the one table and same results. What did work, is I got rid of the "*QU*" and instead put in "CC,SS,UU,QU" which is what is in one of the fields that needs to have the quantity a 0, and that does carry the 0 over from Access to Excel. It doesn't seem to like the Wildcard *. There are too many possible code variations that might have a QU so I can't build a statement to cover them all. Hmm...

  7. #7
    Micron is offline Virtually Inert Person
    Windows 10 Access 2016
    Join Date
    Jun 2014
    Location
    Ontario, Canada
    Posts
    12,737
    There ought to be no doubt as to whether or not it is a lookup field. This selected field is not:

    Click image for larger version. 

Name:	aaaLookup.jpg 
Views:	16 
Size:	20.8 KB 
ID:	47900
    Drop down the list and check if any row source is set for combo or listbox.
    The more we hear silence, the more we begin to think about our value in this universe.
    Paraphrase of Professor Brian Cox.

  8. #8
    orange's Avatar
    orange is offline Moderator
    Windows 10 Office 365
    Join Date
    Sep 2009
    Location
    Ottawa, Ontario, Canada; West Palm Beach FL
    Posts
    16,716
    I agree with micro's comment.
    Your statement
    "CC,SS,UU,QU" which is what is in one of the fields

    suggests this is not a simple short text field.

  9. #9
    Rustin788 is offline Advanced Beginner
    Windows 7 64bit Access 2007
    Join Date
    Mar 2014
    Posts
    47
    The Data is entered Manually through a GUI the customer service teams uses. I'm not sure what that end looks like, but looking in Access it does show as Short Text field. No Data is entered in Access, I'm just using it to try and manipulate the tables.

    I think the solution will be finding an alternate to using the asterisk wildcard.

  10. #10
    Micron is offline Virtually Inert Person
    Windows 10 Access 2016
    Join Date
    Jun 2014
    Location
    Ontario, Canada
    Posts
    12,737
    it does show as Short Text field.
    That has no bearing on whether or not it is a lookup field.
    This
    No Data is entered in Access,
    suggests that it is not but isn't definitive one way or the other IMO.
    The only suggestion I have left is to copy your db, compact and zip it and post it here. You can remove everything that isn't pertinent and possibly randomize sensitive data. If your db is split, it won't be of any use without the back end tables as well.
    The more we hear silence, the more we begin to think about our value in this universe.
    Paraphrase of Professor Brian Cox.

  11. #11
    Join Date
    Jan 2017
    Location
    Swansea,South Wales,UK
    Posts
    4,858
    Try instr() if your data is that bad.
    So QU cancels out all the others like CC which would be added if QU was not present?
    Please use # icon on toolbar when posting code snippets.
    Cross Posting: https://www.excelguru.ca/content.php?184
    Debugging Access: https://www.youtube.com/results?sear...bug+access+vba

  12. #12
    Rustin788 is offline Advanced Beginner
    Windows 7 64bit Access 2007
    Join Date
    Mar 2014
    Posts
    47
    Just wanted to update with the "solution" I found. The Variance Code that makes the Allowed column be 0 is always entered last so I was able to separate the last two digits out and do a regular if statement without the wildcard and that worked. More a work around than a solution, but it transfers the proper numbers to excel now.

Please reply to this thread with any new information or opinions.

Similar Threads

  1. Replies: 4
    Last Post: 12-22-2021, 07:58 AM
  2. Replies: 26
    Last Post: 04-07-2020, 02:29 AM
  3. Changing export Spec breaks code
    By tagteam in forum Access
    Replies: 4
    Last Post: 01-27-2016, 06:13 PM
  4. Report data changing in export to Excel
    By Monterey_Manzer in forum Reports
    Replies: 2
    Last Post: 03-14-2013, 01:55 PM
  5. Replies: 2
    Last Post: 12-17-2012, 03:46 PM

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  
Other Forums: Microsoft Office Forums