asked on

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

Nathan Riley

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.

urjudo

ASKER

RejDate is already system date, I need to pull the note from Note field that the date on the note is match the RejDate.

Nathan Riley

select noteField
from table
where RejDate = DATE_FORMAT(NOW(),'%m-%d-%y')

Open in new window

Rey Obrero (Capricorn1)

urjudo,
you will need a user define function to accomplish this. how is your VBA coding?

urjudo

ASKER

attach is the screen shot. I need to pull the first note in the query
Doc4.docx

Rey Obrero (Capricorn1)

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?

urjudo

ASKER

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

urjudo

ASKER

This form is how I added the note on today's date.

urjudo

ASKER

when I tried it, here is the error
Doc5.docx

Rey Obrero (Capricorn1)

what did you try?

and please stop embedding word document, use .jpg, .bmp to upload image.

Rey Obrero (Capricorn1)

better, just upload a copy of the db.

urjudo

ASKER

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

Rey Obrero (Capricorn1)

here, run Query1
the function "ExtractNote" the note is on module1
PDTest-Rev.accdb

urjudo

ASKER

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

ASKER CERTIFIED SOLUTION

Rey Obrero (Capricorn1)

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

urjudo

ASKER

Hi Rey Obrero,
Thank you so much for your BIG helped. Very very appreciated!!!