If then for Field

[VCPaint]

I am trying to create a unbound field on a form which would show a Date
from different tables that would show depending on a field -if Null-
One Date shows, If not null the other table date shows.
I have tried expressions & codes & I must be missing something.

Form queries two tables

Table 1 - InstallDateA
Table 2 - InstallDateB
Table 2 - ServiceNumber
Form - InstDate
InstallDateA would be in Unbound Datefield if ServiceNumber is Null
InstallDateB would be in Undbound Datefield if Service Number has a
number

I think I am declaring name of fields improperly
Thanks for any input

 

[VCPaint]

I am trying to create a unbound field on a form which would show a Date
from different tables that would show depending on a field -if Null-
One Date shows, If not null the other table date shows.
I have tried expressions & codes & I must be missing something.

Form queries two tables

Table 1 - InstallDateA
Table 2 - InstallDateB
Table 2 - ServiceNumber
Form - InstDate
InstallDateA would be in Unbound Datefield if ServiceNumber is Null
InstallDateB would be in Undbound Datefield if Service Number has a
number

I think I am declaring name of fields improperly
Thanks for any input

Some Things I have tried unsuccessfully

=IIf([ServiceOrder]=Null,[BWSInsDt],[BWSInstS])

or
Private Sub txtComboBWSInst_BeforeUpdate(Cancel As Integer)

Dim ServiceOrder As String
Dim BWSInstS As Date
Dim BWSInsDt As Date
Dim txtComboBWSInst As Date

ServiceOrder = Nz([ServiceOrder])


If ServiceOrder = "" Then BWSInsDt
End Sub

 

[VCPaint]

The first thing that jumps out is how you are checking for Null. Nothing
every is equal to Null. Even Null = Null will return Null. Null = anything
returns Null. The IsNull function is available to test a variable, control,
or field for Null.

=IIf([ServiceOrder]=Null,[BWSInsDt],[BWSInstS])
should be
=IIf(IsNull([ServiceOrder]),[BWSInsDt],[BWSInstS], "")

The problem with the function version is taht you are declaring BWSInsDT and
BWINstS as variables, but never putting a value in either.

Although the Nz function will return a zero length string if not default
return value is specified, it is better to explicitly define it so whomever
may be reading your code will know what you really intend:

ServiceOrder = Nz([ServiceOrder])
Really should be
ServiceOrder = Nz([ServiceOrder])

This one is really confusing:

Dim txtComboBWSInst As Date

the prefix txt indicates to me this is a text box on a form, but the word
Combo makes me wonder if it is a combo box, but you are declaring it as a
variable. Declaring a variable with the same name as a control on your form
may confuse Access.

You state in your post that the dates and the Service order are in fields in
a table, but I don't see you referencing the table anywhere in your code.
Are the field in the table also part of the record source of the form? If
not, how do you know which row in the table to use for this?

I am trying to create a unbound field on a form which would show a Date
from different tables that would show depending on a field -if Null-
One Date shows, If not null the other table date shows.
I have tried expressions & codes & I must be missing something.

Form queries two tables

Table 1 - InstallDateA
Table 2 - InstallDateB
Table 2 - ServiceNumber
Form - InstDate
InstallDateA would be in Unbound Datefield if ServiceNumber is Null
InstallDateB would be in Undbound Datefield if Service Number has a
number

I think I am declaring name of fields improperly
Thanks for any input

Some Things I have tried unsuccessfully

=IIf([ServiceOrder]=Null,[BWSInsDt],[BWSInstS])

or
Private Sub txtComboBWSInst_BeforeUpdate(Cancel As Integer)

Dim ServiceOrder As String
Dim BWSInstS As Date
Dim BWSInsDt As Date
Dim txtComboBWSInst As Date

ServiceOrder = Nz([ServiceOrder])


If ServiceOrder = "" Then BWSInsDt
End Sub

thanks for input
I removed code & tried this
I have changed Unbound control name to txtCBWSInstDt
then used =IIf(IsNull([ServiceOrder]),[BWSInsDt],[BWSInstS], "") in
control source of txtCBWSInstDt
I am getting error -wrong number of arguments

 

[Guest]

=IIf(IsNull([ServiceOrder]),[BWSInsDt],[BWSInstS], "")
should be
=IIf(IsNull([ServiceOrder],""),[BWSInsDt],[BWSInstS])

The first thing that jumps out is how you are checking for Null. Nothing
every is equal to Null. Even Null = Null will return Null. Null = anything
returns Null. The IsNull function is available to test a variable, control,
or field for Null.

