Importing SAP dollar values in Access table

[Peter_W]

Hello forum,

I am writing an Access VBA code module, which imports SAP actual data into an Access table.

I believe all the proper libraries have been tagged under VBA references.
I am using SAP GUI scripting code as well, to navigate thru the SAP screens.

The code works good for pulling any text values. But for pulling amounts, which in this case are dollars, the code breaks.

For the following line of code:
rsSAPActuals!Dollars = GridView.GetCellValue(i, "WTGBTR")

the code breaks with the following error noted:
"Error 3421 Data type conversion error"

The odd thing is, the Access table field, using a decimaled number format does get many correct dollar amounts, but the full table never gets all the records from SAP. It is truncated.

Would anyone know what my problem is? Is in my code?

Thank you in advance for any help here!

 

[theDBguy]

Hi. What data type did you make the Dollars field in your Access table?

 

[Peter_W]

Hell DB Guy,

For the Dollars field in the Access table, I have tried all conceivable types for the field that receives dollars. Both Number and Currency. Field size/format/decimal places are Double/Standard/Auto. Probably tried about 10 different variations of Number or Currency. Nothing works. My prior experience with SAP is there is some trickiness to how they format dollar values. Negative sign is to the right of the number, and so on.
But again, no format in the Access field seems to work. Thanks.

 

[Peter_W]

Sorry, "Hello DB Guy"....please excuse spelling error!

 

[theDBguy]

Hell DB Guy,

For the Dollars field in the Access table, I have tried all conceivable types for the field that receives dollars. Both Number and Currency. Field size/format/decimal places are Double/Standard/Auto. Probably tried about 10 different variations of Number or Currency. Nothing works. My prior experience with SAP is there is some trickiness to how they format dollar values. Negative sign is to the right of the number, and so on.
But again, no format in the Access field seems to work. Thanks.

Have you tried using Text data type?

 

[Peter_W]

Yes, I tried text too for the Dollars field. And that does not work either. I get a different error message.

 

[theDBguy]

Yes, I tried text too for the Dollars field. And that does not work either. I get a different error message.

Sorry to hear that. Since you know the error, then maybe the next approach is to try to fix it before importing the data. For example:

If IsNull(GridView.GetCellValue(i, "WTGBTR")) Then
    'skip bad data
Else
    rsSAPActuals!Dollars = GridView.GetCellValue(i, "WTGBTR")
End If

In the above, I just used IsNull() as an example of checking for a known bad data. You should know how to check for bad data, so just use it in the same manner as the above and let us know if it helps or not. Good luck!

 

[Peter_W]

DBguy, great idea. Let me try that solution. It very well could be the null values causing the problem, thanks.

 

[theDBguy]

DBguy, great idea. Let me try that solution. It very well could be the null values causing the problem, thanks.

Hi. You're welcome. Good luck. Let us know how it goes...

 

[The_Doc_Man]

It very well could be the null values causing the problem

Whoa, Nellie! Having potential nulls in an input conversion is very likely to cause issues that you would prefer to not encounter. Nulls are notoriously nasty if you weren't ready to handle them when they come into play. Access understands nulls but won't do anything with them without explicit instructions on handling them.

 

[Peter_W]

I tried various error loops, but the code still does not work.

My hunch is there is something wrong in this code. GetCellValue works great for text, not dollar amounts. I will try to convert dollars into strings...maybe that will help.

Thanks all for your nice responses...
GridView.GetCellValue(i, "WTGBTR")

 

[theDBguy]

I tried various error loops, but the code still does not work.

My hunch is there is something wrong in this code. GetCellValue works great for text, not dollar amounts. I will try to convert dollars into strings...maybe that will help.

Thanks all for your nice responses...
GridView.GetCellValue(i, "WTGBTR")

Hi. Sorry to hear that. Good luck!

 

[gemma-the-husky]

@OP

For the following line of code:
rsSAPActuals!Dollars = GridView.GetCellValue(i, "WTGBTR")

What does "WTGTBTR" indicate

how large are the dollar amounts you are trying to retrieve?
what field type is your [dollars] field?
does it import any negatives with a trailing minus?
is the data all well formed?

 

[Peter_W]

Dave, thanks for the reply.

"WTGBTR" is the SAP object, which defines the column that holds the dollar amount values.

"Gridview" is the SAP object for the table of data, if you will, that was generated by an SAP query, a few lines of code previously.

So WTGBTR is the column within the table (Gridview) that I am trying to pull, along with two other text columns.

The actual query is only about 800 records, almost nothing for SAP and Access, but the code fails after about 200 totally accurate records are downloaded.

One format, out of many many tried, shows "000" as a value, and the code evidently errors out on that value.

I am trying to understand a little ABAP programming, the language used by SAP. Maybe that will help me de-bug.

So right now, stumped on what to do or try. Thanks again for the help.

 

[Peter_W]

The amounts successfully imported by Access, look fine. The various Access numerical formats I've used, which accommodate 2 decimals, remove the SAP trailing minus sign. So Access will either put the negative sign in front of the number, or it will bracket negative numbers, depending on the exact number format I use in the field data type, in my Access table. Either format would be just fine for me, if I could download all 800 records. Again, the sub aborts the download, and I get a truncated 200 out of 800 possible records. I see a "000" in the download results...not sure if that is an error, null value, or what. Thanks again.

 

[gemma-the-husky]

clearly 000 isn't a normal "number"

I imagine it ACTUALLY represents something strange. Maybe it's a null, or maybe it's non-printing characters.

Can you find out from your SAP system what data is in there?
Can you add an error handler to assess the problem.

It's as likely to be the gridview part that's failing, rather than the assignment
if it's the assignment, then simply nz might stop it crashing.

on error goto fail
    rsSAPActuals!Dollars = GridView.GetCellValue(i, "WTGBTR")
[COLOR="DarkRed"]'or maybe nz to force a default
'    rsSAPActuals!Dollars = nz(GridView.GetCellValue(i, "WTGBTR"),0)

'or even a large marker value to detect an "error"
'    rsSAPActuals!Dollars = nz(GridView.GetCellValue(i, "WTGBTR"),-999999)
[/COLOR]
continuelabel:
  .....
  exit code

fail:
 msgbox "Error with getcell"
 resume continuelabel

----
you haven't made many posts, so maybe vba is new to you - in which case vba offeres a number of ways to trap errors. Another possibility is to assign the grid reference to a variable of type variant, which CAN hold a null.