Tek-Tips is the largest IT community on the Internet today!

Members share and learn making Tek-Tips Forums the best source of peer-reviewed technical information on the Internet!

  • Congratulations strongm on being selected by the Tek-Tips community for having the most helpful posts in the forums last week. Way to Go!

How to make SQL work in VBA

Status
Not open for further replies.

cer5yc

Programmer
Sep 27, 2007
103
US
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])))))
 
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
 
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])))))
 
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
 
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!!
 
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
 
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.
 
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...



[pipe]
Daniel Vlas
Systems Consultant

 
Status
Not open for further replies.

Part and Inventory Search

Sponsor

Back
Top