Dlookup problem - Microsoft Access Forum


	Back to Access Forum Comments List

Upload Images @Reply Bookmark Link Email Next Unseen

Dlookup problem

Eric Michalek

6 years ago

I need help with some Dlookup code.
My form (CriticF) Record Source (CriticT) has a Command button (QA14B).
When the form opens, the OnCurrent event should:
look in the table (QA14T) for any records that have field (B5) filled in but field (B6) blank.
-It makes the Qa14B Backcolor yellow (to show that they have a record needing their actions)

'what I am trying to get working
If DLookup("IDQa14", "Qa14T", "(not isnull(B5Date) or B5Date  '') AND (isnull(B6Date) or B6Date = '')") Then
    Qa14B.BackColor = 62207
    End If
'The first part  DLookup("IDQa14", "Qa14T", "(not isnull(B5Date) or B5Date  '') seems to work fine
'But the second part seems to stop the code

Eric Michalek OP @Reply

6 years ago

My code didn't copy correctly.  This is actually what my code currently looks like:
  If DLookup("IDQa14", "Qa14T", "(not isnull(B5Date) and B5Date  """") AND (isnull(B6Date) or B6Date = """")") Then

If I only use the first part and change the "and" into an "or":
  If DLookup("IDQa14", "Qa14T", "(not isnull(B5Date) or B5Date  '')") Then
The code continues to run the rest of my on current event.

I don't see why it doesn't work.  And that seems to be the issue.

Richard Rost @Reply

6 years ago

Your criteria doesn't seem to make sense. Are you checking for empty strings? A date field shouldn't ever have an empty string. Only string (text) values would. So you should be able to say:

"Not IsNull(B5Date) AND IsNull(B6Date)"

Not sure what all the other quotes are for. If this is a TEXT field, then that's a different story.

"(Not IsNull(B5)) AND (IsNull(B6) AND B6<>"""")"

Stay away from ' characters. They get confusing. Use ""

Eric Michalek OP @Reply

6 years ago

Since I posted, I changed to the code from B5Date and B6Date (date fields) to B5 and B6 (numbers for employee who signed the blocks).  So it reads:
If DLookup("IDQa14", "Qa14T", "(not isnull(B5) and B5   ' ') AND (isnull(B6) or B6Date = ' ')") Then
So it is supposed to look for records that has a badge number in B5 (there are times where B5 badge number can be removed, such as reworking the job).
The second part of the Dlookup, B6. Looking for a record that does not have a badge number in B6.  (like B5, B6 could have had a number at one time)

Update, I changed the code again.  And the following seems to work
If DLookup("IDQa14", "Qa14T", "(not isnull(B5) and B5  1) AND (isnull(B6) or B6 1)") Then
(Since badge numbers are greater than the number 1, I can get around the problem with empty strings.

So... if I needed to get it to work with empty strings (in the future), how would I write this code?

	Eric Michalek OP @Reply 6 years ago
I'm sorry, for some reason, I can't send you text with the greater than or less than symbols. So... it should have greater than symbol as in B5 greater than 1. And less than as B6 less than 1.

	Richard Rost @Reply 6 years ago
That should work fine: (Not IsNull(B5) And B5>1) AND (IsNull(B6) OR B6<1)

This thread is now CLOSED. If you wish to comment, start a NEW discussion in Access Forum.

Next Unseen

New Feature: Comment Live View