using dlookup as control source

[Guest]

Hi!

im trying to use dlookup as a control source on a form. basically, customer
chooses boat, boat looked up in table and hire charge shown in box with the
expression in it. the expression i have is:

=DLookUp("[Hire Charge]","[Hire Charges]","Forms![Form1]![Boat ID]="&"[boat
id]")

the problem is that it works the first time and then when i change the boat
id, it doesnt work.

Thanks for your help in advance!

sf

 

[Marshall Barton]

im trying to use dlookup as a control source on a form. basically, customer
chooses boat, boat looked up in table and hire charge shown in box with the
expression in it. the expression i have is:

=DLookUp("[Hire Charge]","[Hire Charges]","Forms![Form1]![Boat ID]="&"[boat
id]")

the problem is that it works the first time and then when i change the boat
id, it doesnt work.

Some subtle things in this. The way you wrote the DLookup's
condition, you're asking Access to automatically resolve the
value of the BoatId text box while it's performing the
DLookup function. In this scenario, you need to Requery the
text box in the BoatId text box's After update event:
Me.thedlookupbox.Requery

However, if you wrote the DLookup by placing the value of
the BoatId text box in the function, Access would be able to
recognize the dependency and recalculate it automatically.
=DLookUp("[Hire Charge]", "[Hire Charges]", "[boat id]=" &
BoatID)

 

[Guest]

Marsh

I tried what you said by putting:

=DLookUp("[Hire Charge]", "[Hire Charges]", "[boat id]=" & Boat ID) as the
control source. however, it did not work, it said that there was invalid
syntax. i thought i might add that both the boat id fields are text fields.
would that make a difference?

thanks

shaheed

im trying to use dlookup as a control source on a form. basically, customer
chooses boat, boat looked up in table and hire charge shown in box with the
expression in it. the expression i have is:

=DLookUp("[Hire Charge]","[Hire Charges]","Forms![Form1]![Boat ID]="&"[boat
id]")

the problem is that it works the first time and then when i change the boat
id, it doesnt work.

Some subtle things in this. The way you wrote the DLookup's
condition, you're asking Access to automatically resolve the
value of the BoatId text box while it's performing the
DLookup function. In this scenario, you need to Requery the
text box in the BoatId text box's After update event:
Me.thedlookupbox.Requery

However, if you wrote the DLookup by placing the value of
the BoatId text box in the function, Access would be able to
recognize the dependency and recalculate it automatically.
=DLookUp("[Hire Charge]", "[Hire Charges]", "[boat id]=" &
BoatID)

 

[Douglas J. Steele]

If Boat Id is a text field, you need to put quotes around the value:

=DLookUp("[Hire Charge]", "[Hire Charges]", "[boat id]='" & Boat ID & "'")

Exagerated for clarity, that's

=DLookUp("[Hire Charge]", "[Hire Charges]", "[boat id]= ' " & Boat ID & " '
")

If there's a chance that there might be an apostrophe in the ID, try

=DLookUp("[Hire Charge]", "[Hire Charges]", "[boat id]= " & Chr$(34) & Boat
ID & Chr$(34))

--
Doug Steele, Microsoft Access MVP

(no e-mails, please!)

Marsh

I tried what you said by putting:

=DLookUp("[Hire Charge]", "[Hire Charges]", "[boat id]=" & Boat ID) as the
control source. however, it did not work, it said that there was invalid
syntax. i thought i might add that both the boat id fields are text
fields.
would that make a difference?

thanks

shaheed

im trying to use dlookup as a control source on a form. basically,
customer
chooses boat, boat looked up in table and hire charge shown in box with
the
expression in it. the expression i have is:

=DLookUp("[Hire Charge]","[Hire Charges]","Forms![Form1]![Boat
ID]="&"[boat
id]")

the problem is that it works the first time and then when i change the
boat
id, it doesnt work.

Some subtle things in this. The way you wrote the DLookup's
condition, you're asking Access to automatically resolve the
value of the BoatId text box while it's performing the
DLookup function. In this scenario, you need to Requery the
text box in the BoatId text box's After update event:
Me.thedlookupbox.Requery

However, if you wrote the DLookup by placing the value of
the BoatId text box in the function, Access would be able to
recognize the dependency and recalculate it automatically.
=DLookUp("[Hire Charge]", "[Hire Charges]", "[boat id]=" &
BoatID)

 

[Guest]

Douglas,

Thanks a lot! it worked! but now i have another problem. im trying to use
the same principles in another field. the syntax is:

=DLookUp("[DISCOUNT (%)]","[HIRE DISCOUNT]","[NUMBEROFDAYS]>=" & "FROM" And
"[NUMBEROFDAYS]<=" & "TO")

the problem is that it only works based on the first criteria (ie that
number of days > the from field in the hire discount table). any ideas? btw
all the fields here are numbers.

thanks

shaheed

 

[Douglas J. Steele]

Dates have to be delimited with # characters, and must be in mm/dd/yyyy
format.

Try:

=DLookUp("[DISCOUNT (%)]","[HIRE DISCOUNT]","[NUMBEROFDAYS]>=" &
Format(FROM, "\#mm\/dd\/yyyy\#") & " And " & "[NUMBEROFDAYS]<=" & Format(TO,
"\#mm\/dd\/yyyy\#"))

or

=DLookUp("[DISCOUNT (%)]","[HIRE DISCOUNT]","[NUMBEROFDAYS] BETWEEN " &
Format(FROM, "\#mm\/dd\/yyyy\#") & " AND " & Format(TO, "\#mm\/dd\/yyyy\#"))

(Actually, I lied above. The dates don't have to be in mm/dd/yyyy format if
they're in an unambiguous format such as dd mmm yyyy or yyyy-mm-dd. The
point is, even if your regional settings have the short data format as
dd/mm/yyyy, you'll run into problems if you input a date in dd/mm/yyyy
format. Access will ALWAYS treat 12/04/2005 as 04 Dec, 2005, although it
will treat 13/04/2005 as 13 Apr, 2005 since there is no 13th month)

 

[Guest]

Douglas,

I think i did not explain how the discount is calculated. basically, i have
a table called HIre discount, which looks like this:

From To Discount (%)
0 6 5
7 10 8

where from and to are number of days.

i tried to use the syntax below which i adapted from your previous post but
it does not work:

=DLookUp("[DISCOUNT (%)]","[HIRE DISCOUNT]","[NUMBEROFDAYS] BETWEEN" &
"FROM" & "AND" & "TO")

where have i gone wrong?

thanks

shaheed

 

[Douglas J. Steele]

Looks like we both made errors. <g>

Your Where clause is strictly a string: you don't have any variable to which
you're supplying a value. Is NumberOfDays the value you know that you're
trying to use to figure out the discount?

If so, try

=DLookUp("[DISCOUNT (%)]","[HIRE DISCOUNT]", _
"[FROM] <= " & NUMBEROFDAYS & " AND " & _
"[TO] >= " & NUMBEROFDAYS)

If that's still not it, post back with more details.

 

[Guest]

Douglas,

Thanks a lot! the syntax worked ! (minus the underscores). thank you for
your help. i really appreciate it!

Shaheed

 

[Douglas J. Steele]

Aah, but the underscores are line continuation characters.

If you were to have typed exactly what I typed (i.e.: on 3 lines, with space
underscore at the end of the first two lines), it would have worked without
any change.