"SELECT"

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

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

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

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

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

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

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

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

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