SQL to VBA Conversion

O

Oreynolds

Member
Local time
Today, 14:48
Joined
Apr 11, 2020
Messages
166
Hi,

I have the following working SQL which I need to convert to VBA which I have done. When I run my VBA I get a syntax error which through a process of elimination relates to the embedded IIF statement. Can anyone see where I have gone wrong with this?

SELECT Min(tblVisits.VisitDate) AS MinOfVisitDate
FROM Orders INNER JOIN (tblVisits INNER JOIN tblJobs ON tblVisits.JobID = tblJobs.JobID) ON Orders.OrderNumber = tblJobs.InternalRef
WHERE (((Orders.ProjectManager)=IIf([Forms]![JobCostReportFilters]![Combo0]=0,[Orders].[ProjectManager],[Forms]![JobCostReportFilters]![Combo0])) AND ((Orders.ClosedDate) Between [Forms]![JobCostReportFilters]![txtStartDate] And [Forms]![JobCostReportFilters]![txtEndDate]))
ORDER BY Min(tblVisits.VisitDate);

Code: 
Set rs = CurrentDb.OpenRecordset(" SELECT Min(tblVisits.VisitDate) AS MinOfVisitDate " & _
" FROM Orders INNER JOIN (tblVisits INNER JOIN tblJobs ON tblVisits.JobID = tblJobs.JobID) ON Orders.OrderNumber = tblJobs.InternalRef " & _
" WHERE ((Orders.ProjectManager = IIf(" & Me.Combo0 & " = 0,Orders.ProjectManager, " & Me.Combo0 & ")) " & _
" AND (Orders.ClosedDate BETWEEN DateValue('" & Me.txtStartDate & "') AND DateValue('" & Me.txtEndDate & "')) " & _
" ORDER BY Min(tblVisits.VisitDate);")
 
