SQL: Selecting Data

[JWalcott]

I have two Tables: Plans and Tracking. One field in common for these tables, Plans.ID = Tracking.PlanID. Each plan has multiple years of tracking. So plan01 has one row of data in Plans and has multiple years of data in Tracking.
OK, when selecting which plans to view on a form, the user can select criteria based upon fields in both tables.
I am not sure if I need to join these tables temporarily to handle the WHERE clause.
User selects:
ADMIN = Joe Blow
Year = 2003
Criteria1 = True
Criteria2 = True
Criteria3 = False
ADMIN is a field in Plans. Year, Criteria1, Criteria2, and Criteria3 are fields in Tracking.
The result of the criteria search is all of the data in Plans where data in the fields Plans.ADMIN, Tracking.Year, Tracking.Criteria1, Tracking.Criteria2 and Tracking.Criteria3 match the options the user selected.

I am looking for the SQL syntax to provide the desired search results. Any help would be appreciated.

Cheers,
Jason

 

[JWalcott]

Here is what I have so far:

selsql = "SELECT * FROM [Plans], [TrackingNew] WHERE (((Plans.Admin)=""" & ADMIN & """) AND ((TrackingNew.[Year])=""" & TYear & """)AND((TrackingNew.[Census Sent])=""" & Criteria1& """) AND ((TrackingNew.[Census Received])=""" & Criteria2 & """) AND ((TrackingNew.[ADP Test Completed])=""" & Criteria3 & """) AND ((TrackingNew.[5500 Date])=""" & Criteria4 & """));"

[Forms]![Plans].RecordSource = selsql

And I am getting an error message. Any help on where I am going wrong would be greatly appreciated.

 

[anvil19]

Hi Jason

Your SQL statement looks OK to me. However, I have had occasions when error messages popped up and try as I may I could not find the problem It turned out to be the spaces between the leters, numbers, statements and constants in the SQL statement. The bits in bold, are places where there could be a space (not sure if I got all of them but you see my point).

selsql = "SELECT * FROM [Plans], [TrackingNew] WHERE (((Plans.Admin)=""" & ADMIN & """) AND ((TrackingNew.[Year])=""" & TYear & """)AND((TrackingNew.[Census Sent])=""" & Criteria1& """) AND ((TrackingNew.[Census Received])=""" & Criteria2 & """) AND ((TrackingNew.[ADP Test Completed])=""" & Criteria3 & """) AND ((TrackingNew.[5500 Date])=""" & Criteria4 & """));"

Try that

anvil19

 

[JWalcott]

OK, this part is working properly now. The appropriate records are selected and a form can be opened with only the selected records!!! The user also needs to be able to view and print a report instead of viewing the records on a form. I need to establish the record set of a report to the selected records. How do I view the selected data in a preexisting report or a new report?

selsql = "SELECT Plans.* FROM Plans INNER JOIN TrackingNew ON Plans.ID = TrackingNew.PlanID WHERE (((Plans.Admin) = """ & SelAdministrator & """) AND ((TrackingNew.[Year]) = """ & TYear & """) AND ((TrackingNew.[Census Sent]) = -1) AND ((TrackingNew.[Census Received])= -1) AND ((TrackingNew.[ADP Test Completed])=-1));"

'View the only selected records on a form
[Forms]![Plans].RecordSource = selsql
[Forms]![Plans].Requery

'View selected records in a report
????????????????

Any help would be appreciated.

Thanks,
Jason

 

[anvil19]

Hi Jason

I would just right click the query and SaveAs a report. Then just manipulate the properties etc. as you wish.

Any help??

anvil19

 

[JWalcott]

Thanks but maybe I did not explain clearly, the whole process has to be executed using VBA.. The query is changing based upon the user's input. selsql is just a variable as far as I know, so there is not an actual query.

If I could save the selected records in a temporary query and have a report based upon the query, that would work. The report would already exist and would already be formatted, it just needs the data and to be opened. I am looking for the VBA syntax to do this.

Jason