Do you have two table fields or just one? What are the names of the field(s)? What kind of data is in them?
I have to ask since if you have text data you may end up with all kinds of inputs.
By which I mean, Quarters might be entered as "Quarter1", "Q1", "Quarter 1", "Q 1", "1", "One". And years might be entered as "2018" or "18"
This will be better with *your* test data but since I don't have it I'll create my own test data!
So,
assume a table called tbl4 with quarters like this: {"Q1", "Q2", "Q3", "Q4"}, and years like this ("2018", "2019", ...).
We will call the fields CompletedQuarter and CompletedYear.
EDIT: Note that quarters stored as simply {"1", "2", "3", "4"} should also work here too, as well as {"01", "02", "03", "04"}.
then an example of creating a date out of the text values for quarters and years would be:
Code:
SELECT
T.*
FROM
(
select
DateSerial([CompletedYear], CInt(Right("0" + [CompletedQuarter], 1)*3)+1, 0) as MyDate
from
tbl4
where
(
Nz(CompletedYear,"") <> ""
and
Nz(CompletedQuarter,"") <> ""
)
UNION ALL
select
Null as MyDate
from
tbl4
where
(
Nz(CompletedYear,"") = ""
or
Nz(CompletedQuarter,"") = ""
) as T
ORDER BY
T.MyDate
By way of example for someone else looking at this who might have integers instead of text,
so assume a second table called tbl5 with quarters like this: {1, 2, 3, 4}, and years like this (2018, 2019, ...).
Here too the fields are called CompletedQuarter and CompletedYear.
then an example of creating a date out of the integer values for quarters and years would be:
Code:
SELECT
T.*
FROM
(
select
DateSerial([CompletedYear], (([CompletedQuarter]*3)+1), 0)as MyDate
from
tbl5
where
Nz(CompletedYear, 0) <> 0
and Nz(CompletedQuarter, 0) <> 0
UNION ALL
select
Null as MyDate
from
tbl4
where
(
(nz(CompletedYear, 0) = 0)
or
(nz(CompletedQuarter, 0) = 0)
)
) as T
ORDER BY
T.MyDate
In both cases the last day of the month is determined in the slightly non-intuitive way of getting the first day of the *next* month and then subtracting one (which is why in the DateSerial() formulas we add 1 to the month (the next month) and take the 0th day (the day before the first day). This is just an old hack in VB for working with dates. This way we don't have to worry whether months have 30 days or 31 days, or even 28 or 29 days.
Also in both cases we don't want null to show up as a weird date like "12/31/1999" so the strategy is to partition the data into two sets: those that have years and quarters, and those that don't. These should be two sets that together make up the entire data set so when union-ed together you have complete data again (if you take all rows with no nulls in either year or quarter, and add to that all rows with a null in one or both of those two fields, then you should have all rows in the table).
Finally, since it's a little weird working with UNION and you might be interested in doing things like ordering the results, the entire thing is wrapped up a a query on a subquery so we can add an order by at the end. The essential pattern of this overall query of a (sub)query is very simple:
Code:
select T.* from (subquery) as T order by X,Y,Z
EDIT: I can't stress enough how important it is to provide information about your data, so we can write the proper solutions out and test them on your data! I've given examples with my own made up test data, but it's practically guaranteed that this will all have to be tweaked or even started over from scratch, because your data will not be the same as the data that I've created. So always give examples and even better names and datatype information for all you relevant tables and fields.