Hi. What was the error message? Was it 3061 Too few parameters? If so, you could try using this generic function.

 
I am going to go to my grave saying this :(

Put the sql into a string variable and debug.print it.
If you still cannot see your error post the output here.

You can also test the output in the qbe sql window and that should show you the error?
 
theDBguy said:
Hi. What was the error message? Was it 3061 Too few parameters? If so, you could try using this generic function.

Click to expand...
1644776203656.png
 
theDBguy said:
Ah, syntax error. This doesn't look like it's for a passthrough query. If so, you should use the # symbol, rather than single quotes to delimit the date values.
Click to expand...

Hi, no the date part of the string works fine, it only errors when I add the IIF statement back into the WHERE clause
 
Oreynolds said:
Hi, no the date part of the string works fine, it only errors when I add the IIF statement back into the WHERE clause
Click to expand...
Okay, try modifying this part to:
Code: 
" WHERE ((Orders.ProjectManager = " & IIf(Me.Combo0 = 0, Orders.ProjectManager, Me.Combo0) & ") " & _
(untested)
Hope that helps...
 
Oreynolds said:
Hi, no the date part of the string works fine, it only errors when I add the IIF statement back into the WHERE clause
Click to expand...
Are you sure?, as whilst the qbe accepts dd/mm/yyyy it coverts it to mm/dd/yyy in the sql
It even surround the date with # when you go back to qbe window?

1644779033432.png


Code: 
SELECT Transactions.*, Transactions.TransactionDate
FROM Transactions
WHERE (((Transactions.TransactionDate)=#1/8/2016#));

1644779094267.png


1644779218602.png
 
silentwolf said:
Hi,

not sure if you know this link but I use it it is pretty helpful I find

http://vbcity.com/forums/t/142548.aspx
Click to expand...

Not seen that before. Here's my variation on the same idea but with additional functionality
www.isladogs.co.uk

SQL to/from VBA Converter Add-In

This Access add-in is an updated version of my SQL to VBA And Back Again utility which makes it easy to convert query SQL to a VBA string (or vice versa). Additonal functionality includes creating example code from the VBA string and temp queries to test the SQL / VBA output.
www.isladogs.co.uk www.isladogs.co.uk
 
Oreynolds said:
Hi,

I have the following working SQL which I need to convert to VBA which I have done. When I run my VBA I get a syntax error which through a process of elimination relates to the embedded IIF statement. Can anyone see where I have gone wrong with this?

SELECT Min(tblVisits.VisitDate) AS MinOfVisitDate
FROM Orders INNER JOIN (tblVisits INNER JOIN tblJobs ON tblVisits.JobID = tblJobs.JobID) ON Orders.OrderNumber = tblJobs.InternalRef
WHERE (((Orders.ProjectManager)=IIf([Forms]![JobCostReportFilters]![Combo0]=0,[Orders].[ProjectManager],[Forms]![JobCostReportFilters]![Combo0])) AND ((Orders.ClosedDate) Between [Forms]![JobCostReportFilters]![txtStartDate] And [Forms]![JobCostReportFilters]![txtEndDate]))
ORDER BY Min(tblVisits.VisitDate);

Code: 
Set rs = CurrentDb.OpenRecordset(" SELECT Min(tblVisits.VisitDate) AS MinOfVisitDate " & _
" FROM Orders INNER JOIN (tblVisits INNER JOIN tblJobs ON tblVisits.JobID = tblJobs.JobID) ON Orders.OrderNumber = tblJobs.InternalRef " & _
" WHERE ((Orders.ProjectManager = IIf(" & Me.Combo0 & " = 0,Orders.ProjectManager, " & Me.Combo0 & ")) " & _
" AND (Orders.ClosedDate BETWEEN DateValue('" & Me.txtStartDate & "') AND DateValue('" & Me.txtEndDate & "')) " & _
" ORDER BY Min(tblVisits.VisitDate);")
Click to expand...
Check the SQL Converter on the AUG website. I have only used it a couple of times myself, but it does offer a SQL --> VBA conversion. See if that does the job. If so, please let us know. Heck, one way or the other I'd like to know how good it is.
 
FWIW: THis is what the AUG SQL Converter produced from the SQL in your initial post.

strSQL = "SELECT Min(tblVisits.VisitDate) AS MinOfVisitDate " & _
"FROM Orders INNER JOIN (tblVisits INNER JOIN tblJobs ON tblVisits.JobID = tblJobs.JobID) ON Orders.OrderNumber = tblJobs.InternalRef " & _
"WHERE (((Orders.ProjectManager)=IIf([Forms]![JobCostReportFilters]![Combo0]=0,[Orders].[ProjectManager],[Forms]![JobCostReportFilters]![Combo0])) AND ((Orders.ClosedDate) Between [Forms]![JobCostReportFilters]![txtStartDate] And [Forms]![JobCostReportFilters]![txtEndDate])) " & _
"ORDER BY Min(tblVisits.VisitDate); " & _
""
 
Gasman said:
Are you sure?, as whilst the qbe accepts dd/mm/yyyy it coverts it to mm/dd/yyy in the sql
It even surround the date with # when you go back to qbe window?

View attachment 98225

Code: 
SELECT Transactions.*, Transactions.TransactionDate
FROM Transactions
WHERE (((Transactions.TransactionDate)=#1/8/2016#));

View attachment 98226

View attachment 98227
Click to expand...

Yep, the dates definitely work as written, I know what you mean re # in query criteria
 
theDBguy said:
Hi. Have you had a chance to try my suggestion yet? Just curious...
Click to expand...

Hi yes I have, unfortunately that didn't work either. I have found a solution of sorts which is to create two SQL strings as below and perform the IF statement out of the SQL string. This works but obviously slightly more code.....

This is the working code:

Code: 
'Calculate and populate the earliest visit date for labour used calcs
If Me.Combo0 = 0 Then

strSQL = " SELECT Min(tblVisits.VisitDate) AS MinOfVisitDate " & _
" FROM Orders INNER JOIN (tblVisits INNER JOIN tblJobs ON tblVisits.JobID = tblJobs.JobID) ON Orders.OrderNumber = tblJobs.InternalRef " & _
" WHERE (Orders.ClosedDate BETWEEN DateValue('" & Me.txtStartDate & "') AND DateValue('" & Me.txtEndDate & "')) " & _
" ORDER BY Min(tblVisits.VisitDate);"

Else

strSQL = " SELECT Min(tblVisits.VisitDate) AS MinOfVisitDate " & _
" FROM Orders INNER JOIN (tblVisits INNER JOIN tblJobs ON tblVisits.JobID = tblJobs.JobID) ON Orders.OrderNumber = tblJobs.InternalRef " & _
" WHERE (Orders.ProjectManager = " & Me.Combo0 & ") AND (Orders.ClosedDate BETWEEN DateValue('" & Me.txtStartDate & "') AND DateValue('" & Me.txtEndDate & "')) " & _
" ORDER BY Min(tblVisits.VisitDate);"

End If
 
Why not just add the projectmanager criteria if combo not 0?
Switch the order of the criteria?
 
Gasman said:
Why not just add the projectmanager criteria if combo not 0?
Switch the order of the criteria?
Click to expand...
Yer that is what I was trying to do, I presume you mean just have one SQL string and then add the additional criteria? But I couldn’t get my head round getting that to work….?
 
Oreynolds said:
Hi yes I have, unfortunately that didn't work either. I have found a solution of sorts which is to create two SQL strings as below and perform the IF statement out of the SQL string. This works but obviously slightly more code.....

This is the working code:

Code: 
'Calculate and populate the earliest visit date for labour used calcs
If Me.Combo0 = 0 Then

strSQL = " SELECT Min(tblVisits.VisitDate) AS MinOfVisitDate " & _
" FROM Orders INNER JOIN (tblVisits INNER JOIN tblJobs ON tblVisits.JobID = tblJobs.JobID) ON Orders.OrderNumber = tblJobs.InternalRef " & _
" WHERE (Orders.ClosedDate BETWEEN DateValue('" & Me.txtStartDate & "') AND DateValue('" & Me.txtEndDate & "')) " & _
" ORDER BY Min(tblVisits.VisitDate);"

Else

strSQL = " SELECT Min(tblVisits.VisitDate) AS MinOfVisitDate " & _
" FROM Orders INNER JOIN (tblVisits INNER JOIN tblJobs ON tblVisits.JobID = tblJobs.JobID) ON Orders.OrderNumber = tblJobs.InternalRef " & _
" WHERE (Orders.ProjectManager = " & Me.Combo0 & ") AND (Orders.ClosedDate BETWEEN DateValue('" & Me.txtStartDate & "') AND DateValue('" & Me.txtEndDate & "')) " & _
" ORDER BY Min(tblVisits.VisitDate);"

End If
Click to expand...
Hi. Just curious, you were originally having a syntax error. Are you saying when you tried my suggestion, the syntax error didn't go away?
 
You must log in or register to reply here.

Similar threads

P
Trying to order a report using VBA
Replies
18
Views
1,084
theDBguy
theDBguy
L
Solved SQL Select Query in VBA- How to view results
Replies
2
Views
1,657
Minty
Minty
ahmedjamalaboelez
SQL Server Query convert to access query
Replies
4
Views
1,437
theDBguy
theDBguy
T
Can you use VBA to pass parameter as criteria for Report with Grouping/Aggregation in MS Access?
Replies
7
Views
1,563
TechGuy
T
H
Using SQL in VBA
Replies
14
Views
1,320
Cronk
Cronk

Users who are viewing this thread

Total: 1 (members: 0, guests: 1)
Back
Top Bottom