Choose between two fields, display in a datasheet view subform

[Mike DFR]

The database covers service history. The subform lists machine numbers, the
fields are months, (Jan -Dec). The table the info comes from has 25 fields.
'month' service date, 'month' no sevice reason. The service date fields are
in date format. The no sevice reason are in text format.
Sometimes there is no service, I need to put a one word reason in the box.
i.e. If field 1 = "", then field 2, else field 1.
I have tried several ways to solve this but I get #name! or #error! every
time.
I am working with Access in Office XP, and using some VB coding.
The method of finding data is 'Recordset'

 

[Ofer Cohen]

Try something like

IIf([field 1] = "" Or [field 1] Is Null, [field 2], [field 1])

 

[Mike DFR]

Thanks for the help, but it still fills the field with '#Name?'.
I may have the code in the wrong place, I am using expression biulder in
field control properties.
I should maybe have it in the main code. (I am still a novice with VB)
I will keep working with it, thanks again.

Try something like

IIf([field 1] = "" Or [field 1] Is Null, [field 2], [field 1])

--
Good Luck
BS"D

The database covers service history. The subform lists machine numbers, the
fields are months, (Jan -Dec). The table the info comes from has 25 fields.
'month' service date, 'month' no sevice reason. The service date fields are
in date format. The no sevice reason are in text format.
Sometimes there is no service, I need to put a one word reason in the box.
i.e. If field 1 = "", then field 2, else field 1.
I have tried several ways to solve this but I get #name! or #error! every
time.
I am working with Access in Office XP, and using some VB coding.
The method of finding data is 'Recordset'

 

[Ofer Cohen]

Did you use this IIf condition in the control source of the field?
Check the name of the fields if they are correct.


Can you post what you have?
--
Good Luck
BS"D

Thanks for the help, but it still fills the field with '#Name?'.
I may have the code in the wrong place, I am using expression biulder in
field control properties.
I should maybe have it in the main code. (I am still a novice with VB)
I will keep working with it, thanks again.

Try something like

IIf([field 1] = "" Or [field 1] Is Null, [field 2], [field 1])

--
Good Luck
BS"D

The database covers service history. The subform lists machine numbers, the
fields are months, (Jan -Dec). The table the info comes from has 25 fields.
'month' service date, 'month' no sevice reason. The service date fields are
in date format. The no sevice reason are in text format.
Sometimes there is no service, I need to put a one word reason in the box.
i.e. If field 1 = "", then field 2, else field 1.
I have tried several ways to solve this but I get #name! or #error! every
time.
I am working with Access in Office XP, and using some VB coding.
The method of finding data is 'Recordset'

 

[Mike DFR]

Yes, I wrote it in the control source in properties
=IIf([Machine]![JAN]="" Or [Machine]![JAN] Is
Null,[Machine]![JANNS],[Machine]![JAN])
The text box using this control is called JAN
Initially I did not have [Machine]! in the expression.
Using the expression above now gives #Error
The field JAN is date format, the field JANNS is text format
Thanks for your help so far.

Did you use this IIf condition in the control source of the field?
Check the name of the fields if they are correct.


Can you post what you have?
--
Good Luck
BS"D

Thanks for the help, but it still fills the field with '#Name?'.
I may have the code in the wrong place, I am using expression biulder in
field control properties.
I should maybe have it in the main code. (I am still a novice with VB)
I will keep working with it, thanks again.

Try something like

IIf([field 1] = "" Or [field 1] Is Null, [field 2], [field 1])

--
Good Luck
BS"D


:

The database covers service history. The subform lists machine numbers, the
fields are months, (Jan -Dec). The table the info comes from has 25 fields.
'month' service date, 'month' no sevice reason. The service date fields are
in date format. The no sevice reason are in text format.
Sometimes there is no service, I need to put a one word reason in the box.
i.e. If field 1 = "", then field 2, else field 1.
I have tried several ways to solve this but I get #name! or #error! every
time.
I am working with Access in Office XP, and using some VB coding.
The method of finding data is 'Recordset'

 

[Ofer Cohen]

