How to make SQL work in VBA

[cer5yc]

Can anyone tell me what adjustments I need to make to the following to make it work in VBA?

SELECT Data.[Procurement Lead], Data.ProjectStatus
FROM Data
WHERE (((Data.[Procurement Lead])=IIf([Forms]![FilterForm]![FilterFormProcurementLead].[Value]='All',[Data].[Procurement Lead],[Forms]![FilterForm]![FilterFormProcurementLead])) AND ((Data.ProjectStatus)=IIf([Forms]![FilterForm]![FilterFormProjectStatus].[Value]='Open','1',IIf([Forms]![FilterForm]![FilterFormProjectStatus].[Value]='Closed','2',IIf([Forms]![FilterForm]![FilterFormProjectStatus].[Value]='All',[Data].[ProjectStatus])))))

 

[GingerR]

What's not working? Do you get an error message? Or just not the results you want? If the latter, what results are you getting and what is it you want to get?

Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ222-2244. Basics at

http://r937.com/relational.html

 

[cer5yc]

I have the following in VBA and the SQL line is red - can't figure out why

Dim cnnx As ADODB.Connection
Set cnnx = CurrentProject.Connection
Dim myRecordSet As New ADODB.Recordset
myRecordSet.ActiveConnection = cnnx

myRecordSet.Open "SELECT Data.[Procurement Lead], Data.ProjectStatus" FROM Data WHERE (((Data.[Procurement Lead])=IIf([Forms]![FilterForm]![FilterFormProcurementLead].[Value]='All',[Data].[Procurement Lead],[Forms]![FilterForm]![FilterFormProcurementLead])) AND ((Data.ProjectStatus)=IIf([Forms]![FilterForm]![FilterFormProjectStatus].[Value]='Open','1',IIf([Forms]![FilterForm]![FilterFormProjectStatus].[Value]='Closed','2',IIf([Forms]![FilterForm]![FilterFormProjectStatus].[Value]='All',[Data].[ProjectStatus])))))

 

[GingerR]

That's not an exact paste job is it? IF so it is diff than the first post (quotes on first line after "Data.ProjectStatus").

Have you tried it in parts? LIke start with


"Select * from [Data]" and see if that works, etc...

Also, "Data" might be an Access Keyword so maybe you have to enclose it in square brackets.

Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ222-2244. Basics at

http://r937.com/relational.html

 

[cer5yc]

Okay the SQL is no longer red after enclosing Data in parantheses but now I get an error that says "No value given for one or more required parameters". I have no idea what I'm missing here. Please help!!

 

[GingerR]

Again, have you tried doing it in small pieces at a time?

Also, not sure about your setting for when the control is "ALL"

Try this (only has the Lead portion) - just to make sure, is ProcurementLead and the form control a NUMBER?

myRecordSet.Open "SELECT [Data].[Procurement Lead],[Data].[ProjectStatus],[Forms]![FilterForm]![FilterFormProcurementLead] as ChosenLead FROM [Data] WHERE [Forms]![FilterForm]![FilterFormProcurementLead]='ALL' or ([Forms]![FilterForm]![FilterFormProcurementLead]<>'ALL' and [Data].[Procurement Lead] = [Forms]![FilterForm]![FilterFormProcurementLead])"

To test, make a local table of the same structure with the data in it (use a make table query) and throw this sql statement into a query design and see how i did it. You should be able to finish it from here.



Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ222-2244. Basics at

http://r937.com/relational.html

 

[ProgramError]

if using access vba then you cannot run a select sql statement on.y actions statements ie append, make table

put the sql in the record source of a form and do a requery instead

Ian Mayor (UK)
Program Error
Always make your words sweet and nice. Because you never know when you may have to eat them.

 

[danvlas]

myRecordSet.Open "SELECT [Data].[Procurement Lead],[Data].[ProjectStatus], " & [Forms]![FilterForm]![FilterFormProcurementLead] & " as ChosenLead FROM [Data] WHERE " & [Forms]![FilterForm]![FilterFormProcurementLead] & "='ALL' or (" & [Forms]![FilterForm]![FilterFormProcurementLead] & "<>'ALL' and [Data].[Procurement Lead] = " & [Forms]![FilterForm]![FilterFormProcurementLead] & ")"

might work...




Daniel Vlas
Systems Consultant

http://www.geocities.com/danvlas/AutoMail.html