DLookUp - Null

[premysl 71]

Can I use DLookUp with Null
I try to Try to use DLookUp function to insert "Year" value from the one
table to lable on report of another table.
It works if the value of "Year" is correct. If the DLookUp value should be
null it shows error " Wrong use of Null".
It seems like it does not read the "If" part.
Here is the code:

Dim enteredDate
enteredDate = DLookup(("Yearviolation_date])"), "tbEnterYearStreet",
"[violation_date]")
If enteredDate = Null Then
Label33.Caption = "Invalid Entry"
Else
Label33.Caption = enteredDate
End If

 

[RoyVidar]

Can I use DLookUp with Null
I try to Try to use DLookUp function to insert "Year" value from the
one table to lable on report of another table.
It works if the value of "Year" is correct. If the DLookUp value
should be null it shows error " Wrong use of Null".
It seems like it does not read the "If" part.
Here is the code:

Dim enteredDate
enteredDate = DLookup(("Yearviolation_date])"), "tbEnterYearStreet",
"[violation_date]")
If enteredDate = Null Then
Label33.Caption = "Invalid Entry"
Else
Label33.Caption = enteredDate
End If

You can't test for Null like that in VBA, that's the SQL way. You need
for instance the IsNull function

If IsNull(enteredDate) Then

But, looking at the Domain Aggregate, shouldn't you be passing a
criterion there? for instance

enteredDate = DLookup("[Yearviolation_date]", "tbEnterYearStreet", _
"[violation_date] = #" & format$(Me!txtSomeDate, "yyyy-mm-dd")& "#")

where Me!txtSomeDate is a text control on your form containing where
a date is entered?

 

[premysl 71]

You can't test for Null like that in VBA, that's the SQL way. You need
for instance the IsNull function

If IsNull(enteredDate) Then

But, looking at the Domain Aggregate, shouldn't you be passing a
criterion there? for instance

enteredDate = DLookup("[Yearviolation_date]", "tbEnterYearStreet", _
"[violation_date] = #" & format$(Me!txtSomeDate, "yyyy-mm-dd")& "#")

where Me!txtSomeDate is a text control on your form containing where
a date is entered?

I just tried the If IsNull(enteredDate) Then
and it works. I just started with the access and somehow I end up the way I
did it. I will try the other way too.
Thanks again.

 

[RoyVidar]

You can't test for Null like that in VBA, that's the SQL way. You
need for instance the IsNull function

If IsNull(enteredDate) Then

But, looking at the Domain Aggregate, shouldn't you be passing a
criterion there? for instance

enteredDate = DLookup("[Yearviolation_date]", "tbEnterYearStreet", _
"[violation_date] = #" & format$(Me!txtSomeDate, "yyyy-mm-dd")&
"#")

where Me!txtSomeDate is a text control on your form containing where
a date is entered?

I just tried the If IsNull(enteredDate) Then
and it works. I just started with the access and somehow I end up the
way I did it. I will try the other way too.
Thanks again.

What I was hinting at, is that what you actually get as a result from
your DLookup function, is probably not what you are expecting.

The last part of the Domain Aggregate function, the criterion, will
usually either not be there at all, or it will contain a criterion,
which is constructed the same way as a SQL WHERE clause, without the
keyword WHERE, i e, a field in the table and a variable or litteral
criterion. Look the function up in the helpfiles (place the cursor
within the function name, then hit F1)

So your DLookup, should probably either look like this

enteredDate = DLookup("[Yearviolation_date]", "tbEnterYearStreet")

without any criterion, or something along the lines of what I posted
in my previous reply, where the criterion part will refer to both
a field in the table, and pick up a value from the current form.

Just ask again if you need more info.

 

[premysl 71]

Thank you for your response.
Actually your first advice with the IsNull works well. It looks like it is
what I needed. What I do is that from column, with the complete date, I need
take just Year and put it like the Caption in the report label. The year is
same all records so I do not have to specify the criterion. I was look at it
too, but it works as it is. I tried your second advice and it did not work.
I just hope that it will work for the clients. It is my first project in my
Internship and I spend some time to make everything to work.
Thanks again
Premysl71