The name you need to use are the name of the fields in the table, if there is
a field in the table called JAN then change the name of the text box to
somehing else, like JAN1, other wise you'll have a conflict between the two
names and you'll get an error.

Try this after you change the name of the text box


=IIf([JAN]="" Or [JAN] Is Null,[JANNS],[JAN])

--
Good Luck
BS"D

Yes, I wrote it in the control source in properties
=IIf([Machine]![JAN]="" Or [Machine]![JAN] Is
Null,[Machine]![JANNS],[Machine]![JAN])
The text box using this control is called JAN
Initially I did not have [Machine]! in the expression.
Using the expression above now gives #Error
The field JAN is date format, the field JANNS is text format
Thanks for your help so far.

Did you use this IIf condition in the control source of the field?
Check the name of the fields if they are correct.


Can you post what you have?
--
Good Luck
BS"D

Thanks for the help, but it still fills the field with '#Name?'.
I may have the code in the wrong place, I am using expression biulder in
field control properties.
I should maybe have it in the main code. (I am still a novice with VB)
I will keep working with it, thanks again.

:

Try something like

IIf([field 1] = "" Or [field 1] Is Null, [field 2], [field 1])

--
Good Luck
BS"D


:

The database covers service history. The subform lists machine numbers, the
fields are months, (Jan -Dec). The table the info comes from has 25 fields.
'month' service date, 'month' no sevice reason. The service date fields are
in date format. The no sevice reason are in text format.
Sometimes there is no service, I need to put a one word reason in the box.
i.e. If field 1 = "", then field 2, else field 1.
I have tried several ways to solve this but I get #name! or #error! every
time.
I am working with Access in Office XP, and using some VB coding.
The method of finding data is 'Recordset'

 

[Mike DFR]

It works at last
Excuse my excitement, but I have been on this for ages.
As you suggested I changed the text box names.
It works for all 12 months.
Thank you very much indeed.

The name you need to use are the name of the fields in the table, if there is
a field in the table called JAN then change the name of the text box to
somehing else, like JAN1, other wise you'll have a conflict between the two
names and you'll get an error.

Try this after you change the name of the text box


=IIf([JAN]="" Or [JAN] Is Null,[JANNS],[JAN])

--
Good Luck
BS"D

Yes, I wrote it in the control source in properties
=IIf([Machine]![JAN]="" Or [Machine]![JAN] Is
Null,[Machine]![JANNS],[Machine]![JAN])
The text box using this control is called JAN
Initially I did not have [Machine]! in the expression.
Using the expression above now gives #Error
The field JAN is date format, the field JANNS is text format
Thanks for your help so far.

Did you use this IIf condition in the control source of the field?
Check the name of the fields if they are correct.


Can you post what you have?
--
Good Luck
BS"D


:

Thanks for the help, but it still fills the field with '#Name?'.
I may have the code in the wrong place, I am using expression biulder in
field control properties.
I should maybe have it in the main code. (I am still a novice with VB)
I will keep working with it, thanks again.

:

Try something like

IIf([field 1] = "" Or [field 1] Is Null, [field 2], [field 1])

--
Good Luck
BS"D


:

The database covers service history. The subform lists machine numbers, the
fields are months, (Jan -Dec). The table the info comes from has 25 fields.
'month' service date, 'month' no sevice reason. The service date fields are
in date format. The no sevice reason are in text format.
Sometimes there is no service, I need to put a one word reason in the box.
i.e. If field 1 = "", then field 2, else field 1.
I have tried several ways to solve this but I get #name! or #error! every
time.
I am working with Access in Office XP, and using some VB coding.
The method of finding data is 'Recordset'

 

[Ofer Cohen]

Your welcome, I'm glad it worked out.

It works at last
Excuse my excitement, but I have been on this for ages.
As you suggested I changed the text box names.
It works for all 12 months.
Thank you very much indeed.

The name you need to use are the name of the fields in the table, if there is
a field in the table called JAN then change the name of the text box to
somehing else, like JAN1, other wise you'll have a conflict between the two
names and you'll get an error.

Try this after you change the name of the text box


=IIf([JAN]="" Or [JAN] Is Null,[JANNS],[JAN])

