Using Current Date in a Query

[littlewing]

For a school project, I basically need to make a query that lists dates within a month of the current date. The problem is, the dates are laid out as such: PK field, Order FK, Payment 1, Payment 2, ... , Payment 12. I need a query that will, when ran, return to me a list of the orders and payments within one month of the date. Using Month([Schedule].[Payment 1])=Month(Now()) as a criteria returns to me the entire record, not the individual dates. Is there any way to do what I want?

[June7]

Don't understand. Why would you not want the entire record? Which individual dates do you mean? Show sample source data and sample output.

Using month as criteria only returns records dated from the first of the month. Is that what you want?

[littlewing]

Don't understand. Why would you not want the entire record? Which individual dates do you mean? Show sample source data and sample output.

Using month as criteria only returns records dated from the first of the month. Is that what you want?

More specifically: Is there any way to merge multiple fields from a form into one column of a query while still maintaining data integrity with other tables? I have been able to merge all of the dates into one column using UNION, but can not add that column to a query with the customer name and phone number that correspond to each date. It instead shows every customer name with its corresponding phone number, but rather than being followed by just the dates that correspond with the names, each name/phone number is followed by every date.

[June7]

I would have to examine your data and queries if you want to provide project for analysis. Follow instructions at bottom of my post.

A UNION basically rearranges data to a normalized structure. Possibly data should be structured this way to begin with.

[littlewing]

Prototype2.accdb.zip

I would have to examine your data and queries if you want to provide project for analysis. Follow instructions at bottom of my post.

A UNION basically rearranges data to a normalized structure. Possibly data should be structured this way to begin with.

Basically, I need to have a report that will show me which payment dates are within three weeks of the current date, along with the customer that the dates apply to and phone numbers.

[June7]

Try:

Union query: CustomerPayDates
SELECT [Order FK], "P01" As PayID, [Payment 1] As PayDate FROM Schedule
UNION SELECT [Order FK], "P02", [Payment 2] FROM Schedule
...
UNION SELECT [Order FK], "P12", [Payment 12] FROM Schedule;

Then:
SELECT Customers.*, CustomerPayDates.PayID, CustomerPayDates.PayDate
FROM Customers INNER JOIN CustomerPayDates ON Customers.[Customer PK] = CustomerPayDates.[Order FK]
WHERE (((CustomerPayDates.PayDate) Between Date() And Date()+21));

[littlewing]

Try:

Union query: CustomerPayDates
SELECT [Order FK], "P01" As PayID, [Payment 1] As PayDate FROM Schedule
UNION SELECT [Order FK], "P02", [Payment 2] FROM Schedule
...
UNION SELECT [Order FK], "P12", [Payment 12] FROM Schedule;

Then:
SELECT Customers.*, CustomerPayDates.PayID, CustomerPayDates.PayDate
FROM Customers INNER JOIN CustomerPayDates ON Customers.[Customer PK] = CustomerPayDates.[Order FK]
WHERE (((CustomerPayDates.PayDate) Between Date() And Date()+21));

That works perfectly! Thanks a lot