SQL to VBA Conversion (1 Viewer)

[Oreynolds]

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);

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);")

 

[theDBguy]

Hi. What was the error message? Was it 3061 Too few parameters? If so, you could try using this generic function.

Code Snippets

 

[Gasman]

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?

 

[Oreynolds]

Hi. What was the error message? Was it 3061 Too few parameters? If so, you could try using this generic function.

Code Snippets

 

[theDBguy]

View attachment 98224

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.

 

[Oreynolds]

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.

Hi, no the date part of the string works fine, it only errors when I add the IIF statement back into the WHERE clause

 

[silentwolf]

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

 

[theDBguy]

Hi, no the date part of the string works fine, it only errors when I add the IIF statement back into the WHERE clause

Okay, try modifying this part to:

" WHERE ((Orders.ProjectManager = " & IIf(Me.Combo0 = 0, Orders.ProjectManager, Me.Combo0) & ") " & _

(untested)
Hope that helps...

 

[Gasman]

Hi, no the date part of the string works fine, it only errors when I add the IIF statement back into the WHERE clause

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?

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

 

[isladogs]

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

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

SQL to VBA & Back Again

This is an enhanced version of a utility, by Allen Browne which makes it easy to convert a query from SQL to VBA (or vice versa)

www.isladogs.co.uk

 

[GPGeorge]

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);

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);")

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.

 

[silentwolf]

Oh fantastic thanks for the link isladogs!!!

Cheers

 

[GPGeorge]

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); " & _
""

 

[Oreynolds]

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

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

View attachment 98226

View attachment 98227

Yep, the dates definitely work as written, I know what you mean re # in query criteria

 

[theDBguy]

Yep, the dates definitely work as written, I know what you mean re # in query criteria

Hi. Have you had a chance to try my suggestion yet? Just curious...

 

[Oreynolds]

Hi. Have you had a chance to try my suggestion yet? Just curious...

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:

'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

 

[Gasman]

Why not just add the projectmanager criteria if combo not 0?
Switch the order of the criteria?

 

[Oreynolds]

Why not just add the projectmanager criteria if combo not 0?
Switch the order of the criteria?

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….?

 

[theDBguy]

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:

'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

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?