?
Solved

Excel VBA compile error

Posted on 2008-02-04
5
Medium Priority
?
3,461 Views
Last Modified: 2013-12-20
I have this sql statement in Excel VBA and I can't figure out what the problem here, VBA keep complain the with this error compile error;expected expression. this was working fine but it sudden stop working.
VBA complain on the begining of , ' " & dt2 & " '} ) ) " .....

Thanks
.CommandText = Array( _
        "SELECT RepairOrders.ROustNo, RepairOrders.ROCustName, RepairOrders.RODate, RepairOrders.ROMake, RepairOrders.RONo, RODetails.ROItemDesc, RODetails.RONo" & Chr(13) & "" & Chr(10) & "FROM AYS_amdj.dbo.RepairOrders RepairOrders, A" _
        , "YS_amdj.dbo.RODetails RODetails" & Chr(13) & "" & Chr(10) & "WHERE RepairOrders.RONo = RODetails.RONo AND ((RepairOrders.ROCustName Like '" & Name & "%') AND (RepairOrders.RODate>={ts '" & dt1 & "'} And RepairOrders.RODate<={ts _
        , '" & dt2 & "'} ) )" & Chr(13) & "" & Chr(10) & "ORDER BY RepairOrders.RODate")

Open in new window

0
Comment
Question by:amdj
  • 3
  • 2
5 Comments
 
LVL 8

Expert Comment

by:Yiogi
ID: 20819873
Your problem is you split your string into two lines.
The code below should work.
.CommandText = Array( _
        "SELECT RepairOrders.ROustNo, RepairOrders.ROCustName, RepairOrders.RODate, RepairOrders.ROMake, RepairOrders.RONo, RODetails.ROItemDesc, RODetails.RONo" & Chr(13) & "" & Chr(10) & "FROM AYS_amdj.dbo.RepairOrders RepairOrders, A" _
        , "YS_amdj.dbo.RODetails RODetails" & Chr(13) & "" & Chr(10) & "WHERE RepairOrders.RONo = RODetails.RONo AND ((RepairOrders.ROCustName Like '" & Name & "%') AND (RepairOrders.RODate>={ts '" & dt1 & "'} And RepairOrders.RODate<={ts, '" _
        & dt2 & "'} ) )" & Chr(13) & "" & Chr(10) & "ORDER BY RepairOrders.RODate")
 

Open in new window

0
 

Author Comment

by:amdj
ID: 20819917
That's stupid me... isn't that the commas is requres on the second line when it split?

Thanks a bunch!
0
 
LVL 8

Accepted Solution

by:
Yiogi earned 1000 total points
ID: 20819924
No commas are not required. The problem is you actually split your string constant. You cannot have string constants across multiple lines although your code can take as many lines as you wish.
0
 
LVL 8

Expert Comment

by:Yiogi
ID: 20819931
Well not as many as you wish I have had in the past VBA saying that I had exceeded that limit. I don't remember the exact limit though. I also had procedures too large for VBA lol. It does have limits yes but unless running something really complex you won't meet them.
0
 

Author Comment

by:amdj
ID: 20819986
Quick solution and very helpful, I have been pulling my hair all day long on this.

Thanks
0

Featured Post

The new generation of project management tools

With monday.com’s project management tool, you can see what everyone on your team is working in a single glance. Its intuitive dashboards are customizable, so you can create systems that work for you.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

In real business world data are crucial and sometimes data are shared among different information systems. Hence, an agreeable file transfer protocol need to be established.
Windows Explorer let you handle zip folders nearly as any other folder: Copy, move, change, and delete, etc. In VBA you can also handle normal files and folders, but zip folders takes a little more - and that you'll find here.
Visualize your data even better in Access queries. Given a date and a value, this lesson shows how to compare that value with the previous value, calculate the difference, and display a circle if the value is the same, an up triangle if it increased…
Have you created a query with information for a calendar? ... and then, abra-cadabra, the calendar is done?! I am going to show you how to make that happen. Visualize your data!  ... really see it To use the code to create a calendar from a q…

589 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question