Query question

Hi Experts,
I have a query question.  I have a form that user enter a ReJ Date and a note.  I want to run a query that pull all the note that user entered that match the RejDate. (Rej Date is a system date).  For example:
C#01234567,        
               RejDate : 5/13/15
on the note field:  waiting for respond.  J.Doe  4/8/15
                                 called client, no answer.   5/1/15 M. White
                                 05/13/15  called client again.  M. Whit  5/13/15

How do I only pull the last note that either the date at the Begin or the date in the end is match the Rej Date.  I do not need the first two because the first two note date are not match the RejDate.  I need to do this in the query, so I guess I need to use an Expr1:  

Thanks
urjudoAsked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Nathan RileyFounderCommented:
How are you getting the system date to compare and what format is it in?  The same as your dataset above?

I would do:

select *
from table
where RejDate = 'SystemDate'

Open in new window


If the systemdate is formatted differently, you'll need to convert that first so it compares correctly.
0
urjudoAuthor Commented:
RejDate is already system date, I need to pull the note from Note field that the date on the note is match the RejDate.
0
Nathan RileyFounderCommented:
select noteField
from table
where RejDate = DATE_FORMAT(NOW(),'%m-%d-%y')

Open in new window

0
Ultimate Tool Kit for Technology Solution Provider

Broken down into practical pointers and step-by-step instructions, the IT Service Excellence Tool Kit delivers expert advice for technology solution providers. Get your free copy now.

Rey Obrero (Capricorn1)Commented:
urjudo,
you will need a user define function to accomplish this. how is your VBA coding?
0
urjudoAuthor Commented:
attach is the screen shot.  I need to pull the first note in the query
Doc4.docx
0
Rey Obrero (Capricorn1)Commented:
here is a query to find the record with PDREJDATE in the PDNotes field

select PDNotes, PDREJDATE
from table
where Instr([PDNotes], [PDREJDATE]) >0

now to get the particular line in the PDNotes field, as I mentioned above you need a VBA user define function.

again, how is your vba coding?
0
urjudoAuthor Commented:
you mean when user enter the note to the note field?

I create a pop up form when user need to add new note to the case because the Note on the PD form note Field is lock if there is any previous note (this to prevent any old notes get delete from user).  so the coding on the pop up form is

Forms!frmEventView_PleadingLAR.PDNOTES = Format(Date, "mm/dd/yyyy") & " " & Me.txtAddNote & Chr(13) & Chr(10) & Forms!frmEventView_PleadingLAR.PDNOTES
0
urjudoAuthor Commented:
This form is how I added the note on today's date.
0
urjudoAuthor Commented:
when I tried it, here is the error
Doc5.docx
0
Rey Obrero (Capricorn1)Commented:
what did you try?

and please stop embedding word document, use .jpg, .bmp to upload image.
0
Rey Obrero (Capricorn1)Commented:
better, just upload a copy of the db.
0
urjudoAuthor Commented:
ok Rey Obrero,
here is the db.
I have two forms and a report.  Main form is frmPD.  the is only one report that is I'm working on.  you can see more than one note but I only need the one that either date in the note (either the date in the beginning or after) match the ReJdate
PDTest.accdb
0
Rey Obrero (Capricorn1)Commented:
here, run Query1
the function "ExtractNote" the note is on module1
PDTest-Rev.accdb
0
urjudoAuthor Commented:
hi Rey Obrero,
your version works perfect.  I still have one more question that if there is no any date that match the RejDate, I would like to print all the note out.  I had some code on the report, but it seems not working.
PDTest-Rev.accdb
0
Rey Obrero (Capricorn1)Commented:
change this part of the codes

ExtractNote = strNote

with

If strNote & "" = "" Then
    ExtractNote = vNotes
Else
    ExtractNote = strNote
End If
0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
urjudoAuthor Commented:
Hi Rey Obrero,
Thank you so much for your BIG helped.  Very very appreciated!!!
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft Access

From novice to tech pro — start learning today.

Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.