Start/End dates get week numbers (1 Viewer)

[Jayce72]

I have a table with start and end dates.

I need to build a query to show the next 6 months everything that has:

- already started
- going to start
- that has not ended


Then I need to show each line of what calendar week it fits under if it's live.

Yes, I am aware i will get dup lines - but thats ok

 

[plog]

Please provide sample data to demonstrate your issue. Provide 2 sets of data:

A. Starting sample data from your tables. Include table and field names and enough cases to demonstrate all possible issues.

B. Expected results based on A. When you run the data in A, show me exactly what data you expect.

 

[almahmood]

Hi,

Try this query. You will get what you want. This should not result any duplicates until and unless you have any duplicate data.

Select * from YourTable
WHERE EndDate between Date() and DateAdd("m",6,Date())
OR StartDate between Date() and DateAdd("m",6,Date())
OR (StartDate<=Date() and EndDate is null)

Let me know if you run into any issue.