If this is your first visit, be sure to check out the FAQ by clicking the link above. You may have to register before you can post: click the register link above to proceed. To start viewing messages, select the forum that you want to visit from the selection below. |
|
|
Thread Tools | Display Modes |
#1
|
|||
|
|||
If then for Field
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 |
#2
|
|||
|
|||
Addl Info: If then for Field
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 |
#3
|
|||
|
|||
Addl Info: If then for Field
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: 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 |
#4
|
|||
|
|||
Addl Info: If then for Field
Klatuu wrote:
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 |
#5
|
|||
|
|||
Addl Info: If then for Field
=IIf(IsNull([ServiceOrder]),[BWSInsDt],[BWSInstS], "")
should be =IIf(IsNull([ServiceOrder],""),[BWSInsDt],[BWSInstS]) "VCPaint" wrote: Klatuu wrote: 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 |
#6
|
|||
|
|||
If then for Field
On 22 Dec 2006 09:16:50 -0800, "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 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] |
#7
|
|||
|
|||
If then for Field
John Vinson wrote: On 22 Dec 2006 09:16:50 -0800, "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 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 |
#8
|
|||
|
|||
If then for Field
On 27 Dec 2006 06:50:48 -0800, "VCPaint" wrote:
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] |
Thread Tools | |
Display Modes | |
|
|