|
|
Our Hot Pick: Rising Antivirus 2006 - Certified by TUV & Checkmark! Get 10% discount by entering this coupon code: ONDISCOUNT10
I seem to have lost my correspondent from previously so I’ll try my question
again.
I’m trying to find the right function, or query procedure to look up a
registration fee in a table, based on pricing factors a registrar will
identify at sign-in time.
A table called tblScheduleRetreatFees holds 100 individual fees. It has four
fields in it - FeeID, RegistrantTypeID, DurationTypeID, and RetreatFee
(currency data type) - and, of course, 100 records. Perhaps I should add
that RegistrantTypeID and DurationTypeID are in the form of combo boxes in
this table.
Joined one-to-many to tblScheduleRetreatFees are 2 pricing factor tables:
RegistrantTypes, and DurationTypes. RegistrantTypes has 2 fields in it -
RegistrantTypeID, and RegistrantType - and 24 records, with values like
"Adult", "Teen", "Student", "Couple", "TwoAdult1kid," "TwoAdult2kids",
"TwoAdult2kids1teen", etc in the RegistrantType field. DurationTypes also
has 2 fields in it - DurationTypeID and DurationType - with values like
"Full Retreat", "HalfDay", "EveningProgram", etc., 6 records in all.
These 2 pricing factor tables are also joined one-to-many to the table which
will accumulated the fee charges for everyone who attends the retreat,
called InvoiceChargeRetreatFee. That gem has 4 fields in it -
ICRetreatFeeID, MainRegistrantID (a dropdown list box of peoples' names),
RegistrantTypeID (dropdown list), and DurationTypeID (ditto) - and no records
as yet, except my test data. One main registrant pays for himself/his
family, but can also opt to sponsor another individual; hence there could be
more than 1 retreat fee charge attributed to the same registrant.
I've based a data entry form, frmInvoiceChargeRetreatFee, on the
InvoiceChargeRetreatFee table; the registrar will use it as people arrive at
the desk to sign in. It has 3 combo boxes on it, where the registrar can
click on the person’s name, and then click their registrant type (family
type) and duration of stay. Once that’s done, I’d like the form to display
the correct fee, in a text box, looked up from tblScheduleRetreatFees.
I’ve tried setting the text box’s control source to
=DLookUp(tblScheduleRetreatFees!RetreatFee,[tblScheduleRetreatFees],tblScheduleRetreatFees!DurationTypeID=[Combo20]
And tblScheduleRetreatFees!RegistrantTypeID=[Combo16])
but this didn’t work. (I realize the Combo box numbers are high; it took a
while fiddling with the form and discarding the earlier, lower-numbered ones
to get the form roughly the way I wanted it.)
Have I got the DLookup code wrong? Or should I be trying to do this with
some other function?
|
|
The DLookup is the correct function to use, your syntax needs some cleaning up.
=DLookUp("[RetreatFee]" , "tblScheduleRetreatFees", "[DurationTypeID] = '" &
[Combo20] & "' And [RegistrantTypeID] = '" & [Combo16] & "'")
The above syntax assumes both DurationTypeID and RegistrantTypeID are text
fields. The next example assumes they are both numeric.
=DLookUp("[RetreatFee]" , "tblScheduleRetreatFees", "[DurationTypeID] = " &
[Combo20] & " And [RegistrantTypeID] = " & [Combo16])
How about creating your own control names so you and anyone else looking at
your code has an idea of what the control is for? [Combo16] means nothing to
anyone.
"katsup" wrote:
[Quoted Text] > I seem to have lost my correspondent from previously so I’ll try my question
> again.
>
> I’m trying to find the right function, or query procedure to look up a
> registration fee in a table, based on pricing factors a registrar will
> identify at sign-in time.
>
> A table called tblScheduleRetreatFees holds 100 individual fees. It has four
> fields in it - FeeID, RegistrantTypeID, DurationTypeID, and RetreatFee
> (currency data type) - and, of course, 100 records. Perhaps I should add
> that RegistrantTypeID and DurationTypeID are in the form of combo boxes in
> this table.
>
> Joined one-to-many to tblScheduleRetreatFees are 2 pricing factor tables:
> RegistrantTypes, and DurationTypes. RegistrantTypes has 2 fields in it -
> RegistrantTypeID, and RegistrantType - and 24 records, with values like
> "Adult", "Teen", "Student", "Couple", "TwoAdult1kid," "TwoAdult2kids",
> "TwoAdult2kids1teen", etc in the RegistrantType field. DurationTypes also
> has 2 fields in it - DurationTypeID and DurationType - with values like
> "Full Retreat", "HalfDay", "EveningProgram", etc., 6 records in all.
>
> These 2 pricing factor tables are also joined one-to-many to the table which
> will accumulated the fee charges for everyone who attends the retreat,
> called InvoiceChargeRetreatFee. That gem has 4 fields in it -
> ICRetreatFeeID, MainRegistrantID (a dropdown list box of peoples' names),
> RegistrantTypeID (dropdown list), and DurationTypeID (ditto) - and no records
> as yet, except my test data. One main registrant pays for himself/his
> family, but can also opt to sponsor another individual; hence there could be
> more than 1 retreat fee charge attributed to the same registrant.
>
> I've based a data entry form, frmInvoiceChargeRetreatFee, on the
> InvoiceChargeRetreatFee table; the registrar will use it as people arrive at
> the desk to sign in. It has 3 combo boxes on it, where the registrar can
> click on the person’s name, and then click their registrant type (family
> type) and duration of stay. Once that’s done, I’d like the form to display
> the correct fee, in a text box, looked up from tblScheduleRetreatFees.
>
> I’ve tried setting the text box’s control source to
>
> =DLookUp(tblScheduleRetreatFees!RetreatFee,[tblScheduleRetreatFees],tblScheduleRetreatFees!DurationTypeID=[Combo20]
> And tblScheduleRetreatFees!RegistrantTypeID=[Combo16])
>
> but this didn’t work. (I realize the Combo box numbers are high; it took a
> while fiddling with the form and discarding the earlier, lower-numbered ones
> to get the form roughly the way I wanted it.)
>
> Have I got the DLookup code wrong? Or should I be trying to do this with
> some other function?
>
|
|
Wow – that was a quick answer. And your second example worked perfectly!
But it mystifies me as to why. And searching through help files and my
textbook for expression syntax rules hasn’t clarified the problem.
When I use the expression builder (probably unskillfully), it doesn’t put
quotes around anything. Your solution enclosed both the RetreatFee field,
and the tblScheduleRetreatFees domain in quotes. What effect does that have?
Then the quotes in the criteria really stump me, along with the ampersands.
What do the quotes enclose? Is it
[DurationTypeID] = and later And
[RegistrantTypeID] =
or is it
& [Combo20] & and all of the criteria except for
& [Combo16]
Neither of these interpretations makes any sense to me. And how is the
ampersand, which my textbook says is for concatenating strings, working in
this criteria that uses only numeric fields? Does it have something to do
with the DurationType and RegistrantType being combo boxes that thus have
both the numeric and the text fields represented, e.g.
DurationTypeID DurationType
1 Full Retreat
2 One Full Day
3 Two Full Days
4 Half Day
5 Evening Program with dinner
6 Evening Program without dinner
I appreciate the suggestion to rename the controls - I didn't know I could
do that.
Thank you so much for your help, Klatuu. I've been fussing away at the
DLookup solution for several days trying to get it to work. Your advice has
given me hope I might actually get this application up and running. It's
volunteer work for a non-profit org I'm part of, and I was thinking I might
just abandon the effort, but now I'll forge ahead with renewed enthusiasm.
"Klatuu" wrote:
[Quoted Text] > The DLookup is the correct function to use, your syntax needs some cleaning up.
>
> =DLookUp("[RetreatFee]" , "tblScheduleRetreatFees", "[DurationTypeID] = '" &
> [Combo20] & "' And [RegistrantTypeID] = '" & [Combo16] & "'")
>
> The above syntax assumes both DurationTypeID and RegistrantTypeID are text
> fields. The next example assumes they are both numeric.
>
> =DLookUp("[RetreatFee]" , "tblScheduleRetreatFees", "[DurationTypeID] = " &
> [Combo20] & " And [RegistrantTypeID] = " & [Combo16])
>
> How about creating your own control names so you and anyone else looking at
> your code has an idea of what the control is for? [Combo16] means nothing to
> anyone.
>
>
> "katsup" wrote:
>
> > I seem to have lost my correspondent from previously so I’ll try my question
> > again.
> >
> > I’m trying to find the right function, or query procedure to look up a
> > registration fee in a table, based on pricing factors a registrar will
> > identify at sign-in time.
> >
> > A table called tblScheduleRetreatFees holds 100 individual fees. It has four
> > fields in it - FeeID, RegistrantTypeID, DurationTypeID, and RetreatFee
> > (currency data type) - and, of course, 100 records. Perhaps I should add
> > that RegistrantTypeID and DurationTypeID are in the form of combo boxes in
> > this table.
> >
> > Joined one-to-many to tblScheduleRetreatFees are 2 pricing factor tables:
> > RegistrantTypes, and DurationTypes. RegistrantTypes has 2 fields in it -
> > RegistrantTypeID, and RegistrantType - and 24 records, with values like
> > "Adult", "Teen", "Student", "Couple", "TwoAdult1kid," "TwoAdult2kids",
> > "TwoAdult2kids1teen", etc in the RegistrantType field. DurationTypes also
> > has 2 fields in it - DurationTypeID and DurationType - with values like
> > "Full Retreat", "HalfDay", "EveningProgram", etc., 6 records in all.
> >
> > These 2 pricing factor tables are also joined one-to-many to the table which
> > will accumulated the fee charges for everyone who attends the retreat,
> > called InvoiceChargeRetreatFee. That gem has 4 fields in it -
> > ICRetreatFeeID, MainRegistrantID (a dropdown list box of peoples' names),
> > RegistrantTypeID (dropdown list), and DurationTypeID (ditto) - and no records
> > as yet, except my test data. One main registrant pays for himself/his
> > family, but can also opt to sponsor another individual; hence there could be
> > more than 1 retreat fee charge attributed to the same registrant.
> >
> > I've based a data entry form, frmInvoiceChargeRetreatFee, on the
> > InvoiceChargeRetreatFee table; the registrar will use it as people arrive at
> > the desk to sign in. It has 3 combo boxes on it, where the registrar can
> > click on the person’s name, and then click their registrant type (family
> > type) and duration of stay. Once that’s done, I’d like the form to display
> > the correct fee, in a text box, looked up from tblScheduleRetreatFees.
> >
> > I’ve tried setting the text box’s control source to
> >
> > =DLookUp(tblScheduleRetreatFees!RetreatFee,[tblScheduleRetreatFees],tblScheduleRetreatFees!DurationTypeID=[Combo20]
> > And tblScheduleRetreatFees!RegistrantTypeID=[Combo16])
> >
> > but this didn’t work. (I realize the Combo box numbers are high; it took a
> > while fiddling with the form and discarding the earlier, lower-numbered ones
> > to get the form roughly the way I wanted it.)
> >
> > Have I got the DLookup code wrong? Or should I be trying to do this with
> > some other function?
> >
|
|
Don't give up! I know it can be frustrating, but this is how we learn. And,
if this is non profit work, then it is worth the effort.
The ampersand is a concatenation sign. It is normally used to concatenate
strings. When you use any Domain Agrogate function, you are talking to the
Jet engine. Jet is not actually Access. Access uses Jet for data definition
and manipulation. The language Jet uses is Jet SQL. The syntax rules for
Jet dictate values passed to compare to field values have to be formatted to
to match the data type of the field. Controls on forms do not have data
types, so don't confuse a recordset field with a form control. You have to
match to the data type of the recordset field.
Values for numeric fields should have no delimiters.
=DLookup("[FieldToReturn]", "DomainName", "[FieldToCompare] = " & 63)
Values for text fields should use either single or double quotes.
=DLookup("[FieldToReturn]", "DomainName", "[FieldToCompare] = '" & 63 & "'")
Vaules for date/teim fields should use #
=DLookup("[FieldToReturn]", "DomainName", "[FieldToCompare] = #7/4/2006#")
Now, the single/double quotes issue. This is one I struggle with every time
I have to use it. The advantage of using single quotes, is it make the
syntax easier to write. The disadvantage is if a field contains a single
qoute, it will cause an error.
These two are equivilant:
=DLookup("[FieldToReturn]", "DomainName", "[FieldToCompare] = '" & 63 & "'")
=DLookup("[FieldToReturn]", "DomainName", "[FieldToCompare] = " & """63 &
"""")
I can understand your confusion over the contcatenation of a numeric value
using &.
What is happening is that Access is doing what is known as Type Coersion;
that is, it will internally change a data type when it has to. In this case,
Jet needs the correct delimiters. so it will append the number to the string,
but without any delimiters. Try this as an experiment in the immediate
window.
Enter x = "FooBah"
?x
you will see
FooBah
Now enter
x = x & 3
?x
you will see
FooBah3
x = x & 3 is the same as x = x & Cstr(3)
The difference is, that with the Cstr function you dictate the conversioni
of the number to string. Without it, VBA knows it is putting a number in a
string and does the conversion for you.
I hope this has helped you along your way. Keep at it, and visit this site
often. I have learned even more than I have helped. Please post back when
(not if) you have more questions.
"katsup" wrote:
[Quoted Text] >
>
> Wow – that was a quick answer. And your second example worked perfectly!
>
> But it mystifies me as to why. And searching through help files and my
> textbook for expression syntax rules hasn’t clarified the problem.
>
> When I use the expression builder (probably unskillfully), it doesn’t put
> quotes around anything. Your solution enclosed both the RetreatFee field,
> and the tblScheduleRetreatFees domain in quotes. What effect does that have?
>
> Then the quotes in the criteria really stump me, along with the ampersands.
> What do the quotes enclose? Is it
>
> [DurationTypeID] = and later And
> [RegistrantTypeID] =
>
> or is it
>
> & [Combo20] & and all of the criteria except for
> & [Combo16]
>
>
>
> Neither of these interpretations makes any sense to me. And how is the
> ampersand, which my textbook says is for concatenating strings, working in
> this criteria that uses only numeric fields? Does it have something to do
> with the DurationType and RegistrantType being combo boxes that thus have
> both the numeric and the text fields represented, e.g.
>
> DurationTypeID DurationType
> 1 Full Retreat
> 2 One Full Day
> 3 Two Full Days
> 4 Half Day
> 5 Evening Program with dinner
> 6 Evening Program without dinner
>
> I appreciate the suggestion to rename the controls - I didn't know I could
> do that.
>
> Thank you so much for your help, Klatuu. I've been fussing away at the
> DLookup solution for several days trying to get it to work. Your advice has
> given me hope I might actually get this application up and running. It's
> volunteer work for a non-profit org I'm part of, and I was thinking I might
> just abandon the effort, but now I'll forge ahead with renewed enthusiasm.
>
>
> "Klatuu" wrote:
>
> > The DLookup is the correct function to use, your syntax needs some cleaning up.
> >
> > =DLookUp("[RetreatFee]" , "tblScheduleRetreatFees", "[DurationTypeID] = '" &
> > [Combo20] & "' And [RegistrantTypeID] = '" & [Combo16] & "'")
> >
> > The above syntax assumes both DurationTypeID and RegistrantTypeID are text
> > fields. The next example assumes they are both numeric.
> >
> > =DLookUp("[RetreatFee]" , "tblScheduleRetreatFees", "[DurationTypeID] = " &
> > [Combo20] & " And [RegistrantTypeID] = " & [Combo16])
> >
> > How about creating your own control names so you and anyone else looking at
> > your code has an idea of what the control is for? [Combo16] means nothing to
> > anyone.
> >
> >
> > "katsup" wrote:
> >
> > > I seem to have lost my correspondent from previously so I’ll try my question
> > > again.
> > >
> > > I’m trying to find the right function, or query procedure to look up a
> > > registration fee in a table, based on pricing factors a registrar will
> > > identify at sign-in time.
> > >
> > > A table called tblScheduleRetreatFees holds 100 individual fees. It has four
> > > fields in it - FeeID, RegistrantTypeID, DurationTypeID, and RetreatFee
> > > (currency data type) - and, of course, 100 records. Perhaps I should add
> > > that RegistrantTypeID and DurationTypeID are in the form of combo boxes in
> > > this table.
> > >
> > > Joined one-to-many to tblScheduleRetreatFees are 2 pricing factor tables:
> > > RegistrantTypes, and DurationTypes. RegistrantTypes has 2 fields in it -
> > > RegistrantTypeID, and RegistrantType - and 24 records, with values like
> > > "Adult", "Teen", "Student", "Couple", "TwoAdult1kid," "TwoAdult2kids",
> > > "TwoAdult2kids1teen", etc in the RegistrantType field. DurationTypes also
> > > has 2 fields in it - DurationTypeID and DurationType - with values like
> > > "Full Retreat", "HalfDay", "EveningProgram", etc., 6 records in all.
> > >
> > > These 2 pricing factor tables are also joined one-to-many to the table which
> > > will accumulated the fee charges for everyone who attends the retreat,
> > > called InvoiceChargeRetreatFee. That gem has 4 fields in it -
> > > ICRetreatFeeID, MainRegistrantID (a dropdown list box of peoples' names),
> > > RegistrantTypeID (dropdown list), and DurationTypeID (ditto) - and no records
> > > as yet, except my test data. One main registrant pays for himself/his
> > > family, but can also opt to sponsor another individual; hence there could be
> > > more than 1 retreat fee charge attributed to the same registrant.
> > >
> > > I've based a data entry form, frmInvoiceChargeRetreatFee, on the
> > > InvoiceChargeRetreatFee table; the registrar will use it as people arrive at
> > > the desk to sign in. It has 3 combo boxes on it, where the registrar can
> > > click on the person’s name, and then click their registrant type (family
> > > type) and duration of stay. Once that’s done, I’d like the form to display
> > > the correct fee, in a text box, looked up from tblScheduleRetreatFees.
> > >
> > > I’ve tried setting the text box’s control source to
> > >
> > > =DLookUp(tblScheduleRetreatFees!RetreatFee,[tblScheduleRetreatFees],tblScheduleRetreatFees!DurationTypeID=[Combo20]
> > > And tblScheduleRetreatFees!RegistrantTypeID=[Combo16])
> > >
> > > but this didn’t work. (I realize the Combo box numbers are high; it took a
> > > while fiddling with the form and discarding the earlier, lower-numbered ones
> > > to get the form roughly the way I wanted it.)
> > >
> > > Have I got the DLookup code wrong? Or should I be trying to do this with
> > > some other function?
> > >
|
|
Ah...! Controls on forms don't have data types. That basic concept clears
up a lot of confusion for me. (I was assuming that since the controls
inherit the format property of the underlying table fields they'd inherit the
data type property too.)
I'm still struggling with the quotes in your examples though. In the
criteria portion, why are there quotes enclosing BOTH the field name and the
equals sign comparison operator, smushing them in together i.e.
=DLookup("[FieldToReturn]", "DomainName", "[FieldToCompare] = " & 63)
Then again, in the solution that actually worked in my database, it seems
like the 'And' logical operator is smushed in with the field name by the
quotes that enclose those two, i.e.,
=DLookUp("[RetreatFee]" , "tblScheduleRetreatFees", "[DurationTypeID] = " &
[Combo20] & " And [RegistrantTypeID] = " & [Combo16])
Even if Type Coersion is forcing a data type conversion, how can the logical
operator get included in with a string element and still be recognized as a
logcial operator.
Since you've enclosed the first 2 elements of the DLookup function,
[FieldToReturn] and DomaineName in quotes, I'm guessing quotes don't say to
SQL that what's enclosed within them is a string, but then why use them at
all?
"Klatuu" wrote:
[Quoted Text] > Don't give up! I know it can be frustrating, but this is how we learn. And,
> if this is non profit work, then it is worth the effort.
>
> The ampersand is a concatenation sign. It is normally used to concatenate
> strings. When you use any Domain Agrogate function, you are talking to the
> Jet engine. Jet is not actually Access. Access uses Jet for data definition
> and manipulation. The language Jet uses is Jet SQL. The syntax rules for
> Jet dictate values passed to compare to field values have to be formatted to
> to match the data type of the field. Controls on forms do not have data
> types, so don't confuse a recordset field with a form control. You have to
> match to the data type of the recordset field.
>
> Values for numeric fields should have no delimiters.
> =DLookup("[FieldToReturn]", "DomainName", "[FieldToCompare] = " & 63)
> Values for text fields should use either single or double quotes.
> =DLookup("[FieldToReturn]", "DomainName", "[FieldToCompare] = '" & 63 & "'")
> Vaules for date/teim fields should use #
> =DLookup("[FieldToReturn]", "DomainName", "[FieldToCompare] = #7/4/2006#")
>
> Now, the single/double quotes issue. This is one I struggle with every time
> I have to use it. The advantage of using single quotes, is it make the
> syntax easier to write. The disadvantage is if a field contains a single
> qoute, it will cause an error.
> These two are equivilant:
> =DLookup("[FieldToReturn]", "DomainName", "[FieldToCompare] = '" & 63 & "'")
> =DLookup("[FieldToReturn]", "DomainName", "[FieldToCompare] = " & """63 &
> """")
>
> I can understand your confusion over the contcatenation of a numeric value
> using &.
> What is happening is that Access is doing what is known as Type Coersion;
> that is, it will internally change a data type when it has to. In this case,
> Jet needs the correct delimiters. so it will append the number to the string,
> but without any delimiters. Try this as an experiment in the immediate
> window.
> Enter x = "FooBah"
> ?x
> you will see
> FooBah
>
> Now enter
> x = x & 3
> ?x
> you will see
> FooBah3
>
> x = x & 3 is the same as x = x & Cstr(3)
>
> The difference is, that with the Cstr function you dictate the conversioni
> of the number to string. Without it, VBA knows it is putting a number in a
> string and does the conversion for you.
>
> I hope this has helped you along your way. Keep at it, and visit this site
> often. I have learned even more than I have helped. Please post back when
> (not if) you have more questions.
>
> "katsup" wrote:
>
> >
> >
> > Wow – that was a quick answer. And your second example worked perfectly!
> >
> > But it mystifies me as to why. And searching through help files and my
> > textbook for expression syntax rules hasn’t clarified the problem.
> >
> > When I use the expression builder (probably unskillfully), it doesn’t put
> > quotes around anything. Your solution enclosed both the RetreatFee field,
> > and the tblScheduleRetreatFees domain in quotes. What effect does that have?
> >
> > Then the quotes in the criteria really stump me, along with the ampersands.
> > What do the quotes enclose? Is it
> >
> > [DurationTypeID] = and later And
> > [RegistrantTypeID] =
> >
> > or is it
> >
> > & [Combo20] & and all of the criteria except for
> > & [Combo16]
> >
> >
> >
> > Neither of these interpretations makes any sense to me. And how is the
> > ampersand, which my textbook says is for concatenating strings, working in
> > this criteria that uses only numeric fields? Does it have something to do
> > with the DurationType and RegistrantType being combo boxes that thus have
> > both the numeric and the text fields represented, e.g.
> >
> > DurationTypeID DurationType
> > 1 Full Retreat
> > 2 One Full Day
> > 3 Two Full Days
> > 4 Half Day
> > 5 Evening Program with dinner
> > 6 Evening Program without dinner
> >
> > I appreciate the suggestion to rename the controls - I didn't know I could
> > do that.
> >
> > Thank you so much for your help, Klatuu. I've been fussing away at the
> > DLookup solution for several days trying to get it to work. Your advice has
> > given me hope I might actually get this application up and running. It's
> > volunteer work for a non-profit org I'm part of, and I was thinking I might
> > just abandon the effort, but now I'll forge ahead with renewed enthusiasm.
> >
> >
> > "Klatuu" wrote:
> >
> > > The DLookup is the correct function to use, your syntax needs some cleaning up.
> > >
> > > =DLookUp("[RetreatFee]" , "tblScheduleRetreatFees", "[DurationTypeID] = '" &
> > > [Combo20] & "' And [RegistrantTypeID] = '" & [Combo16] & "'")
> > >
> > > The above syntax assumes both DurationTypeID and RegistrantTypeID are text
> > > fields. The next example assumes they are both numeric.
> > >
> > > =DLookUp("[RetreatFee]" , "tblScheduleRetreatFees", "[DurationTypeID] = " &
> > > [Combo20] & " And [RegistrantTypeID] = " & [Combo16])
> > >
> > > How about creating your own control names so you and anyone else looking at
> > > your code has an idea of what the control is for? [Combo16] means nothing to
> > > anyone.
> > >
> > >
> > > "katsup" wrote:
> > >
> > > > I seem to have lost my correspondent from previously so I’ll try my question
> > > > again.
> > > >
> > > > I’m trying to find the right function, or query procedure to look up a
> > > > registration fee in a table, based on pricing factors a registrar will
> > > > identify at sign-in time.
> > > >
> > > > A table called tblScheduleRetreatFees holds 100 individual fees. It has four
> > > > fields in it - FeeID, RegistrantTypeID, DurationTypeID, and RetreatFee
> > > > (currency data type) - and, of course, 100 records. Perhaps I should add
> > > > that RegistrantTypeID and DurationTypeID are in the form of combo boxes in
> > > > this table.
> > > >
> > > > Joined one-to-many to tblScheduleRetreatFees are 2 pricing factor tables:
> > > > RegistrantTypes, and DurationTypes. RegistrantTypes has 2 fields in it -
> > > > RegistrantTypeID, and RegistrantType - and 24 records, with values like
> > > > "Adult", "Teen", "Student", "Couple", "TwoAdult1kid," "TwoAdult2kids",
> > > > "TwoAdult2kids1teen", etc in the RegistrantType field. DurationTypes also
> > > > has 2 fields in it - DurationTypeID and DurationType - with values like
> > > > "Full Retreat", "HalfDay", "EveningProgram", etc., 6 records in all.
> > > >
> > > > These 2 pricing factor tables are also joined one-to-many to the table which
> > > > will accumulated the fee charges for everyone who attends the retreat,
> > > > called InvoiceChargeRetreatFee. That gem has 4 fields in it -
> > > > ICRetreatFeeID, MainRegistrantID (a dropdown list box of peoples' names),
> > > > RegistrantTypeID (dropdown list), and DurationTypeID (ditto) - and no records
> > > > as yet, except my test data. One main registrant pays for himself/his
> > > > family, but can also opt to sponsor another individual; hence there could be
> > > > more than 1 retreat fee charge attributed to the same registrant.
> > > >
> > > > I've based a data entry form, frmInvoiceChargeRetreatFee, on the
> > > > InvoiceChargeRetreatFee table; the registrar will use it as people arrive at
> > > > the desk to sign in. It has 3 combo boxes on it, where the registrar can
> > > > click on the person’s name, and then click their registrant type (family
> > > > type) and duration of stay. Once that’s done, I’d like the form to display
> > > > the correct fee, in a text box, looked up from tblScheduleRetreatFees.
> > > >
> > > > I’ve tried setting the text box’s control source to
> > > >
> > > > =DLookUp(tblScheduleRetreatFees!RetreatFee,[tblScheduleRetreatFees],tblScheduleRetreatFees!DurationTypeID=[Combo20]
> > > > And tblScheduleRetreatFees!RegistrantTypeID=[Combo16])
> > > >
> > > > but this didn’t work. (I realize the Combo box numbers are high; it took a
> > > > while fiddling with the form and discarding the earlier, lower-numbered ones
> > > > to get the form roughly the way I wanted it.)
> > > >
> > > > Have I got the DLookup code wrong? Or should I be trying to do this with
> > > > some other function?
> > > >
|
|
Remember, Access is sending the request to Jet for processing. What we have
to do is construct a string that Jet can then parse and take action on. It
is creating an SQL statement. All the queries you construct using the query
builder are stored as SQL statements. You can see what they look like by
switching to SQL view in the query builder. Note that is is they way they
are stored in Access. You can also get to them in access by:
strSQL = CurrentDb.Querydefs("SomeQueryNameHere").SQL
Now strSQL contrains a string that is a Jet SQL statement. As you progress
in your Access knowledge, you will find that you can modify the SQL and use
it however you want. In many cases, you will want to provide the user the
ability to filter what a query will return based on their choices in a form.
Here is an example of how this is done:
strSQL = CurrentDb.Querdefs("SomeQueryNameHere").SQL
strWhere = BuildWhere() ' A function that sets up the WHERE clause
strSQL = Replace(strSQL, strWhere, ";")
CurrentDb.Querydefs("ProductionQueryName").SQL = strSQL
CurrentDb.Execute(strSQL), dbFailOnError
So, You are not constructing commands to be executed like VBA, but using VBA
to create an SQL statement.
"katsup" wrote:
[Quoted Text] > Ah...! Controls on forms don't have data types. That basic concept clears
> up a lot of confusion for me. (I was assuming that since the controls
> inherit the format property of the underlying table fields they'd inherit the
> data type property too.)
>
> I'm still struggling with the quotes in your examples though. In the
> criteria portion, why are there quotes enclosing BOTH the field name and the
> equals sign comparison operator, smushing them in together i.e.
> =DLookup("[FieldToReturn]", "DomainName", "[FieldToCompare] = " & 63)
>
> Then again, in the solution that actually worked in my database, it seems
> like the 'And' logical operator is smushed in with the field name by the
> quotes that enclose those two, i.e.,
> =DLookUp("[RetreatFee]" , "tblScheduleRetreatFees", "[DurationTypeID] = " &
> [Combo20] & " And [RegistrantTypeID] = " & [Combo16])
> Even if Type Coersion is forcing a data type conversion, how can the logical
> operator get included in with a string element and still be recognized as a
> logcial operator.
>
> Since you've enclosed the first 2 elements of the DLookup function,
> [FieldToReturn] and DomaineName in quotes, I'm guessing quotes don't say to
> SQL that what's enclosed within them is a string, but then why use them at
> all?
>
>
>
> "Klatuu" wrote:
>
> > Don't give up! I know it can be frustrating, but this is how we learn. And,
> > if this is non profit work, then it is worth the effort.
> >
> > The ampersand is a concatenation sign. It is normally used to concatenate
> > strings. When you use any Domain Agrogate function, you are talking to the
> > Jet engine. Jet is not actually Access. Access uses Jet for data definition
> > and manipulation. The language Jet uses is Jet SQL. The syntax rules for
> > Jet dictate values passed to compare to field values have to be formatted to
> > to match the data type of the field. Controls on forms do not have data
> > types, so don't confuse a recordset field with a form control. You have to
> > match to the data type of the recordset field.
> >
> > Values for numeric fields should have no delimiters.
> > =DLookup("[FieldToReturn]", "DomainName", "[FieldToCompare] = " & 63)
> > Values for text fields should use either single or double quotes.
> > =DLookup("[FieldToReturn]", "DomainName", "[FieldToCompare] = '" & 63 & "'")
> > Vaules for date/teim fields should use #
> > =DLookup("[FieldToReturn]", "DomainName", "[FieldToCompare] = #7/4/2006#")
> >
> > Now, the single/double quotes issue. This is one I struggle with every time
> > I have to use it. The advantage of using single quotes, is it make the
> > syntax easier to write. The disadvantage is if a field contains a single
> > qoute, it will cause an error.
> > These two are equivilant:
> > =DLookup("[FieldToReturn]", "DomainName", "[FieldToCompare] = '" & 63 & "'")
> > =DLookup("[FieldToReturn]", "DomainName", "[FieldToCompare] = " & """63 &
> > """")
> >
> > I can understand your confusion over the contcatenation of a numeric value
> > using &.
> > What is happening is that Access is doing what is known as Type Coersion;
> > that is, it will internally change a data type when it has to. In this case,
> > Jet needs the correct delimiters. so it will append the number to the string,
> > but without any delimiters. Try this as an experiment in the immediate
> > window.
> > Enter x = "FooBah"
> > ?x
> > you will see
> > FooBah
> >
> > Now enter
> > x = x & 3
> > ?x
> > you will see
> > FooBah3
> >
> > x = x & 3 is the same as x = x & Cstr(3)
> >
> > The difference is, that with the Cstr function you dictate the conversioni
> > of the number to string. Without it, VBA knows it is putting a number in a
> > string and does the conversion for you.
> >
> > I hope this has helped you along your way. Keep at it, and visit this site
> > often. I have learned even more than I have helped. Please post back when
> > (not if) you have more questions.
> >
> > "katsup" wrote:
> >
> > >
> > >
> > > Wow – that was a quick answer. And your second example worked perfectly!
> > >
> > > But it mystifies me as to why. And searching through help files and my
> > > textbook for expression syntax rules hasn’t clarified the problem.
> > >
> > > When I use the expression builder (probably unskillfully), it doesn’t put
> > > quotes around anything. Your solution enclosed both the RetreatFee field,
> > > and the tblScheduleRetreatFees domain in quotes. What effect does that have?
> > >
> > > Then the quotes in the criteria really stump me, along with the ampersands.
> > > What do the quotes enclose? Is it
> > >
> > > [DurationTypeID] = and later And
> > > [RegistrantTypeID] =
> > >
> > > or is it
> > >
> > > & [Combo20] & and all of the criteria except for
> > > & [Combo16]
> > >
> > >
> > >
> > > Neither of these interpretations makes any sense to me. And how is the
> > > ampersand, which my textbook says is for concatenating strings, working in
> > > this criteria that uses only numeric fields? Does it have something to do
> > > with the DurationType and RegistrantType being combo boxes that thus have
> > > both the numeric and the text fields represented, e.g.
> > >
> > > DurationTypeID DurationType
> > > 1 Full Retreat
> > > 2 One Full Day
> > > 3 Two Full Days
> > > 4 Half Day
> > > 5 Evening Program with dinner
> > > 6 Evening Program without dinner
> > >
> > > I appreciate the suggestion to rename the controls - I didn't know I could
> > > do that.
> > >
> > > Thank you so much for your help, Klatuu. I've been fussing away at the
> > > DLookup solution for several days trying to get it to work. Your advice has
> > > given me hope I might actually get this application up and running. It's
> > > volunteer work for a non-profit org I'm part of, and I was thinking I might
> > > just abandon the effort, but now I'll forge ahead with renewed enthusiasm.
> > >
> > >
> > > "Klatuu" wrote:
> > >
> > > > The DLookup is the correct function to use, your syntax needs some cleaning up.
> > > >
> > > > =DLookUp("[RetreatFee]" , "tblScheduleRetreatFees", "[DurationTypeID] = '" &
> > > > [Combo20] & "' And [RegistrantTypeID] = '" & [Combo16] & "'")
> > > >
> > > > The above syntax assumes both DurationTypeID and RegistrantTypeID are text
> > > > fields. The next example assumes they are both numeric.
> > > >
> > > > =DLookUp("[RetreatFee]" , "tblScheduleRetreatFees", "[DurationTypeID] = " &
> > > > [Combo20] & " And [RegistrantTypeID] = " & [Combo16])
> > > >
> > > > How about creating your own control names so you and anyone else looking at
> > > > your code has an idea of what the control is for? [Combo16] means nothing to
> > > > anyone.
> > > >
> > > >
> > > > "katsup" wrote:
> > > >
> > > > > I seem to have lost my correspondent from previously so I’ll try my question
> > > > > again.
> > > > >
> > > > > I’m trying to find the right function, or query procedure to look up a
> > > > > registration fee in a table, based on pricing factors a registrar will
> > > > > identify at sign-in time.
> > > > >
> > > > > A table called tblScheduleRetreatFees holds 100 individual fees. It has four
> > > > > fields in it - FeeID, RegistrantTypeID, DurationTypeID, and RetreatFee
> > > > > (currency data type) - and, of course, 100 records. Perhaps I should add
> > > > > that RegistrantTypeID and DurationTypeID are in the form of combo boxes in
> > > > > this table.
> > > > >
> > > > > Joined one-to-many to tblScheduleRetreatFees are 2 pricing factor tables:
> > > > > RegistrantTypes, and DurationTypes. RegistrantTypes has 2 fields in it -
> > > > > RegistrantTypeID, and RegistrantType - and 24 records, with values like
> > > > > "Adult", "Teen", "Student", "Couple", "TwoAdult1kid," "TwoAdult2kids",
> > > > > "TwoAdult2kids1teen", etc in the RegistrantType field. DurationTypes also
> > > > > has 2 fields in it - DurationTypeID and DurationType - with values like
> > > > > "Full Retreat", "HalfDay", "EveningProgram", etc., 6 records in all.
> > > > >
> > > > > These 2 pricing factor tables are also joined one-to-many to the table which
> > > > > will accumulated the fee charges for everyone who attends the retreat,
> > > > > called InvoiceChargeRetreatFee. That gem has 4 fields in it -
> > > > > ICRetreatFeeID, MainRegistrantID (a dropdown list box of peoples' names),
> > > > > RegistrantTypeID (dropdown list), and DurationTypeID (ditto) - and no records
> > > > > as yet, except my test data. One main registrant pays for himself/his
> > > > > family, but can also opt to sponsor another individual; hence there could be
> > > > > more than 1 retreat fee charge attributed to the same registrant.
> > > > >
> > > > > I've based a data entry form, frmInvoiceChargeRetreatFee, on the
> > > > > InvoiceChargeRetreatFee table; the registrar will use it as people arrive at
> > > > > the desk to sign in. It has 3 combo boxes on it, where the registrar can
> > > > > click on the person’s name, and then click their registrant type (family
> > > > > type) and duration of stay. Once that’s done, I’d like the form to display
> > > > > the correct fee, in a text box, looked up from tblScheduleRetreatFees.
> > > > >
> > > > > I’ve tried setting the text box’s control source to
> > > > >
> > > > > =DLookUp(tblScheduleRetreatFees!RetreatFee,[tblScheduleRetreatFees],tblScheduleRetreatFees!DurationTypeID=[Combo20]
> > > > > And tblScheduleRetreatFees!RegistrantTypeID=[Combo16])
> > > > >
> > > > > but this didn’t work. (I realize the Combo box numbers are high; it took a
> > > > > while fiddling with the form and discarding the earlier, lower-numbered ones
> > > > > to get the form roughly the way I wanted it.)
> > > > >
> > > > > Have I got the DLookup code wrong? Or should I be trying to do this with
> > > > > some other function?
> > > > >
|
|
I think I finally get it.
When I look at the SQL view of a query there are no quotes anywhere (at
least, not in any of my test queries) because the QBE grid has already been
parsed into SQL. The 3 parts of a domain aggregate function like DLookup
however, haven't yet gone through that parsing: so quotes and concatenators
are needed to enable the "strings" to eventually be parsed into the field
names, operators, values etc. of executable code.
Mystery solved, thanks to your clear explanation. This is a huge step
forward for me in understanding the big picture; now all those confusing
references to "strings" in the help files make sense.
A corollary question occurs to me now, about the text DLookup examples:
=DLookup("[FieldToReturn]", "DomainName", "[FieldToCompare] = '" & 63 & "'")
=DLookUp("[RetreatFee]" , "tblScheduleRetreatFees", "[DurationTypeID] = '" &
[Combo20] & "' And [RegistrantTypeID] = '" & [Combo16] & "'")
What's the purpose of the trailing ampersand just before the closing "'") in
each of these? What's being concatenated to the text value 63, or whatever
text value might be in [Combo16], other than the closing quotes?
Once again, your help has been invaluable, klatuu.
"Klatuu" wrote:
[Quoted Text] > Remember, Access is sending the request to Jet for processing. What we have
> to do is construct a string that Jet can then parse and take action on. It
> is creating an SQL statement. All the queries you construct using the query
> builder are stored as SQL statements. You can see what they look like by
> switching to SQL view in the query builder. Note that is is they way they
> are stored in Access. You can also get to them in access by:
>
> strSQL = CurrentDb.Querydefs("SomeQueryNameHere").SQL
>
> Now strSQL contrains a string that is a Jet SQL statement. As you progress
> in your Access knowledge, you will find that you can modify the SQL and use
> it however you want. In many cases, you will want to provide the user the
> ability to filter what a query will return based on their choices in a form.
> Here is an example of how this is done:
>
> strSQL = CurrentDb.Querdefs("SomeQueryNameHere").SQL
>
> strWhere = BuildWhere() ' A function that sets up the WHERE clause
> strSQL = Replace(strSQL, strWhere, ";")
> CurrentDb.Querydefs("ProductionQueryName").SQL = strSQL
> CurrentDb.Execute(strSQL), dbFailOnError
>
> So, You are not constructing commands to be executed like VBA, but using VBA
> to create an SQL statement.
>
>
> "katsup" wrote:
>
> > Ah...! Controls on forms don't have data types. That basic concept clears
> > up a lot of confusion for me. (I was assuming that since the controls
> > inherit the format property of the underlying table fields they'd inherit the
> > data type property too.)
> >
> > I'm still struggling with the quotes in your examples though. In the
> > criteria portion, why are there quotes enclosing BOTH the field name and the
> > equals sign comparison operator, smushing them in together i.e.
> > =DLookup("[FieldToReturn]", "DomainName", "[FieldToCompare] = " & 63)
> >
> > Then again, in the solution that actually worked in my database, it seems
> > like the 'And' logical operator is smushed in with the field name by the
> > quotes that enclose those two, i.e.,
> > =DLookUp("[RetreatFee]" , "tblScheduleRetreatFees", "[DurationTypeID] = " &
> > [Combo20] & " And [RegistrantTypeID] = " & [Combo16])
> > Even if Type Coersion is forcing a data type conversion, how can the logical
> > operator get included in with a string element and still be recognized as a
> > logcial operator.
> >
> > Since you've enclosed the first 2 elements of the DLookup function,
> > [FieldToReturn] and DomaineName in quotes, I'm guessing quotes don't say to
> > SQL that what's enclosed within them is a string, but then why use them at
> > all?
> >
> >
> >
> > "Klatuu" wrote:
> >
> > > Don't give up! I know it can be frustrating, but this is how we learn. And,
> > > if this is non profit work, then it is worth the effort.
> > >
> > > The ampersand is a concatenation sign. It is normally used to concatenate
> > > strings. When you use any Domain Agrogate function, you are talking to the
> > > Jet engine. Jet is not actually Access. Access uses Jet for data definition
> > > and manipulation. The language Jet uses is Jet SQL. The syntax rules for
> > > Jet dictate values passed to compare to field values have to be formatted to
> > > to match the data type of the field. Controls on forms do not have data
> > > types, so don't confuse a recordset field with a form control. You have to
> > > match to the data type of the recordset field.
> > >
> > > Values for numeric fields should have no delimiters.
> > > =DLookup("[FieldToReturn]", "DomainName", "[FieldToCompare] = " & 63)
> > > Values for text fields should use either single or double quotes.
> > > =DLookup("[FieldToReturn]", "DomainName", "[FieldToCompare] = '" & 63 & "'")
> > > Vaules for date/teim fields should use #
> > > =DLookup("[FieldToReturn]", "DomainName", "[FieldToCompare] = #7/4/2006#")
> > >
> > > Now, the single/double quotes issue. This is one I struggle with every time
> > > I have to use it. The advantage of using single quotes, is it make the
> > > syntax easier to write. The disadvantage is if a field contains a single
> > > qoute, it will cause an error.
> > > These two are equivilant:
> > > =DLookup("[FieldToReturn]", "DomainName", "[FieldToCompare] = '" & 63 & "'")
> > > =DLookup("[FieldToReturn]", "DomainName", "[FieldToCompare] = " & """63 &
> > > """")
> > >
> > > I can understand your confusion over the contcatenation of a numeric value
> > > using &.
> > > What is happening is that Access is doing what is known as Type Coersion;
> > > that is, it will internally change a data type when it has to. In this case,
> > > Jet needs the correct delimiters. so it will append the number to the string,
> > > but without any delimiters. Try this as an experiment in the immediate
> > > window.
> > > Enter x = "FooBah"
> > > ?x
> > > you will see
> > > FooBah
> > >
> > > Now enter
> > > x = x & 3
> > > ?x
> > > you will see
> > > FooBah3
> > >
> > > x = x & 3 is the same as x = x & Cstr(3)
> > >
> > > The difference is, that with the Cstr function you dictate the conversioni
> > > of the number to string. Without it, VBA knows it is putting a number in a
> > > string and does the conversion for you.
> > >
> > > I hope this has helped you along your way. Keep at it, and visit this site
> > > often. I have learned even more than I have helped. Please post back when
> > > (not if) you have more questions.
> > >
> > > "katsup" wrote:
> > >
> > > >
> > > >
> > > > Wow – that was a quick answer. And your second example worked perfectly!
> > > >
> > > > But it mystifies me as to why. And searching through help files and my
> > > > textbook for expression syntax rules hasn’t clarified the problem.
> > > >
> > > > When I use the expression builder (probably unskillfully), it doesn’t put
> > > > quotes around anything. Your solution enclosed both the RetreatFee field,
> > > > and the tblScheduleRetreatFees domain in quotes. What effect does that have?
> > > >
> > > > Then the quotes in the criteria really stump me, along with the ampersands.
> > > > What do the quotes enclose? Is it
> > > >
> > > > [DurationTypeID] = and later And
> > > > [RegistrantTypeID] =
> > > >
> > > > or is it
> > > >
> > > > & [Combo20] & and all of the criteria except for
> > > > & [Combo16]
> > > >
> > > >
> > > >
> > > > Neither of these interpretations makes any sense to me. And how is the
> > > > ampersand, which my textbook says is for concatenating strings, working in
> > > > this criteria that uses only numeric fields? Does it have something to do
> > > > with the DurationType and RegistrantType being combo boxes that thus have
> > > > both the numeric and the text fields represented, e.g.
> > > >
> > > > DurationTypeID DurationType
> > > > 1 Full Retreat
> > > > 2 One Full Day
> > > > 3 Two Full Days
> > > > 4 Half Day
> > > > 5 Evening Program with dinner
> > > > 6 Evening Program without dinner
> > > >
> > > > I appreciate the suggestion to rename the controls - I didn't know I could
> > > > do that.
> > > >
> > > > Thank you so much for your help, Klatuu. I've been fussing away at the
> > > > DLookup solution for several days trying to get it to work. Your advice has
> > > > given me hope I might actually get this application up and running. It's
> > > > volunteer work for a non-profit org I'm part of, and I was thinking I might
> > > > just abandon the effort, but now I'll forge ahead with renewed enthusiasm.
> > > >
> > > >
> > > > "Klatuu" wrote:
> > > >
> > > > > The DLookup is the correct function to use, your syntax needs some cleaning up.
> > > > >
> > > > > =DLookUp("[RetreatFee]" , "tblScheduleRetreatFees", "[DurationTypeID] = '" &
> > > > > [Combo20] & "' And [RegistrantTypeID] = '" & [Combo16] & "'")
> > > > >
> > > > > The above syntax assumes both DurationTypeID and RegistrantTypeID are text
> > > > > fields. The next example assumes they are both numeric.
> > > > >
> > > > > =DLookUp("[RetreatFee]" , "tblScheduleRetreatFees", "[DurationTypeID] = " &
> > > > > [Combo20] & " And [RegistrantTypeID] = " & [Combo16])
> > > > >
> > > > > How about creating your own control names so you and anyone else looking at
> > > > > your code has an idea of what the control is for? [Combo16] means nothing to
> > > > > anyone.
> > > > >
> > > > >
> > > > > "katsup" wrote:
> > > > >
> > > > > > I seem to have lost my correspondent from previously so I’ll try my question
> > > > > > again.
> > > > > >
> > > > > > I’m trying to find the right function, or query procedure to look up a
> > > > > > registration fee in a table, based on pricing factors a registrar will
> > > > > > identify at sign-in time.
> > > > > >
> > > > > > A table called tblScheduleRetreatFees holds 100 individual fees. It has four
> > > > > > fields in it - FeeID, RegistrantTypeID, DurationTypeID, and RetreatFee
> > > > > > (currency data type) - and, of course, 100 records. Perhaps I should add
> > > > > > that RegistrantTypeID and DurationTypeID are in the form of combo boxes in
> > > > > > this table.
> > > > > >
> > > > > > Joined one-to-many to tblScheduleRetreatFees are 2 pricing factor tables:
> > > > > > RegistrantTypes, and DurationTypes. RegistrantTypes has 2 fields in it -
> > > > > > RegistrantTypeID, and RegistrantType - and 24 records, with values like
> > > > > > "Adult", "Teen", "Student", "Couple", "TwoAdult1kid," "TwoAdult2kids",
> > > > > > "TwoAdult2kids1teen", etc in the RegistrantType field. DurationTypes also
> > > > > > has 2 fields in it - DurationTypeID and DurationType - with values like
> > > > > > "Full Retreat", "HalfDay", "EveningProgram", etc., 6 records in all.
> > > > > >
> > > > > > These 2 pricing factor tables are also joined one-to-many to the table which
> > > > > > will accumulated the fee charges for everyone who attends the retreat,
> > > > > > called InvoiceChargeRetreatFee. That gem has 4 fields in it -
> > > > > > ICRetreatFeeID, MainRegistrantID (a dropdown list box of peoples' names),
> > > > > > RegistrantTypeID (dropdown list), and DurationTypeID (ditto) - and no records
> > > > > > as yet, except my test data. One main registrant pays for himself/his
> > > > > > family, but can also opt to sponsor another individual; hence there could be
> > > > > > more than 1 retreat fee charge attributed to the same registrant.
> > > > > >
> > > > > > I've based a data entry form, frmInvoiceChargeRetreatFee, on the
> > > > > > InvoiceChargeRetreatFee table; the registrar will use it as people arrive at
> > > > > > the desk to sign in. It has 3 combo boxes on it, where the registrar can
> > > > > > click on the person’s name, and then click their registrant type (family
> > > > > > type) and duration of stay. Once that’s done, I’d like the form to display
> > > > > > the correct fee, in a text box, looked up from tblScheduleRetreatFees.
> > > > > >
> > > > > > I’ve tried setting the text box’s control source to
> > > > > >
> > > > > > =DLookUp(tblScheduleRetreatFees!RetreatFee,[tblScheduleRetreatFees],tblScheduleRetreatFees!DurationTypeID=[Combo20]
> > > > > > And tblScheduleRetreatFees!RegistrantTypeID=[Combo16])
> > > > > >
> > > > > > but this didn’t work. (I realize the Combo box numbers are high; it took a
> > > > > > while fiddling with the form and discarding the earlier, lower-numbered ones
> > > > > > to get the form roughly the way I wanted it.)
> > > > > >
> > > > > > Have I got the DLookup code wrong? Or should I be trying to do this with
> > > > > > some other function?
> > > > > >
|
|
Yep, you got it.
Glad I could help.
"katsup" wrote:
[Quoted Text] > I think I finally get it.
>
> When I look at the SQL view of a query there are no quotes anywhere (at
> least, not in any of my test queries) because the QBE grid has already been
> parsed into SQL. The 3 parts of a domain aggregate function like DLookup
> however, haven't yet gone through that parsing: so quotes and concatenators
> are needed to enable the "strings" to eventually be parsed into the field
> names, operators, values etc. of executable code.
>
> Mystery solved, thanks to your clear explanation. This is a huge step
> forward for me in understanding the big picture; now all those confusing
> references to "strings" in the help files make sense.
>
> A corollary question occurs to me now, about the text DLookup examples:
>
> =DLookup("[FieldToReturn]", "DomainName", "[FieldToCompare] = '" & 63 & "'")
> =DLookUp("[RetreatFee]" , "tblScheduleRetreatFees", "[DurationTypeID] = '" &
> [Combo20] & "' And [RegistrantTypeID] = '" & [Combo16] & "'")
>
> What's the purpose of the trailing ampersand just before the closing "'") in
> each of these? What's being concatenated to the text value 63, or whatever
> text value might be in [Combo16], other than the closing quotes?
>
> Once again, your help has been invaluable, klatuu.
>
>
>
> "Klatuu" wrote:
>
> > Remember, Access is sending the request to Jet for processing. What we have
> > to do is construct a string that Jet can then parse and take action on. It
> > is creating an SQL statement. All the queries you construct using the query
> > builder are stored as SQL statements. You can see what they look like by
> > switching to SQL view in the query builder. Note that is is they way they
> > are stored in Access. You can also get to them in access by:
> >
> > strSQL = CurrentDb.Querydefs("SomeQueryNameHere").SQL
> >
> > Now strSQL contrains a string that is a Jet SQL statement. As you progress
> > in your Access knowledge, you will find that you can modify the SQL and use
> > it however you want. In many cases, you will want to provide the user the
> > ability to filter what a query will return based on their choices in a form.
> > Here is an example of how this is done:
> >
> > strSQL = CurrentDb.Querdefs("SomeQueryNameHere").SQL
> >
> > strWhere = BuildWhere() ' A function that sets up the WHERE clause
> > strSQL = Replace(strSQL, strWhere, ";")
> > CurrentDb.Querydefs("ProductionQueryName").SQL = strSQL
> > CurrentDb.Execute(strSQL), dbFailOnError
> >
> > So, You are not constructing commands to be executed like VBA, but using VBA
> > to create an SQL statement.
> >
> >
> > "katsup" wrote:
> >
> > > Ah...! Controls on forms don't have data types. That basic concept clears
> > > up a lot of confusion for me. (I was assuming that since the controls
> > > inherit the format property of the underlying table fields they'd inherit the
> > > data type property too.)
> > >
> > > I'm still struggling with the quotes in your examples though. In the
> > > criteria portion, why are there quotes enclosing BOTH the field name and the
> > > equals sign comparison operator, smushing them in together i.e.
> > > =DLookup("[FieldToReturn]", "DomainName", "[FieldToCompare] = " & 63)
> > >
> > > Then again, in the solution that actually worked in my database, it seems
> > > like the 'And' logical operator is smushed in with the field name by the
> > > quotes that enclose those two, i.e.,
> > > =DLookUp("[RetreatFee]" , "tblScheduleRetreatFees", "[DurationTypeID] = " &
> > > [Combo20] & " And [RegistrantTypeID] = " & [Combo16])
> > > Even if Type Coersion is forcing a data type conversion, how can the logical
> > > operator get included in with a string element and still be recognized as a
> > > logcial operator.
> > >
> > > Since you've enclosed the first 2 elements of the DLookup function,
> > > [FieldToReturn] and DomaineName in quotes, I'm guessing quotes don't say to
> > > SQL that what's enclosed within them is a string, but then why use them at
> > > all?
> > >
> > >
> > >
> > > "Klatuu" wrote:
> > >
> > > > Don't give up! I know it can be frustrating, but this is how we learn. And,
> > > > if this is non profit work, then it is worth the effort.
> > > >
> > > > The ampersand is a concatenation sign. It is normally used to concatenate
> > > > strings. When you use any Domain Agrogate function, you are talking to the
> > > > Jet engine. Jet is not actually Access. Access uses Jet for data definition
> > > > and manipulation. The language Jet uses is Jet SQL. The syntax rules for
> > > > Jet dictate values passed to compare to field values have to be formatted to
> > > > to match the data type of the field. Controls on forms do not have data
> > > > types, so don't confuse a recordset field with a form control. You have to
> > > > match to the data type of the recordset field.
> > > >
> > > > Values for numeric fields should have no delimiters.
> > > > =DLookup("[FieldToReturn]", "DomainName", "[FieldToCompare] = " & 63)
> > > > Values for text fields should use either single or double quotes.
> > > > =DLookup("[FieldToReturn]", "DomainName", "[FieldToCompare] = '" & 63 & "'")
> > > > Vaules for date/teim fields should use #
> > > > =DLookup("[FieldToReturn]", "DomainName", "[FieldToCompare] = #7/4/2006#")
> > > >
> > > > Now, the single/double quotes issue. This is one I struggle with every time
> > > > I have to use it. The advantage of using single quotes, is it make the
> > > > syntax easier to write. The disadvantage is if a field contains a single
> > > > qoute, it will cause an error.
> > > > These two are equivilant:
> > > > =DLookup("[FieldToReturn]", "DomainName", "[FieldToCompare] = '" & 63 & "'")
> > > > =DLookup("[FieldToReturn]", "DomainName", "[FieldToCompare] = " & """63 &
> > > > """")
> > > >
> > > > I can understand your confusion over the contcatenation of a numeric value
> > > > using &.
> > > > What is happening is that Access is doing what is known as Type Coersion;
> > > > that is, it will internally change a data type when it has to. In this case,
> > > > Jet needs the correct delimiters. so it will append the number to the string,
> > > > but without any delimiters. Try this as an experiment in the immediate
> > > > window.
> > > > Enter x = "FooBah"
> > > > ?x
> > > > you will see
> > > > FooBah
> > > >
> > > > Now enter
> > > > x = x & 3
> > > > ?x
> > > > you will see
> > > > FooBah3
> > > >
> > > > x = x & 3 is the same as x = x & Cstr(3)
> > > >
> > > > The difference is, that with the Cstr function you dictate the conversioni
> > > > of the number to string. Without it, VBA knows it is putting a number in a
> > > > string and does the conversion for you.
> > > >
> > > > I hope this has helped you along your way. Keep at it, and visit this site
> > > > often. I have learned even more than I have helped. Please post back when
> > > > (not if) you have more questions.
> > > >
> > > > "katsup" wrote:
> > > >
> > > > >
> > > > >
> > > > > Wow – that was a quick answer. And your second example worked perfectly!
> > > > >
> > > > > But it mystifies me as to why. And searching through help files and my
> > > > > textbook for expression syntax rules hasn’t clarified the problem.
> > > > >
> > > > > When I use the expression builder (probably unskillfully), it doesn’t put
> > > > > quotes around anything. Your solution enclosed both the RetreatFee field,
> > > > > and the tblScheduleRetreatFees domain in quotes. What effect does that have?
> > > > >
> > > > > Then the quotes in the criteria really stump me, along with the ampersands.
> > > > > What do the quotes enclose? Is it
> > > > >
> > > > > [DurationTypeID] = and later And
> > > > > [RegistrantTypeID] =
> > > > >
> > > > > or is it
> > > > >
> > > > > & [Combo20] & and all of the criteria except for
> > > > > & [Combo16]
> > > > >
> > > > >
> > > > >
> > > > > Neither of these interpretations makes any sense to me. And how is the
> > > > > ampersand, which my textbook says is for concatenating strings, working in
> > > > > this criteria that uses only numeric fields? Does it have something to do
> > > > > with the DurationType and RegistrantType being combo boxes that thus have
> > > > > both the numeric and the text fields represented, e.g.
> > > > >
> > > > > DurationTypeID DurationType
> > > > > 1 Full Retreat
> > > > > 2 One Full Day
> > > > > 3 Two Full Days
> > > > > 4 Half Day
> > > > > 5 Evening Program with dinner
> > > > > 6 Evening Program without dinner
> > > > >
> > > > > I appreciate the suggestion to rename the controls - I didn't know I could
> > > > > do that.
> > > > >
> > > > > Thank you so much for your help, Klatuu. I've been fussing away at the
> > > > > DLookup solution for several days trying to get it to work. Your advice has
> > > > > given me hope I might actually get this application up and running. It's
> > > > > volunteer work for a non-profit org I'm part of, and I was thinking I might
> > > > > just abandon the effort, but now I'll forge ahead with renewed enthusiasm.
> > > > >
> > > > >
> > > > > "Klatuu" wrote:
> > > > >
> > > > > > The DLookup is the correct function to use, your syntax needs some cleaning up.
> > > > > >
> > > > > > =DLookUp("[RetreatFee]" , "tblScheduleRetreatFees", "[DurationTypeID] = '" &
> > > > > > [Combo20] & "' And [RegistrantTypeID] = '" & [Combo16] & "'")
> > > > > >
> > > > > > The above syntax assumes both DurationTypeID and RegistrantTypeID are text
> > > > > > fields. The next example assumes they are both numeric.
> > > > > >
> > > > > > =DLookUp("[RetreatFee]" , "tblScheduleRetreatFees", "[DurationTypeID] = " &
> > > > > > [Combo20] & " And [RegistrantTypeID] = " & [Combo16])
> > > > > >
> > > > > > How about creating your own control names so you and anyone else looking at
> > > > > > your code has an idea of what the control is for? [Combo16] means nothing to
> > > > > > anyone.
> > > > > >
> > > > > >
> > > > > > "katsup" wrote:
> > > > > >
> > > > > > > I seem to have lost my correspondent from previously so I’ll try my question
> > > > > > > again.
> > > > > > >
> > > > > > > I’m trying to find the right function, or query procedure to look up a
> > > > > > > registration fee in a table, based on pricing factors a registrar will
> > > > > > > identify at sign-in time.
> > > > > > >
> > > > > > > A table called tblScheduleRetreatFees holds 100 individual fees. It has four
> > > > > > > fields in it - FeeID, RegistrantTypeID, DurationTypeID, and RetreatFee
> > > > > > > (currency data type) - and, of course, 100 records. Perhaps I should add
> > > > > > > that RegistrantTypeID and DurationTypeID are in the form of combo boxes in
> > > > > > > this table.
> > > > > > >
> > > > > > > Joined one-to-many to tblScheduleRetreatFees are 2 pricing factor tables:
> > > > > > > RegistrantTypes, and DurationTypes. RegistrantTypes has 2 fields in it -
> > > > > > > RegistrantTypeID, and RegistrantType - and 24 records, with values like
> > > > > > > "Adult", "Teen", "Student", "Couple", "TwoAdult1kid," "TwoAdult2kids",
> > > > > > > "TwoAdult2kids1teen", etc in the RegistrantType field. DurationTypes also
> > > > > > > has 2 fields in it - DurationTypeID and DurationType - with values like
> > > > > > > "Full Retreat", "HalfDay", "EveningProgram", etc., 6 records in all.
> > > > > > >
> > > > > > > These 2 pricing factor tables are also joined one-to-many to the table which
> > > > > > > will accumulated the fee charges for everyone who attends the retreat,
> > > > > > > called InvoiceChargeRetreatFee. That gem has 4 fields in it -
> > > > > > > ICRetreatFeeID, MainRegistrantID (a dropdown list box of peoples' names),
> > > > > > > RegistrantTypeID (dropdown list), and DurationTypeID (ditto) - and no records
> > > > > > > as yet, except my test data. One main registrant pays for himself/his
> > > > > > > family, but can also opt to sponsor another individual; hence there could be
> > > > > > > more than 1 retreat fee charge attributed to the same registrant.
> > > > > > >
> > > > > > > I've based a data entry form, frmInvoiceChargeRetreatFee, on the
> > > > > > > InvoiceChargeRetreatFee table; the registrar will use it as people arrive at
> > > > > > > the desk to sign in. It has 3 combo boxes on it, where the registrar can
> > > > > > > click on the person’s name, and then click their registrant type (family
> > > > > > > type) and duration of stay. Once that’s done, I’d like the form to display
> > > > > > > the correct fee, in a text box, looked up from tblScheduleRetreatFees.
> > > > > > >
> > > > > > > I’ve tried setting the text box’s control source to
> > > > > > >
> > > > > > > =DLookUp(tblScheduleRetreatFees!RetreatFee,[tblScheduleRetreatFees],tblScheduleRetreatFees!DurationTypeID=[Combo20]
> > > > > > > And tblScheduleRetreatFees!RegistrantTypeID=[Combo16])
> > > > > > >
> > > > > > > but this didn’t work. (I realize the Combo box numbers are high; it took a
> > > > > > > while fiddling with the form and discarding the earlier, lower-numbered ones
> > > > > > > to get the form roughly the way I wanted it.)
> > > > > > >
> > > > > > > Have I got the DLookup code wrong? Or should I be trying to do this with
> > > > > > > some other function?
> > > > > > >
|
|
|