Sql --> vba

DonkeyKong

Registered User.
Local time
Today, 07:50
Joined
Jan 24, 2013
Messages
61
I'm trying to turn an access query into VBA code and was wondering if I could get some pointers on what I'm doing wrong like things I should change and things I shouldn't.

The query:
Code:
SELECT Suspense.CUSIP, Suspense.Account, Suspense.[Registration Code], Suspense.[Payable Date], Suspense.Balance
FROM Suspense
WHERE (((Suspense.CUSIP) Like "0*"))
ORDER BY Suspense.CUSIP;

The code I have for what I want to do with this portion:
Code:
strSQL1 = "SELECT [CUSIP], [Account], [Registration Code], " & _
"[Payable Date], [Balance] & FROM Suspense " & _
"WHERE(([CUSIP] Like '0*'))ORDER BY [CUSIP]"
CurrentDb.Execute strSQL, dbFailOnError
strSQL = "INSERT INTO [SuspenseTemp] SELECT * FROM " & strSQL1 & ""
CurrentDb.Execute strSQL, dbFailOnError
 
Where to start...you set one variable and then execute another. You can't execute the first SQL anyway, as it isn't an action query. There's also a missing space. The second SQL string you create results in invalid SQL. Why not just execute a saved query? It's more efficient than running SQL built in code anyway (it gets compiled).
 
get rid of lines 1 through 4. don't need, plus you have an extra ampersand between Balance and From that isn't needed.
1 strSQL1 = "SELECT [CUSIP], [Account], [Registration Code], " & _
2 "[Payable Date], [Balance] & FROM Suspense " & _
3 "WHERE(([CUSIP] Like '0*'))ORDER BY [CUSIP]"
4 CurrentDb.Execute strSQL, dbFailOnError

You should also explicitly code your field names in each table. I assume SuspenseTemp has the same fields in the same order as Suspense?
In this statement you are doubling up your SELECT * FROM - since you are appending strSQL1 to the end. All you need is this:

strSQL = "INSERT INTO [SuspenseTemp] (field1, field2, field3) SELECT Field1, field2, field3 FROM Suspense WHERE CUSIP Like "0*"

CurrentDb.Execute strSQL, dbFailOnError

Of course you will need to replace Field1, Field2, Field3 with your actual field names.
 
AccessMSSQL - Thanks! That's what I'm looking for! Only problem is that I can't quite get it to work. I think I've narrowed the problem down to the like statement.

I've tried it with different sets of quotes around the 0* but it still grabs everything and dumps it into suspenseTemp. Any ideas?

strSQL = "INSERT INTO [SuspenseTemp] SELECT * FROM SUSPENSE Where CUSIP Like ""0*"""
CurrentDb.Execute strSQL, dbFailOnError

or

strSQL = "INSERT INTO [SuspenseTemp] SELECT * FROM SUSPENSE Where CUSIP Like '0*'"
CurrentDb.Execute strSQL, dbFailOnError
 
What field type is your CUSIP? What kind of data is in that field?

If it's integer than all you need is CUSIP = 0. Can you post some of the data?
 
The cusip is formatted for Text as it can contain letters and numbers. I only want those that begin with 0.

It's tough to get it in the right format but an example of a cusip is: 094147BS3 or 94985PAE6 or 55265K7M5. Is that enough? Account is text also, Reg is number, date is date, amount is number: CPTSUS, 10, 1/25/2013, -2,253.00
 
Are you using SQL Server or is this an Access table? If it's SQL use % instead of *

Also, are you clearing your temporary table before inserting new records into it? Just checking. Because "WHERE CUSIP Like '0*'" should work - if it's access table.

You can also try using the LEFT function: " WHERE LEFT(CUSIP,1) = '0'"
 
AccessMSSQL - Thank you. You're right it was working. I actually had some other previous code dumping info in there first lol. I'm learning slowly.
Thanks for that left clarification also cause I was trying to work with that before the like 0*.
 
always remember that you need to explicitly write your punctuation. If you are passing a value into your SQL statement make sure you wrap it in the correct characters. Here are my most frequently used ascii character codes

Character Equivalent Use
=======================================
# chr(35) wrap date values
" chr(34) embed quotes inside of a string
* chr(42) wild card
/n chr(10) line wraps in string output
/r chr(13) line wraps in string output


Example
strWHERE = "WHERE LEFT(CUSIP,1) = " & chr(34) & "0" & chr(34)


As was previously mentioned (but since it's not well documented I'll say it again) when writing wildcards within the ADO data model, you will want to use a precentage character, not an asterisk. When you are writing wildcards within the DAO data model you will want to use an asterisk, not a percentage character. If you use the wrong wildcard character your code will fail at run-time without generating an error.
 

Users who are viewing this thread

Back
Top Bottom