How to filter a query in Ms Access
Financial Journal Editing
I have a simple form which I propose to be used to edit some underlying data in the tables in Ms Access, the purpose of this is to help users at senior level to amend the Journal if it has the following errors:
(1) Wrong account code selected
(2) Incorrect amount
(3) Incorrect date
(4) Incorrect narration
Now I have binded a form on the query below:
Requirements
(1) If the field called tblJournalHeader.Status has a character or data in it then that record should never be populated on the form (frmJournalEditing]). The objectives here is not to edit either approved or cancelled journals. Only un actioned journals must be accessed! I have tried the filters above but still nothing is working I still see even approved or cancelled Journals.
Regards
Chris
I have a simple form which I propose to be used to edit some underlying data in the tables in Ms Access, the purpose of this is to help users at senior level to amend the Journal if it has the following errors:
(1) Wrong account code selected
(2) Incorrect amount
(3) Incorrect date
(4) Incorrect narration
Now I have binded a form on the query below:
SELECT tblJournalHeader.CreateID, tblJournalHeader.Journaltype, tblJournalHeader.FCRate, tblVoucher.AccountID, tblVoucher.Dr, tblVoucher.Cr, tblJournalHeader.Status
FROM tblJournalHeader INNER JOIN tblVoucher ON tblJournalHeader.CreateID = tblVoucher.CreateID
WHERE (((tblJournalHeader.CreateID)=[Forms]![frmJournalEditing]![txtJvEditing]) AND ((tblJournalHeader.Status) Is Null));Requirements
(1) If the field called tblJournalHeader.Status has a character or data in it then that record should never be populated on the form (frmJournalEditing]). The objectives here is not to edit either approved or cancelled journals. Only un actioned journals must be accessed! I have tried the filters above but still nothing is working I still see even approved or cancelled Journals.
Regards
Chris
Dear Daniel;
I have tried your code , but it is giving a syntax error see the screen shoot.
Regards
Chris
I have tried your code , but it is giving a syntax error see the screen shoot.
Regards
Chris
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
So then you wish to only shown those whose value IS NULL, now since you say that doesn't work it make me believe you allow zero length values.
Try
SELECT tblJournalHeader.CreateID, tblJournalHeader.Journaltype, tblJournalHeader.FCRate, tblVoucher.AccountID, tblVoucher.Dr, tblVoucher.Cr, tblJournalHeader.Status
FROM tblJournalHeader INNER JOIN tblVoucher ON tblJournalHeader.CreateID = tblVoucher.CreateID
WHERE (((tblJournalHeader.CreateID)=[Forms]![frmJournalEditing]![txtJvEditing]) AND ((tblJournalHeader.Status Is Null) OR (Len(Trim(tblJournalHeader.Status))=0));