Access Query to include null

[reggiete]

Hello All,

I have a query i created that pull notes for a record set. The issue i am having is for records that dont have any notes are not being pulled within the query.

Action Taken Notes: [Action Taken].[Date] & "-" & [Action Taken].[Notes]

I believe i need to add in the Isnull to the statement above to include all records even those without any Action Taken Notes.

 

[pbaldy]

Not sure what you mean, as what you posted would not affect which records were returned by the query. You'd end up with:

5/17/2016-

 

[GohDiamond]

SELECT [Action Taken].[Action Date] & "-" & [Action Taken].[Action Notes] AS [Action Taken Notes]
FROM [Action Taken];

Produces:
Action Taken Notes
4/1/2016-Ate an Apple
4/2/2016-Ate a Pear
4/3/2016-
4/4/2016-Bought another Apple
4/5/2016-Bought another Pear
4/6/2016-
4/7/2016-

Which includes records with or without notes, do you have some limiting criteria in your query? Seems pretty simple to me in as far as you've explained the problem.
Also don't use DATE as a field name as it's a reserved word in Access and could cause confusion later.

Cheers!
Goh

 

[reggiete]

Heres the issue, there are records missing from the query. The records that are missing from the query are records that have no text in the Action Taken Date/Notes field.

 

[pbaldy]

There must be a criteria on that field. What's the SQL of the query?

 

[reggiete]

i believe i need to add in isNull[Action Taken].[Date] & "-" & [Action Taken].[Notes] so that the query will pull records that have null values in the Action Taken Date/Action Taken Notes

 

[reggiete]

SELECT RTMasterTbl.[Date of Request], RTMasterTbl.[Requestor Dep], RTMasterTbl.[Loan Number], RTMasterTbl.[Borrower Name], RTMasterTbl.[Detailed Description of Issue], RTMasterTbl.Category, RTMasterTbl.[Sub Category], RTMasterTbl.[Team #], RTMasterTbl.Performer, RTMasterTbl.[RT Final Rating], RTMasterTbl.[Loan Amount], RTMasterTbl.[NPV Savings], RTMasterTbl.Status, RTMasterTbl.[Final Resolution], RTMasterTbl.[Rec to HFI or S/R], [Action Taken].[Date] & "-" & [Action Taken].[Notes] AS [Action Taken Notes], [Research Notes]![Date] & "-" & [Research Notes]![Notes] AS [Research Comments]
FROM (RTMasterTbl INNER JOIN [Research Notes] ON RTMasterTbl.ID = [Research Notes].ID) INNER JOIN [Action Taken] ON RTMasterTbl.ID = [Action Taken].ID
WHERE (((RTMasterTbl.[Date of Request]) Between [Enter Start Date] And [End Date]) AND ((RTMasterTbl.[Requestor Dep])="Investor Delivery"));

 

[reggiete]

I have criteria on the Date of Request with a parameter to enter a date and criteria on Requestor Dept

 

[pbaldy]

I suspect the joins are the problem. Try changing INNER to LEFT.

 

[reggiete]

ok changing inner to Left seemed to work. Thanks Man!!!!!!!!!!!!!!!

 

[pbaldy]

Happy to help!

 

[reggiete]

Hey one more question, with the above query my output is like this:

Loan Number|Action Taken Date|Action Notes | Combined Field
12345465 5/1/2016 Hey im cool 5/1/2016-Hey Im cool
12345465 5/2/2016 Hey im cool 5/2/2016-Hey Im cool
12345465 5/3/2016 Hey im cool 5/3/2016-Hey Im cool

Is there a way to combine the above text to output to where the loan number shows once and the combine field text has all of the text string in concatenate combined field all together?

 

[pbaldy]

Sounds like this type of thing:

http://allenbrowne.com/func-concat.html