Convert sql to vba

[GinaWhipp]

Okay, so I got the WHERE clause to work but there is a problem...

WHERE (((CIAC_DATA.Sailor_Status)= '" & Me.MOB_SAIL_STATS & "') OR ((CIAC_DATA.Assigned_CIAC_Name)= '" & Me.Process_CIACs & " ') OR ((IIf([status(1)]='S' Or [status(2)]='S' Or [status(3)]='S' Or [status(4)]='S','YES','NO'))='NO'))"

But 50% of those fields are DLookup's and they will (and are) error out after resting the Record Source. Can't just use ApplyFilter because it can't find the all the Status and if they are on the FOrm, well, with the way those fields are named I couldn't find them. (In your spare time you want to give those fields names that anyone can understand. Text1 thru whatever makes it difficult for one to write code.)

My suggestion, if the query works, then use it...

 

[giddyhead]

Thanks for the information. The date is when the report was ran and yes I have to standardize the naming. The other version of the same database I will be making those name changes and literally this morning I did make those dlookup changes in a query on the other version. When I amended the change an error occurred Compile error: Invalid use of me keyword. In addition the Recordset is in the module called training in the DoSendEmail function.

 

[GinaWhipp]

On what are you getting that error?

 

[giddyhead]

I get the error message when I added the modified where clause with the Me Code in the module.

 

[GinaWhipp]

Where did you add it and exactly what does the message say?

 

[giddyhead]

I amended the original submitted code with the where clause with the Me in it in the module and the following error came up Compile error: Invalid use of Me Keyword. I modified the Me and use the forms! code to point it to the location and the following error came up: Run-time error '3061' Too few parameters. Expected 2.

 

[GinaWhipp]

Again, where are you putting the code and please post what you have no here. Can't tell what's going on without the code itself...

 

[giddyhead]

currently working on it.

 

[giddyhead]

Finding to code button and also have to have 10 post.

 

[giddyhead]

Set Rsaa = CurrentDb.OpenRecordset("SELECT CIAC_DATA.Sailor_Name, CIAC_DATA.Sailor_Status, MOB_SAILOR_INFO.[Email-Address-P], MOB_SAILOR_INFO.[Email-Address-S], CIAC_DATA.Primary_E_mail_Address, CIAC_DATA.[Secondary E-mail Address], CIAC_DATA.[Age of Oldest Family Member in Years], CIAC_DATA.[Home Phone Number], CIAC_DATA.[Work Phone Number], CIAC_DATA.[Cell Phone Number], CIAC_DATA.Assigned_CIAC_Name, CIAC_FILTER.CIAC_Name, Signature_Card.Subject_Returning, Signature_Card.Returning, Signature_Card.Subject_Executing_Orders, Signature_Card.Executing_Orders, Signature_Card.Subject_Under_Orders, Signature_Card.Under_Orders, Signature_Card.IA_Family, Signature_Card.CIAC_Members, MOB_SAILOR_INFO.[Status(1)], MOB_SAILOR_INFO.[Status(2)], MOB_SAILOR_INFO.[Status(3)], MOB_SAILOR_INFO.[Status(4)], MOB_SAILOR_INFO.[FM_Status(1)], MOB_SAILOR_INFO.[FM_Status(2)], MOB_SAILOR_INFO.[FM_Status(3)], MOB_SAILOR_INFO.[FM_Status(4)] " & _
"FROM Signature_Card RIGHT JOIN (CIAC_DATA LEFT JOIN (CIAC_FILTER RIGHT JOIN MOB_SAILOR_INFO ON CIAC_FILTER.Sailor_Name = MOB_SAILOR_INFO.SAILOR_NAME) ON CIAC_DATA.Sailor_Name = MOB_SAILOR_INFO.SAILOR_NAME) ON Signature_Card.CIAC_Members = CIAC_FILTER.Assigned_CIAC_Name " & _
"WHERE (((CIAC_DATA.Sailor_Status)= '" & Me.MOB_SAIL_STATS & "') OR ((CIAC_DATA.Assigned_CIAC_Name)= '" & Me.Process_CIACs & " ') OR ((IIf([status(1)]='S' Or [status(2)]='S' Or [status(3)]='S' Or [status(4)]='S','YES','NO'))='NO'))")

The above code has been put into a bas file and activates when a button is pushed. Hope I did the code function correctly.

 

[GinaWhipp]

Hmm, okay why the .BAS file? Why not just put it behind the Form, I know it works there.

 

[giddyhead]

no luck. I have put the code into a command button on the form and clicked on the button and following occurred. Run-time error '3061' Too few parameters. Expected 2. came up. I literally just copied and pasted the code into the form and that error message came up. If I may can you send the version that worked for you.

 

[GinaWhipp]

Wait, does the Report have parameters (criteria)?

I have not done it that way BUT if you can wait till tonight, I'm getting ready to barbecue, I *play* around with it to get a working version.

 

[vbaInet]

Yep, looks giddyhead is using a recordset queries and if one of those queries includes parameters the recordset it will throw an error.

You'll have to use a querydef to open the query, pass the parameters to the querydef, set a recordset object to the querydef's recordset and run it from there.

Gina, please save us some sausages.

 

[GinaWhipp]

This is what worked for me...

DoCmd.ApplyFilter , "(((CIAC_DATA.Sailor_Status)= '" & Me.MOB_SAIL_STATS & "') OR ((CIAC_DATA.Assigned_CIAC_Name)= '" & Me.Process_CIACs & " ') OR ((IIf(MOB_SAILOR_INFO.[status(1)]='S' Or MOB_SAILOR_INFO.[status(2)]='S' Or MOB_SAILOR_INFO.[status(3)]='S' Or MOB_SAILOR_INFO.[status(4)]='S','YES','NO'))='NO'))"

 

[giddyhead]

That is correct I want to send emails to personnel based upon a query and put it into a recordset.

 

[GinaWhipp]

Did you try the line I posted that works for me? Did it work for you?

 

[giddyhead]

I just tried the code and it does filter the issue right now it does not put everything in email so I can send. I added a few ands due to the multiple matches needed.

 

[vbaInet]

What do you actually mean by it doesn't put everything in an email? Is the problem you have with your query sorted?

 

[GinaWhipp]

@giddyhead

Umm, first I heard we were working on an eMail. I thought the only issue was the filtering. I don't even see an eMail button on the sample file sent, please elaborate...

EDIT/ADD: I just found the post saying you want to send eMails. Please provide how you are doing that and what problem you are having.