How can I use a variable in my macro to import data from as-400 to excel?

20 pts.
Tags:
AS/400
Date Type Variables
excel
I import data from our as-400 to an excel spreadsheet to do further analysis.  One of my criteria is the customer number and that is what I would like to be a variable.

Hence under the vba that retrieve the info, that criteria now read like this   "WHERE (ORDDET01.CUSN='3559')"

When i tried to replace 3559 with a variable define at beginning of sub, I either get nothing or get a syntax error.

Your help is needed!

 

ASKED: December 21, 2009  4:42 PM
UPDATED: December 24, 2009  12:53 AM

Answer Wiki

Thanks. We'll let you know when a new response is added.

Review the actual resulting SQL string after variable insertion. In the example WHERE clause, we see a quoted value — CUSN=’3559′. Those quotes are necessary for SQL to interpret the characters as a character literal value.

You might debug the script to look at the full SELECT statement just before it is passed for execution. The variable value should show in the statement, surrounded by quotes for a character column and not surrounded by quotes for a numeric column.

Getting the quotes right is one of the tricky parts of creating SQL statements for a server.

Tom

Discuss This Question: 2  Replies

 
There was an error processing your information. Please try again later.
Thanks. We'll let you know when a new response is added.
Send me notifications when members answer or reply to this question.

REGISTER or login:

Forgot Password?
By submitting you agree to receive email from TechTarget and its partners. If you reside outside of the United States, you consent to having your personal data transferred to and processed in the United States. Privacy
  • TomLiotta
    Are you including the quotes around the variable value in the final SELECT string? That's simply a first guess because you don't tell us anything about what error you actually get. For all we know, the error has nothing to do with how you're inserting the value. Also, you don't show us the SELECT string that results from inserting your variable value -- we can't tell if the SELECT statement is even correct. Tom
    125,585 pointsBadges:
    report
  • Yves695
    Actually you are right. I was including the variable inside the quotation marks of the string. I corrected it using concatanation resulting in following : "WHERE (ORDDET01.CUSN=" & var &")..... This works beautiful.
    20 pointsBadges:
    report

Forgot Password

No problem! Submit your e-mail address below. We'll send you an e-mail containing your password.

Your password has been sent to:

To follow this tag...

There was an error processing your information. Please try again later.

REGISTER or login:

Forgot Password?
By submitting you agree to receive email from TechTarget and its partners. If you reside outside of the United States, you consent to having your personal data transferred to and processed in the United States. Privacy

Thanks! We'll email you when relevant content is added and updated.

Following