list a field based on a date

[Lila]

I have a table that is a list of contacts.

Info_FirstName
Info_LastName
Info_NewOwner
Info_SaleDate

I need to create a query that shows the name and the sale date. The name
needs to show Info_FirstName, Info_LastName if the Info_SaleDate is before
10/31/2003 and the Info_NewOwner if Info_SaleDate is after 10/31/2003.

I created a query and in the Field I wrote
Owner: IIf(Between Date(10/31/2003) And ( ) [Info_SaleDate]),
[Info_NewOwner], ([Info_FirstName] & " " & [Info_LastName]))

But I get an error The expression you entered contains invalid syntax. I've
tried about a dozen different ways, but I still get the same message, what
am I doing wrong?

 

[John Spencer]

Would this work for you?

Owner: IIf(Info_SaleDate <= #10/31/2003#,[Info_NewOwner], [Info_FirstName] & " "
& [Info_LastName])


If Info_NewOwner _NEVER_ has a value if Info_FirstName or Info_LastName do have
a value then, you could even use

Owner: NZ(Info_NewOwner,[Info_FirstName] & " " & [Info_LastName])

 

[Lila]

The first one gives the same syntax error, but your second one works great!

Would this work for you?

Owner: IIf(Info_SaleDate <= #10/31/2003#,[Info_NewOwner], [Info_FirstName] & " "
& [Info_LastName])


If Info_NewOwner _NEVER_ has a value if Info_FirstName or Info_LastName do have
a value then, you could even use

Owner: NZ(Info_NewOwner,[Info_FirstName] & " " & [Info_LastName])

I have a table that is a list of contacts.

Info_FirstName
Info_LastName
Info_NewOwner
Info_SaleDate

I need to create a query that shows the name and the sale date. The name
needs to show Info_FirstName, Info_LastName if the Info_SaleDate is before
10/31/2003 and the Info_NewOwner if Info_SaleDate is after 10/31/2003.

I created a query and in the Field I wrote
Owner: IIf(Between Date(10/31/2003) And ( ) [Info_SaleDate]),
[Info_NewOwner], ([Info_FirstName] & " " & [Info_LastName]))

But I get an error The expression you entered contains invalid syntax. I've
tried about a dozen different ways, but I still get the same message, what
am I doing wrong?

 

[John Spencer]

That's strange the first one should not give you a syntax error. It might
fail, but it should not give a syntax error.
Is the expression being typed into the query all on one line?
Does Info_SaleDate contain a space? Just in case try surrounding the
fieldname with []
Did you copy and paste the expression from the post? Sometimes that
introduces extraneous characters that have to be removed. This usually
happens if the newsreader has wrapped the expression onto two or more lines.
I've added two dashed lines below. All the text between the lines should be
on ONE line.

-------------------------------------
Owner: IIf([Info_SaleDate] > #10/31/2003#,[Info_NewOwner], [Info_FirstName]
& " " & [Info_LastName])
-------------------------------------

In revisiting this I see that I did reverse the logic of what you wanted to
show. So in the above, I changed the comparison to "greater than" instead
of "less than or equal to".

Anyway, if the second method is working for you, then this is just an
attempt to increase your knowledge.

The first one gives the same syntax error, but your second one works
great!

Would this work for you?

Owner: IIf(Info_SaleDate <= #10/31/2003#,[Info_NewOwner],
[Info_FirstName] & " "
& [Info_LastName])


If Info_NewOwner _NEVER_ has a value if Info_FirstName or Info_LastName
do have
a value then, you could even use

Owner: NZ(Info_NewOwner,[Info_FirstName] & " " & [Info_LastName])

I have a table that is a list of contacts.

Info_FirstName
Info_LastName
Info_NewOwner
Info_SaleDate

I need to create a query that shows the name and the sale date. The
name
needs to show Info_FirstName, Info_LastName if the Info_SaleDate is before
10/31/2003 and the Info_NewOwner if Info_SaleDate is after 10/31/2003.

I created a query and in the Field I wrote
Owner: IIf(Between Date(10/31/2003) And ( ) [Info_SaleDate]),
[Info_NewOwner], ([Info_FirstName] & " " & [Info_LastName]))

But I get an error The expression you entered contains invalid syntax. I've
tried about a dozen different ways, but I still get the same message, what
am I doing wrong?

 

[Lila]

I copied and pasted the expression you put between the two lines into
notebook and made sure they were all one line, then I copied it and pasted
it into the Field line. It's working great now!

By the way, what does NZ mean? Is it like null?

 

[John Spencer]

NZ can be expanded to "Null To Zero"

What it does is return the first argument unless the first argument is null. If
the first argument is null, it returns the second argument.

I copied and pasted the expression you put between the two lines into
notebook and made sure they were all one line, then I copied it and pasted
it into the Field line. It's working great now!

By the way, what does NZ mean? Is it like null?

That's strange the first one should not give you a syntax error. It might
fail, but it should not give a syntax error.
Is the expression being typed into the query all on one line?
Does Info_SaleDate contain a space? Just in case try surrounding the
fieldname with []
Did you copy and paste the expression from the post? Sometimes that
introduces extraneous characters that have to be removed. This usually
happens if the newsreader has wrapped the expression onto two or more lines.
I've added two dashed lines below. All the text between the lines should be
on ONE line.

-------------------------------------
Owner: IIf([Info_SaleDate] > #10/31/2003#,[Info_NewOwner], [Info_FirstName]
& " " & [Info_LastName])
-------------------------------------

In revisiting this I see that I did reverse the logic of what you wanted to
show. So in the above, I changed the comparison to "greater than" instead
of "less than or equal to".

Anyway, if the second method is working for you, then this is just an
attempt to increase your knowledge.