Jim;
Thank you for the detailed explanation....question I will need to use a date criteria how do I set that up....do i need to create a query prior to running the sql you provided? Then I need to display the information on a report...
Thank you,
Steve
1) Create a table called tblDateCriteria consisting of two fields
first field called 'DateFrom' and the second field called 'DateTo'
both of these obviously with datetime format
2) You can if you wish set a composite primary key for this table
it is not essential though (ie select the two fields in table design ensure they are both selected and hit the primary key tool on the menubar. both fields will acquire the Access yellow key denoting them both as part of a composite primary key.
3) Why are we doing this in 2) above? it merely ensures you put a value in both fields for the row record save to succeed thats all you can do it other ways if you wish but for the purposes of this this is the quickest method thats all.
4) Open the table put a date from value in and a date to value in (this will be your criteria) Close the table.
5) Create a form based on the table and save it as 'fdlgDateCriteria'
6) Jig around with this form make it pretty or whatever, make it a dialog popup, centred on screen etc, whatever you wish the usual stuff. Put a close button on it blah blah blah... remove the record selector and navigation buttons and in the form design set the form property to
NOT allow additions.
7) If you are with me thus far when you open the form you will see your date criteria in the fields but not be able to delete the ROW (no selectors and not be able to add anything either, which doesnt matter because you have access to the table. In effect you have a ONE row form record that will always be there so to speak.
9) Now with your main yes/no table thing which I have no idea of what the real field names are this end, create a query based on the table drag all fields to the grid, then add the tblDateCriteria table to the query window
BUT DO NOT JOIN THEM leave them as they are (this is the cross join I spoke of) Save the query as 'qryTest' or something you can always rename it
10) Now if you look at the SQL statement I did.... the syntax must match the fieldnames. you either edit my SQL to suit your fieldnames
OR if you quickly want to see the results as is, without affecting your data as is, then create a copy of your table and name it as tblYourTableName explicitly substitute your fieldnames in this table for mine and do with this table that which you did in 9) above.
11) Remember I have created the SQL query my end with no knowledge of your table name and fields, you obviously have more fields than I created in the query
so you'll have to build that by looking at my fieldnames and substituting it with yours.......I' m sure you get me.
12) When you succeed in replicating the query structure.......when you then open the query you will see only rows returned where the survey date is between the dates provided by the cross joined table thats its.... so to speak. You don't need to do anything else.
13) Obviously when you need to edit the date criteria you will have the flexibility of being able to open the dialog form, edit the values close it and run the query again and so on. (No editing the query grid on this one you are in effect doing what is referred to in Access parlance as QBF query by form or a subtly different version of it)
14) Any Report you create can be based on the same query and obviously with it being so... the report will open exactly the same ....nothing more to add.... it gets it data from the underlying query the date parameters of which are passed by this CROSS JOIN method all part and parcel of the query itself rather unusual you might think!
You might want to look up the purpose of 'cross join' on the net. In this scenario we are putting it to a somewhat 'useful' purpose the logic in this case being (compare all rows in one table with all rows in the other table) the twist in actual fact here is to compare only ONE row in one table with hundreds or maybe thousands in the other and in so doing, restrict the return set by the values of the dates 'from and to' in one of the tables compared against the 'survey date' in the other you'll get the hang of it!!
I am a high speed typist Steve so don't worry about the length of this...but I can't read very fast hahahahahahahahaha
Hope this helps
Jim