Need Query to Return same record twice

[smaumau]

Each record in my table has six possible sale dates as a result of cancellations. My problem results when a record is sold twice during the queried time frame (SaleDate1 and SaleDate2). Currently my query uses an if statement and if they are in the same time period, it will only return SaleDate1. I would like to see the record returned twice (once for SaleDate1 and once for SaleDate2). Any suggestions?

 

[davewhite04]

Would removing the If statement not solve this? (I may have misunderstood you here).

 

[smaumau]

The column in the query is as follows:
SaleDate: IIf([salesdate1] Between [Forms]![frmFlash]![StartDate] And [Forms]![frmFlash]![EndDate],[salesdate1],IIf([salesdate2] Between [Forms]![frmFlash]![StartDate] And [Forms]![frmFlash]![EndDate],[salesdate2],IIf([salesdate3] Between [Forms]![frmFlash]![StartDate] And [Forms]![frmFlash]![EndDate],[salesdate3],IIf([salesdate4] Between [Forms]![frmFlash]![StartDate] And [Forms]![frmFlash]![EndDate],[salesdate4],IIf([salesdate5] Between [Forms]![frmFlash]![StartDate] And [Forms]![frmFlash]![EndDate],[salesdate5],[salesdate6])))))

The If statement should make more sense here. I enter the start date and end date of the report on the form frmFlash. I need the query to look through all of my records and find the ones with a sale date during the given time period. This query works great when the property does not have multiple sale dates in the range (SalesDate1, SalesDate2, SalesDate3, etc.). However, if it does I need to display the record twice, once for SalesDate1 and once for SalesDate2. I hope helps to clarify. Thank you in advance.

 

[davewhite04]

I'll have a look at this tomorrow, in the meantime if anyone else cares to tackle it feel free, I'm pretty sure it's possible what you're trying to do though.

 

[davewhite04]

SELECT RECORDID,IIf([salesdate1] Between [Forms]![frmFlash]![StartDate] And [Forms]![frmFlash]![EndDate],[salesdate1]) As Saledate
FROM Table3
UNION SELECT RECORDID,IIf([salesdate2] Between [Forms]![frmFlash]![StartDate] And [Forms]![frmFlash]![EndDate],[salesdate2]) As Saledate
FROM Table3
UNION SELECT RECORDID,IIf([salesdate3] Between [Forms]![frmFlash]![StartDate] And [Forms]![frmFlash]![EndDate],[salesdate3]) As Saledate
FROM Table3
UNION SELECT RECORDID,IIf([salesdate4] Between [Forms]![frmFlash]![StartDate] And [Forms]![frmFlash]![EndDate],[salesdate4]) As Saledate
FROM Table3
UNION SELECT RECORDID,IIf([salesdate5] Between [Forms]![frmFlash]![StartDate] And [Forms]![frmFlash]![EndDate],[salesdate5]) As Saledate
FROM Table3
UNION SELECT RECORDID,IIf([salesdate6] Between [Forms]![frmFlash]![StartDate] And [Forms]![frmFlash]![EndDate],[salesdate6]) As Saledate
FROM Table3;

I think this is what you need, I could be wrong though(I'm assuming an item cannot be cancelled twice).

Hope it helps.

 

[smaumau]

I appreciate you helping me. Perhaps I am missing a step somewhere, but it is not allowing me to use the above statement in my query. I received an error message as follows: "The syntax of the subquery in this expression is incorrect. Check the subquery's syntax and enclose the subquery in parenthesis." I could be missing a step, but I created a new query to test out what you gave me and included the table (table3) and simply pasted your statement into the query. Thank you for your help and I appreciate your patience as I am fairly new at Access.

Thanks again.

 

[davewhite04]

Strange... When you create the new query don't add any tables or nothing. Click on SQL to open the SQL window and make sure when you paste there is only one ";" at the end of the statement.

I have modified the statement to handle potential problems with Nulls(fields that contain nothing). Replace tblTEST with the name of a table that contains the fields: RecordID, salesdate1,salesdate2,salesdate3,salesdate4,salesdate5,salesdate6

SELECT RECORDID,IIf([salesdate1] Between [Forms]![frmFlash]![StartDate] And [Forms]![frmFlash]![EndDate],[salesdate1]) As Saledate
FROM tblTEST
WHERE Not IsNull(salesdate1)
UNION
SELECT RECORDID,IIf([salesdate2] Between [Forms]![frmFlash]![StartDate] And [Forms]![frmFlash]![EndDate],[salesdate2])
FROM tblTEST
WHERE Not IsNull(salesdate2)
UNION
SELECT RECORDID,IIf([salesdate3] Between [Forms]![frmFlash]![StartDate] And [Forms]![frmFlash]![EndDate],[salesdate3])
FROM tblTEST
WHERE Not IsNull(salesdate3)
UNION
SELECT RECORDID,IIf([salesdate4] Between [Forms]![frmFlash]![StartDate] And [Forms]![frmFlash]![EndDate],[salesdate4])
FROM tblTEST
WHERE Not IsNull(salesdate4)
UNION
SELECT RECORDID,IIf([salesdate5] Between [Forms]![frmFlash]![StartDate] And [Forms]![frmFlash]![EndDate],[salesdate5])
FROM tblTEST
WHERE Not IsNull(salesdate5)
UNION SELECT RECORDID,IIf([salesdate6] Between [Forms]![frmFlash]![StartDate] And [Forms]![frmFlash]![EndDate],[salesdate6])
FROM tblTEST
WHERE Not IsNull(salesdate6);

Give me a shout if you still get errors.

 

[smaumau]

Very nice work, it works like a charm now. I did forget to ask you one thing. I need there to be another If statement to return not only records between those two dates, but also make the field Division from my table equal to[forms]![frmflash]![Division]. You are a lifesafer and I appreciate all of your help.

 

[smaumau]

The query did eliminate most of the null dates, but included a few (4 out of 273) blank dates. Any thoughts?

 

[smaumau]

David - thank you so much for your help. I was able to solve the division problem and that seemed to eliminate the blank dates for whatever reason. Thanks again.

 

[davewhite04]

David - thank you so much for your help. I was able to solve the division problem and that seemed to eliminate the blank dates for whatever reason. Thanks again.

No problem.

The blank dates you were getting might not have actually been Nulls, but empty strings instead. For example if a user enters a record of sale then decides to clear the date field and leave the record half completed the record will contain an empty string instead of a Null in the date field.

Nevermind, you seem to have sorted it