=IIf([ServiceOrder]=Null,[BWSInsDt],[BWSInstS])
should be
=IIf(IsNull([ServiceOrder]),[BWSInsDt],[BWSInstS], "")

The problem with the function version is taht you are declaring BWSInsDT and
BWINstS as variables, but never putting a value in either.

Although the Nz function will return a zero length string if not default
return value is specified, it is better to explicitly define it so whomever
may be reading your code will know what you really intend:

ServiceOrder = Nz([ServiceOrder])
Really should be
ServiceOrder = Nz([ServiceOrder])

This one is really confusing:

Dim txtComboBWSInst As Date

the prefix txt indicates to me this is a text box on a form, but the word
Combo makes me wonder if it is a combo box, but you are declaring it as a
variable. Declaring a variable with the same name as a control on your form
may confuse Access.

You state in your post that the dates and the Service order are in fields in
a table, but I don't see you referencing the table anywhere in your code.
Are the field in the table also part of the record source of the form? If
not, how do you know which row in the table to use for this?

VCPaint wrote:
I am trying to create a unbound field on a form which would show a Date
from different tables that would show depending on a field -if Null-
One Date shows, If not null the other table date shows.
I have tried expressions & codes & I must be missing something.

Form queries two tables

Table 1 - InstallDateA
Table 2 - InstallDateB
Table 2 - ServiceNumber
Form - InstDate
InstallDateA would be in Unbound Datefield if ServiceNumber is Null
InstallDateB would be in Undbound Datefield if Service Number has a
number

I think I am declaring name of fields improperly
Thanks for any input

Some Things I have tried unsuccessfully

=IIf([ServiceOrder]=Null,[BWSInsDt],[BWSInstS])

or
Private Sub txtComboBWSInst_BeforeUpdate(Cancel As Integer)

Dim ServiceOrder As String
Dim BWSInstS As Date
Dim BWSInsDt As Date
Dim txtComboBWSInst As Date

ServiceOrder = Nz([ServiceOrder])


If ServiceOrder = "" Then BWSInsDt
End Sub

thanks for input
I removed code & tried this
I have changed Unbound control name to txtCBWSInstDt
then used =IIf(IsNull([ServiceOrder]),[BWSInsDt],[BWSInstS], "") in
control source of txtCBWSInstDt
I am getting error -wrong number of arguments

 

[John Vinson]

I am trying to create a unbound field on a form which would show a Date
from different tables that would show depending on a field -if Null-
One Date shows, If not null the other table date shows.
I have tried expressions & codes & I must be missing something.

Form queries two tables

Table 1 - InstallDateA
Table 2 - InstallDateB
Table 2 - ServiceNumber
Form - InstDate
InstallDateA would be in Unbound Datefield if ServiceNumber is Null
InstallDateB would be in Undbound Datefield if Service Number has a
number

How are the tables related? What if there are 31225 records with
different dates? Which date do you want to see?

John W. Vinson[MVP]

 

[VCPaint]

How are the tables related? What if there are 31225 records with
different dates? Which date do you want to see?

John W. Vinson[MVP]

I don't know if anyone is still looking here. I had to leave for
Christmas party. The two tables are related by record number. Main
table has record number with subtable using record number plus .01,
..02, .03 ect. Main table uses Install date. Secondary table is for
services of same record. I have created a form that shows both
original job & service jobs in datasheet view. I would like one column
that shows install date for original job if it has no service. But also
switches to service install date if it has service.

job#/Service# Inst Date
2000 12/1/06
2000.01 12/3/06
2001 12/1/06
2002 12/2/06
2002.01 12/15/06

I have first column working fine but second column I do not know how to
combine tables
Initial Job Inst Date is in main table
Service Inst Date is in sub table
If it is service I want to view subtable's install date, not the
original Install date

 

[John Vinson]

I don't know if anyone is still looking here. I had to leave for
Christmas party. The two tables are related by record number. Main
table has record number with subtable using record number plus .01,
.02, .03 ect. Main table uses Install date. Secondary table is for
services of same record. I have created a form that shows both
original job & service jobs in datasheet view. I would like one column
that shows install date for original job if it has no service. But also
switches to service install date if it has service.

job#/Service# Inst Date
2000 12/1/06
2000.01 12/3/06
2001 12/1/06
2002 12/2/06
2002.01 12/15/06

Wolo... then they're NOT related by record number. They're related by
the first four characters of the improperly-designed, non-atomic field
(2000 matching 2000.01). This really should be *two different fields*
- record number, and *separately*, ServiceNumber.

What's the datatype of this field? Number or Text? Either one can be
joined (inefficiently), but the technique is different.

John W. Vinson[MVP]