Shanan212
asked on
Running SQL query via VBA
SELECT ZONE.CITY, ZONE.PROVINCE, ZONE.TCOMBI, INTO T65
FROM [ZONE]
WHERE (((ZONE.PROVINCE)="ONTARIO") AND ((ZONE.ZONE)="BEYOND"));
I am trying to run the above SQL via VBA using this
CurrentDb.Execute "SELECT ZONE.CITY, ZONE.PROVINCE, ZONE.TCOMBI, INTO T65
FROM [ZONE]
WHERE (((ZONE.PROVINCE)="ONTARIO") AND ((ZONE.ZONE)="BEYOND"))"
I am getting syntax error. Could anyone please help?
SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Dale >>> " INTO T65"...
mx
mx
Oops,
Looks like there is an extra comma, just before the "INTO T65"
and I agree with Joe (MX) that you should use the dbFailOnError to close it out.
Looks like there is an extra comma, just before the "INTO T65"
and I agree with Joe (MX) that you should use the dbFailOnError to close it out.
ASKER
The above answers giving me error higlighting the 'execute' part (Wrong Number of arguments)
I am trying to run 2 queries.
1 - Filtering based on user entered values. A simplified version would be this
2. I am also trying to do a calculation on the above code itself. But I am confused on that as well.
So now the question is in 2 parts. The #1 is not working with the error. I also want to know if #2 is possible. Would I be using the '.execute' for #2?
Thanks!
I am trying to run 2 queries.
1 - Filtering based on user entered values. A simplified version would be this
CurrentDb.Execute "SELECT ZONE.CITY, ZONE.PROVINCE, ZONE.TCOMBI, INTO T65
FROM [ZONE]
WHERE (((ZONE.PROVINCE)=[Forms]![Point2Province]![oprovcombo]))"
2. I am also trying to do a calculation on the above code itself. But I am confused on that as well.
[code]CurrentDb.Execute "SELECT ZONE.CITY, ZONE.PROVINCE, ZONE[highlight]*0.65[/highlight].TCOMBI, INTO T65
FROM [ZONE]
WHERE (((ZONE.PROVINCE)=[Forms]![Point2Province]![oprovcombo]))"
So now the question is in 2 parts. The #1 is not working with the error. I also want to know if #2 is possible. Would I be using the '.execute' for #2?
Thanks!
I almost ... made that embarrassing post ... fortunately you did first! LOL :-)
Extra comma ... yes ...
' removed ...............vv
CurrentDb.Execute "SELECT ZONE.CITY, ZONE.PROVINCE, ZONE.TCOMBI INTO T65
FROM [ZONE]
WHERE ZONE.PROVINCE=" & Chr(34) & "ONTARIO" & Chr(34) & " AND ZONE.ZONE = " & Chr(34) & "BEYOND" & Chr(34) , dbFailOnError
Extra comma ... yes ...
' removed ...............vv
CurrentDb.Execute "SELECT ZONE.CITY, ZONE.PROVINCE, ZONE.TCOMBI INTO T65
FROM [ZONE]
WHERE ZONE.PROVINCE=" & Chr(34) & "ONTARIO" & Chr(34) & " AND ZONE.ZONE = " & Chr(34) & "BEYOND" & Chr(34) , dbFailOnError
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
The table is working as I wanted
I also designed a 'calculation based' query and that is outputting as well. I had errors but I corrected them myself by doing [zone]![city]*0.65 (bringing into brackets)
Thanks for the help so far!
1. Now I have the query output to a table. However, I want the query to overwrite the existing table, everytime the query is run.
My coding is like this
Should I be putting something so it overwrites the existing query output table? (the full query is in above comments)
2. The query itself is taking my WHOLE screen going to the left and I have to scroll a mile (as the original I have is huge) Is there a way to break it down within VBA console?
Thanks!
I also designed a 'calculation based' query and that is outputting as well. I had errors but I corrected them myself by doing [zone]![city]*0.65 (bringing into brackets)
Thanks for the help so far!
1. Now I have the query output to a table. However, I want the query to overwrite the existing table, everytime the query is run.
My coding is like this
DoCmd.SetWarnings False
CurrentDb.Execute "SELECT ZONE.CITY, .....
DoCmd.SetWarnings True
Should I be putting something so it overwrites the existing query output table? (the full query is in above comments)
2. The query itself is taking my WHOLE screen going to the left and I have to scroll a mile (as the original I have is huge) Is there a way to break it down within VBA console?
Thanks!
Well ... did we clean up your original question/issue ... syntax error?
If so, best to close out this Q and ask a new one with new issue.
thx.mx
If so, best to close out this Q and ask a new one with new issue.
thx.mx
ASKER
Thanks all!
What are you actually trying to do? Do you want to open the query? Why would you want to do so from within VBA? A better approach would be to use this select statement as the recordsource for a form or something like that.