Ofer Cohen said:Using DlookUp in the TextBox control source
=DlookUp("[FieldName]","[TableName]","[NumberFieldNameInTable] = " &
[NumberFieldNameInForm] & " And [StringFieldNameInTable] = '" &
[StringFieldNameInForm] & "'")
Note: The strig field name has single quote before and after the value, the
number has none.
If you filter by DateField then you need to add # before and after
--
Good Luck
BS"D
Harry F. said:Hi,
I need to create a form where a user supplies two parameters, and based on
those parameters, the form should run them against a table and provide a
value. How do I do this????
Harry F. said:Ofer,
Thanks, this is a great start. Can you please explain what
"[NumberFieldNameInTable] = " &
[NumberFieldNameInForm] & " And [StringFieldNameInTable] = '" &
[StringFieldNameInForm] relate to?
The fields I am using are:
Parameter 1: ClassCode
Parameter 2: BusinessCode
Desired Result: CounterpartyType
Thanks again!
Harry
Ofer Cohen said:Using DlookUp in the TextBox control source
=DlookUp("[FieldName]","[TableName]","[NumberFieldNameInTable] = " &
[NumberFieldNameInForm] & " And [StringFieldNameInTable] = '" &
[StringFieldNameInForm] & "'")
Note: The strig field name has single quote before and after the value, the
number has none.
If you filter by DateField then you need to add # before and after
--
Good Luck
BS"D
Harry F. said:Hi,
I need to create a form where a user supplies two parameters, and based on
those parameters, the form should run them against a table and provide a
value. How do I do this????
Ofer Cohen said:Hi Harry, sorry it took me a while to answer
The DLookUp consist of three parts
=DLookUp("Retrieve the value from this field","From this table","using this
filter")
the filter can be fixed like:
=DlookUp("Field1Name","TableName","Field2Name = 'aaa'")
so the dlookup will return the value of field1name where field2 in the table
equal "aaa"
===========================================
the filter can use parameters that are used to filter a field in the table
To filter string field (StringFieldName is a name of the field you want to
filter in the table)
=DlookUp("Field1Name","TableName","StringFieldName = '" & Parameter & "'")
To filter numeric field
=DlookUp("Field1Name","TableName","NumericFieldName = " & Parameter)
To filter date field
=DlookUp("Field1Name","TableName","DateFieldName = #" & Parameter & "#")
=========================================
You can filter two fields together or even more, by adding "Filter And
AnotherFilter"
=DlookUp("Field1Name","TableName","StringFieldName = '" & Parameter & "' And
NumericFieldName = " & Parameter2)
======================
I hope it's clear, if you need more help, I'll need this details
1. Table Name
2. Fields name in the table and there type (the value you need and which
fields are needed for the filter)
3. The parameters name and to which field in the table they refer to
--
Good Luck
BS"D
Harry F. said:Ofer,
Thanks, this is a great start. Can you please explain what
"[NumberFieldNameInTable] = " &
[NumberFieldNameInForm] & " And [StringFieldNameInTable] = '" &
[StringFieldNameInForm] relate to?
The fields I am using are:
Parameter 1: ClassCode
Parameter 2: BusinessCode
Desired Result: CounterpartyType
Thanks again!
Harry
Ofer Cohen said:Using DlookUp in the TextBox control source
=DlookUp("[FieldName]","[TableName]","[NumberFieldNameInTable] = " &
[NumberFieldNameInForm] & " And [StringFieldNameInTable] = '" &
[StringFieldNameInForm] & "'")
Note: The strig field name has single quote before and after the value, the
number has none.
If you filter by DateField then you need to add # before and after
--
Good Luck
BS"D
:
Hi,
I need to create a form where a user supplies two parameters, and based on
those parameters, the form should run them against a table and provide a
value. How do I do this????
Harry F. said:Hi Ofer,
Thanks again for the help.
Now I'm getting an error message. I'll explain first what I'm trying to do,
and then what I have. Perhaps you can help me find my error.
I have a table called "tblMapping" In that table are three fields (all text
fields): ClassCode, BusinessCode, and CounterpartyType. I have two combo
boxes and a text box on the form. The two combo boxes apply to ClassCode and
BusinessCode. When a user selects a value from those two combo boxes, I need
the text box to display the corresponding CounterpartyType.
Presently my code looks like this:
DLookup("CounterpartyType", "tblMapping", "ClassCode = '" & [cboClass] & "'
BusinessCode = " & [cboBusiness])
What am I doing wrong?
Thanks again,
Harry
Ofer Cohen said:Hi Harry, sorry it took me a while to answer
The DLookUp consist of three parts
=DLookUp("Retrieve the value from this field","From this table","using this
filter")
the filter can be fixed like:
=DlookUp("Field1Name","TableName","Field2Name = 'aaa'")
so the dlookup will return the value of field1name where field2 in the table
equal "aaa"
===========================================
the filter can use parameters that are used to filter a field in the table
To filter string field (StringFieldName is a name of the field you want to
filter in the table)
=DlookUp("Field1Name","TableName","StringFieldName = '" & Parameter & "'")
To filter numeric field
=DlookUp("Field1Name","TableName","NumericFieldName = " & Parameter)
To filter date field
=DlookUp("Field1Name","TableName","DateFieldName = #" & Parameter & "#")
=========================================
You can filter two fields together or even more, by adding "Filter And
AnotherFilter"
=DlookUp("Field1Name","TableName","StringFieldName = '" & Parameter & "' And
NumericFieldName = " & Parameter2)
======================
I hope it's clear, if you need more help, I'll need this details
1. Table Name
2. Fields name in the table and there type (the value you need and which
fields are needed for the filter)
3. The parameters name and to which field in the table they refer to
--
Good Luck
BS"D
Harry F. said:Ofer,
Thanks, this is a great start. Can you please explain what
"[NumberFieldNameInTable] = " &
[NumberFieldNameInForm] & " And [StringFieldNameInTable] = '" &
[StringFieldNameInForm] relate to?
The fields I am using are:
Parameter 1: ClassCode
Parameter 2: BusinessCode
Desired Result: CounterpartyType
Thanks again!
Harry
:
Using DlookUp in the TextBox control source
=DlookUp("[FieldName]","[TableName]","[NumberFieldNameInTable] = " &
[NumberFieldNameInForm] & " And [StringFieldNameInTable] = '" &
[StringFieldNameInForm] & "'")
Note: The strig field name has single quote before and after the value, the
number has none.
If you filter by DateField then you need to add # before and after
--
Good Luck
BS"D
:
Hi,
I need to create a form where a user supplies two parameters, and based on
those parameters, the form should run them against a table and provide a
value. How do I do this????
Ofer Cohen said:Yours
DLookup("CounterpartyType", "tblMapping", "ClassCode = '" & [cboClass] & "'
BusinessCode = " & [cboBusiness])
Try
DLookup("CounterpartyType", "tblMapping", "ClassCode = '" & [cboClass] & "'
And BusinessCode = '" & [cboBusiness] & "'")
Adding Änd between the criteria and single quote before and after
cboBusiness, because you said that all are text fields, unless cboBusiness is
number, then try
DLookup("CounterpartyType", "tblMapping", "ClassCode = '" & [cboClass] & "'
And BusinessCode = " & [cboBusiness])
--
Good Luck
BS"D
Harry F. said:Hi Ofer,
Thanks again for the help.
Now I'm getting an error message. I'll explain first what I'm trying to do,
and then what I have. Perhaps you can help me find my error.
I have a table called "tblMapping" In that table are three fields (all text
fields): ClassCode, BusinessCode, and CounterpartyType. I have two combo
boxes and a text box on the form. The two combo boxes apply to ClassCode and
BusinessCode. When a user selects a value from those two combo boxes, I need
the text box to display the corresponding CounterpartyType.
Presently my code looks like this:
DLookup("CounterpartyType", "tblMapping", "ClassCode = '" & [cboClass] & "'
BusinessCode = " & [cboBusiness])
What am I doing wrong?
Thanks again,
Harry
Ofer Cohen said:Hi Harry, sorry it took me a while to answer
The DLookUp consist of three parts
=DLookUp("Retrieve the value from this field","From this table","using this
filter")
the filter can be fixed like:
=DlookUp("Field1Name","TableName","Field2Name = 'aaa'")
so the dlookup will return the value of field1name where field2 in the table
equal "aaa"
===========================================
the filter can use parameters that are used to filter a field in the table
To filter string field (StringFieldName is a name of the field you want to
filter in the table)
=DlookUp("Field1Name","TableName","StringFieldName = '" & Parameter & "'")
To filter numeric field
=DlookUp("Field1Name","TableName","NumericFieldName = " & Parameter)
To filter date field
=DlookUp("Field1Name","TableName","DateFieldName = #" & Parameter & "#")
=========================================
You can filter two fields together or even more, by adding "Filter And
AnotherFilter"
=DlookUp("Field1Name","TableName","StringFieldName = '" & Parameter & "' And
NumericFieldName = " & Parameter2)
======================
I hope it's clear, if you need more help, I'll need this details
1. Table Name
2. Fields name in the table and there type (the value you need and which
fields are needed for the filter)
3. The parameters name and to which field in the table they refer to
--
Good Luck
BS"D
:
Ofer,
Thanks, this is a great start. Can you please explain what
"[NumberFieldNameInTable] = " &
[NumberFieldNameInForm] & " And [StringFieldNameInTable] = '" &
[StringFieldNameInForm] relate to?
The fields I am using are:
Parameter 1: ClassCode
Parameter 2: BusinessCode
Desired Result: CounterpartyType
Thanks again!
Harry
:
Using DlookUp in the TextBox control source
=DlookUp("[FieldName]","[TableName]","[NumberFieldNameInTable] = " &
[NumberFieldNameInForm] & " And [StringFieldNameInTable] = '" &
[StringFieldNameInForm] & "'")
Note: The strig field name has single quote before and after the value, the
number has none.
If you filter by DateField then you need to add # before and after
--
Good Luck
BS"D
:
Hi,
I need to create a form where a user supplies two parameters, and based on
those parameters, the form should run them against a table and provide a
value. How do I do this????