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 |
#11
|
|||
|
|||
Yes. I am trying to pull the fee for the amount of hours submitted on the
form (frmInvoiceDetails) in the "DailyRateCode" field, from the tblDayRate table that matches the Currency Code and Vehicle Code that is in both tables. My criteria for the rate is that the Correct Currency Code is selected as well as the Correct Vehicle Code to properly give the rate for the Hours with the correct Currency and Vehicle for the job. I'm rewriting a VB 5 app in Access 2002(XP) for an internation travel agency so the tables already existed and I have to connect the dots after the fact. I would've set up my own primary keys and Unique ID's to set up relationships but I have to work with what was given to me... Thanks Dan, I mean it, Thanks... Bennie "Dan Artuso" wrote: Hi Bennie, Yes! That's more like it. Now you're giving me a better understanding of what you're after. DLookup() seems like it will fit the bill for this. There are a few more things I need to know. What are the fields in tblDayRate? Is there a CurrencyCode and VehicleCode field that we can use??? And do you actually have 3 fields called 4, 8, and 10?? If tblDayRate does not have a CurrencyCode and VehicleCode field, then I need to know how we can relate the DayRate to these values. -- HTH Dan Artuso, Access MVP "REVBJONES" wrote in message ... Thanks again Dan, you're really a big help to me. The table is tblDayRate, the form is my frmInvoiceDetails. There is a field on the form (A different Source) that requires an entry of 4, 8, or 10. I need to access the DayRate table to pull the rate for that amount of hours based on the Currency Code and Vehicle Code that match the C Code and V Code on my current form... Is that good Dan? Bennie... "Dan Artuso" wrote: Hi, You're just not being very clear. Are you saying that you have three fields named: 4, 8 and 10 ?? or is it 4 hour rates, 8 hour rates etc... See what I mean? And what is the name of the table these fields are in? In any case, I've answered the best I can with the info you've provided. Good Luck! Dan Artuso, MVP "REVBJONES" wrote in message ... I did Dan, 4, 8, and 10 hour rates... Bennie "Dan Artuso" wrote: Something like this: Dim strSql As String Dim rs As DAO.Recordset strSql = "Select " & nameOfField & " From yourTable" Set rs = CurrentDb.OpnRecordset(strSql) I don't what your reluctance is to provide the actual names of your tables, forms and controls but it sure would make it easier if you did so. -- HTH Dan Artuso, Access MVP "REVBJONES" wrote in message ... Yes now I need to know how to pull the right field from another table based on the value of a field on my current form... ex: value of the rate field on the form is 4, options in table are feild 4 or field 8 or field 10, three different fields that hold the rates for a 4 hour rate, 8 hr rate or a 10 hr rate... "Dan Artuso" wrote: Hi, Once you know what field you want to query, how do you determine which record you want? I mean you could build a Select statement and open a recordset, but do you not want to limit it in some way? -- HTH Dan Artuso, Access MVP "REVBJONES" wrote in message ... Thanks, you're a big help. Now I need to know how to pull the right field from another table based on the value of a field on my current form... ex: value of the rate field on the form is 4, options in table are 4 or 8 or 10, three different fields... Bennie... "Dan Artuso" wrote: Hi, As I said in your other thread, DLookup() will be easier than writing a SQL statement and opening up a recordset. The code would probably go in the Current event of the form, although without more info I can't be sure. It depends on when you want to populate the control. Here is the syntax for DLookup: DLookup(expr, domain[, criteria]) so you would have something like this: yourControl = DLookup("[yourField]","yourTable", "[someField] = '" & control1 & _ "' And [someOtherField] = '" & control2 "'" If you want exact code, you have to provide the names of your controls, fields, tables etc Also the datatype of your criteria is important as well (string,numeric or dates) as each one requires a different delimiter. I noticed you have three threads going, please reply to this one so everyone can follow what's happening and you'll have more chance of other people replying as well. -- HTH Dan Artuso, Access MVP "REVBJONES" wrote in message ... How do I use SELECT to pull into a form a value from another source based on 2 values on the currenr form and record and where do I put the code? Bennie |
#12
|
|||
|
|||
Hi,
Well, I still don't know what fields are in tblDayRate so I'm going to assume a few things: You do have 3 fields: 4, 8 and 10 and that the entry on the form tells us which one to query on. The DLookup statement will look something like this: Dim strField As String strField = Me.TheControlThatHasTheValue DLookup(strField,"tblDayRate", "CurrencyCode = " & Me.CCode & " And VehicleCode = " & Me.VCode) The above assumes that the codes are numeric. If they're not, you need to delimit the criteria with single quotes. Where you put the statement will depend on when you want the control on your form populated. -- HTH Dan Artuso, Access MVP "REVBJONES" wrote in message news Yes. I am trying to pull the fee for the amount of hours submitted on the form (frmInvoiceDetails) in the "DailyRateCode" field, from the tblDayRate table that matches the Currency Code and Vehicle Code that is in both tables. My criteria for the rate is that the Correct Currency Code is selected as well as the Correct Vehicle Code to properly give the rate for the Hours with the correct Currency and Vehicle for the job. I'm rewriting a VB 5 app in Access 2002(XP) for an internation travel agency so the tables already existed and I have to connect the dots after the fact. I would've set up my own primary keys and Unique ID's to set up relationships but I have to work with what was given to me... Thanks Dan, I mean it, Thanks... Bennie "Dan Artuso" wrote: Hi Bennie, Yes! That's more like it. Now you're giving me a better understanding of what you're after. DLookup() seems like it will fit the bill for this. There are a few more things I need to know. What are the fields in tblDayRate? Is there a CurrencyCode and VehicleCode field that we can use??? And do you actually have 3 fields called 4, 8, and 10?? If tblDayRate does not have a CurrencyCode and VehicleCode field, then I need to know how we can relate the DayRate to these values. -- HTH Dan Artuso, Access MVP "REVBJONES" wrote in message ... Thanks again Dan, you're really a big help to me. The table is tblDayRate, the form is my frmInvoiceDetails. There is a field on the form (A different Source) that requires an entry of 4, 8, or 10. I need to access the DayRate table to pull the rate for that amount of hours based on the Currency Code and Vehicle Code that match the C Code and V Code on my current form... Is that good Dan? Bennie... "Dan Artuso" wrote: Hi, You're just not being very clear. Are you saying that you have three fields named: 4, 8 and 10 ?? or is it 4 hour rates, 8 hour rates etc... See what I mean? And what is the name of the table these fields are in? In any case, I've answered the best I can with the info you've provided. Good Luck! Dan Artuso, MVP "REVBJONES" wrote in message ... I did Dan, 4, 8, and 10 hour rates... Bennie "Dan Artuso" wrote: Something like this: Dim strSql As String Dim rs As DAO.Recordset strSql = "Select " & nameOfField & " From yourTable" Set rs = CurrentDb.OpnRecordset(strSql) I don't what your reluctance is to provide the actual names of your tables, forms and controls but it sure would make it easier if you did so. -- HTH Dan Artuso, Access MVP "REVBJONES" wrote in message ... Yes now I need to know how to pull the right field from another table based on the value of a field on my current form... ex: value of the rate field on the form is 4, options in table are feild 4 or field 8 or field 10, three different fields that hold the rates for a 4 hour rate, 8 hr rate or a 10 hr rate... "Dan Artuso" wrote: Hi, Once you know what field you want to query, how do you determine which record you want? I mean you could build a Select statement and open a recordset, but do you not want to limit it in some way? -- HTH Dan Artuso, Access MVP "REVBJONES" wrote in message ... Thanks, you're a big help. Now I need to know how to pull the right field from another table based on the value of a field on my current form... ex: value of the rate field on the form is 4, options in table are 4 or 8 or 10, three different fields... Bennie... "Dan Artuso" wrote: Hi, As I said in your other thread, DLookup() will be easier than writing a SQL statement and opening up a recordset. The code would probably go in the Current event of the form, although without more info I can't be sure. It depends on when you want to populate the control. Here is the syntax for DLookup: DLookup(expr, domain[, criteria]) so you would have something like this: yourControl = DLookup("[yourField]","yourTable", "[someField] = '" & control1 & _ "' And [someOtherField] = '" & control2 "'" If you want exact code, you have to provide the names of your controls, fields, tables etc Also the datatype of your criteria is important as well (string,numeric or dates) as each one requires a different delimiter. I noticed you have three threads going, please reply to this one so everyone can follow what's happening and you'll have more chance of other people replying as well. -- HTH Dan Artuso, Access MVP "REVBJONES" wrote in message ... How do I use SELECT to pull into a form a value from another source based on 2 values on the currenr form and record and where do I put the code? Bennie |
#13
|
|||
|
|||
Man we're so close I can taste it... The form has a field "DailyRateCode"
where 4, 8, or 10 is entered. I did say in my last xmission : ("Yes. I am trying to pull the fee for the amount of hours submitted on the form (frmInvoiceDetails) in the "DailyRateCode" field, from the tblDayRate table that matches the Currency Code and Vehicle Code that is in BOTH TABLES.") So the form has an ENTRY VALUE of 4, 8, or 10 in the DailyRateCode field and the tblDayRate has fields named 4, 8, and 10 with the amount to be charged but the Currency Code in both the form and the table as well as the Vehicle Codes must match. We're almost there Dan, Thanx... Bennie... "Dan Artuso" wrote: Hi, Well, I still don't know what fields are in tblDayRate so I'm going to assume a few things: You do have 3 fields: 4, 8 and 10 and that the entry on the form tells us which one to query on. The DLookup statement will look something like this: Dim strField As String strField = Me.TheControlThatHasTheValue DLookup(strField,"tblDayRate", "CurrencyCode = " & Me.CCode & " And VehicleCode = " & Me.VCode) The above assumes that the codes are numeric. If they're not, you need to delimit the criteria with single quotes. Where you put the statement will depend on when you want the control on your form populated. -- HTH Dan Artuso, Access MVP "REVBJONES" wrote in message news Yes. I am trying to pull the fee for the amount of hours submitted on the form (frmInvoiceDetails) in the "DailyRateCode" field, from the tblDayRate table that matches the Currency Code and Vehicle Code that is in both tables. My criteria for the rate is that the Correct Currency Code is selected as well as the Correct Vehicle Code to properly give the rate for the Hours with the correct Currency and Vehicle for the job. I'm rewriting a VB 5 app in Access 2002(XP) for an internation travel agency so the tables already existed and I have to connect the dots after the fact. I would've set up my own primary keys and Unique ID's to set up relationships but I have to work with what was given to me... Thanks Dan, I mean it, Thanks... Bennie "Dan Artuso" wrote: Hi Bennie, Yes! That's more like it. Now you're giving me a better understanding of what you're after. DLookup() seems like it will fit the bill for this. There are a few more things I need to know. What are the fields in tblDayRate? Is there a CurrencyCode and VehicleCode field that we can use??? And do you actually have 3 fields called 4, 8, and 10?? If tblDayRate does not have a CurrencyCode and VehicleCode field, then I need to know how we can relate the DayRate to these values. -- HTH Dan Artuso, Access MVP "REVBJONES" wrote in message ... Thanks again Dan, you're really a big help to me. The table is tblDayRate, the form is my frmInvoiceDetails. There is a field on the form (A different Source) that requires an entry of 4, 8, or 10. I need to access the DayRate table to pull the rate for that amount of hours based on the Currency Code and Vehicle Code that match the C Code and V Code on my current form... Is that good Dan? Bennie... "Dan Artuso" wrote: Hi, You're just not being very clear. Are you saying that you have three fields named: 4, 8 and 10 ?? or is it 4 hour rates, 8 hour rates etc... See what I mean? And what is the name of the table these fields are in? In any case, I've answered the best I can with the info you've provided. Good Luck! Dan Artuso, MVP "REVBJONES" wrote in message ... I did Dan, 4, 8, and 10 hour rates... Bennie "Dan Artuso" wrote: Something like this: Dim strSql As String Dim rs As DAO.Recordset strSql = "Select " & nameOfField & " From yourTable" Set rs = CurrentDb.OpnRecordset(strSql) I don't what your reluctance is to provide the actual names of your tables, forms and controls but it sure would make it easier if you did so. -- HTH Dan Artuso, Access MVP "REVBJONES" wrote in message ... Yes now I need to know how to pull the right field from another table based on the value of a field on my current form... ex: value of the rate field on the form is 4, options in table are feild 4 or field 8 or field 10, three different fields that hold the rates for a 4 hour rate, 8 hr rate or a 10 hr rate... "Dan Artuso" wrote: Hi, Once you know what field you want to query, how do you determine which record you want? I mean you could build a Select statement and open a recordset, but do you not want to limit it in some way? -- HTH Dan Artuso, Access MVP "REVBJONES" wrote in message ... Thanks, you're a big help. Now I need to know how to pull the right field from another table based on the value of a field on my current form... ex: value of the rate field on the form is 4, options in table are 4 or 8 or 10, three different fields... Bennie... "Dan Artuso" wrote: Hi, As I said in your other thread, DLookup() will be easier than writing a SQL statement and opening up a recordset. The code would probably go in the Current event of the form, although without more info I can't be sure. It depends on when you want to populate the control. Here is the syntax for DLookup: DLookup(expr, domain[, criteria]) so you would have something like this: yourControl = DLookup("[yourField]","yourTable", "[someField] = '" & control1 & _ "' And [someOtherField] = '" & control2 "'" If you want exact code, you have to provide the names of your controls, fields, tables etc Also the datatype of your criteria is important as well (string,numeric or dates) as each one requires a different delimiter. I noticed you have three threads going, please reply to this one so everyone can follow what's happening and you'll have more chance of other people replying as well. -- HTH Dan Artuso, Access MVP "REVBJONES" wrote in message ... How do I use SELECT to pull into a form a value from another source based on 2 values on the currenr form and record and where do I put the code? Bennie |
#14
|
|||
|
|||
Hi Bennie,
Okay, from the info you've provided, this should be it: Dim strField As String strField = Me.DailyRateCode DLookup(strField,"tblDayRate", "CurrencyCode = " & Me.CCode & " And VehicleCode = " & Me.VCode) -- HTH Dan Artuso, Access MVP "REVBJONES" wrote in message ... Man we're so close I can taste it... The form has a field "DailyRateCode" where 4, 8, or 10 is entered. I did say in my last xmission : ("Yes. I am trying to pull the fee for the amount of hours submitted on the form (frmInvoiceDetails) in the "DailyRateCode" field, from the tblDayRate table that matches the Currency Code and Vehicle Code that is in BOTH TABLES.") So the form has an ENTRY VALUE of 4, 8, or 10 in the DailyRateCode field and the tblDayRate has fields named 4, 8, and 10 with the amount to be charged but the Currency Code in both the form and the table as well as the Vehicle Codes must match. We're almost there Dan, Thanx... Bennie... "Dan Artuso" wrote: Hi, Well, I still don't know what fields are in tblDayRate so I'm going to assume a few things: You do have 3 fields: 4, 8 and 10 and that the entry on the form tells us which one to query on. The DLookup statement will look something like this: Dim strField As String strField = Me.TheControlThatHasTheValue DLookup(strField,"tblDayRate", "CurrencyCode = " & Me.CCode & " And VehicleCode = " & Me.VCode) The above assumes that the codes are numeric. If they're not, you need to delimit the criteria with single quotes. Where you put the statement will depend on when you want the control on your form populated. -- HTH Dan Artuso, Access MVP "REVBJONES" wrote in message news Yes. I am trying to pull the fee for the amount of hours submitted on the form (frmInvoiceDetails) in the "DailyRateCode" field, from the tblDayRate table that matches the Currency Code and Vehicle Code that is in both tables. My criteria for the rate is that the Correct Currency Code is selected as well as the Correct Vehicle Code to properly give the rate for the Hours with the correct Currency and Vehicle for the job. I'm rewriting a VB 5 app in Access 2002(XP) for an internation travel agency so the tables already existed and I have to connect the dots after the fact. I would've set up my own primary keys and Unique ID's to set up relationships but I have to work with what was given to me... Thanks Dan, I mean it, Thanks... Bennie "Dan Artuso" wrote: Hi Bennie, Yes! That's more like it. Now you're giving me a better understanding of what you're after. DLookup() seems like it will fit the bill for this. There are a few more things I need to know. What are the fields in tblDayRate? Is there a CurrencyCode and VehicleCode field that we can use??? And do you actually have 3 fields called 4, 8, and 10?? If tblDayRate does not have a CurrencyCode and VehicleCode field, then I need to know how we can relate the DayRate to these values. -- HTH Dan Artuso, Access MVP "REVBJONES" wrote in message ... Thanks again Dan, you're really a big help to me. The table is tblDayRate, the form is my frmInvoiceDetails. There is a field on the form (A different Source) that requires an entry of 4, 8, or 10. I need to access the DayRate table to pull the rate for that amount of hours based on the Currency Code and Vehicle Code that match the C Code and V Code on my current form... Is that good Dan? Bennie... "Dan Artuso" wrote: Hi, You're just not being very clear. Are you saying that you have three fields named: 4, 8 and 10 ?? or is it 4 hour rates, 8 hour rates etc... See what I mean? And what is the name of the table these fields are in? In any case, I've answered the best I can with the info you've provided. Good Luck! Dan Artuso, MVP "REVBJONES" wrote in message ... I did Dan, 4, 8, and 10 hour rates... Bennie "Dan Artuso" wrote: Something like this: Dim strSql As String Dim rs As DAO.Recordset strSql = "Select " & nameOfField & " From yourTable" Set rs = CurrentDb.OpnRecordset(strSql) I don't what your reluctance is to provide the actual names of your tables, forms and controls but it sure would make it easier if you did so. -- HTH Dan Artuso, Access MVP "REVBJONES" wrote in message ... Yes now I need to know how to pull the right field from another table based on the value of a field on my current form... ex: value of the rate field on the form is 4, options in table are feild 4 or field 8 or field 10, three different fields that hold the rates for a 4 hour rate, 8 hr rate or a 10 hr rate... "Dan Artuso" wrote: Hi, Once you know what field you want to query, how do you determine which record you want? I mean you could build a Select statement and open a recordset, but do you not want to limit it in some way? -- HTH Dan Artuso, Access MVP "REVBJONES" wrote in message ... Thanks, you're a big help. Now I need to know how to pull the right field from another table based on the value of a field on my current form... ex: value of the rate field on the form is 4, options in table are 4 or 8 or 10, three different fields... Bennie... "Dan Artuso" wrote: Hi, As I said in your other thread, DLookup() will be easier than writing a SQL statement and opening up a recordset. The code would probably go in the Current event of the form, although without more info I can't be sure. It depends on when you want to populate the control. Here is the syntax for DLookup: DLookup(expr, domain[, criteria]) so you would have something like this: yourControl = DLookup("[yourField]","yourTable", "[someField] = '" & control1 & _ "' And [someOtherField] = '" & control2 "'" If you want exact code, you have to provide the names of your controls, fields, tables etc Also the datatype of your criteria is important as well (string,numeric or dates) as each one requires a different delimiter. I noticed you have three threads going, please reply to this one so everyone can follow what's happening and you'll have more chance of other people replying as well. -- HTH Dan Artuso, Access MVP "REVBJONES" wrote in message ... How do I use SELECT to pull into a form a value from another source based on 2 values on the currenr form and record and where do I put the code? Bennie |
#15
|
|||
|
|||
Thanks Dan...
OK, here's another problem... I need to connect a form that stores the Agencies that she uses, to the form you just mastered for me. There is no common field between the two. 1 form source is the tblInvoiceDetails and the other is the tblAgencies. I guess I should give the tblAgencies a UniqueID, as I stated before, the tables existed and were populated when I started... I know how to create an AutoFill/AutoLookup form but I know that requires a one to many relationship with the tblAgencies having a Primary Key as the one and the same data field as the many in the tblInvoiceDetails table to place in the grid. This I think will put the final obstacle to rest and complete the New App, If not, you'll know about it :-) I've already linked the form by Primary key to a report "rptInvoice" and I need the Acency Info as well as the Invoice Details to create Invoices... Any ideas? Bennie... "Dan Artuso" wrote: Hi Bennie, Okay, from the info you've provided, this should be it: Dim strField As String strField = Me.DailyRateCode DLookup(strField,"tblDayRate", "CurrencyCode = " & Me.CCode & " And VehicleCode = " & Me.VCode) -- HTH Dan Artuso, Access MVP |
#16
|
|||
|
|||
Hi Dan,
I tried this Dim strField As String strField = Me.Dailyrate DLookup(strField,"tblDayRate", "CurrencyCode = " & Me.CurrencyCode & " And VehicleCode = " & Me.VehicleCode) and I got "Compile Error" "Syntax Error" HELP! Bennie... "Dan Artuso" wrote: Hi Bennie, Okay, from the info you've provided, this should be it: Dim strField As String strField = Me.DailyRateCode DLookup(strField,"tblDayRate", "CurrencyCode = " & Me.CCode & " And VehicleCode = " & Me.VCode) -- HTH Dan Artuso, Access MVP "REVBJONES" wrote in message ... Man we're so close I can taste it... The form has a field "DailyRateCode" where 4, 8, or 10 is entered. I did say in my last xmission : ("Yes. I am trying to pull the fee for the amount of hours submitted on the form (frmInvoiceDetails) in the "DailyRateCode" field, from the tblDayRate table that matches the Currency Code and Vehicle Code that is in BOTH TABLES.") So the form has an ENTRY VALUE of 4, 8, or 10 in the DailyRateCode field and the tblDayRate has fields named 4, 8, and 10 with the amount to be charged but the Currency Code in both the form and the table as well as the Vehicle Codes must match. We're almost there Dan, Thanx... Bennie... "Dan Artuso" wrote: Hi, Well, I still don't know what fields are in tblDayRate so I'm going to assume a few things: You do have 3 fields: 4, 8 and 10 and that the entry on the form tells us which one to query on. The DLookup statement will look something like this: Dim strField As String strField = Me.TheControlThatHasTheValue DLookup(strField,"tblDayRate", "CurrencyCode = " & Me.CCode & " And VehicleCode = " & Me.VCode) The above assumes that the codes are numeric. If they're not, you need to delimit the criteria with single quotes. Where you put the statement will depend on when you want the control on your form populated. -- HTH Dan Artuso, Access MVP "REVBJONES" wrote in message news Yes. I am trying to pull the fee for the amount of hours submitted on the form (frmInvoiceDetails) in the "DailyRateCode" field, from the tblDayRate table that matches the Currency Code and Vehicle Code that is in both tables. My criteria for the rate is that the Correct Currency Code is selected as well as the Correct Vehicle Code to properly give the rate for the Hours with the correct Currency and Vehicle for the job. I'm rewriting a VB 5 app in Access 2002(XP) for an internation travel agency so the tables already existed and I have to connect the dots after the fact. I would've set up my own primary keys and Unique ID's to set up relationships but I have to work with what was given to me... Thanks Dan, I mean it, Thanks... Bennie "Dan Artuso" wrote: Hi Bennie, Yes! That's more like it. Now you're giving me a better understanding of what you're after. DLookup() seems like it will fit the bill for this. There are a few more things I need to know. What are the fields in tblDayRate? Is there a CurrencyCode and VehicleCode field that we can use??? And do you actually have 3 fields called 4, 8, and 10?? If tblDayRate does not have a CurrencyCode and VehicleCode field, then I need to know how we can relate the DayRate to these values. -- HTH Dan Artuso, Access MVP "REVBJONES" wrote in message ... Thanks again Dan, you're really a big help to me. The table is tblDayRate, the form is my frmInvoiceDetails. There is a field on the form (A different Source) that requires an entry of 4, 8, or 10. I need to access the DayRate table to pull the rate for that amount of hours based on the Currency Code and Vehicle Code that match the C Code and V Code on my current form... Is that good Dan? Bennie... "Dan Artuso" wrote: Hi, You're just not being very clear. Are you saying that you have three fields named: 4, 8 and 10 ?? or is it 4 hour rates, 8 hour rates etc... See what I mean? And what is the name of the table these fields are in? In any case, I've answered the best I can with the info you've provided. Good Luck! Dan Artuso, MVP "REVBJONES" wrote in message ... I did Dan, 4, 8, and 10 hour rates... Bennie "Dan Artuso" wrote: Something like this: Dim strSql As String Dim rs As DAO.Recordset strSql = "Select " & nameOfField & " From yourTable" Set rs = CurrentDb.OpnRecordset(strSql) I don't what your reluctance is to provide the actual names of your tables, forms and controls but it sure would make it easier if you did so. -- HTH Dan Artuso, Access MVP "REVBJONES" wrote in message ... Yes now I need to know how to pull the right field from another table based on the value of a field on my current form... ex: value of the rate field on the form is 4, options in table are feild 4 or field 8 or field 10, three different fields that hold the rates for a 4 hour rate, 8 hr rate or a 10 hr rate... "Dan Artuso" wrote: Hi, Once you know what field you want to query, how do you determine which record you want? I mean you could build a Select statement and open a recordset, but do you not want to limit it in some way? -- HTH Dan Artuso, Access MVP "REVBJONES" wrote in message ... Thanks, you're a big help. Now I need to know how to pull the right field from another table based on the value of a field on my current form... ex: value of the rate field on the form is 4, options in table are 4 or 8 or 10, three different fields... Bennie... "Dan Artuso" wrote: Hi, As I said in your other thread, DLookup() will be easier than writing a SQL statement and opening up a recordset. The code would probably go in the Current event of the form, although without more info I can't be sure. It depends on when you want to populate the control. Here is the syntax for DLookup: DLookup(expr, domain[, criteria]) so you would have something like this: yourControl = DLookup("[yourField]","yourTable", "[someField] = '" & control1 & _ "' And [someOtherField] = '" & control2 "'" If you want exact code, you have to provide the names of your controls, fields, tables etc Also the datatype of your criteria is important as well (string,numeric or dates) as each one requires a different delimiter. I noticed you have three threads going, please reply to this one so everyone can follow what's happening and you'll have more chance of other people replying as well. -- HTH Dan Artuso, Access MVP "REVBJONES" wrote in message ... How do I use SELECT to pull into a form a value from another source based on 2 values on the currenr form and record and where do I put the code? Bennie |
#17
|
|||
|
|||
You have to assign the return value of DLookup to something!
Usually it would a control on your form used to display the value. Are you not looking in the Help files? There are sample code snippets for DLookup() Me.yourControl =DLookup(strField,"tblDayRate", "CurrencyCode = " & Me.CurrencyCode & " And VehicleCode = " & Me.VehicleCode) If that's not the prblem, are you sure the names are all correct? -- HTH Dan Artuso, Access MVP "REVBJONES" wrote in message ... Hi Dan, I tried this Dim strField As String strField = Me.Dailyrate DLookup(strField,"tblDayRate", "CurrencyCode = " & Me.CurrencyCode & " And VehicleCode = " & Me.VehicleCode) and I got "Compile Error" "Syntax Error" HELP! Bennie... "Dan Artuso" wrote: Hi Bennie, Okay, from the info you've provided, this should be it: Dim strField As String strField = Me.DailyRateCode DLookup(strField,"tblDayRate", "CurrencyCode = " & Me.CCode & " And VehicleCode = " & Me.VCode) -- HTH Dan Artuso, Access MVP "REVBJONES" wrote in message ... Man we're so close I can taste it... The form has a field "DailyRateCode" where 4, 8, or 10 is entered. I did say in my last xmission : ("Yes. I am trying to pull the fee for the amount of hours submitted on the form (frmInvoiceDetails) in the "DailyRateCode" field, from the tblDayRate table that matches the Currency Code and Vehicle Code that is in BOTH TABLES.") So the form has an ENTRY VALUE of 4, 8, or 10 in the DailyRateCode field and the tblDayRate has fields named 4, 8, and 10 with the amount to be charged but the Currency Code in both the form and the table as well as the Vehicle Codes must match. We're almost there Dan, Thanx... Bennie... "Dan Artuso" wrote: Hi, Well, I still don't know what fields are in tblDayRate so I'm going to assume a few things: You do have 3 fields: 4, 8 and 10 and that the entry on the form tells us which one to query on. The DLookup statement will look something like this: Dim strField As String strField = Me.TheControlThatHasTheValue DLookup(strField,"tblDayRate", "CurrencyCode = " & Me.CCode & " And VehicleCode = " & Me.VCode) The above assumes that the codes are numeric. If they're not, you need to delimit the criteria with single quotes. Where you put the statement will depend on when you want the control on your form populated. -- HTH Dan Artuso, Access MVP "REVBJONES" wrote in message news Yes. I am trying to pull the fee for the amount of hours submitted on the form (frmInvoiceDetails) in the "DailyRateCode" field, from the tblDayRate table that matches the Currency Code and Vehicle Code that is in both tables. My criteria for the rate is that the Correct Currency Code is selected as well as the Correct Vehicle Code to properly give the rate for the Hours with the correct Currency and Vehicle for the job. I'm rewriting a VB 5 app in Access 2002(XP) for an internation travel agency so the tables already existed and I have to connect the dots after the fact. I would've set up my own primary keys and Unique ID's to set up relationships but I have to work with what was given to me... Thanks Dan, I mean it, Thanks... Bennie "Dan Artuso" wrote: Hi Bennie, Yes! That's more like it. Now you're giving me a better understanding of what you're after. DLookup() seems like it will fit the bill for this. There are a few more things I need to know. What are the fields in tblDayRate? Is there a CurrencyCode and VehicleCode field that we can use??? And do you actually have 3 fields called 4, 8, and 10?? If tblDayRate does not have a CurrencyCode and VehicleCode field, then I need to know how we can relate the DayRate to these values. -- HTH Dan Artuso, Access MVP "REVBJONES" wrote in message ... Thanks again Dan, you're really a big help to me. The table is tblDayRate, the form is my frmInvoiceDetails. There is a field on the form (A different Source) that requires an entry of 4, 8, or 10. I need to access the DayRate table to pull the rate for that amount of hours based on the Currency Code and Vehicle Code that match the C Code and V Code on my current form... Is that good Dan? Bennie... "Dan Artuso" wrote: Hi, You're just not being very clear. Are you saying that you have three fields named: 4, 8 and 10 ?? or is it 4 hour rates, 8 hour rates etc... See what I mean? And what is the name of the table these fields are in? In any case, I've answered the best I can with the info you've provided. Good Luck! Dan Artuso, MVP "REVBJONES" wrote in message ... I did Dan, 4, 8, and 10 hour rates... Bennie "Dan Artuso" wrote: Something like this: Dim strSql As String Dim rs As DAO.Recordset strSql = "Select " & nameOfField & " From yourTable" Set rs = CurrentDb.OpnRecordset(strSql) I don't what your reluctance is to provide the actual names of your tables, forms and controls but it sure would make it easier if you did so. -- HTH Dan Artuso, Access MVP "REVBJONES" wrote in message ... Yes now I need to know how to pull the right field from another table based on the value of a field on my current form... ex: value of the rate field on the form is 4, options in table are feild 4 or field 8 or field 10, three different fields that hold the rates for a 4 hour rate, 8 hr rate or a 10 hr rate... "Dan Artuso" wrote: Hi, Once you know what field you want to query, how do you determine which record you want? I mean you could build a Select statement and open a recordset, but do you not want to limit it in some way? -- HTH Dan Artuso, Access MVP "REVBJONES" wrote in message ... Thanks, you're a big help. Now I need to know how to pull the right field from another table based on the value of a field on my current form... ex: value of the rate field on the form is 4, options in table are 4 or 8 or 10, three different fields... Bennie... "Dan Artuso" wrote: Hi, As I said in your other thread, DLookup() will be easier than writing a SQL statement and opening up a recordset. The code would probably go in the Current event of the form, although without more info I can't be sure. It depends on when you want to populate the control. Here is the syntax for DLookup: DLookup(expr, domain[, criteria]) so you would have something like this: yourControl = DLookup("[yourField]","yourTable", "[someField] = '" & control1 & _ "' And [someOtherField] = '" & control2 "'" If you want exact code, you have to provide the names of your controls, fields, tables etc Also the datatype of your criteria is important as well (string,numeric or dates) as each one requires a different delimiter. I noticed you have three threads going, please reply to this one so everyone can follow what's happening and you'll have more chance of other people replying as well. -- HTH Dan Artuso, Access MVP "REVBJONES" wrote in message ... How do I use SELECT to pull into a form a value from another source based on 2 values on the currenr form and record and where do I put the code? Bennie |
#18
|
|||
|
|||
Man, I am so frustrated. I keep getting either the error message "The value
you entered isn't valid for this field" or "You cancled the previous operation" I click on the help option and I get a BLANK help screen, no info, just blank... I'm almost about to throw in the towel Dan... the code is good but I can't get past these error messages... Bennie... "Dan Artuso" wrote: You have to assign the return value of DLookup to something! Usually it would a control on your form used to display the value. Are you not looking in the Help files? There are sample code snippets for DLookup() Me.yourControl =DLookup(strField,"tblDayRate", "CurrencyCode = " & Me.CurrencyCode & " And VehicleCode = " & Me.VehicleCode) If that's not the prblem, are you sure the names are all correct? -- HTH Dan Artuso, Access MVP "REVBJONES" wrote in message ... Hi Dan, I tried this Dim strField As String strField = Me.Dailyrate DLookup(strField,"tblDayRate", "CurrencyCode = " & Me.CurrencyCode & " And VehicleCode = " & Me.VehicleCode) and I got "Compile Error" "Syntax Error" HELP! Bennie... "Dan Artuso" wrote: Hi Bennie, Okay, from the info you've provided, this should be it: Dim strField As String strField = Me.DailyRateCode DLookup(strField,"tblDayRate", "CurrencyCode = " & Me.CCode & " And VehicleCode = " & Me.VCode) -- HTH Dan Artuso, Access MVP "REVBJONES" wrote in message ... Man we're so close I can taste it... The form has a field "DailyRateCode" where 4, 8, or 10 is entered. I did say in my last xmission : ("Yes. I am trying to pull the fee for the amount of hours submitted on the form (frmInvoiceDetails) in the "DailyRateCode" field, from the tblDayRate table that matches the Currency Code and Vehicle Code that is in BOTH TABLES.") So the form has an ENTRY VALUE of 4, 8, or 10 in the DailyRateCode field and the tblDayRate has fields named 4, 8, and 10 with the amount to be charged but the Currency Code in both the form and the table as well as the Vehicle Codes must match. We're almost there Dan, Thanx... Bennie... "Dan Artuso" wrote: Hi, Well, I still don't know what fields are in tblDayRate so I'm going to assume a few things: You do have 3 fields: 4, 8 and 10 and that the entry on the form tells us which one to query on. The DLookup statement will look something like this: Dim strField As String strField = Me.TheControlThatHasTheValue DLookup(strField,"tblDayRate", "CurrencyCode = " & Me.CCode & " And VehicleCode = " & Me.VCode) The above assumes that the codes are numeric. If they're not, you need to delimit the criteria with single quotes. Where you put the statement will depend on when you want the control on your form populated. -- HTH Dan Artuso, Access MVP "REVBJONES" wrote in message news Yes. I am trying to pull the fee for the amount of hours submitted on the form (frmInvoiceDetails) in the "DailyRateCode" field, from the tblDayRate table that matches the Currency Code and Vehicle Code that is in both tables. My criteria for the rate is that the Correct Currency Code is selected as well as the Correct Vehicle Code to properly give the rate for the Hours with the correct Currency and Vehicle for the job. I'm rewriting a VB 5 app in Access 2002(XP) for an internation travel agency so the tables already existed and I have to connect the dots after the fact. I would've set up my own primary keys and Unique ID's to set up relationships but I have to work with what was given to me... Thanks Dan, I mean it, Thanks... Bennie "Dan Artuso" wrote: Hi Bennie, Yes! That's more like it. Now you're giving me a better understanding of what you're after. DLookup() seems like it will fit the bill for this. There are a few more things I need to know. What are the fields in tblDayRate? Is there a CurrencyCode and VehicleCode field that we can use??? And do you actually have 3 fields called 4, 8, and 10?? If tblDayRate does not have a CurrencyCode and VehicleCode field, then I need to know how we can relate the DayRate to these values. -- HTH Dan Artuso, Access MVP "REVBJONES" wrote in message ... Thanks again Dan, you're really a big help to me. The table is tblDayRate, the form is my frmInvoiceDetails. There is a field on the form (A different Source) that requires an entry of 4, 8, or 10. I need to access the DayRate table to pull the rate for that amount of hours based on the Currency Code and Vehicle Code that match the C Code and V Code on my current form... Is that good Dan? Bennie... "Dan Artuso" wrote: Hi, You're just not being very clear. Are you saying that you have three fields named: 4, 8 and 10 ?? or is it 4 hour rates, 8 hour rates etc... See what I mean? And what is the name of the table these fields are in? In any case, I've answered the best I can with the info you've provided. Good Luck! Dan Artuso, MVP "REVBJONES" wrote in message ... I did Dan, 4, 8, and 10 hour rates... Bennie "Dan Artuso" wrote: Something like this: Dim strSql As String Dim rs As DAO.Recordset strSql = "Select " & nameOfField & " From yourTable" Set rs = CurrentDb.OpnRecordset(strSql) I don't what your reluctance is to provide the actual names of your tables, forms and controls but it sure would make it easier if you did so. -- HTH Dan Artuso, Access MVP "REVBJONES" wrote in message ... Yes now I need to know how to pull the right field from another table based on the value of a field on my current form... ex: value of the rate field on the form is 4, options in table are feild 4 or field 8 or field 10, three different fields that hold the rates for a 4 hour rate, 8 hr rate or a 10 hr rate... "Dan Artuso" wrote: Hi, Once you know what field you want to query, how do you determine which record you want? I mean you could build a Select statement and open a recordset, but do you not want to limit it in some way? -- HTH Dan Artuso, Access MVP "REVBJONES" wrote in message ... Thanks, you're a big help. Now I need to know how to pull the right field from another table based on the value of a field on my current form... ex: value of the rate field on the form is 4, options in table are 4 or 8 or 10, three different fields... Bennie... "Dan Artuso" wrote: Hi, As I said in your other thread, DLookup() will be easier than writing a SQL statement and opening up a recordset. The code would probably go in the Current event of the form, although without more info I can't be sure. It depends on when you want to populate the control. Here is the syntax for DLookup: DLookup(expr, domain[, criteria]) so you would have something like this: yourControl = DLookup("[yourField]","yourTable", "[someField] = '" & control1 & _ "' And [someOtherField] = '" & control2 "'" If you want exact code, you have to provide the names of your controls, fields, tables etc Also the datatype of your criteria is important as well (string,numeric or dates) as each one requires a different delimiter. I noticed you have three threads going, please reply to this one so everyone can follow what's happening and you'll have more chance of other people replying as well. -- HTH Dan Artuso, Access MVP "REVBJONES" wrote in message ... How do I use SELECT to pull into a form a value from another source based on 2 values on the currenr form and record and where do I put the code? Bennie |
#19
|
|||
|
|||
P.S. the control I placed on the form "txtDayRate" to hold the retrieved data
from the tblDayRate shows #Name on the form in display mode... "Dan Artuso" wrote: You have to assign the return value of DLookup to something! Usually it would a control on your form used to display the value. Are you not looking in the Help files? There are sample code snippets for DLookup() Me.yourControl =DLookup(strField,"tblDayRate", "CurrencyCode = " & Me.CurrencyCode & " And VehicleCode = " & Me.VehicleCode) If that's not the prblem, are you sure the names are all correct? -- HTH Dan Artuso, Access MVP "REVBJONES" wrote in message ... Hi Dan, I tried this Dim strField As String strField = Me.Dailyrate DLookup(strField,"tblDayRate", "CurrencyCode = " & Me.CurrencyCode & " And VehicleCode = " & Me.VehicleCode) and I got "Compile Error" "Syntax Error" HELP! Bennie... "Dan Artuso" wrote: Hi Bennie, Okay, from the info you've provided, this should be it: Dim strField As String strField = Me.DailyRateCode DLookup(strField,"tblDayRate", "CurrencyCode = " & Me.CCode & " And VehicleCode = " & Me.VCode) -- HTH Dan Artuso, Access MVP "REVBJONES" wrote in message ... Man we're so close I can taste it... The form has a field "DailyRateCode" where 4, 8, or 10 is entered. I did say in my last xmission : ("Yes. I am trying to pull the fee for the amount of hours submitted on the form (frmInvoiceDetails) in the "DailyRateCode" field, from the tblDayRate table that matches the Currency Code and Vehicle Code that is in BOTH TABLES.") So the form has an ENTRY VALUE of 4, 8, or 10 in the DailyRateCode field and the tblDayRate has fields named 4, 8, and 10 with the amount to be charged but the Currency Code in both the form and the table as well as the Vehicle Codes must match. We're almost there Dan, Thanx... Bennie... "Dan Artuso" wrote: Hi, Well, I still don't know what fields are in tblDayRate so I'm going to assume a few things: You do have 3 fields: 4, 8 and 10 and that the entry on the form tells us which one to query on. The DLookup statement will look something like this: Dim strField As String strField = Me.TheControlThatHasTheValue DLookup(strField,"tblDayRate", "CurrencyCode = " & Me.CCode & " And VehicleCode = " & Me.VCode) The above assumes that the codes are numeric. If they're not, you need to delimit the criteria with single quotes. Where you put the statement will depend on when you want the control on your form populated. -- HTH Dan Artuso, Access MVP "REVBJONES" wrote in message news Yes. I am trying to pull the fee for the amount of hours submitted on the form (frmInvoiceDetails) in the "DailyRateCode" field, from the tblDayRate table that matches the Currency Code and Vehicle Code that is in both tables. My criteria for the rate is that the Correct Currency Code is selected as well as the Correct Vehicle Code to properly give the rate for the Hours with the correct Currency and Vehicle for the job. I'm rewriting a VB 5 app in Access 2002(XP) for an internation travel agency so the tables already existed and I have to connect the dots after the fact. I would've set up my own primary keys and Unique ID's to set up relationships but I have to work with what was given to me... Thanks Dan, I mean it, Thanks... Bennie "Dan Artuso" wrote: Hi Bennie, Yes! That's more like it. Now you're giving me a better understanding of what you're after. DLookup() seems like it will fit the bill for this. There are a few more things I need to know. What are the fields in tblDayRate? Is there a CurrencyCode and VehicleCode field that we can use??? And do you actually have 3 fields called 4, 8, and 10?? If tblDayRate does not have a CurrencyCode and VehicleCode field, then I need to know how we can relate the DayRate to these values. -- HTH Dan Artuso, Access MVP "REVBJONES" wrote in message ... Thanks again Dan, you're really a big help to me. The table is tblDayRate, the form is my frmInvoiceDetails. There is a field on the form (A different Source) that requires an entry of 4, 8, or 10. I need to access the DayRate table to pull the rate for that amount of hours based on the Currency Code and Vehicle Code that match the C Code and V Code on my current form... Is that good Dan? Bennie... "Dan Artuso" wrote: Hi, You're just not being very clear. Are you saying that you have three fields named: 4, 8 and 10 ?? or is it 4 hour rates, 8 hour rates etc... See what I mean? And what is the name of the table these fields are in? In any case, I've answered the best I can with the info you've provided. Good Luck! Dan Artuso, MVP "REVBJONES" wrote in message ... I did Dan, 4, 8, and 10 hour rates... Bennie "Dan Artuso" wrote: Something like this: Dim strSql As String Dim rs As DAO.Recordset strSql = "Select " & nameOfField & " From yourTable" Set rs = CurrentDb.OpnRecordset(strSql) I don't what your reluctance is to provide the actual names of your tables, forms and controls but it sure would make it easier if you did so. -- HTH Dan Artuso, Access MVP "REVBJONES" wrote in message ... Yes now I need to know how to pull the right field from another table based on the value of a field on my current form... ex: value of the rate field on the form is 4, options in table are feild 4 or field 8 or field 10, three different fields that hold the rates for a 4 hour rate, 8 hr rate or a 10 hr rate... "Dan Artuso" wrote: Hi, Once you know what field you want to query, how do you determine which record you want? I mean you could build a Select statement and open a recordset, but do you not want to limit it in some way? -- HTH Dan Artuso, Access MVP "REVBJONES" wrote in message ... Thanks, you're a big help. Now I need to know how to pull the right field from another table based on the value of a field on my current form... ex: value of the rate field on the form is 4, options in table are 4 or 8 or 10, three different fields... Bennie... "Dan Artuso" wrote: Hi, As I said in your other thread, DLookup() will be easier than writing a SQL statement and opening up a recordset. The code would probably go in the Current event of the form, although without more info I can't be sure. It depends on when you want to populate the control. Here is the syntax for DLookup: DLookup(expr, domain[, criteria]) so you would have something like this: yourControl = DLookup("[yourField]","yourTable", "[someField] = '" & control1 & _ "' And [someOtherField] = '" & control2 "'" If you want exact code, you have to provide the names of your controls, fields, tables etc Also the datatype of your criteria is important as well (string,numeric or dates) as each one requires a different delimiter. I noticed you have three threads going, please reply to this one so everyone can follow what's happening and you'll have more chance of other people replying as well. -- HTH Dan Artuso, Access MVP "REVBJONES" wrote in message ... How do I use SELECT to pull into a form a value from another source based on 2 values on the currenr form and record and where do I put the code? Bennie |
|
Thread Tools | |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Thread Starter | Forum | Replies | Last Post |
SQL: Select the row with the latest time in groups | fix | Running & Setting Up Queries | 6 | August 19th, 2004 05:50 AM |
Ctrl Select and Copy Paste | kgeldhof | General Discussion | 2 | August 16th, 2004 06:39 PM |
select and update query combined | lynn atkinson | Running & Setting Up Queries | 5 | July 15th, 2004 03:48 PM |
UNION query question | Dale Peart | Running & Setting Up Queries | 6 | July 14th, 2004 12:26 AM |
Excel Worksheets | Richard | General Discussion | 2 | July 8th, 2004 04:34 AM |