Arvil - Ajax - Thanks for pitchin in on this one
Arvil - I did add Txt to Printed - unfortunately, same blank results
Ajax - Good to see you're still out there...
Ok, so I did assign the query to a String Variable so I can view it in the immediate window -
However, I may not be clear on the what you mean about copy/pasting into a query to get more detailed error messages - perhaps you can eloborate.
Also, I thought by putting the ampersand then a space then the quotation mark I was putting a space at the front of each line - so I may be unclear on that as well.
Finally, for the IIf statement I have tried the following: 0; '0'; "0"; #0#; False; 'False'; "False" And #False#
Some efforts produced predictible errors while the others produced more of the same - nothing.
Below are the queries that work in SQL Server 2014 & in the Access VBE - Plus a few iterations I have tried in VBE that fail (No errors - but no results)
This produces the correct results in VBE - (But with a zero in the Printed field of the ListBox)
Code:
Me.ListSearchResults.RowSource = _
"SELECT RefNum, UIN, Recipient, RequestDate, RqstProvdrName, PatientName, Printed, ID " _
& "FROM tblUINPort " _
& "WHERE (Printed = False AND DataEntered = True)" _
& "ORDER BY [RequestDate] DESC;"
This produces the correct results in SQL 2014 - (With 'No' in the Printed field)
Code:
SELECT RefNum, UIN, Recipient, RequestDate, RqstProvdrName, PatientName, IIf(Printed = 0, 'No', 'Yes') AS Printed, ID
FROM tri.UINPort
WHERE (Printed = 'False' AND DataEntered = 'True')
ORDER BY [RequestDate] DESC
The following all produce no results
Code:
Me.ListSearchResults.RowSource = _
"SELECT RefNum, UIN, Recipient, RequestDate, RqstProvdrName, PatientName, IIf(Printed = 0 'No', 'Yes') AS Printed, ID " & _
"FROM tblUINPort " & _
"WHERE (Printed = False AND DataEntered = True) " & _
"ORDER BY [RequestDate] DESC;"
Code:
StrSQL = "SELECT RefNum, UIN, Recipient, RequestDate, RqstProvdrName, PatientName, IIf(Printed = 0 'No', 'Yes') AS Printed, ID " _
& "FROM tblUINPort " _
& "WHERE (Printed = False AND DataEntered = True) " _
& "ORDER BY [RequestDate] DESC;"
Me.ListSearchResults.RowSource = StrSQL
Code:
StrSQL = "SELECT RefNum, UIN, Recipient, RequestDate, RqstProvdrName, PatientName, IIf(Printed = 0 'No', 'Yes') AS Printed, ID " & _
"FROM tblUINPort " & _
"WHERE (Printed = False AND DataEntered = True) " & _
"ORDER BY [RequestDate] DESC;"
Me.ListSearchResults.RowSource = StrSQL