--
Good Luck
BS"D

Yes, I wrote it in the control source in properties
=IIf([Machine]![JAN]="" Or [Machine]![JAN] Is
Null,[Machine]![JANNS],[Machine]![JAN])
The text box using this control is called JAN
Initially I did not have [Machine]! in the expression.
Using the expression above now gives #Error
The field JAN is date format, the field JANNS is text format
Thanks for your help so far.

:

Did you use this IIf condition in the control source of the field?
Check the name of the fields if they are correct.


Can you post what you have?
--
Good Luck
BS"D


:

Thanks for the help, but it still fills the field with '#Name?'.
I may have the code in the wrong place, I am using expression biulder in
field control properties.
I should maybe have it in the main code. (I am still a novice with VB)
I will keep working with it, thanks again.

:

Try something like

IIf([field 1] = "" Or [field 1] Is Null, [field 2], [field 1])

--
Good Luck
BS"D


:

The database covers service history. The subform lists machine numbers, the
fields are months, (Jan -Dec). The table the info comes from has 25 fields.
'month' service date, 'month' no sevice reason. The service date fields are
in date format. The no sevice reason are in text format.
Sometimes there is no service, I need to put a one word reason in the box.
i.e. If field 1 = "", then field 2, else field 1.
I have tried several ways to solve this but I get #name! or #error! every
time.
I am working with Access in Office XP, and using some VB coding.
The method of finding data is 'Recordset'

 

[Mike DFR]

Following our last discussion, this has worked fine until my employer
upgraded to Access 2003, ( the original was using 2002).
The problem now is that the datasheet cells flash.
If you click on one cell the whole sheet appears to scroll. The data is
unchanged but the cells keep clearing and refilling. It is diffcult to
describe the visual affect.
While this is happening, the other list boxes do not fill.
If you clickon the 'Record Selector' in the subdatasheet all is OK.
But selecting another combo box starts the problem again.
It is as if the the program is contiually evaluating the Iif statement for
all records, causing a loop.
I am using 'Me.Recordsetclone.findfirst' for the combo boxes if this helps
any.
This probably needs some form of 'stop' statement somewhere.
Can you help again please?

Your welcome, I'm glad it worked out.

It works at last
Excuse my excitement, but I have been on this for ages.
As you suggested I changed the text box names.
It works for all 12 months.
Thank you very much indeed.

The name you need to use are the name of the fields in the table, if there is
a field in the table called JAN then change the name of the text box to
somehing else, like JAN1, other wise you'll have a conflict between the two
names and you'll get an error.

Try this after you change the name of the text box


=IIf([JAN]="" Or [JAN] Is Null,[JANNS],[JAN])

--
Good Luck
BS"D


:

Yes, I wrote it in the control source in properties
=IIf([Machine]![JAN]="" Or [Machine]![JAN] Is
Null,[Machine]![JANNS],[Machine]![JAN])
The text box using this control is called JAN
Initially I did not have [Machine]! in the expression.
Using the expression above now gives #Error
The field JAN is date format, the field JANNS is text format
Thanks for your help so far.

:

Did you use this IIf condition in the control source of the field?
Check the name of the fields if they are correct.


Can you post what you have?
--
Good Luck
BS"D


:

Thanks for the help, but it still fills the field with '#Name?'.
I may have the code in the wrong place, I am using expression biulder in
field control properties.
I should maybe have it in the main code. (I am still a novice with VB)
I will keep working with it, thanks again.

:

Try something like

IIf([field 1] = "" Or [field 1] Is Null, [field 2], [field 1])

--
Good Luck
BS"D


:

The database covers service history. The subform lists machine numbers, the
fields are months, (Jan -Dec). The table the info comes from has 25 fields.
'month' service date, 'month' no sevice reason. The service date fields are
in date format. The no sevice reason are in text format.
Sometimes there is no service, I need to put a one word reason in the box.
i.e. If field 1 = "", then field 2, else field 1.
I have tried several ways to solve this but I get #name! or #error! every
time.
I am working with Access in Office XP, and using some VB coding.
The method of finding data is 'Recordset'