Jun 16 2011, 06:33 PM
I am finding that I am not retrieving all of my records from a single table query where I am selecting field#1 - dates between, "or" field#2 - dates between, "or" field#3 - dates between. I would like to get All the records if any of these criteria exist.
Basically 3 different date fields in one table.
I would like to show all the rows that any date in either of the 3 fields are between my parameters.
Also looking to return row of data if one date field IS NULL but the other date field Is between parameters.
I'm fairly new to the Access 2003 query and it's builder. Any advice would be greatly appreciated.
Jun 16 2011, 06:46 PM
Before you proceed, are you saying that your table has three separate fields that hold "dates"? If so, please describe your table and fields in a bit more detail. "Repeating fields" is more commonly associated with spreadsheets ...
Jun 16 2011, 06:51 PM
Thank you Jeff, for your time.
The table fields are:
These records will be updated if task was cancelled or when it was completed.
My report should show all newly assigned tasks within date parameter, any task that has been completed within date parameter or any task that has been cancelled within date parameter.
User will run report each month and see what has been added, completed or cancelled.
Does this help?
Jun 17 2011, 02:58 AM
You're new to the query builder. Hopefully this is not a silly answer.
se the graphical query builder.
Put the three dates you're interested in in separate columns.
The put your criteria uunder each of the three columns, but in dsifferent rows.
If you are using paramters, you will have to put the dates in three times.
CAn alternatve, which works in access but not in many othetr SQL 'flavours' is to have a single record table with your from date and to date, add that table to yor query, but do not link it.
You can then put the fields for from and to in your query, and just run it.
So rather than typing in your date each time, update the 'Parameter' table and then run the query.
Hope this give you something to think about.
Jun 17 2011, 08:33 AM
The OR statement is bad at handling more than two conditions, but you can try nesting one inside another.
Try writing your conditions this way
WHERE ((field#1 - dates between) OR (field#2 - dates between)) OR (field#3 - dates between)
it should automatically ignore null fields if another field fulfills your other conditions.
Let me know if this works!
Jun 17 2011, 10:10 AM
Absolutely not a necessity, but it might make your life easier (and you could get better use of Access) if you used a slightly different table structure. Instead of three "date" fields (by the way, think about all the work you'd have to do if you had to add a FOURTH?!?), use a single date-related field. ... and add a "type" (or "status") field. From the above description, it seems like you have multiple statuses (?stati?) for each Account. If so, have multiple records, one for each "status".
Jun 17 2011, 11:24 AM
I agree with the table structure - we are in the thinking stage of rebuilding our db.
Maybe I'm going about this all wrong.
The user needs to see those dates fields in the report. But she only wants to see those records where the STATUS FIELD has been added (New record) or changed (like updated to completed or cancelled). Would I be able to drop the multiple "OR" queries on the completed and cancelled date field and only select if the status field was changed between dates entered?
Does this make sense?
Jun 21 2011, 11:20 AM
Sounds like a matter of using the selection criteria you need in a query, then using that query as your source.
Jul 1 2011, 11:55 AM
Thank you.....That worked for me.
This is a "lo-fi" version of UA. To view the full version with more information, formatting and images, please click here